[Linux] Otimize o desempenho do seu MySQL com o MySQLTuner

[Nightz 23]

Bom dia cidadãos do XTibia!

 

Trago hoje um tutorial básico de como melhorar o desempenho do seu MySQL e deixar seu servidor ainda mais rápido! Esse tutorial não é tão básico, então recomendo que siga os passos atentamente.

 

Vamos lá?

Antes de tudo, vou listar os requisitos para que o tutorial funcione, ou seja, o que precisa estar instalado no servidor:

unzip
perl
nano

Caso algum não esteja instalado, basta rodar o comando apt-get install nome_do_requisito.

Ainda antes de começar, é recomendável que façamos um backup do arquivo de configuração do MySQL com o seguinte comando:

cp /etc/mysql/my.cnf /etc/mysql/my.cnf.bkp

Caso algo aconteça, basta digitar os comandos abaixo para que a configuração volte para a que estava anteriormente:

rm -rf /etc/mysql/my.cnf
mv /etc/mysql/my.cnf.bkp /etc/mysql/my.cnf
service mysql restart

1. Para começar o tutorial faremos o download do MySQLTuner, que é nada mais do que um arquivo em perl que gera um relatório de como está a configuração do seu MySQL hoje.

mkdir /root/mysqltuner
cd /root/mysqltuner
wget https://github.com/major/MySQLTuner-perl/archive/master.zip
unzip master.zip
cd MySQLTuner-perl-master

2. Em seguida, vamos rodar o MySQLTuner para ver em quais pontos o MySQL pode ser otimizado.

perl mysqltuner.pl

O resultado deve ser semelhante ao abaixo:

 >>  MySQLTuner 1.4.0 - 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.
[OK] Currently running supported MySQL version 5.5.41-0+wheezy1
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +ARCHIVE +BLACKHOLE +CSV -FEDERATED +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 57K (Tables: 32)
[--] Data in InnoDB tables: 6M (Tables: 34)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 35

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 1d 1h 37m 12s (1M q [20.393 qps], 5K conn, TX: 606M, RX: 324M)
[--] Reads / Writes: 5% / 95%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (5% of installed RAM)
[OK] Slow queries: 0% (0/1M)
[OK] Highest usage of available connections: 7% (11/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/155.0K
[OK] Key buffer hit rate: 100.0% (9K cached / 2 reads)
[OK] Query cache efficiency: 74.7% (212K cached / 284K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 19K sorts)
[!!] Joins performed without indexes: 596
[!!] Temporary tables created on disk: 45% (1K on disk / 3K total)
[OK] Thread cache hit rate: 99% (13 created / 5K connections)
[!!] Table cache hit rate: 16% (163 open / 1K opened)
[OK] Open file limit used: 13% (134/1K)
[OK] Table locks acquired immediately: 99% (1M immediate / 1M locks)
[OK] InnoDB buffer pool / data size: 128.0M/6.0M
[OK] InnoDB log waits: 0
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    Enable the slow query log to troubleshoot bad queries
    Adjust your join queries to always utilize indexes
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Increase table_open_cache gradually to avoid file descriptor limits
    Read this before increasing table_open_cache over 64: http://bit.ly/1mi7c4C
Variables to adjust:
    join_buffer_size (> 128.0K, or always use indexes with joins)
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)
    table_open_cache (> 400)

Cada linha que estiver marcada como [OK] significa que o MySQL está bem configurado e otimizado. No final do relatório, o MySQLTuner descreve o que deve ser feito para que seu MySQL fique mais rápido do que nunca, logo após a linha "-------- Recommendations (...)".

Caso você tenha um conhecimento no mínimo razoável, faça o que está descrito em "General recommendations". Infelizmente não há como eu explicar cada sugestão, pois cada servidor terá uma sugestão diferente.

3. Em "Variables to adjust" basta utilizar o nano para editar o arquivo de configuração do MySQL de acordo com a sugestão do MySQLTuner:

nano /etc/mysql/my.cnf

Em seguida, procure pelas variáveis que o script citou, como por exemplo join_buffer_size. Caso não exista, basta adicionar em uma linha, seguindo as recomendações do script, por exemplo, para "join_buffer_size (> 128.0K, or always use indexes with joins)":

join_buffer_size = 128K

Após ter feito todas modificações necessárias, basta reiniciar o MySQL (lembre-se de estar com seu OTServ desligado para isso):

service mysql restart

Atenção:

1. Gostaria de ressaltar que o MySQLTuner gera estatísticas que permitem muito mais configurações e otimizações do que as citadas nesse tutorial, porém é recomendável que um profissional seja contatado para tal otimização.
2. Ressalto ainda que o MySQLTuner gera relatórios específicos para cada máquina, ou seja, pode ser que seu relatório esteja completamente diferente do exemplo citado.

Espero que ajude! Até a próxima

(Esse tutorial foi completamente escrito por mim. Qualquer cópia deve ser previamente autorizada e conter os devidos créditos.)

[Beeki 284]

Já tem um tutorial como esse aqui no fórum.

 

http://www.xtibia.com/forum/topic/182845-otimizando-server-save-limites-do-otserv-100-linux/

[Nightz 23]

Já tem um tutorial como esse aqui no fórum.

 

http://www.xtibia.com/forum/topic/182845-otimizando-server-save-limites-do-otserv-100-linux/

 

Desconhecia esse tutorial, mas o que eu escrevi acredito que esteja com um foco mais voltado para o MySQL e mais completo neste sentido.