O banco de dados em questão: AWS RDS, MySQL 8, InnoDB. Armazenamento GP3.
Estou tentando fazer uma inserção em massa de linhas em várias tabelas no banco de dados.
Omiti todos os índices secundários da tabela de destino, ela possui apenas PK. A tabela de destino não está particionada.
Os dados de origem para a importação (que não estão no MySQL) são particionados por intervalo de datas. Para cada partição eu tenho um script que seleciona um lote de dados e os insere no MySQL. Os scripts de loop em lote por partição estão sendo executados simultaneamente em paralelo.
Cada lote é carregado como um dataframe do pandas por tabela, várias transformações são feitas e, em seguida, os dataframes do lote são inseridos no MySQL (em uma transação de banco de dados) usando o to_sqlmétodo de inserção "multi" do pandas.
Posso pensar em várias maneiras de melhorar isso. Duas sugestões que aparecem em todos os conselhos de inserção em massa do MySQL são: a) inserir na ordem PK eb) usar LOAD DATA IN FILE. Atualmente não estou fazendo nenhum dos dois. Mas antes de reescrever radicalmente todo o código, gostaria de entender o sintoma que vejo quando executo o código atual:
Podemos ver a importação em execução por aproximadamente 1 hora. O número de scripts em lote paralelos é totalmente consistente. O tamanho do lote é consistente por toda parte. Cada lote leva cerca de 60 segundos para ser processado e inserido. No gráfico acima, cerca de 200 lotes (vários milhões de linhas) são processados com sucesso. Mas o IOPS aumenta aproximadamente linearmente por 30 minutos até atingir o limite provisionado e ser limitado.
Minha pergunta é: se minha taxa de inserção é constante, por que o IOPS continua aumentando linearmente?
Eu tinha uma hipótese para isso antes de fazer a pergunta, mas também havia muitas outras coisas que poderiam estar acontecendo e que talvez eu não saiba.
No final, testei minha hipótese e parece que ela estava correta.
Conforme mencionado na pergunta, todos os índices secundários (e restrições de chave única e estrangeira) foram omitidos da tabela. Mas a chave primária foi mantida.
O motivo para manter o PK foi que eu li alguns conselhos de que adicionar PK após a importação seria muito caro, pois é necessário reconstruir a tabela inteira por ser um índice "clusterizado".
Eu fiz alguns testes em minha máquina local com dados menores (~ 250 mil linhas) e observei que era mais lento omitir o PK + adicioná-lo depois do que apenas inserir com o PK ainda no lugar.
No entanto, ao executar a importação em tamanho real e observar o IOPS cada vez maior, imaginei que isso poderia ser devido à forma como estávamos inserindo linhas em paralelo, impossibilitando que as inserções chegassem em ordem PK estrita. A hipótese era que cada commit de um lote de linhas tinha que reorganizar o índice PK, de modo que à medida que o tamanho do índice aumentava, a quantidade de trabalho por commit crescia linearmente.
Então executei novamente a importação com o PK removido
CREATE TABLEe vi isto:...belos gráficos planos, bem abaixo do limite de IOPS.
Então, agora tenho todos os dados lá e "só" preciso adicionar os índices necessários novamente.