GUOB Tech Day 2017

25 maio 2017

MySQL Performance Tuning: sempre configure o InnoDB Buffer Pool

Se você for um usuário iniciante de MySQL, 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 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.

Nenhum comentário: