Kbase 47396: Guia básico de tunning de MySQL
| Autor |
Marco Aurelio Scheidt - Tecnologia |
| Acesso |
Público |
| Publicação |
17/07/2012 |
|
O problema de performance na utilização do TSS com MySQL na maioria dos casos é causada pelo uso excessivo de disco, por conta de dimensionamento inadequado do buffer pool do InnoDB e outras configurações.
É possível ajustar este e outros parâmetros no arquivo de configuração da instância. Normalmente o arquivo chama-se my.ini (Windows) ou my.cnf (Linux), no diretório do MySQL.
Para certificar-se, olhe nas propriedades do serviço qual é o arquivo (Windows):
"C:\Program Files\MySQL\MySQL Server 5.1\bin\mysqld" --defaults-file="C:\Program Files\MySQL\MySQL Server 5.1\my.ini" MySQL
O arquivo indicado no parâmetro "--defaults-file" é o que deve ser alterado.
1) O arquivo de configuração é praticamente auto-explicável, mas o parâmetro relevante para este caso é:
innodb_buffer_pool_size=42M
O valor padrão do buffer pool é muito pequeno, normalmente menor que 50MB. Valores de até 1,5GB já foram utilizados com bons resultados. Após alterar este parâmetro é necessário reiniciar a instância.
É possível exibir algumas informações sobre IO, memória, transações, etc, executando o comando diretamente no console do MySQL:
mysql> SHOW INNODB STATUS;
2) O parâmetro "query_cache_size" é a quantidade de memória alocada para o cache de resultado de queries.
O valor default é 0, siginifica que o cache está desabilitado por padrão.
O valor mínimo para esse parâmetro é 40Kb, reservado para alocação de suas estruturas.
O tamanho exato depende da arquitetura do sistema. Recomendado um valor por volta de 128M, é claro que deve ser avaliado os recursos do servidor antes de trabalhar com esse valor, principalmente memória.
query_cache_size = 128M
3) O parâmetro "thread_cache_size" 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%';
O campo que nos interessa na lista é o Threads_created, para o exemplo será utilizado o valor de 600 threads novas 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%';
Aqui o campo que interessa é o Connections para que seja utilizado no cálculo, como exemplo será utilizado o valor de 10000.
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%, sendo assim o parâmetro thread_cache_size, está baixo e precisa ser aumentado.
Neste exemplo pode aumentar para que o cálculo resulte por volta de 99%, pode ser alterado o parâmetro para 60
Exemplo:
thread_cache_size = 60
4) Uma sugestão adicional é executar os comandos OPTIMIZE TABLE e ANALYZE TABLE para as maiores tabelas:
OPTIMIZE NO_WRITE_TO_BINLOG TABLE sped050,sped052,sped054;
ANALYZE NO_WRITE_TO_BINLOG TABLE sped050,sped052,sped054;
UPDATE 17/07/2012
Mais informações:
http://www.douglaspasqua.com/2011/04/22/tuning-no-mysql/
http://www.mysqlperformanceblog.com/2006/09/29/what-to-tune-in-mysql-server-after-installation/