Pergunta originalfundo

É bem conhecido que o SQLiteprecisa estar bem sintonizado para conseguir velocidades de inserção na ordem de 50k inserts / s. Há muitas perguntas aqui em relação a velocidades de inserção lentas e uma variedade de conselhos e referências.

Há tambémafirma que o SQLite pode manipular grandes quantidades de dados, com relatórios de mais de 50 GB, sem causar problemas com as configurações corretas.

Eu segui o conselho aqui e em outros lugares para alcançar essas velocidades e estou feliz com 35k-45k inserts / s. O problema que tenho é que todos os benchmarks demonstram apenas velocidades de inserção rápidas com registros <1m. O que estou vendo é quevelocidade de inserção parece ser inversamente proporcional ao tamanho da mesa.

Questão

Meu caso de uso requer o armazenamento de 500m a 1b tuplas ([x_id, y_id, z_id]) em alguns anos (1m linhas / dia) em uma tabela de links. Os valores são todos os IDs inteiros entre 1 e 2.000.000. Existe um único índice emz_id.

O desempenho é ótimo para as primeiras linhas de 10m, ~ 35k inserts / s, mas no momento em que a tabela tem ~ 20m linhas, o desempenho começa a sofrer. Agora estou vendo cerca de 100 inserções / s.

O tamanho da tabela não é particularmente grande. Com 20m linhas, o tamanho no disco é de cerca de 500MB.

O projeto está escrito em Perl.

Questão

Esta é a realidade de grandes tabelas no SQLite ou há algum segredo paramantendo altas taxas de inserção para tabelas com linhas> 10m?

Soluções conhecidas que gostaria de evitar, se possívelElimine o índice, adicione os registros e re-indexe: Isso é bom como uma solução alternativa, mas não funciona quando o banco de dados ainda precisa ser utilizável durante as atualizações. Não vai funcionar para tornar o banco de dados completamente inacessível parax minutos / diaQuebre a mesa em subtabelas / arquivos menores: Isso funcionará no curto prazo e eu já experimentei isso. O problema é que eu preciso recuperar dados de todo o histórico ao consultar, o que significa que, eventualmente, atingirei o limite de 62 anexos de tabela. Anexar, coletar resultados em uma tabela temporária e desanexar centenas de vezes por solicitação parece ser muito trabalho e sobrecarga, mas tentarei se não houver outras alternativas.Conjunto SQLITE_FCNTL_CHUNK_SIZE: Eu não sei C (?!), Então eu prefiro não aprender apenas para fazer isso. Eu não consigo ver qualquer maneira de definir esse parâmetro usando Perl embora.ATUALIZAR

SegueSugestão de Tim que um índice estava causando tempos de inserção cada vez mais lentos, apesar das afirmações do SQLite de que ele é capaz de lidar com grandes conjuntos de dados, fiz uma comparação de benchmark com as seguintes configurações:

linhas inseridas:14 milhõesconfirmar tamanho do lote:50.000 registroscache_size pragma:10.000page_size pragma:4.096temp_store pragma:memóriajournal_mode pragma:excluirsynchronous pragma:fora

No meu projeto, como nos resultados de benchmark abaixo, uma tabela temporária baseada em arquivo é criada e o suporte embutido do SQLite para importar dados CSV é usado. A tabela temporária é então anexada ao banco de dados de recebimento e conjuntos de 50.000 linhas são inseridos com uminsert-select declaração. Portanto, os tempos de inserção não refletemarquivo para banco de dados inserir tempos, mas simmesa para mesa inserir velocidade. Levando em conta o tempo de importação de CSV, reduziríamos as velocidades em 25-50% (uma estimativa muito grosseira, não demorou muito para importar os dados CSV).

Claramente ter um índice causa a lentidão na velocidade de inserção conforme o tamanho da tabela aumenta.

É bem claro pelos dados acima que a resposta correta pode ser atribuída aA resposta de Tim em vez das afirmações de que o SQLite simplesmente não consegue lidar com isso. Claramenteposso lidar com grandes conjuntos de dadosE se indexar esse conjunto de dados não faz parte do seu caso de uso. Eu tenho usado SQLite para isso, como um backend para um sistema de log, por um tempo agora, o que faznão precisa ser indexado, então fiquei bastante surpreso com a lentidão que experimentei.

Conclusão

Se alguém se encontrar querendo armazenar uma grande quantidade de dados usando o SQLitee tê-lo indexado,usando shards pode ser a resposta. Eu finalmente resolvi usar os três primeiros caracteres de um hash MD5 uma coluna única emz para determinar a atribuição a uma das 4.096 bases de dados. Como o meu caso de uso é essencialmente de natureza arquivística, o esquema não será alterado e as consultas nunca exigirão o shard walking. Existe um limite para o tamanho do banco de dados, uma vez que os dados extremamente antigos serão reduzidos e, eventualmente, descartados, portanto, essa combinação de particionamento, configurações de pragma e até mesmo algumasdeA normalização me dá um bom equilíbrio que irá, com base no benchmarking acima, manter uma velocidade de inserção de pelo menos 10k inserts / segundo.