Artigos Banco de Dados MySQL Performance Diagnostics & Tuning - Parte 1
Demais posts desta série:
MySQL Performance Diagnostics & Tuning - Parte 2
Artigo no estilo: Curso
Por que eu devo ler este artigo:De que se trata o artigo?

Da configuração para Performance Tuning de um banco de dados em MySQL com objetivo de otimizar o desempenho deste Banco de Dados. Este artigo é o primeiro de uma séria sobre Performance Tuning em MySQL.


Para que serve?

Para configurar o MySQL corretamente para que ele utilize de forma otimizada o máximo de recursos possíveis do ambiente computacional onde está instalado.


Em que situação o tema é útil?

Em casos onde o poder de processamento são características fundamentais do ambiente.

Como todos sabem, a Oracle comprou a Sun, que por sua vez, anteriormente havia comprado a MySQL AB (Nota DevMan 1), empresa criadora do Banco de Dados MySQL.

Nota DevMan 1. MySQL AB: MySQL AB (fundada em 1995 e adquirida pela Sun Microsystems em 2008) foi a empresa responsável pela criação do MySQL assim como de produtos tais como MySQL Cluster. A empresa está dualmente sediada em Uppsala, na Suécia, e em Cupertino, nos Estados Unidos, com escritórios em outros países (Paris, França; Munique, Alemanha; Dublin, Irlanda; Milão, Itália; e Tóquio, Japão).

Com cerca de 400 funcionários em 25 países, a MySQL AB foi uma das maiores empresas de open source do mundo. Cerca de 70% dos empregados trabalhavam para o MySQL a partir de suas casas.

Eu não tive, e continuo não tendo dúvidas de que a Oracle irá investir cada vez mais no MySQL, pois creio que, para a Oracle Corporation, ele não é um competidor direto dos Bancos de Dados Oracle: ele é um competidor direto do SQL Server e do PostgreSQL. Não quero dizer aqui que um Banco de Dados é melhor do que outro, acredito que cada um tem seu campo de uso. Portanto, espero ver mais alguns anúncios da Oracle a respeito de melhorias de escalabilidade do MySQL, especialmente em ambientes Windows Server, muito em breve.

Confirmando minhas suspeitas, há alguns meses a Oracle anunciou, na própria MySQL Conf, melhorias de escalabilidade no MySQL, adotando a árvore 5.5 como a sucessora da 5.1 - descartando a 5.4 (que foi iniciada pela Sun) e 6.0 (que foi iniciada antes da 5.4, pela MySQL AB), pelo menos por enquanto. Mesmo descartando as versões anteriores, as funcionalidades criadas nestas versões foram incorporadas à versão 5.5, hoje em estágio Beta.

Mesmo com as melhorias de escalabilidade apresentadas nos Benchmarks, o MySQL não tem um bom desempenho com a configuração padrão. Além disso, é um dos Bancos de Dados mais sensíveis a parâmetros do mercado, fazendo com que uma pequena alteração no arquivo de inicialização traga um grande benefício, ou cause um grande desastre na aplicação. Para complicar ainda mais, o MySQL não é propriamente um SGBD, mas um encapsulador de Engines de SGBDs, como o MyISAM e InnoDB, que já vem habilitados por padrão. A propósito, a Innobase, mantenedora do Engine InnoDB (o mais utilizado em Bancos de Dados MySQL) já fora comprada pela Oracle antes mesmo da compra da MySQL AB pela Sun.

O MySQL é um banco de dados perigoso para os DBAs dentro das empresas. Isto porque muitas vezes estes bancos de dados aparecem como suporte a alguma aplicação específica, criada para resolver algum problema pontual (por exemplo, gerar um relatório executivo) sem contato com a área de TI (e os DBAs), até que ele se torne um sistema importante demais e, subitamente, passe a ser de responsabilidade da equipe de infraestrutura. Quando chega a este ponto, geralmente ele já está com problemas de desempenho, e sem uma estrutura de Backup ou sem uma estratégia de Disaster Recovery (“Recuperação de Desastre”). Adicionalmente, são raros os bons Treinamentos Avançados em MySQL para DBAs, e muitos DBAs com experiência de outros bancos de dados tendem a negligenciar estes aspectos do MySQL, por achar que ele é um SGBD fácil.

Mas como já explicamos, a arquitetura de encapsulamento de Engines do MySQL o torna um dos mais complexos do mercado, principalmente para Performance Diagnostic & Tuning. E se você acha que apenas clientes ou projetos pequenos, ou programas atrás de Websites utilizam o MySQL, você está enganado. Os bancos de dados com uso mais intenso com que já trabalhei foram todos MySQL, e todos de clientes grandes.

