MySQL Performance Tuning: sempre configure o InnoDB Buffer Pool

Se você for um usuário iniciante de MySQL↳MySQL8 conteúdosMySQL + Adminer + Docker Compose: montando rapidamente um ambiente para usoData · abr 2019Banco de dados MYSQL no PHPStormDev (Back & Front) · jul 2019Criando um ambiente de desenvolvimento PHP mínimo com Docker – Parte 3Dev (Back & Front) · out 2019Ver tudo em Data →, provavelmente vai esbarrar com a necessidade de fazer algum tuning no servidor para melhorar a performance e vai ficar na dúvida de onde iniciar. Alguns ajustes básicos podem ser antecipados, já no momento da instalação do servidor, sendo o principal innodb_buffer_pool_size.

O InnoDB Buffer Pool é a principal área da memória usada pelo InnoDB, que é o storage engine default do MySQL. O ideal é que haja neste buffer espaço suficiente para os dados “mais quentes”. A quantidade de memória que o MySQL vai reservar para o Buffer Pool é controlada pela variável de sistema innodb_buffer_pool_size.

Após a instalação do MySQL, o valor padrão da variável de sistema innodb_buffer_pool_size é de 128 MB (versão 5.7), o que é bem baixo para os hardwares atuais. Você pode verificar qual atual configurado no seu MySQL com o comando:

mysql> SHOW GLOBAL VARIABLES LIKE 

'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)

Uma boa prática é, logo após a instalação, editar seu arquivo my.cnf (ou my.ini no Windows) e atribuir um valor adequado para a variável de configuração innodb_buffer_pool_size. Muitas pessoas vão recomendar como valor “70% da memória RAM disponível no seu servidor”. Isto é um bom começo caso o servidor seja dedicado. A melhor prática é monitorar o uso do Buffer Pool e ajustar seu tamanho conforme a carga de trabalho específica que você submete ao MySQL. Uma boa ferramenta para fazer isso é o MySQL Enterprise Monitor.

Ajudei recentemente um cliente que tinha um servidor compartilhado entre banco de dados e aplicação. O cliente chegou a configurar innodb_buffer_pool_size, porém o manteve baixo demais. O resultado é que a performance não estava agradando. Foi fácil identificar olhando para o gráfico “InnoDB Buffer Pool” do MySQL Enterprise Monitor:

Veja que a quantidade de memória usada pelo buffer (linha vermelha) era maior que a quantidade de memória alocada (linha verde). Como analogia, pense numa máquina fazendo Swap. Depois que fiz o ajuste para um valor maior que ‘used’, as linhas se inverteram e o banco de dados começou muito mais queries por segundo:

Como o cliente usava o MySQL 5.7, fiz a alteração online (sem reiniciar o servidor) com os comandos:

mysql> SELECT ROUND(1.8*1024*1024*1024);
+---------------------------+
| ROUND(1.8*1024*1024*1024) |
+---------------------------+
| 1932735283 |
+---------------------------+
1 row in set (0.00 sec)

mysql> SET GLOBAL innodb_buffer_pool_size=1932735283;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> SHOW GLOBAL VARIABLES LIKE 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 2013265920 |
+-------------------------+------------+
1 row in set (0.00 sec)

Depois de verificado que a configuração teve o efeito desejado, editei o arquivo my.cnf com o valor ideal.

Interessante notar que houve um pico maior de Queries Por Segundo logo após o ajuste (mais de 300 QPS). O valor ‘used’ do Buffer Pool também chegou em certo momento a superar novamente o valor ‘free’ (em torno de 1.2GB). Contudo, depois de um tempo o sistema estabilizou. A explicação é que havia um contenção no sistema e depois que eliminamos o gargalo o sistema levou um tempo para estabilizar, porém, uma vez estabilizado (em torno de 200 QPS), manteve uma boa quantidade de Buffer Pool livre.

Portanto, algumas lições:

1. Sempre que instalar o MySQL, ajuste o valor de innodb_buffer_pool_size;
2. Monitore o comportamento do Buffer Pool e faça o tuning de acordo com sua carga de trabalho;
3. Logo depois do tuning, espere o sistema estabilizar;
4. Mantenha uma folga no buffer pool, permitindo que o sistema recupere-se e estabilize-se de eventuais momentos de pico de demanda;
5. Não se esqueça de salvar a configuração no my.cnf ou my.ini.