Zabbix – MySQL Tuning | Adail Spinola

Reiteradas vezes tenho observado questionamentos relacionados à problemas de performance em servidores Zabbix durante a execução do housekeeping.

Pessoalmente eu não tenho problemas com isso, e já administrei bases beeeem grandes (pessoalmente uma com mais de 80gb, indiretamente onde trabalho temos bases de mais de meio tera – tamanho total).

Tenho um servidor Zabbix rodando em um VPS de 6$ (aqueles beeem fraquinhos com virtualização baseada em contexto) e recebi mensagem do suporte técnico informando que o meu MySQL estaria consumindo demasiadamente o I/O do servidor. Nesta mesma mensagem eles me recomendaram a utilização de um script para tuning do MySQL o mysqltuner.pl (https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl), até então não conhecia este script e posso dizer agora: ele é ótimo.

É um script PERL muito completo que faz a verificação das estatísticas do banco de dados e já recomenda ações de melhoria que podem ser executadas no my.cnf. Alguns parâmetros (configurações no my.cnf) ele já informa exatamente qual parâmetro que deve ser ajustado. Outras configurações (tal qual a fragmentação de tabelas) ele informa que existe o problema e que você deve desfragmentar, mas não informa como.

O roteiro para executar o tuning no meu laboratório foi:

Baixar script

wget https://raw.github.com/rackerhacker/MySQLTuner-perl/master/mysqltuner.pl

Executar script (no meu caso a integração do shell com o mysql já permite a execução sem senhas, se o SO não permitir ele irá perguntar usuário e senha)

 >>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
 >>  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.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.1.63-0+squeeze1
[OK] Operating on 32-bit architecture with less than 2GB RAM

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in InnoDB tables: 842M (Tables: 206)
[!!] Total fragmented tables: 206

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 37d 16h 56m 6s (93M q [28.619 qps], 279K conn, TX: 116B, RX: 14B)
[--] Reads / Writes: 72% / 28%
[--] Total buffers: 58.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 463.8M (46% of installed RAM)
[OK] Slow queries: 0% (119/93M)
[OK] Highest usage of available connections: 68% (104/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/90.0K
[OK] Key buffer hit rate: 100.0% (3M cached / 0 reads)
[!!] Query cache efficiency: 13.8% (8M cached / 65M selects)
[!!] Query cache prunes per day: 691938
[OK] Sorts requiring temporary tables: 0% (3 temp sorts / 121K sorts)
[OK] Temporary tables created on disk: 6% (51K on disk / 772K total)
[OK] Thread cache hit rate: 99% (1K created / 279K connections)
[!!] Table cache hit rate: 0% (64 open / 50K opened)
[OK] Open file limit used: 0% (0/1K)
[OK] Table locks acquired immediately: 100% (85M immediate / 85M locks)
[!!] InnoDB data size / buffer pool: 842.0M/8.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
    query_cache_limit (> 1M, or use smaller result sets)
    query_cache_size (> 16M)
    table_cache (> 64)
    innodb_buffer_pool_size (>= 842M)

Observem, ao final ele já me informa quais parâmetros ele recomenda que sejam otimizados, informa que seria interessante a habilitação do log de querys lentas e que existem tabelas fragmentadas e que sua desfragmentação poderia melhorar o sistema.

As variáveis para ajustar são todas feitas através da alteração do arquivo my.cnf (/etc/my.cnf ou /etc/mysql/my.cnf) e precisam de restart do banco de dados. Após o ajuste e o restart recomendam um período de 24hrs com o banco rodando antes de rodar o script novamente para poder visualizar as novas estatísticas.

O script não informa o comando para desfragmentar as tabelas. Após busca rápida no google encontrei a linha de comando abaixo. A execução dela pode ser LENTA, logo recomendo que seja feita fora do expediente normal (aqui no meu laboratório foram mais de 3 horas de execução).

mysqlcheck -u root -p --auto-repair --check --optimize --all-databases

Caso o ambiente suporte a desfragmentação das tabelas ele irá executar a correção diretamente, caso contrário, ele irá recriar a tabela (também automaticamente).

Monitorando e abusando ;)