- Ao mover um banco de dados SQLite grande de um servidor remoto para o ambiente local, copiar um dump SQL compactado com gzip em vez do arquivo
.dboriginal reduz bastante o volume de dados transferidos - A principal causa do aumento do arquivo são os índices, que armazenam dados de tabelas de forma duplicada para acelerar consultas e acabam ampliando o uso de disco
- O
.dumpdo SQLite transforma o banco de dados em texto com instruções SQL, e os índices são representados não pelos dados reais, mas por uma única linhaCREATE INDEX, evitando transferência redundante - No banco de dados de exemplo, o original tinha 3,4GB, o dump em texto 1,3GB e a versão compactada com gzip 240MB; o dump compactado ficou 14 vezes menor que o original
- Gerar e fixar o arquivo de dump antes da cópia ajuda a reduzir situações em que o original muda durante a transferência e provoca o erro
database disk image is malformed
Copiar um dump SQLite compactado
- Bancos de dados SQLite pequenos podem ser copiados diretamente com
rsync- Exemplo:
rsync --progress username@server:my_remote_database.db my_local_database.db
- Exemplo:
- Quando o banco cresce, a cópia direta pode ficar lenta e menos confiável
- Baixar um banco de dados de 250MB de um servidor web por uma conexão residencial leva cerca de 1 minuto
- Em bancos com vários GB, o tempo de espera aumenta ainda mais
- O principal gargalo no crescimento do tamanho está nos índices
- Índices aumentam bastante a velocidade das consultas, mas também fazem o arquivo do banco crescer
- Em um banco de dados, um único índice específico ocupava metade do espaço em disco
- Em vez de armazenar dados únicos, os índices duplicam dados de outras tabelas para acelerar a busca
- O SQLite pode fazer um dump do banco inteiro para um arquivo de texto
- Comando:
sqlite3 my_database.db .dump > my_database.db.txt - O arquivo resultante é composto por instruções SQL como
CREATE TABLE,INSERT INTOeCREATE INDEX - Os índices são salvos não como dados reais do índice, mas como comandos de criação, como
CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]);
- Comando:
- O arquivo de dump pode ser restaurado de volta para um banco SQLite
- Comando:
cat my_database.db.txt | sqlite3 my_reconstructed_database.db
- Comando:
- Dumps SQL têm muita repetição, então a compactação com gzip é muito eficiente
- Exemplo:
sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz - Comparação de tamanho:
- Banco SQLite original: 3,4GB
- Dump em texto: 1,3GB
- Texto compactado com gzip: 240MB
- A versão compactada com gzip ficou 14 vezes menor que o banco SQLite original
- Exemplo:
Procedimento real e ganhos de confiabilidade
- O novo método cria um dump compactado no servidor remoto, baixa apenas esse arquivo e depois reconstrói o banco localmente
- O processo de cópia é o seguinte
- Gerar no servidor o arquivo de texto compactado com gzip:
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz" - Copiar para o ambiente local:
rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz - Apagar o arquivo compactado temporário no servidor:
ssh username@server "rm my_remote_database.db.txt.gz" - Descompactar localmente:
gunzip my_local_database.db.txt.gz - Reconstruir o banco de dados local:
cat my_local_database.db.txt | sqlite3 my_local_database.db - Apagar o arquivo de texto local:
rm my_local_database.db.txt
- Gerar no servidor o arquivo de texto compactado com gzip:
- O dump compactado funciona como uma fonte consistente para cópia
- Se o banco original for atualizado durante uma cópia demorada de um banco grande, o começo do arquivo pode refletir o estado anterior à atualização e o final, o estado posterior
- Ao abrir esse arquivo localmente, pode surgir o erro
database disk image is malformed
- Ao criar o dump em texto antes da cópia, o arquivo transferido pelo
rsyncnão muda no meio do processo- Isso aumenta a chance de copiar um arquivo de texto completo e consistente
- Se você move bancos SQLite grandes com frequência, esse método pode reduzir o tempo de download e aumentar a confiabilidade da cópia
1 comentários
Opiniões no Hacker News
Se você simplesmente copiar um arquivo de banco de dados que está em execução e recebendo atualizações, é claro que ele pode acabar corrompido
Para replicar um banco de dados SQLite com segurança, existe o Litestream: https://github.com/benbjohnson/litestream
Há serviços preferidos, mas qualquer servidor SFTP serve
[1] https://github.com/benbjohnson/litestream/issues/140
[2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
Claro que, se forem vários arquivos, eles precisam ser agrupados em um subvolume para que todos sejam copiados dentro da mesma transação; simplesmente usar
cp --reflink=alwaysnão bastaParar o processo com
SIGSTOPtambém pode produzir um resultado parecido, mas eu não gostaria de depender disso.backupintegrado também é uma ferramenta oficial para permitir criar e copiar uma versão snapshot de um banco de dados em execuçãoSe eu me lembro direito, ele escreveu um guia perfeito, mas a chance de eu encontrá-lo é próxima de zero. Seria ótimo se alguém que conheça essa referência publicasse o link
Chamar isso de cópia de banco de dados entre computadores parece aquele desenho em que só fazem o círculo e pulam o resto da coruja
Como outras pessoas disseram,
rsyncincremental seria muito mais rápido, mas o que me incomoda mais é a parte em que dizem que enviar instruções SQL é mais rápido do que enviar o banco de dados, enquanto omitem completamente o fato de que essas instruções SQL precisam ser executadas. Depois disso ainda é preciso rodar/optimize/e/vacuum/No momento tenho uma situação em que preciso “reconstruir incrementalmente” um banco de dados a partir de arquivos CSV e, no meu caso, é mais otimizado refazê-lo do zero. Mesmo assim, apesar de muita otimização, só inserir em lote em um banco de dados vazio em memória e criar os índices leva 30 minutos
É um texto muito bom resumindo como fazer inserções rapidamente no SQLite3
Foi dito que baixar um banco de dados de 250 MB leva 1 minuto; graças à minha fibra de 1 Gbps, eu trouxe do servidor da empresa um banco de dados SQLite de teste de 2 GB em 15 segundos
Tive de usar truques como filtro de Bloom e cache LRU, mas agora consigo criar um banco de dados de 6 GB em cerca de 20 minutos
O SQLite tem uma ferramenta oficial para esse propósito: https://www.sqlite.org/rsync.html
Ela funciona em nível de página
“O protocolo funciona fazendo com que a réplica envie ao original o hash criptográfico de cada página, e então o original devolve o conteúdo completo das páginas cujos hashes não coincidem”
Para usar essa ferramenta, a única opção é compilar por conta própria
O utilitário
sqlite_rsync, lançado recentemente, usa uma versão do algoritmo rsync otimizada para a estrutura interna dos bancos de dados SQLite. Ele compara de forma eficiente as páginas internas de dados e sincroniza apenas as páginas alteradas ou ausentesAs dicas do artigo também são boas, mas agora é mais fácil usar o utilitário integrado :)
Escrevi em detalhes no blog como ele funciona: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works
sqlite3_rsyncagora está integrado à plataforma rsync.netssh user@rsync.net sqlite3_rsync … blah blah …Foi adicionado só na semana passada e ainda não foi implantado em todas as regiões, mas os primeiros usuários disseram que tudo funcionou exatamente como esperado
sqlite_rsyncsó pode ser usado em modo WAL. Como restrição adicional do modo WAL, o arquivo de banco de dados precisa estar em um disco localNa maioria dos casos isso obviamente será verdade, mas, se não for possível, não dá para usar esse utilitário
Foi surpreendente que não tenham tentado a compressão durante a transferência oferecida pelo
rsync-z, --compresscomprime os dados dos arquivos durante a transferência, e--compress-level=NUMespecifica explicitamente o nível de compressãoTalvez comprimir com gzip antes de transferir seja mais rápido, mas é bom poder melhorar a transferência com uma única flag
sqlite3_rsync(https://sqlite.org/rsync.html) junto com-zSeria como ter um rsync que entende transações do SQLite e WAL, com compressão durante a transferência
Ao fazer esse tipo de operação, costumo fazer streaming do dump direto para gzip. Normalmente também dá para encontrar uma forma de fazer streaming direto para o destino, sem arquivo intermediário
Além disso, assim ele fica armazenado comprimido no destino. Especialmente se o objetivo for backup, não uma replicação de pobre
rsyncem um arquivo comprimido, acaba contornando completamente o ponto principal de usarrsync: a transferência de diferenças baseada em checksum rolling--remove-source-filese evitar entrar de novo por SSH para executarrmSalvar como arquivo de texto é ineficiente. Eu salvo bancos SQLite com
VACUUM INTOsqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"Segundo https://sqlite.org/lang_vacuum.html, o comando
VACUUMcom a cláusulaINTOé uma alternativa à API de backup para criar uma cópia de backup do banco de dados em execução. A vantagem é que o banco de backup resultante fica com tamanho mínimo, o que pode reduzir I/O no sistema de arquivosVACUUM INTOmantém os índicesUm arquivo de texto pode ser ineficiente em estado bruto, mas comprime bem até com ferramentas primitivas como gzip. Não tenho certeza se o formato binário do SQLite comprime tão bem quanto isso, mas é possível
No DuckDB, dá para fazer a mesma coisa, mas exportando para Parquet, o que deixa o tamanho dos dados uma ordem de grandeza menor do que instruções SQL baseadas em texto. A transferência é mais rápida e o carregamento também
https://duckdb.org/docs/stable/sql/statements/export.html
duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"No meu banco de testes, ficou cerca de 20% menor que instruções SQL em texto comprimidas com gzip
O SQLite tem uma extensão de sessão que rastreia alterações em um conjunto de tabelas e cria changesets/patchsets que podem ser aplicados a uma versão anterior de um banco SQLite
https://www.sqlite.org/sessionintro.html
Em um dos meus projetos, implementei minha própria sessão de pobre registrando todas as instruções SQL e parâmetros em um banco separado, depois sincronizando e reproduzindo isso. Funciona bem o suficiente para um banco de cerca de 30 GB em que só uns 0,1% mudam por dia
Se você sincroniza periodicamente de uma versão antiga para uma nova, dá para otimizar mais com a opção
--rsyncabledo gzipA taxa de compressão cai cerca de 1%, mas as diferenças de uma versão para a seguinte ficam localizadas em vez de se propagarem por toda a saída comprimida
Outra forma é não comprimir a saída do dump e deixar o
rsynccalcular a diferença entre o dump anterior não comprimido e o dump atual, comprimindo apenas o conjunto de alterações enviado pela rede. Ou seja, usarrsync -zParece que isso poderia ser tratado como um único pipeline
ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db-oCompression=onou-C,gzip/gunziptalvez seja redundantepvnisso