MySQL Performance Diagnostics & Tuning - Parte 2

MySQL Performance Diagnostics & Tuning - Parte IV

Atenção: esse artigo tem um vídeo complementar. Clique e assista!

Recapitulando esta série sobre Tuning do MySQL, no primeiro artigo vimos como diminuir o tempo das gravações no banco de dados, e iniciamos a melhoria de velocidade de leituras, aumentando o tamanho do cache do engine do InnoDB. Na segunda parte, abordamos principalmente as variáveis que devem ser alteradas para um melhor desempenho em leituras, além de explicar algumas complicações sobre estas alterações no MySQL. Na terceira parte, aprendemos a utilizar uma ferramenta gráfica para auxiliar na análise de desempenho, e customizá-la para analisar o sistema de tabelas temporárias implícitas.

Neste quarto artigo da série, iremos passar da análise do servidor e seus parâmetros para abordar a monitoração e correção de comandos SQL danosos ao sistema. Este tipo de análise não pode ser ignorada em nenhum ambiente, uma vez que, mesmo sabendo que os valores padrão das variáveis de sistema do MySQL não são adequados, a maior causa de lentidão em um banco de dados são comandos SQL mal escritos, ou que rodam sobre um design ineficiente. Eu costumo até brincar que o servidor MySQL com melhor desempenho é aquele que não executa nenhum comando SQL! Mas como a promessa original da linguagem SQL é que o desenvolvedor não precisaria mais saber detalhes do design dos dados – no nível que são obrigados a entender como COBOL, por exemplo – creio que temos que auxiliar os desenvolvedores a alcançar uma utilização otimizada dos recursos do banco de dados, e não apenas exigir deles um código performático. Saliento que este artigo não irá abordar melhorias da linguagem SQL em si – isto daria outra série ou mesmo um livro – e sim as ferramentas que o MySQL nos fornece para responder à seguinte pergunta: que SQL está causando lentidão no banco de dados?

Primeiramente, o MySQL nos fornece um excelente recurso – simples e eficiente - para analisar os SQLs mais lentos, o Slow Query Log (“Registro de Consultas Lentas”). Para ativá-lo primeiramente, temos que habilitar um parâmetro, exibido na Listagem 1.

Repare que neste artigo, já estou utilizando a versão de produção da árvore 5.5, a subversão 5.5.8, já sob os direitos da Oracle.

  1. [root@MySQL01 ~]# mysql -u root -p MinhaSenha
  2. Welcome to the MySQL monitor.  Commands end with ; or \g.
  3. Your MySQL connection id is 2
  4. Server version: 5.5.8 MySQL Community Server (GPL)
  5. 
  6. Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved.
  7. 
  8. Oracle is a registered trademark of Oracle Corporation and/or its
  9. affiliates. Other names may be trademarks of their respective
  10. owners.
  11. 
  12. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  13. 
  14. mysql> SHOW VARIABLES LIKE '%SLOW%';
  15. +---------------------+---------------------------------+
  16. | Variable_name       | Value                           |
  17. +---------------------+---------------------------------+
  18. | log_slow_queries    | OFF                             |
  19. | slow_launch_time    | 1                               |
  20. | slow_query_log      | OFF                             |
  21. | slow_query_log_file | /var/lib/mysql/MySQL01-slow.log |
  22. +---------------------+---------------------------------+
  23. 4 rows in set (0.00 sec)
  24. 
  25. mysql>

Primeiramente, conectamos no MySQL com o usuário administrador, que torna possível a alteração de variáveis em tempo de execução (Linha 1 da Listagem 1). Em seguida, verificamos qual o valor atual da variável “slow_query_log”, procurando por todas as variáveis que tem a string SLOW no nome (linha 14), e verificamos que a “slow_query_log” está com o valor OFF (linha 20), o padrão.

Lembre-se que esta variável, como qualquer outra, também deve ser alterada no arquivo de inicialização do MySQL (no Linux, por padrão fica em “/etc/my.cnf”) para que a mudança persista após um possível reinício do MySQL. Segundo a documentação do MySQL 5.5, a variável “log_slow_queries” será descontinuada em uma versão futura e não deve ser utilizada (curiosamente, a documentação menciona que será na versão 7). Para a ativação do Slow Query Log, a variável “slow_query_log” deve ser alterada para ON (ou 1) e, por padrão, ela vem desativada, como é demonstrado na Listagem 2.

  1. mysql> SET GLOBAL slow_query_log=1;
  2. Query OK, 0 rows affected (0.03 sec)
  3. 
  4. mysql> SHOW VARIABLES LIKE '%SLOW%';
  5. +---------------------+---------------------------------+
  6. | Variable_name       | Value                           |
  7. +---------------------+---------------------------------+
  8. | log_slow_queries    | ON                              |
  9. | slow_launch_time    | 1                               |
  10. | slow_query_log      | ON                              |
  11. | slow_query_log_file | /var/lib/mysql/MySQL01-slow.log |
  12. +---------------------+---------------------------------+
  13. 4 rows in set (0.00 sec)
  14. 
  15. mysql>

Alterando a variável para 1 (linha 1 da Listagem 2), instruímos o MySQL para que ative o Slow Query Log. Em seguida validamos a alteração, verificando novamente os valores das variáveis (linha 4) e constatando que a “slow_query_log” está agora em ON (linha 10).

Repare que após a alteração executada, a variável descontinuada “log_slow_queries” também foi alterada internamente pelo MySQL. Outra variável relevante, mas que não foi alterada é o “slow_query_log_file”, que aponta a localização do Slow Query Log. Como este valor esta satisfatório para nossos testes, deixaremos ele como está.

Agora vamos causar uma carga no banco de dados para verificar como funciona o Slow Query Log. Iremos utilizar o programa mysqlslap, que vem junto com o MySQL, e permite a realização de diversos tipos de testes de carga. Um recurso muito interessante deste programa é que ele permite a simulação de clientes simultâneos (através da opção “concurrency”), para simular uma real lentidão em um ambiente multiusuário. Em nossos testes, iremos utilizar a opção de SQLs autogerados (“auto-generate-sql”) em tabelas com 10 colunas CHAR (“number-char-cols=10”) e 5 colunas INT (“--number-int-cols=5”). Por padrão, o mysqlslap irá gerar uma carga mista de comandos INSERTs e SELECTs, mas ele também pode fazer outros tipos de cargas: veja ao final deste artigo, na seção Referências, o link para a documentação do mysqlslap. Veja a execução do teste de carga na ...