Artemagnetica Tecnologia e Segurança

Cpu(s):  91.0% us,  1.0% sy,  0.0% ni, 7.5% id,  1.0% wa,  0.0% hi,  0.5% si

Essa situação é sem dúvida a mais comum, por abranger uma variedade enorme de possíveis causas. Alguns exemplos são queries redundantes, falta (ou excesso) de índices, estrutura das tabelas, ou até mesmo a concorrência com outros processos. São incontáveis as variáveis que influenciam no processamento do MySQL, portanto, descobrir a real causa do problema costuma não ser uma tarefa simples, bem como resolvê-lo.

Não bastasse isso, o top poderá produzir conclusões precipitadas se o administrador não avaliar bem seus detalhes. É importante ter atenção sobre os processos que realmente estão ocupando CPU durante um evento de sobrecarga, para não culparmos o MySQL por problemas causados por outras aplicações. Também observar se a porcentagem maior de utilização de CPU dá-se na coluna wa (wait time), o que possivelmente indica um gargalo em algum subsistema de I/O.

Novamente, a informação de um problema de processamento, apesar de genérica, é importante para estreitar o caminho das possíveis soluções. A partir dela, o administrador deverá priorizar as otimizações que levam a diminuição do processamento das queries executadas no banco de dados.

Exemplo de resultado do comando top referente a utilização de CPU.

O centro das atenções quando o assunto é memória tem que ser o Swap. Caso o sistema esteja alocando e liberando muita memória Swap, que é um recurso muito mais lento se comparado a memória RAM, é hora de pensar em otimizar algumas queries, modificar os limites dos caches do MySQL, ou adicionar mais memória RAM.

Mem:   2057224k total,  1982012k used,    75212k free,    13544k buffers
Swap:  2064376k total,   100752k used,  1963624k free,   701748k cached

Exemplo de resultado do comando top referente a utilização de memória.

Alterar o motor de armazenamento (storage engine), configurações do sistema de arquivos, ou melhorar o sistema de I/O, como usar RAID 10 ou discos rígidos mais velozes, são possíveis soluções para esse tipo de gargalo.

vmstat e iostat

A ferramenta vmstat fornece um relatório com informações relevantes sobre os processos, memória, paginação (swap), I/O e CPU. Sua interatividade é limitada, no entanto, é uma excelente alternativa para uso em scripts que automatizam a coleta de informações durante uma sobrecarga.

Por exemplo, ele poderá ser usado em um shell script para obter os dados do servidor durante 5 segundos e, logo em seguida, enviá-los por e-mail para o administrador tomar ciência. Depois de criado o script, bastaria configurar o sistema para que o execute quando a carga (load average) atingir um determinado valor.

Visto nesse ângulo, o vmstat pode ser considerado uma ferramenta estratégica para os administradores de sistemas, auxiliando a coleta de informações em um servidor onde ocorrem sobrecargas em intervalos aleatórios e de curta duração.

# vmstat -n 1 5
procs -----------memory---------- ---swap-- -----io---- --system-- ----cpu----
 r  b   swpd   free   buff  cache   si   so    bi    bo   in    cs us sy id wa
 0  0 100752  62980  24048 701212    0    0   244   162    1     6 19  6 71  4
 0  0 100752  62972  24068 701192    0    0    20   320 1083   129  0  0 98  1
 1  1 100752  62124  24076 701184    0    0    20   196 1518   995 10  3 86  0
 1  0 100752  62852  24076 701252    0    0    28     0 1285   395 54 10 36  1

No caso do iostat, seu funcionamento e resultado são similares aos do vmstat, porém, ele é especializado nas informações sobre as operações de I/O, com a ligeira vantagem de mostrar as estatísticas separadas por dispositivos e partições.

mytop

Como o próprio nome sugere, o mytop é um clone do comando top para o MySQL. Sua interface exibe, ao invés de processos, as threads (queries em execução, por exemplo) e informações gerais sobre o servidor MySQL. Essas informações incluem o total de queries, média de queries por segundo, bytes enviados e recebidos, e o número de queries lentas, que são aquelas cujo tempo de processamento ultrapassa, por padrão, 10 segundos.

Apesar do punhado de informações, o ponto forte do mytop é o seu método de visualização e a sua interatividade. Isso faz dele uma ferramenta importante para uma primeira e superficial análise do servidor MySQL. Queries problemáticas geralmente têm um tempo de execução que chama a atenção durante o monitoramento, deixando-as no início da tabela de threads, com um comportamento semelhante ao do top em relação aos processos. Essas queries se tornam então as primeiras candidatas a análise e otimização.

MySQL on mysql.ha-mc.org (4.1.20-log)               up 30+22:45:55 [23:56:01]
 Queries Total: 362,681        Avg/Sec: 15.63   Slow: 65
 Threads Total: 4        Active: 3     Cached: 0
 Key Efficiency: 99.84%  Bytes in: 54,308,835      Bytes out: 1,904,516,983