Bem, vamos começar esta série abordando alguns destes parâmetros, apresentando exemplos práticos, com melhoras reais de desempenho. Fiz os testes abaixo no meu notebook, um Dual Core com 4GB de RAM, rodando Ubuntu 10.4, e o MySQL padrão desta distribuição, o 5.1.41.

Otimizando as gravações

Para iniciar criamos uma tabela simples no MySQL, conforme Listagem 1, com uma coluna numérica auto incrementável (id_cliente) e uma coluna de caracteres de comprimento variável (nome_cliente), e inserimos nela strings aleatórias (utilizando um script Perl), até que ela ficasse com um número considerável de linhas - mais de 18 milhões de linhas, ocupando mais de 800MB em disco. Com uma tabela deste tamanho, fica mais fácil simular problemas de desempenho.


  1. ricardo@ricardo-laptop:~$ mysql -u root -pMinhaSenha
  2. Welcome to the MySQL monitor.  Commands end with ; or \g.
  3. Your MySQL connection id is 6
  4. Server version: 5.1.41-3ubuntu12.6 (Ubuntu)
  5. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  6.
  7. mysql> USE test;
  8. Reading table information for completion of table and column names
  9. You can turn off this feature to get a quicker startup with -A
  10. Database changed
  11. 
  12. mysql> DESC CLIENTES;
  13. +--------------+--------------+------+-----+---------+----------------+
  14. | Field        | Type         | Null | Key | Default | Extra          |
  15. +--------------+--------------+------+-----+---------+----------------+
  16. | id_cliente   | int(10)      | NO   | PRI | NULL    | auto_increment |
  17. | nome_cliente | varchar(255) | NO   |     | NULL    |                |
  18. +--------------+--------------+------+-----+---------+----------------+
  19. 2 rows in set (0.05 sec)
  20. 
  21. mysql> SELECT * FROM CLIENTES LIMIT 5;
  22. +------------+----------------------+
  23. | id_cliente | nome_cliente         |
  24. +------------+----------------------+
  25. |          1 | c?h_%IGn[Y3jO*z"D50~ |
  26. |          2 | ,,,14W+})\&q/a1;2_E* |
  27. |          3 | >e5+677v<XIy6n?v9FsL |
  28. |          4 | !1:ihxFk]BCKfkfZiz)k |
  29. |          5 | z[4k@t/"&8e`!K~ps>?* |
  30. +------------+----------------------+
  31. 5 rows in set (0.04 sec)
  32. 
  33. mysql> SELECT COUNT(*) FROM CLIENTES;
  34. 
  35. +----------+
  36. 
  37. | COUNT(*) |
  38. 
  39. +----------+
  40. 
  41. | 18105728 |
  42. 
  43. +----------+
  44. 
  45. 1 row in set (18.06 sec)
Listagem 1. Comandos SELECT para mostrar a estrutura e tamanho da tabela teste

Primeiramente conectamos no MySQL (linha 1 da Listagem 1), e alteramos o banco de dados em uso para o test (linha 7). Em seguida, conferimos a estrutura da tabela CLIENTES (linhas 12 a 19), e fiz um SELECT de 5 linhas, para conferir o conteúdo com a coluna id_cliente sequencial, e a coluna nome_cliente com os nomes aleatórios (linhas 21 a 31). Depois fizemos um COUTN desta tabela, para conferir a quantidade de registros (linhas 33 a 45), o que demorou 18 segundos.

Agora, conforme a Listagem 2, veremos a velocidade de duplicação desta tabela com a configuração padrão do MySQL. Para isso, executamos o teste 3 vezes, para validar melhor o tempo, e excluir variáveis como o armazenamento de dados em cache.


  1. mysql> CREATE TABLE CLIENTES_02 Engine=InnoDB SELECT * FROM CLIENTES;
  2. Query OK, 18105728 rows affected (7 min 15.27 sec)
  3. Records: 18105728  Duplicates: 0  Warnings: 0
  4. 
  5. mysql> CREATE TABLE CLIENTES_03 Engine=InnoDB SELECT * FROM CLIENTES;
  6. Query OK, 18105728 rows affected (6 min 45.10 sec)
  7. Records: 18105728  Duplicates: 0  Warnings: 0
  8. 
  9. mysql> CREATE TABLE CLIENTES_04 Engine=InnoDB SELECT * FROM CLIENTES;
  10. Query OK, 18105728 rows affected (7 min 6.74 sec)
  11. Records: 18105728  Duplicates: 0  Warnings: 0
Listagem 2. Comandos SELECT para duplicar a tabela 3 vezes, medindo o tempo

Veja que primeiramente criamos a tabela CLIENTES_02, como uma cópia exata da CLIENTES (linhas 1 a 3 da Listagem 2). Em seguida repetimos o procedimento mais duas vezes, agora criando as tabelas CLIENTES_03 (linha 5) e CLIENTES_04 (linha 9). É importante reparar nos tempos de cada uma das execuções (linhas 2, 6 e 10, respectivamente).

Nos três testes, obtivemos os seguintes resultados: 7' 15.27”, 6' 45.10” e 7' 6,74” respectivamente.

Se precisamos otimizar este tempo de execução, precisamos saber como ele é gasto, antes de alterarmos algo. O MySQL fornece este tipo de informação através dos Thread States (“Estado de Processos”) - similares aos Wait Events do Oracle, embora sejam muito menos detalhados, infelizmente. Para se ter uma ideia, o Oracle 11gR2 tem mais de 1100 Wait Events documentados, enquanto o MySQL 5.1 tem apenas 67. Mas mesmo assim eles nos darão uma boa pista do que está acontecendo.

Para exibir os Thread States atuais, o comando SHOW PROCESSLIST pode ser utilizado no prompt do MySQL, ou o comando PROCESSLIST pode ser utilizado no utilitário mysqladmin, conforme Listagem 3, onde o executamos diversas vezes seguidas durante a duplicação da tabela CLIENTES. Veja que a coluna "State" mostrou o estado "Sending data" o tempo todo da execução. Repare também a coluna “Time”, que indica o número de segundos que o comando executado está aguardando pelo Thread State indicado.

Como facilidade, para não ter que executar o comando repetidamente, utilizamos o utilitário do Linux watch, que faz com que um comando seja repetido na tela até que seja digitado Control+C. Veja como utilizar o comando watch na Listagem 4.


  1. ricardo@ricardo-laptop:~$ mysqladmin -u root -pMinhaSenha processlist
   
  2. +----+------+-----------+------+---------+------+-------+------------------+
   
  3. | Id | User | Host      | db   | Command | Time | State | Info             |
   
  4. +----+------+-----------+------+---------+------+-------+------------------+
   
  5. | 35 | root | localhost | test | Sleep   | 729  |       |                  |
   
  6. | 36 | root | localhost |      | Query   | 0    |       | show processlist |
   
  7. +----+------+-----------+------+---------+------+-------+------------------+
   
  8. 
   
  9. ricardo@ricardo-laptop:~$ mysqladmin -u root -pMinhaSenha PROCESSLIST
   
  10. +----+------+-----------+------+---------+------+--------------+-------------
        --------------------------------------------------+
   
  11. | Id | User | Host      | db   | Command | Time | State        | Info 
                                                         |
  12. +----+------+-----------+------+---------+------+--------------+------------
        ---------------------------------------------------+
   
  13. | 35 | root | localhost | test | Query   | 22   | Sending data | 
       CREATE TABLE CLIENTES_03 Engine=InnoDB SELECT * FROM CLIENTES |
   
  14. | 64 | root | localhost |      | Query   | 0    |              | show processlist  
                                            |
   
  15. +----+------+-----------+------+---------+------+--------------+-----------
        ----------------------------------------------------+
   
  16. 
   
  17. ricardo@ricardo-laptop:~$ mysqladmin -u root -pMinhaSenha PROCESSLIST
   
  18. +-----+------+-----------+------+---------+------+--------------+--------------
        -------------------------------------------------+
   
  19. | Id  | User | Host      | db   | Command | Time | State        | Info   
                                |
  20. +-----+------+-----------+------+---------+------+--------------+--------
        -------------------------------------------------------+
   
  21. | 35  | root | localhost | test | Query   | 218  | Sending data | 
       CREATE TABLE CLIENTES_03 Engine=InnoDB SELECT * FROM CLIENTES |
   
  22. | 158 | root | localhost |      | Query   | 0    |              | show processlist   
   
  23. +-----+------+-----------+------+---------+------+--------------+------------
        ---------------------------------------------------+
   
  24. 
   
  25. ricardo@ricardo-laptop:~$ mysqladmin -u root -pMinhaSenha PROCESSLIST
   
  26. +-----+------+-----------+------+---------+------+-------+------------------+
   
  27. | Id  | User | Host      | db   | Command | Time | State | Info             |
   
  28. +-----+------+-----------+------+---------+------+-------+------------------+
   
  29. | 35  | root | localhost | test | Sleep   | 411  |       |                  |
   
  30. | 254 | root | localhost |      | Query   | 0    |       | show processlist |
   
  31. +-----+------+-----------+------+---------+------+-------+------------------+ ...

Quer ler esse conteúdo completo? Tenha acesso completo