Otimização do MySQL: Monitoramento

      6 comentários em Otimização do MySQL: Monitoramento

Desempenho. Essa é a palavra chave que orienta e estimula o desenvolvimento do popular sistema de gerenciamento de banco de dados MySQL. Considerado também o fator determinante para o seu sucesso, é o desempenho que se sobressaí sobre as demais qualidades (e defeitos) desse banco de dados.

Entretanto, por si só o MySQL não irá garantir a melhor performance para uma determinada aplicação, pois assim como todo banco de dados, o desempenho em relação a aplicação depende de vários conceitos inter-relacionados. Durante uma análise, conhecer esses conceitos e aplicá-los de maneira correta poderão, na maioria dos casos, fabricar resultados mais positivos na performance do sistema do que simplesmente adicionar memória RAM.

Em linhas gerais, para detectar e solucionar um problema de desempenho no banco de dados, o administrador deverá considerar cronologicamente os seguintes passos:

  • Encontrar o gargalo (CPU, Memória, I/O, Rede)
  • Otimizar a aplicação (Remover queries desnecessárias, usar conexões persistentes)
  • Otimizar as queries (Usando índices, tabelas temporárias, eliminando a redundância)
  • Otimizar o banco de dados (Alterar os limites dos caches, motor de armazenamento)
  • Otimizar o sistema operacional (Sistema de arquivos, Swap, Kernel)
  • Otimizar o hardware (Acrescentar memória, usar controladora RAID)

A partir desse momento iremos explorar esses itens mais especificamente, procurando expor os principais procedimentos de monitoramento e otimização do MySQL, considerando também as variáveis que poderão ser alteradas de acordo com os recursos exigidos para o banco de dados.

Encontrar o gargalo

Tratando-se de ciência da computação, é costumeiro afirmar que, em diversos casos, 99% do processo de resolução de um problema é descobrir onde está exatamente o problema. E pelo vício insaciável que a maioria dos administradores de sistemas têm por resolvê-los, é frustrante quando eles se deparam com sistemas que não fornecem as ferramentas necessárias para a detecção.

No caso dos sistemas Unix – plataforma mais comum dos servidores MySQL – são várias as ferramentas que permitem fazer um levantamento da utilização de Memória, Swap, CPU e I/O, além do monitoramento do próprio servidor MySQL, como as queries em execução, média de queries por segundo, tempo de processamento, etc. Entre essas ferramentas, algumas já são muito conhecidas pelos administradores de sistemas, como o top, ps, vmstat e iostat.

Logo a seguir, serão analisadas as principais ferramentas de monitoramento dos sistemas Unix, mais uma ferramenta específica para o monitoramento do MySQL, acrescentando uma visão geral do que representa seus resultados no processo de análise de performance do servidor.

ps e top

O ps, e principalmente o top, são duas ferramentas importantes para avaliar a utilização de CPU e memória do sistema, com os valores discriminados por processos ou de todo sistema operacional. Com base em suas informações, é possível determinar, com uma precisão reduzida, se o processo do MySQL está sofrendo por conta de processamento (CPU), memória, ou até mesmo I/O.

  • CPU: Se o processo do MySQL está utilizando muita porcentagem de CPU, sobretudo referente a coluna us (user), isso indica um óbvio problema de processamento. As soluções para esse tipo de gargalo são as mais variadas, como a necessidade de otimização das queries, criação de índices, upgrade de CPU, ou configurações do servidor MySQL, visando reduzir o tempo de processamento das queries.
    Cpu(s): 91.0% us, 1.0% sy, 0.0% ni, 7.5% id, 1.0% wa, 0.0% hi, 0.5% si

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

    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.

  • Memória: No cabeçalho do comando top existem duas linhas dedicadas a utilização de memória do sistema, além de colunas específicas para calcular a quantidade de memória usada por cada processo em execução.

    Pouca memória livre (coluna Free da linha Mem) não significa necessariamente que o servidor de banco de dados esteja sedento por mais memória RAM. Às vezes poderá até indicar o contrário, visto que um banco de dados otimizado aproveita muito bem o recurso para armazenar o cache de tabelas e consultas. Portanto, é imprescindível verificar como essa memória está sendo utilizada. Valores altos de memória livre, somados a memória utilizada para cache (coluna cached da linha Swap), não são indícios de problemas de memória.

    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.

  • I/O: Como dito anteriormente, quando a porcentagem de utilização de CPU é maior na coluna wa (wait time), significa que os processos do sistema estão aguardando a finalização das operações de I/O (como a leitura e escrita no disco rígido) para que o processamento continue. Esse é um evento incomum, porém, não menos importante durante a análise. Nesse caso, o banco de dados, sistema operacional e o hardware tem que ser otimizado de forma específica, de acordo com as necessidades exigidas pela aplicação.

    O comando ps também poderá ajudar a detectar qual processo está aguardando I/O. Esses processos contêm a letra “D” na coluna STAT, e dependendo de qual processo estiver nessa situação, isso deverá indicar como resolver o gargalo. Por exemplo, caso o processo kjournald, que gerencia o journal do sistema de arquivos ext3, esteja freqüentemente com o STAT “D”, é um sinal que o banco de dados está realizando muitas operações de escrita e, conseqüentemente, requerendo que o sistema de arquivos armazene todas essas modificações no journal.

    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

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

    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 hamc 127.0.0.1 test 0 Query show processlist
    202 user4 189.116.23.1 mydb 11 Sleep

    Exemplo de visualização do mytop.

    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:

  • Log de todas as queries:

    Pode ser ativado com a opção log=”nome_do_arquivo”.

    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.

  • Log das queries lentas:

    Pode ser ativado com a opção log-slow-queries=”nome_do_arquivo”.

    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’;

    Exemplo de log do MySQL.

    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.

    Próximos artigos

    Após discorrer sobre algumas ferramentas para monitoramento e análise de desempenho do MySQL, os próximos artigos serão dedicados as várias possibilidades de otimização da aplicação, banco de dados e sistema operacional, com base nas informações obtidas durante essa investigação.

    Referências

    MySQL (01-01-2004). How to Monitor MySQL’s performance. MySQL Newsletter. Acessado em 22-04-2008.
    Drouin, Matthew (29-11-2003). Mytop Introduction. OpenSource Tutorials. Acessado em 24-04-2008.
    Wikibooks (02-02-2008). MySQL Optimization. Wikibooks. Acessado em 24-04-2008.

  • 6 thoughts on “Otimização do MySQL: Monitoramento

    1. Felipe Ribeiro

      Ola Heitor.

      Otimo artigo, parabens! Muito minucioso, objetivo e claro. Gostaria de saber se por acaso há alguma ferramenta parecida com o MyTOP para PostgreSQL.

      No mais, meu muito obrigado!

      Felipe R.

      Reply
    2. sr concursos

      Muito bom o artigo! Bastante abrangente e detalhado! Só sugeriria que você desse mais destaque à otimização de queries. Você pode rodar tranqüilamente uma aplicação com milhares de usuários diários sem otimizar banco de dados, servidor Web ou aplicação, mas experimente ter uma página bem visitada rodando uma query que leva 5 segundos pra terminar pra ver o que acontece. Enfim, esse é o fator isolado mais importante que se deve cuidar ao escrever ou administrar uma aplicação, na minha opinião.

      Nas referências eu te sugiro colocar um blog muito bom, o MySQL Performance Blog.

      No mais, um abraço!

      Reply

    Deixe uma resposta

    O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *