Como Otimizar um Servidor MySQL

Ajustando os Caches

Vamos agora analisar as informações de cache dadas pelo mysqltuner.pl.

As duas linhas abaixo oferecem o tamanho e a eficiência do cache que armazena as chaves do banco de dados, chamada “key buffer”:

Key buffer size / total MyISAM indexes: 64.0M/2.7G

Key buffer hit rate: 98.8% (243M cached / 3M reads)

Em nosso caso, o key buffer está configurado com 64 MiB e o tamanho parece ser excelente, porque o buffer foi usado 98,8% do tempo. Se esta percentagem for abaixo de 90%, você deverá aumentar o tamanho deste cache, que é feito através da opção key_buffer_size = 64M, onde você deverá substituir “64M” pela quantidade de memória que você quer reservar para este cache.

O segundo mais importante cache disponível é o cache de queries, que armazena queries que já foram requisitadas previamente. Você pode verificar sua eficiência através das seguintes linhas:

Query cache efficiency: 59.7% (2M cached / 3M selects)

Query cache prunes per day: 566279

Quanto mais alta a percentagem mostrada, melhor. Além disso, você quer ter um número baixo de limpezas do cache (“query cache prunes”). A linha “query cache prunes per day” indica quantas vezes o cache teve de ser apagado porque mais espaço era necessário.

Para aumentar esta percentagem e diminuir o número de limpezas por dia, você deverá aumentar o tamanho do cache de queries.

Se a linha “query cache efficiency” apresentar “0%” nela, isto significa que você deverá habilitar o mecanismo de cache de queries, porque ele está desabilitado (está faltando a linha query_cache_type=1 em seu arquivo my.cnf).

Os três parâmetros de configuração para o cache de queries que você deverá usar em seu arquivo my.cnf são:

query_cache_type = 1

query_cache_size = 4M

query_cache_limit = 1M

O primeiro parâmetro habilita o cache de queries. O segundo configura o tamanho do cache. E o terceiro configura o tamanho máximo da query que pode ser armazenada no cache. Isto é para evitar que queries muito grandes sejam armazenadas no cache, o que apagaria várias queries menores que estavam anteriormente dentro do cache, se o cahe não for grande o bastante para armazenar todas as queries ao mesmo tempo.

A próxima informação indica o número de tabelas temporárias que foram criadas para lidar com ordenações. Você quer que este número seja o mais baixo possível.

Sorts requiring temporary tables: 0% (58 temp sorts / 641K sorts)

Se o número de tabelas temporárias requeridas para ordenações for alto, você deverá aumentar o parâmetro sort_buffer_size = 4M (onde você deve substituir “4M” pelo tamanho que você desejar para este cache) no arquivo my.cnf.

A eficiência do cache para comandos JOIN pode ser checada através da seguinte linha:

Joins performed without indexes: 86064

Se você achar que o número mostrado é muito alto, você pode tentar aumentar o join buffer através do parâmetro join_buffer_size = 4M (onde você deve substituir “4M” pelo tamanho que você desejar para este cache). No entanto, isto significa que os comandos JOIN no código fonte do seu site não estão usando índices e você deverá tentar corrigir isto no código fonte.

A seguir nós temos o número de tabelas temporárias criadas em disco, que deve ser o mais baixo possível, já que o acesso a dados armazenados em um disco rígido é muito mais lento que o acesso a dados armazenado em memória.

Temporary tables created on disk: 6% (726 on disk / 11K total)

Se esta percentagem for alta, você deverá primeiro otimizar a estrutura das tabelas do seu banco de dados. Você deverá trocar todas as colunas que sejam do tipo TEXT e que estejam armazenando 255 caracteres ou menos por colunas do tipo VARCHAR. Em nosso site nós estávamos com cerca de 50% de tabelas temporárias sendo criados em disco; após esta otimização esta percentagem caiu para apenas 4%.

Se após fazer esse ajuste o número de tabelas temporárias criadas em disco continuar alto, experimente aumentar a quantidade de memória configurada através dos parâmetros tmp_table_size = 4M e max_heap_table_size = 4M (onde você deve substituir “4M” pelo tamanho que você desejar; o valor índica a quantidade de memória máxima que poderá ser usada para a geração de tabelas temporárias em memória). Ambos deverão ser configurados com o mesmo valor.

A seguir nós temos o cache de “threads” e sua eficiência é dada por uma linha como:

Thread cache hit rate: 97% (938 created / 41K connections)

Aqui o ideal é que a percentagem seja a mais alta possível. Se a percentagem mostrada for menor que 90%, você deverá aumentar o cache de “threads” através do parâmetro thread_cache_size = 4M (onde você deve substituir “4M” pelo tamanho que você desejar para este cache).

E, finalmente, nós temos o cache de tabelas e sua eficiência pode ser verificada através da linha:

Table cache hit rate: 96% (1K open / 1K opened)

Mais uma vez, nós queremos que a percentagem seja a mais alta possível. Se a percentagem mostrada for menor que 90%, você deverá ajustar o parâmetro table_open_cache= 4096 (onde você deve substituir “4096” pelo tamanho que você desejar para este cache) no arquivo my.cnf.

A linha abaixo indica dá uma dimensão do problema de travamentos de tabela em seu servidor:

Table locks acquired immediately: 99% (17M immediate / 17M locks)

Como você pode ver, nós não estamos tendo problemas de travamento de tabelas neste servidor em particular. No entanto, se esta percentagem for baixa, você deverá considerar migrar do MyISAM para o InnoDB.