Tuning no MySQL

Posted on abril 22, 2011. Filed under: MySQL, Tuning |

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 processlist\G
*************************** 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.





Make a Comment

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair /  Alterar )

Foto do Google

Você está comentando utilizando sua conta Google. Sair /  Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair /  Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair /  Alterar )

Conectando a %s

6 Respostas to “Tuning no MySQL”

RSS Feed for Pasqua Tecnologia Comments RSS Feed

Boa tarde..

Temos um servidor de dados instalado o mysql 5.5.. Porém quando meu MAX_CONNECTIONS chega a aproximandamente 1200 meu servidor acusa erro de thread dizendo que não foi possível criar uma nova thread. Isso só volta a funcionar quando reinicio o banco de dados. Alguém tem alguma idéia do que se pode fazer para melhorar?

Desde ja agradeço

Feche as conexoes apos o uso! e se possivel poe um timeout..

Parabens, ótimo tutorial.

Muitas pessoas falam sobre tunning na internet, porém poucas explicam as fórmulas e como fazer para calcular os devidos valores de cada parametro.

Fico aguardando os próximos :).

Parabéns Pelo tutorial, espero que ajude todos que tem essa dificuldade com banco.

Parabéns você deu uma nova perspectiva para o tuning em Mysql

Obrigado Tiago!

Boa sorte!


Where's The Comment Form?

Liked it here?
Why not try sites on the blogroll...

%d blogueiros gostam disto: