Eu tenho um aplicativo em execução no Amazon MySQL/RDS que precisa manter 2 semanas de histórico transacional após o qual os dados podem (geralmente) ser limpos. Como essa não é uma regra rígida, não posso usar o particionamento por dia ou semana como meio de excluir dados antigos com mais eficiência.
Existem várias tabelas que precisam de limpeza, muitas delas têm vários índices e restrições de chave estrangeira (pai/filho).
Estou excluindo dados em partes (1000-3000 linhas por vez) e confirmando após cada parte. Após a exclusão de um determinado número de blocos, o código de limpeza é pausado por um determinado período de tempo.
Meu problema é que o processo é muito eficiente por 10 a 15 minutos, após o qual começo a ver grandes atrasos na exclusão de blocos de dados. Acredito que meu SQL seja o mais eficiente possível. Onde no MySQL posso procurar para entender melhor o gargalo? Se eu parar meu código e esperar de 15 a 20 minutos, o desempenho de exclusão será restaurado por mais 10 minutos.
Eu olharia para o tamanho do arquivo de log de redo do InnoDB.
Os sintomas que você descreve são típicos se você preencher o redo log com alterações, o que força uma "limpeza síncrona" — o MySQL bloqueia outras alterações até que possa liberar uma parte do redo log liberando páginas sujas do buffer pool.
O RDS costumava usar um tamanho de arquivo de log de redo absurdamente pequeno por padrão, 128M se bem me lembro. Durante anos eles não permitiram mudar o tamanho. Mas nos últimos dois anos eles permitem mudá-lo.
Veja como verificar o tamanho do seu arquivo de redo log em megabytes:
Para alterá-lo, acho que você usaria a interface do usuário dos grupos de parâmetros do RDS e, em seguida, reiniciaria sua instância do RDS para aplicar a alteração.
Para monitorar isso, eu observaria o número de bytes gravados no redo log:
Meça isso a cada 10 minutos ou mais e faça um gráfico. Os arquivos de log de redo são de tamanho fixo, e as gravações eventualmente chegarão ao final e voltarão ao início do arquivo. Eles não devem substituir as alterações no log que representam páginas sujas no pool de buffers, portanto, antes de chegar perto de fazer isso, o MySQL força uma liberação síncrona.
Assim, você pode observar a taxa de Innodb_os_log_written, lendo essa variável periodicamente em intervalos regulares. Compare essa taxa de gravações de log com o tamanho do arquivo de log (lembre-se de que você tem dois arquivos de redo log por padrão, portanto, sua capacidade de redo log é Innodb_log_file_size * 2).
Isso permite que você estime "nós sobrescrevemos todo o(s) arquivo(s) de redo log a cada N minutos". Isso deve estar correlacionado (aproximadamente) ao seu período de 10 a 15 minutos quando as exclusões são rápidas.
Acho que lembro que existem algumas nuances nesse cálculo... o Innodb_os_log_written pode incluir algumas sobregravações, ou seja, algumas gravações procuram reescrever um bloco em algumas circunstâncias. Portanto, pode haver alguns casos em que os números não batem. Eu não sei detalhes profundos aqui.
De qualquer forma, o InnoDB é conhecido há muito tempo por ser mais capaz de lidar com a carga de trabalho de gravação pesada se você aumentar o tamanho dos logs de redo. É tentador aumentá-lo para o máximo permitido, mas isso pode ser um exagero para a maior parte de sua carga de trabalho diária com tráfego de gravação mais modesto.
Veja também:
Depende de como você faz o
DELETEs. Se cada umDELETEvarre desde o início da tabela, passando por cima de linhas que não devem ser excluídas, fica cada vez mais lento.Discuto várias técnicas aqui para fazer exclusões eficientes.
Alguns envolvem lembrar de onde você parou, em vez de começar de novo.