Tuning no MySQL

Neste Post iremos abordar alguns parâmetros da configuração do MySQL que influenciam na perfomance do banco de dados. A idéia é que você, leitor, tenha conhecimento para poder manipular esses parâmetros e melhorar o desempenho do seu banco de dados. Estou abordando somente alguns parâmetros envolvidos: max_connections, table_cache, thread_cache_size, query_cache_size.  Existem outros parâmetros que podem ajudar a melhorar a perfomance, mas irei abordar em posts futuros.

max_connections:

Determina o número máximo de conexões simultâneas permitidas. Por padrão o valor é 100. Caso sua aplicação esteja recebendo a mensagem de erro “Too many connections” significa que estourou o limite máximo de conexões simultâneas, portanto esta na hora de aumentar o valor desse parâmetro. O valor desse parâmetro influência no consumo de memória do servidor, portano esteja atento à esse ponto. Para monitorar as conexões ativas no momento, use o comando SHOW PROCESSLIST no console do mysql. Exemplo:

mysql> show processlistG
*************************** 1. row ***************************
Id: 1
User: internet
Host: 192.168.0.10:36461
db: testedb
Command: Sleep
Time: 0
State:
Info: NULL
*************************** 2. row ***************************
Id: 2
User: internet
Host: 192.168.0.10:36460
db: testedb
Command: Sleep
Time: 0
State:
Info: NULL
*************************** 3. row ***************************
Id: 3
User: internet
Host: 192.168.0.10:36461
db: testedb
Command: Query
Time: 0
State: Writing to net
Info: select * from tabela_teste
3 rows in set (0.00 sec)

Nesse exemplo, no momento da excução do comando SHOW PROCESSLIST, haviam 3 processos simultânos em execução. A saída traz outras informações interessantes conforme podem observar acima. Exemplo:

max_connections = 200

PS: Esses parâmetros devem estar setados na sessão [mysqld] do arquivo my.cnf

table_cache:

table_cache é o número de tabelas abertas em cache para todos threads. Aumentar esse valor aumenta o número de file descriptors que o MySQL irá precisar abrir para trabalhar. Esse parâmetro esta relacionado com o max_connections: Caso tenha o valor de 200 para max_connections, você deve setar table_cache para 200 * N, onde N é o número máximo de tabelas usada por join em qualquer das queries que você estiver usando na sua aplicação. Aumentar muito o valor desse parâmetro pode fazer com que o MySQL exceda o número máximo de file descriptors abertos por processo. Este é um limite definido pelo S.O. Tenha certeza que o seu S.O.  permita abrir o número de file descritptors definido em table_cache. No Linux, para checar o número máximo de file descriptors permitidos use o comando abaixo:

# cat /proc/sys/fs/file-max
2210424

Exemplo:

table_cache = 800

PS: Esses parâmetros devem estar setados na sessão [mysqld] do arquivo my.cnf

query_cache_size:

É a quantidade de memória alocada para o cache de resultado de queries. O valor padrão é 0. Isso siginifica que o cache é desabilitado por padrão. Os valores permitidos são múltiplos de 1024.  O valor mínimo para esse parâmetro é 40Kb, reservado para alocação de suas estruturas. O tamanho exato depende da arquitetura do sistema. Aconselho setar o valor por volta de 128M, é claro que você deve avaliar a estrutura do seu servidor antes de trabalhar com esses valores, principalmente memória.

query_cache_size = 128M

PS: Esses parâmetros devem estar setados na sessão [mysqld] do arquivo my.cnf

thread_cache_size:

Quantidade de threads que  o servidor irá cachear para re-utilização. Quando um cliente desconecta a thread irá para um cache podendo dessa forma ser reutilizadaDessa maneira diminuimos a criação de novas threads para atender novos clientes. Esse parâmetro pode ser aumentado para melhorar a performance caso seu servidor tenha muitas conexões por segundo.  Através de um cálculo utilizando o número de conexões criadas e o número de threads novas criadas é possível determinar a eficiência desse cache, e então determinar um valor mais apropriado para esse parâmetro. A taxa de acerto baseado no cálculo abaixo deve estar o mais próximo possível de 99%:

100 - ((Threads_created / Connections) * 100)

Threads_created é o número de thread criadas desde que o MySQL foi iniciado. Connections é o número total de conexões criadas desde que o servidor do MySQL foi iniciado.  Nossa intenção é que o número de threads criadas seja o menor possível em relação ao número total de conexões realizadas.

Para obter o número de threads criadas, execute o comando abaixo no console do mysql:

mysql> SHOW STATUS LIKE '%thread%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| Delayed_insert_threads | 0     |
| Slow_launch_threads    | 0     |
| Threads_cached         | 30    |
| Threads_connected      | 5     |
| Threads_created        | 600   |
| Threads_running        | 1     |
+------------------------+-------+
6 rows in set (0.00 sec)

O campo que nos interessa na lista acima é o Threads_created, valor 600. Portanto, desde que o MySQL foi iniciado somente 600 threads novas foram criadas.

Para obter o número de conexões criadas desde a inicialização do MySql, execute o comando abaixo no console do MySQL:

mysql> SHOW STATUS LIKE '%connections%';
+----------------------+----------+
| Variable_name        | Value    |
+----------------------+----------+
| Connections          | 10000    |
| Max_used_connections | 600      |
+----------------------+----------+
2 rows in set (0.00 sec)

O valor que nos interessa é o 10000.  Vamos agora ao nosso cálculo:

100 - ((600 / 10000) * 100)

O Resultado é 94% (significa que 94% das conexões utilizaram threads em cache). O ideal é que o resultado esteja pelo menos por volta de 99%. Portanto, o valor do nosso parâmetro, thread_cache_size, esta baixo e precisa ser aumentado. Aconselho ir aumentado esse campo até que o nosso calculo resulte por volta de 99%. No exemplo acima, o valor do campo thread_cache_size esta 30. Podemos ir aumentando de 10 em 10 até que cheguemos no resultado desejado.

Exemplo:

thread_cache_size = 60
PS: Esses parâmetros devem estar setados na sessão [mysqld] do arquivo my.cnf

O objetivo deste post foi abordar alguns parâmetros do MySQL que influenciam no desempenho do banco de dados. Não abordei todos, porém ao trabalhar com esses parâmetros acima o desempenho do seu banco irá melhorar de forma significativa. Já tive grandes resultados fazendo esses tunings em um banco que recebe muitos acessos. Espero que aproveitem.





Please follow and like us:

Comments

  1. By Emerson

    • By Bruno Buger

  2. By Jorge

  3. By Tiago Teleken

    • mm By pasquati

    • mm By Douglas V. Pasqua

Follow

Get every new post on this blog delivered to your Inbox.

Join other followers: