Optimização de MySQL em Linux

13/08/2010

em Como Fazer,Linha de comando,MySQL

Quando se pretende optimizar a velocidade de um website ou de um servidor, um dos serviços que mais recursos consome é o MySQL, devido ao possível excesso de pedidos à base de dados. Infelizmente, nem todos somos administradores de bases de dados, o que nos coloca numa posição difícil, para a análise da perca de performance do servidor e/ou do website.

Existem vários programas que nos permitem analisar o uso de base de dados MySQL e nos fornece indicações de como poderemos melhorar para obter uma melhor rentabilidade e um decréscimo de uso intensivo de pedidos a uma base de dados. Neste artigo, será focado nos programas MySQLTuner e MySQL Performance Tuning Primer Script, dado que um complementa o outro e permite-nos obter um resultado muito próximo do ideal.

Uma nota importante; Estes programas permitem uma optimização do serviço MySQL, mas, terá de ter em conta que existem outras situações em que por mais que o serviço esteja optimizado, é importante também rever a programação do website(s) de forma a complementar a utilização intensiva do uso de bases de dados.

Para transferir a ultima versão dos programas (GPL), utilize os seguintes sites:
MySQLTuner
MySQL Performance Tuning Primer Script

Após a transferência, necessita de tornar os programas, executáveis:

# chmod +x mysqltuner.pl
# chmod +x tuning-primer.sh

Note, que a partir deste ponto, necessita de saber a sua senha/pasword de “root” de forma a que sejam efectuados os pedidos de análise do serviço de MySQL. É recomendado que o serviço esteja operacional pelo menos, á mais de 48h, de forma a garantir que os elementos estatisticos estão disponiveis para análise;

Execute;

# ./mysqltuner.pl

e insira o utilizador da base de dados (“root”) e respectiva password

Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:

O programa/script será executado e as recomendações estão disponiveis no fim, que poderá ser algo similar a;

-------- Recommendations -----------------------------------------------------
General recommendations:
 Add skip-bdb to MySQL configuration to disable BDB
 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_cache gradually to avoid file descriptor limits
Variables to adjust:
 query_cache_size (> 16M)
 join_buffer_size (> 1.0M, or always use indexes with joins)
 tmp_table_size (> 64M)
 max_heap_table_size (> 64M)
 table_cache (> 128)

Edite o seu ficheiro de configuração MySQL em /etc/my.cnf e ajuste as variáveis recomendadas (se necessitar de uma “calculadora” para obter os valores de Megabytes para Bytes, sugiro a Bit Calculator ) e o resultado (de acordo com os parametros recomendados) serão:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
old_passwords=1
max_connections=100
max_connect_errors=10
connect_timeout=10
wait_timeout=28800
key_buffer_size=8384512
sort_buffer_size=2097144
read_buffer_size=131072
max_allowed_packet=1048576
thread_cache_size=4
table_cache=256
query_cache_limit=1048576
query_cache_size=25165824
join_buffer_size=2M
tmp_table_size=128M
max_heap_table_size=128M
innodb_buffer_pool_size=20M

Execute agora, o outro programa de sugestão de optimização MySQL, MySQL Tuning Primer;

# ./tuning-primer.sh

No caso de não detectar as suas definições de utilizador “root” do serviço de MySQL, será apresentado um erro, e deverá responder “N” para “Would you like to provide a different socket?” e “y” para “Do you have your login handy ?“, inserindo de seguida o utilizador (“root”) e respectiva password do serviço MySQL.

MySQL Optimization Screenshot

Compare as recomendações com o programa/script mysqltuner.pl e siga-as, de forma a complementar as informações de ambas as recomendações e “afine” o /etc/my.cnf de acordo com ambas

Reinicie o serviço de MySQL

# /etc/init.d/mysqld restart

Antes de verificar se as novas alterações continuam correctas, aguarde pelo menos, cerca de 48h.

Artigo anterior:

Próximo artigo: