Como Otimizar um Servidor MySQL

Ajuste Fino do Servidor MySQL

Existem vários parâmetros que podem ser ajustados em um servidor MySQL para torná-lo mais rápido. Os valores exatos que devem ser usados para cada parâmetro, no entanto, serão específicos para o seu sistema baseados no padrão de uso do seu servidor MySQL. Portanto, é impossível para nós publicarmos valores recomendados que servissem para todos os leitores.

Felizmente, o servidor MySQL armazena estatísticas que ajudarão você a determinar os melhores valores a serem usados. Além disso, existem dois utilitários bem úteis que podem ser usados para ler estas estatísticas e para mostrá-las em um formato fácil de entender: o tuning-primer.sh e o mysqltuner.pl. Em nossos exemplos, nós usaremos o mysqltuner.pl.

Baixe o mysqltuner.pl em seu servidor, execute o comando chmod 755 mysqltuner.pl e rode-o. Ele lhe dará várias estatísticas importantes. Pare que os dados sejam confiáveis, você deverá deixar o servidor rodando por pelo menos 48 horas e nós recomendamos que isso seja feito durante dias de alto tráfego (por exemplo, durante a semana e não no final de semana ou durante um feriadão).

O script mysqltuner.pl lhe dará, no final de sua análise, uma dica dos parâmetros que deverão ser ajustados em seu servidor. No entanto, nós ensinaremos a você como fazer uma análise mais detalhada.

Os parâmetros são geralmente configurados no arquivo my.cnf, que é normalmente armazenado em /etc (para editar este arquivo, rode vi /etc/my.cnf). Para que os novos parâmetros sejam efetivados você deverá reiniciar o MySQL. Já que você terá que esperar 48 horas até sua próxima sessão de ajuste fino, nós recomendamos que você reinicie o MySQL somente após o ajuste de todos os parâmetros.

Vamos discutir as informações mais importantes oferecidas pelo mysqltuner.pl usando exemplos reais. Idealmente, você quer ver todos os parâmetros marcados com um “OK” na cor verde.

Total fragmented tables: 30

Isto indica que existem 30 tabelas fragmentadas e nós devemos rodar OPTIMIZE TABLE para desfragmentá-las. Você pode usar este script para desfragmentar todas as tabelas. Só há um problema: em servidor muito movimentado e com muitos dados, a desfragmentação leva tempo e a tabela que estiver sendo desfragmentada ficará travada, o que afetará a usabilidade do seu site ou aplicação. Portanto, nós recomendamos que você desabilite o seu servidor web durante a desfragmentação.

Security Recommendations

Nesta seção, o mysqltuner.pl fará uma lista de possíveis problemas de segurança. Você deverá corrigir todos eles.

Maximum possible memory usage: 23.4G (149% of installed RAM)

Maximum possible memory usage: 12.2G (77% of installed RAM)

Esta opção lista a quantidade máxima de memória RAM que o servidor usará no pior cenário possível, baseado em sua configuração atual. Os dois parâmetros que mais influenciam a quantidade de memória RAM requerida são max_connections e a quantidade de memória RAM reservada para o buffer do InnoDB através do parâmetro innodb_buffer_pool_size.

No primeiro exemplo, nós temos o servidor mostrado anteriormente na Figura 1. Se você lembrar, aquele sistema tinha 16 GiB de RAM e não estava usando o arquivo de troca. No entanto, se o servidor usar todos os recursos permitidos baseado em sua configuração atual, ele precisará de 23,4 GiB de RAM. Em outras palavras, ele não está usando o arquivo de troca agora, mas quando sob tráfego pesado, ele irá, deixando o servidor lento.

Então, para rodar este servidor corretamente, nós precisamos reajustar todos os parâmetros de cache e o parâmetro max_connections (mais sobre isto mais adiante). No entanto, se mesmo após o ajuste destes parâmetros a quantidade de memória RAM recomendada for mais alta que a quantidade de memória que o servidor tem instalado, está na hora de aumentar a quantidade de memória. Neste exemplo, nós deveríamos aumentar a quantidade de memória deste servidor de 16 GiB para 32 GiB (24 GiB ficaria muito próximo da quantidade recomendada e nós precisamos deixar uma margem para o sistema operacional e para uso futuro), caso após o ajuste de outros parâmetros a quantidade de memória requerida não for diminuída.

O segundo exemplo é de outro servidor MySQL também com 16 GiB de RAM. Este servidor tem uma boa quantidade de memória RAM instalada, já que o MySQL pode usar apenas 12,2 GiB. É claro que após ajustar outros parâmetros nós devemos checar novamente para ver se a quantidade máxima de memória RAM que o MySQL pode usar “cabe” na quantidade de que temos instalada neste servidor.

Slow queries: 0% (5/5M)

Quantas queries levam mais tempo para executar que o número de segundos configurado através da opção long_query_time. Se esta opção não existir dentro do arquivo my.cnf, o valor padrão é 10 segundos. Para ver o valor atual da long_query_time, rode:

SHOW VARIABLES WHERE variable_name='long_query_time';

Em nosso exemplo, apenas cinco queries levam mais que 10 segundos (nós mantivemos o valor padrão em nosso servidor) de um total de cinco milhões de queries que foram rodadas desde a última vez que iniciamos o servidor MySQL.

Idealmente este número deve ser zero ou próximo a zero. Se não for, você deverá ficar de olho para saber o que está errado. Você pode ajustar os outros parâmetros, mas geralmente para corrigir queries lentas (assumindo que a carga no servidor MySQL seja baixa e a quantidade de memória seja adequada), você deverá otimizar o código fonte. Adicionando o parâmetro log_slow_queries=/var/log/mysql/log-slow-queries.log ao arquivo my.cnf criará um arquivo de log contendo todas as queries lentas e, a partir daí, você poderá analisar o que está acontecendo.

Highest usage of available connections: 37% (938/2500)

Este é o parâmetro de maior impacto na quantidade de memória necessária em seu servidor MySQL e é configurado através de um parâmetro chamado max_connections no my.cnf.

Um número de conexões máximas disponíveis menor que o número de conexões atuais impede que usuários acessem o banco de dados, tornando seu site inacessível e/ou lento. Um número muito alto de conexões disponíveis para um número muito baixo de conexões reais fará com que o servidor MySQL requeira mais memória do que ele realmente necessita.

Em nosso exemplo, nosso arquivo my.cnf foi configurado com max_connections=2500, que parece ser muito alto, já que a quantidade máxima de conexões que tivemos até o momento era 938. Portanto, seria melhor reduzir este parâmetro para um valor menor, como 1200. É sempre importante deixar uma margem para aumento de tráfego no futuro.

No entanto, é fundamental que você tenha certeza de estar rodando o mysqltuner.pl após o servidor ter passado por períodos de alto tráfego antes de ajustar este parâmetro, caso contrário você poderá configurar erroneamente um número baixo para max_connections.