Id     User    Host         Dbase   Idle  Command Query Info
--     ----    ----         -----   ----  ------- ----------
238    user3   189.116.23.4 mydb    12    Query   SELECT * from table1
240    user3   189.116.23.4 mydb    2     Query   SELECT * from table2
280    user4   127.0.0.1    test    0     Query   show processlist
202    user4   189.116.23.1 mydb    11    Sleep

Pressionar “?” durante a execução do mytop irá enumerar e resumir as teclas de controle que poderão ser usadas para definir as opções de monitoramento e gerenciar as threads em execução. Entre as mais usadas estão a tecla “i“, que esconde as threads com o status “Sleep”, ou seja, sem processamento, e a tecla “s“, seguido de um valor em segundos, que altera o intervalo de atualização da tela do mytop.

É importante ressaltar que todas as informações do mytop são extraídas através de comandos do próprio MySQL. Por exemplo, o comando show full processlist pode ser usado para obter a lista de todas as threads ativas e inativas. Já o comando show status mostra variáveis como o número total de queries, queries lentas, tabelas abertas, e diversos outros valores úteis para a análise de desempenho. O mytop nada mais faz do que coletar os dados do MySQL através desses comandos e depois organizá-los para facilitar a visualização.

Logs

Em situações mais complexas, muitos administradores encontram dificuldades para detectar um problema de desempenho baseado somente nas informações das ferramentas citadas. Em vista disso, os logs do MySQL preenche a exigência de uma fonte de informação mais completa, para garimpar os dados em meio a um histórico e, assim, formular conclusões precisas a respeito das queries que estão sendo executadas no servidor.

As opções para gerar os logs do MySQL precisam ser inseridas em seu arquivo de configuração ou direto no comando de inicialização. De modo geral, apenas dois são importantes para análise:

Exemplo de visualização do mytop. 

Exemplo de resultado do comando vmstat, com intervalo de 1 segundo e 5 interações.

Em servidores sobrecarregados, onde muitas queries são executadas por segundo, é importante não manter essa opção ligada por um longo período de tempo, por conta do grande volume de log gerado e o conseqüente excesso de utilização de I/O. Logo, o ideal é mantê-la apenas durante a investigação do problema.

A análise desses logs poderá esclarecer algumas questões específicas, entre elas:

• As queries com os maiores tempos de execução.
• As queries mais executadas, separadas por banco de dados.
• As queries com maiores quantidades de resultados (linhas retornadas).
• As tabelas com maior número de consultas.

Embora o processo de análise desse log não seja um procedimento simples, a experiência e o estudo minucioso irá garantir, em conjunto com as demais ferramentas, o conhecimento exato do que realmente está ocorrendo em um banco de dados MySQL.

Essa opção produz um arquivo de log com as queries que ultrapassam, por padrão, 10 segundos de execução. Esse valor pode ser ajustado através da variável long_query_time.

Embora contenha uma informação valiosa, é preciso verificar com cuidado se as queries listadas nesse log estão realmente com um alto tempo de execução. Existe a possibilidade de uma determinada query simplesmente ter demorado por conta de uma sobrecarga gerada por outra query. Recomenda-se então executar as queries quando o banco de dados não estiver sobrecarregado, e acompanhar o impacto que sua execução terá na utilização dos recursos do servidor.

# Time: 080410 20:44:25
# User@Host: hamc[database1] @  [189.113.21.41]
# Query_time: 11  Lock_time: 0  Rows_sent: 15  Rows_examined: 278418
SELECT * from table1 where name='heitor';

Outras ferramentas

Obviamente uma investigação de desempenho do sistema operacional e do MySQL não se atem apenas as ferramentas apresentadas. Existe uma boa variedade de ferramentas, inclusive comerciais, que facilitam a tarefa de monitoramento. Abaixo estão algumas que merecem menção:

• ntop: Seguindo o raciocínio do top e do mytop, o ntop é uma ferramenta com as mesmas características, só que dedicada ao tráfego de rede do servidor.Como em algumas circunstâncias a rede poderá ser o gargalo de um banco de dados MySQL, o monitoramento desse tráfego não pode ser tratado com negligência, e o ntop é uma excelente opção para realizar essa tarefa.
• mysqlreport: Transforma os valores retornados pelo comando SHOW STATUS do MySQL em um relatório de fácil leitura, contendo, por exemplo, a quantidade de queries separadas por tipo (SELECT, INSERT, UPDATE), uso de cache para as consultas, número de conexões ao servidor, etc.

Considerações finais

Monitoramento é sempre um processo interativo e contínuo. É essencial analisar quais são os padrões de normalidade de um servidor MySQL e quais são os sinais que indicam qualquer problema de performance. Considerar e examinar os pormenores durante uma sobrecarga do servidor é a chave para o sucesso de uma investigação.

O MySQL provê bons recursos para acompanhar o desempenho do servidor, além de ferramentas desenvolvidas por terceiros que auxiliam em processos específicos. Contudo, as ferramentas ainda carecem de opções para cobrir todos os requisitos de um exame minucioso. Um exemplo disso são os próprios logs do MySQL, que poderiam ser melhor aproveitados. Uma ferramenta para processamento desse histórico de informações seria, sem dúvida, muito bem-vinda.