Tenho um grande e complicado conjunto de dados de neurociência que estou tentando organizar em um banco de dados para acesso simultâneo e consistente com meu laboratório. Esta é minha primeira incursão além de experimentos muito menores com SQLite, e eu mordi mais do que posso mastigar.
A tabela primária que contém as gravações neurais tem cerca de 10 bilhões de linhas. Antes de preencher esta tabela com dados, estabeleci uma chave primária composta (id, experiment_id)
onde id
é exclusivo para cada linha. Fiz isso para poder particionar a tabela por experiment_id
. Isso parecia uma boa ideia na época. No entanto, uma vez que a tabela foi preenchida, a adição de índices adicionais falhou no phpMyAdmin (o pop-up de erro indicou que a nova chave estava corrompida).
Diante desse obstáculo, encontrei https://mariadb.com/kb/en/partition-maintenance/ , que tem como primeiro marcador "# 1: Não use o PARTITIONing até saber como e por que isso ajudará. ", seguido por "o tamanho da tabela raramente é um problema de desempenho". Então, na esperança de "fazer de novo", criei uma nova tabela com as mesmas colunas (menos a id
coluna), mas sem esquema de particionamento e sem índices e executei:
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
INSERT INTO tfconv_2 (experiment_id, electrode_id, trial_id, frequency_id, time_id, value_real, value_imag)
SELECT experiment_id, electrode_id, trial_id, frequency_id, time_id, value_real, value_imag
FROM tfconv;
COMMIT;
Isso durou vários dias (o que não foi uma surpresa, já que levou dias para colocar os dados na tabela original em primeiro lugar). Enquanto o MariaDB trabalhava, pude ver a contagem de linhas na página de resumo do phpMyAdmin subindo para a nova tabela. Monitorando recursos em meu servidor, parecia que o processo estava funcionando de maneira regular e sistemática para copiar os dados para a nova tabela.
Então, uma vez, quando fui verificar, a contagem de linhas na nova tabela havia diminuído. SHOW PROCESSLIST
revelou:
Id User Host db Command Time State
10237 root localhost singlecell Killed 217191 Reset for next command
Com esse pano de fundo, tenho três perguntas:
- Por que essa consulta pode ter falhado? (Os arquivos de log de erros ficaram silenciosos durante a janela de tempo relevante.)
- Se eu tentar criar uma tabela sem partições novamente, você recomenda que eu faça algo diferente?
- Existe uma maneira de configurar índices na tabela particionada original, que possa evitar a corrupção?
É possível que você tenha atingido o tempo limite de nível de instrução controlado por
max_statement_time
. O fato de aSHOW PROCESSLIST
saída mostrar o status comoKilled
pode apontar para isso como sendo o motivo.Também pode ser um tempo limite que está no MyPhpAdmin ou no conector que ele usa para se conectar ao banco de dados. Para operações de longa duração como essa, uma conexão confiável com o banco de dados é essencial.
Uma abordagem alternativa é eliminar completamente a rede. Fazer as operações localmente por SSH e usar um programa como
screen
para manter o terminal ativo deve ajudar a evitar situações em que as operações sejam canceladas devido ao cliente sair muito cedo.Outra abordagem seria definir um novo item único
EVENT
que faça a mudança. Isso eliminaria todas as falhas de comunicação, pois a operação é feita dentro do banco de dados.Se a tabela para a qual você está copiando os dados estiver vazia, você pode definir
autocommit=0
,unique_checks=0
eforeign_key_checks=0
durante a sessão para habilitar a inserção em massa no InnoDB. Apenas lembre-se deCOMMIT
sua transação depois, seautocommit
não estiver habilitada, ou envolva as inserções em uma transação explícita.Esta postagem de blog no mariadb.org descreve algumas partes do recurso e com uma versão recente do MariaDB, isso deve ser muito mais rápido do que a inserção "normal". Este commit no servidor MariaDB é o que implementa este carregamento em massa e também dá um exemplo de como ele é habilitado:
Isso não é algo fácil de estimar sem o erro exato, mas, com base em sua pesquisa, o particionamento provavelmente não resolverá seus problemas de desempenho.