MySQL Performance Diagnostics & Tuning

Demais posts desta s�rie:
MySQL Performance Diagnostics & Tuning - Parte 2

Artigo no estilo: Curso

Fique por dentro

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. +-----+------+-----------+------+---------+------+-------+------------------+"

[...] continue lendo...

MySQL Performance Diagnostics & Tuning - Parte 1

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.

Otimizando as gravaç�es

Artigos relacionados