Otimizando o desempenho do servidor MySQL usando a ferramenta de tuning MySQLTuner-perl



Tuning, na administração de sistemas computacionais, é a prática adotada por sysadmins que visam otimizar o desempenho de determinado tipo de serviço; bem como: serviços web, de banco de dados e o próprio sistema operacional.

No caso de servidores de banco de dados, as técnicas de Tuning visam otimizar o desempenho na recuperação ou atualização de dados. Aumentar o tempo de resposta, fazer caches de consultas, alocar mais memória para o serviço e entre outros.

Não existem receitas prontas. Cada banco de dados possui peculiaridades distintas, pois o número de base de dados, tabelas e consultas feitas ao servidor irá determinar as técnias/métodos de Tuning a serem adotadas.

Assim, por se tratar de uma tarefa nada fácil, foi criado uma ferramenta que facilita a descoberta do diagnóstico do problema e sugere dicas/técnicas de otimização relacionadas ao problema encontrado.

Desenvolvida por Major Hayden, a ferramenta MySQLTuner-perl - escrita na linguagem Perl - foi criada para auxilar todo sysadmin que deseja aprimorar as configurações do seu servidor MySQL e sugerir recomendações para aumentar a performance e estabilidade desse servidor.

Otimização (Tuning) em banco de dados é um assunto extenso e complexo. Portanto, este material contempla apenas o básico da detecção do problema e refinamento do serviço MySQL. Assim, é extremamente importante que você entenda completamente cada alteração que deve ser feita num servidor de banco de dados MySQL. Caso contrário, todas as sugestões de melhorias, indicadas pela ferramenta, podem afetar, negativamente, seu servidor MySQL.

A Ferramenta MySQLTuner-perl

Em resumo, conforme site oficial, o MySQLTuner é um script, escrito em Perl, que permite avaliar rapidamente a instalação do seu servidor MySQL e sugerir ajustes para aumentar o desempenho e estabilidade.

A ferramenta é bem robusta, pois possui compatibilidade com:

Além disso, possui documentação aberta.

Mão na massa

A instalação da ferramenta é simples e possui um único pré-requisito (a instalação do Perl).

Download MySQLTuner:

wget http://mysqltuner.pl/ -O mysqltuner.pl

Antes de executar o script é recomandado que seu servidor MySQL esteja ativo por mais de 24h. Pois, quanto mais tempo o servidor estiver ativo, mais a ferramenta terá dados a serem coletados e poderá mostrar resultados conclusivos na análise do comportamento e uso do sistema de gerenciamento de banco de dados. Além disso, o tempo de execução do script pode variar de servidor para servidor. Quanto mais bases de dados, tabelas e usuários estiverem presentes; mais tempo poderá levar.

Sendo assim, para executar o script:

perl mysqltuner.pl

Pronto, aguarde o relatório ;-) Caso prefira, você poderá jogar a saída do comando para um arquivo. Execute:

perl mysqltuner.pl --outputfile /tmp/result_mysqltuner.txt

EXEMPLO DE SAÍDA DO SCRIPT:

>>  MySQLTuner 1.6.12 - Major Hayden <major@mhtx.net>
 >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
 >>  Run with '--help' for additional options and output filtering
[OK] Logged in using credentials from debian maintenance account.

[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.49-0+deb8u1-log
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -----------------------------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MEMORY +MRG_MYISAM +MyISAM +PERFORMANCE_SCHEMA 
[--] Data in InnoDB tables: 784K (Tables: 3)
[OK] Total fragmented tables: 0
 
-------- Security Recommendations ------------------------------------------------------------------
[OK] There are no anonymous accounts for any database users
[OK] All database users have passwords assigned
[!!] There is no basic password file list!

[...]
[...]

EXEMPLO DO RELATÓRIO FINAL:

-------- Recommendations ---------------------------------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Restrict Host for user@% to user@SpecificDNSorIp
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Reduce or eliminate unclosed connections and network issues
    Adjust your join queries to always utilize indexes
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
    Beware that open_files_limit (2500) variable 
    should be greater than table_open_cache ( 500)
Variables to adjust:
    query_cache_type (=0)
    join_buffer_size (> 128.0K, or always use indexes with joins)
    table_open_cache (> 500)
    innodb_buffer_pool_size (>= 5G) if possible.
    innodb_log_buffer_size (>= 8M)

Otimizando o desempenho do servidor MySQL

Antes de fazer as alterações sugeridas pela ferramenta, faça um backup do arquivo de configuração do servidor MySQL:

cp /etc/mysql/my.cnf /etc/mysql/my.cnf.original

A fim de evitar situações inesperadas, recomenda-se que seja feita cada alteração por vez; não em lote. Assim, a cada alteração reinicie o serviço MySQL.

Por fim, alguns ajustes, sugeridos no exemplo e que podem ser feitos, se referem aos parâmetros/funções:

Globalmente, os parâmetros mais usados para Tuning de servidor MySQL são (todos presentes no arquivo /etc/mysql/my.cnf):

Faça as devidas alterações conforme seu conhecimento e por sua conta e risco.

gravatar

Ricardo Ferreira

Criador e administrador desse portal para Sysadmin Linux. Possui mais de 6 anos de experiência com administração de servidores Linux. Pretende compartilhar todo seu conhecimento e experiência, da mesma forma como no Linux Descomplicado, onde também é criador e administrador ;-)

Quer receber novidades diretamente no e-mail?!

Informe seu endereço de e-mail:

Deixe seu comentário:



2016 - Para este material, você precisa informar a origem e criar conteúdo com a mesma licença para poder usá-lo. Licença Creative Commons BY-NC-SA 4.0

"Este material foi publicado originalmente por Ricardo Ferreira em Sysadmin | Linux Descomplicado com título e link Otimizando o desempenho do servidor MySQL usando a ferramenta de tuning MySQLTuner-perl."

comments powered by Disqus