[Tutorial] Configurando Seu My.cnf: Como Usar O Mysqltuner

[joaopaulo]

[sEM REVISÃO ORTOGRÁFICA]

Bom dia pessoal, 

 

Depois de um tempo fora, volto aqui para contribuir com um tutorial bacana de uma dúvida recorrente no fórum: Como configurar o Msql usando o mysqltuner. Tenho visto já diversos usuários que dizem que basta deixar tudo "verdinho" e pronto. Engano, não deve e nem pode ficar tudo verdinho! Isso é mito.

 

Sendo breve, o Mysqltuner é um verificador de carga de banco de dados e um pequeno script de benchmark do servidor. Ele vem instalado por default com o cPanel, mas... pode ser usado em qualquer servidor Linux, e nesse caso, pode ser instalado com os seguintes comandos:

cd /root/
wget http://mysqltuner.com/mysqltuner.pl
chmod +x mysqltuner.pl

Para rodar o script, execute:

#Para servidores com cPanel
/usr/local/cpanel/3rdparty/mysqltuner/mysqltuner.pl

#Para outros servidores Linux
./mysqltuner.pl

Se você o executou via cPanel, não irá pedir nem login e nem senha, se foi via outro sistema, informe o usuário root e sua respectiva senha.

• O Problema

  Para não falar de fábulas, vou pegar um exemplo real que aconteceu com um amigo do fórum nesses tempos (27 maio), me veio com o seguinte problema:

  

  E como combinado, me enviou o resultado do mysqltuner, não vou postar o my.cnf antigo porque o foco aqui é outro:

  

  Meu primeiro comentário foi: 

   

  JOAO PAULO: nossa man

  JOAO PAULO: no seu servidor nem tem bd

  XXXXXXXXXXX: Como não? Todos sites usam mysql

  JOAO PAULO: sim, mas mt pouco

  JOAO PAULO: mt pouco mesmo

  XXXXXXXXXXX: Hm..

  XXXXXXXXXXX: Essa config tem nem 2h que colocou

  XXXXXXXXXXX: Tem alguma sugestão?

  JOAO PAULO: sim

  JOAO PAULO: vamos reduzir pacas umas coisinhas

  XXXXXXXXXXX: hm

  Okay, sei que uma parte de vocês não entendeu meus comentários, mas vamos entende-lo e fundamenta-lo:

• O princípio da Magia: Tirando as notas

  Pegue um pedaço de papel, lapiseira e calculadora. Não banque o espertinho do notepad.exe mais wincalc.exe!

  Veja essas colunas do Mysqltuner:

  [--] Data in MyISAM tables: 81M (Tables: 1461)
  [--] Data in InnoDB tables: 79M (Tables: 1202)
  [--] Data in MEMORY tables: 0B (Tables: 79)
  

  Some e anote:

  Tamanho das bases de dados = 81 + 79 + 0 = 160MB

  Quantidade de Tabelas = 1461 + 1202 = 2663 (não some as tabelas in Memory)

  Entendendo esses números:

  O tamanho de todas bases de dados desse servidor é de apenas 160MB, o que é muito, mas muito pouco mesmo. Em média encontramos 10, 12GB por aí.

  Esse servidor tem 2663 tabelas, essa informação vamos usar mais a frente.

   

  Veja agora essas outras colunas:

  [--] Total buffers: 2.3G global + 3.6M per thread (350 max threads)
  [OK] Maximum possible memory usage: 3.5G (59% of installed RAM)
  [OK] Slow queries: 0% (63/102K)
  [OK] Highest usage of available connections: 6% (24/350)
  

  Anote:

  Uso de memória = 59% (okay, bom, mas pode melhorar)

  Queries Lentas = Apenas 63 de 102.000 

  Uso de conexões = 6%.

  Entenda:

  O uso de memória idel nunca deve ultrapassar 60%, em média de webhosting usa-se de 25 a 50%. Nesse caso, a frente explicarei porque pode melhorar e em muito.

  As queries lentas, são um número insignificante perto do número de queries rápidas, está em 0%, ou seja, não são consultas mal feitas que estão fazendo esse servidor travar.

  O uso de conexões está em 6% na média alta, ou seja, o servidor tem 350 conexões disponíveis mas não está fazendo uso disso. Está vendo aquela linha terminada com " + 3,6M per thread (350 max threads)"? Isso quer dizer, que cada conexão vai usar 3,6MB, o que dá um total de 1,2GB. Porém, memória, processador, etc... sempre são recursos escassos, para que alocar tanta memória se não está usando todas essas conexões? Vamos reduzi-las em breve.

  E por fim o nosso total de buffers é igual a 2,3GB, o que é um pouco alto quando pensamos que temos só 160MB de BD.

• Organizando o my.cnf: O inicio da Coisa

  Bom, antes de mais nada o my.cnf que acompanha qualquer servidor é muito limitado. Vou propor a você um modelo de my.cnf que deve ser guardado e sempre bastará adaptar as variáveis as suas necessidades. NUNCA USE ESSE MY.CNF COMO CONFIGURAÇÃO PADRÃO. Guarde esse modelo consigo:

  [mysqld]
  #Serviços Desabilitados
  #skip-locking
  #skip-innodb
  #skip-name-resolve
  #skip-networking
  #skip-bdb
  local-infile=0
  
  # Buffers Geral
  key_buffer_size=600M 
  sort_buffer=1M
  join_buffer=1M
  max_join_size=2M
  
  # Threads
  thread_concurrency=16
  thread_cache=5
  
  # Table_cache
  table_cache=512
  
  # Temp tables
  max_heap_table_size=16M
  tmp_table_size=800M
  
  # Query_cache
  query_cache_limit=512K
  query_cache_size=128M
  
  # Timeouts
  
  	interactive_timeout=45
  	wait_timeout=45
  	 
  	# Connections
  	max_connections=750
  	max_user_connections=25
  	
  	#[innodb]
  	innodb_buffer_pool_size=500M
  	innodb_file_per_table=1
  	innodb_log_file_size=5M
  	innodb_thread_concurrency=4
  	 
  	 
  	

   

  Separei todas as informações em pequenos blocos e vamos ao princípio:

  Bloco: Serviços desabilitados

  Antes de substituir seu velho my.cnf por este, veja quais linhas estão sem comentários (iniciadas por #) e mantenha o mesmo padrão aqui. Todas as linhas que existirem antes desse bloco, mantenha igual, pois ali em alguns servidores há informações de sockets, etc...

  Bloco: Buffers Geral

  key_buffer_size: Procure no resultado do mysqltuner essa linha:

  [OK] Key buffer size / total MyISAM indexes: 256.0M/25.1M
  

  Peque o último número (25.1M) e acrescente + 20%. Se você a diferença entre o primeiro número (256M) for muito maior que o valor + 20%, e o uso total de memória (59%) for menor do que o total, considere usar um valor mais alto, arbitrado por você mesmo.

  Meu novo key_buffer_size = 40M. Porém....

  Veja esta linha:

   

  [OK] Key buffer hit rate: 99.8% (3M cached / 6K reads)
  

  Embora ela esteja verdinha, quando apliquei 40MB ficou assim:

   

  [!!] Key buffer hit rate: 86.2% (25K cached / 3K reads)
  

  Aí como tínhamos memória de sobra, optei por colocar 196M, embora 150M já resolvesse.

   

   

  sort_buffer: Para webhosting, o padrão é 1M e não há santo que mude isso. Nem pense em mudar. Essa varíavel define o número máximo de linhas retornadas de uma query. Se a pessoa não achou o que precisa em 1000 linhas, por favor, tem que melhorar a consulta.

  join_buffer = O padrão dela deve ser 1MB para webhosting também, porém em casos de servidores com Interspire, isso pode aumentar e muito se o servidor for apenas para emkt. Porém, em raros casos maior que 4MB.!

  max_join_size= Essa variável não influencia muito no consumo de RAM, porém tal como o join_buffer deve ser usada com sabedoria, o padrão é 2MB para webhosting, com muito raras exceções.

  Bloco: Threads

  thread_concurrency: O número de cores * 2.

  thread_cache: Apenas1/3 do thread_concurrency

  Bloco: Table Cache

  table_cache: Essa é uma das variáveis mais importantes para o desempenho geral do sistema. Ela é responsável por definir quantas tabelas ficarão em cache para serem rapidamente acessadas quando solicitadas. Estão lembrados do nosso número total de tabelas? Colocar 90% delas aqui.

  No nosso exemplo isso fica em 2000.

  Bloco: Temp Table

  Aqui é simples,

  max_heap_table_size Essa varíavel vejo por aí valores gigantes, ela nada mais é do que o tamanho de uma tabela do tipo MEMORY. Pode ser usado um valor qualquer até 128M. A não ser que realmente você precise desse tipo de tabela (memory), do contrarário qualquer valor até 128M está excelente. No nosso exemplo usei 128M.

  tmp_table_size O tamanho do cache para as tabelas armazenadas. Eu geralmente tenho o hábito de usar o mesmo número que usei em table_cache se o tamanho total absoluto das tabelas (160) for menor que a 20% do número delas (2000), mas convertido para MB ou GB, no nosso caso ficou 2G. 

  Bloco: Query Cache

  Essa é a mais simples:

  query_cache_limit  O tamanho máximo de uma consulta para ser guardada em cache. 1MB é o valor padrão, apenas em poucos casos usaremos valores maiores que este. Vale a pena conferir essa linha do mysqltuner:

  [OK] Query cache efficiency: 66.9% (56K cached / 84K selects)
  

  Caso o percentual ali seja menor que 20%, aí sim você pode pensar em aumentar essa variável. Mesmo que ali ficasse vermelho, não teria problema para Webhosting.

  query_cache_size= E aqui o tamanho do nosso cache de queries. No nosso caso, vamos usar 512M, Nesse caso o número MÍNIMO de queries que vamos armezenar será 512. Encorajo você a testar suas configurações com  32,64,128(para vps com pouca ram) e em dedicados pode ser números maiores. A média para dedicados de webhosting com 4GB de ram é algo entre 256 a 768M. Acima dessa quantidade, só se realmente o servidor necessitar, como um servidor exclusivo para magento, por exemplo.

  Bloco: Timeouts

  As duas variáveis desse bloco tem a função de derrubar a conexão caso não haja interação ou o script pare de responder. Pode ser configurada para 15s + tempo execução script PHP (que por padrão é 30s). Logo, ambas levam 45

  Bloco: Connections

  max_connections: Como é fácil perceber, o número máximo de conexões mysql. Lembram do nosso uso de conexões que comentei que estava baixo? Então, vamos reduzi-las visto que estamos gastando memória a toa. OPAAA! Reduzir nada. Vamos aumentar. É sério. Já reduzimos muita memória ram, muita mesmo. Nada mais justo do que deixar o servidor pronto para uma carga maior de usuários. Logo, aumentei isso para 500, mesmo sabendo que serão por ora desnecessárias.

  max_user_connections: Aqui sim irás economizar pacas. Veja bem, ninguém no servidor deve sair abocanhando recursos demasiados, correto? Então pensemos: qual é o máximo de usuários onlines no mesmo minuto em um site que utilize mysql que cada cliente seu deve ter em média para o seu negócio ser sustentável? 25 é um número bom? Coloque esse número aqui.

  No nosso caso usei 30. Ou seja, cada usuário de banco de dados poderá abrir no máximo 30 conexões simultâneas. Acredite, as grandes empresas como Kinghost, Uol e Locaweb deixam esse valor padrão em 25. (Até a última vez que indaguei sobre isso).Um atendente experto da Kinghost me incentivou certa vez a criar vários usuários para um mesmo BD para burlar isso.

  Bloco: innodb

  Alguns servidores tem o innodb desabilitado, logo, esse bloco pode ser inteiro comentado acrescentando # na frente de cada linha. Para consultar se seu servidor tem o Innodb habilitado, procure nessa linha no Mysqltuner:

  [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
  

  Se houver um "+" na frente do InnoDB é porque está habilitado.

  innodb_buffer_pool_size: Para configuramos essa varíavel, basta olharmos essa linha do Mysqltuner:

  [OK] InnoDB data size / buffer pool: 79.3M/512.0M
  

  Pegue o primeiro número (79.3M) e acrescente uns 30%, ou caso o segundo número (512) seja muito maior que o primeiro, arbitre um valor qualquer aí. No nosso caso usei 128M.

  innodb_file_per_table: O padrão é 1 e não vamos mudar. Isso só seria maior se o servidor fosse para um BD com tabelas astronomicamente gigantes. O que nunca vi em webhosting.

  innodb_log_file_size: 5M. Isso é padrão, nem pense em alterar aqui, pois para alterar esse número é necessário recriar os logs das tabelas, que é bastante difícil. Só seria útil um valor maior se um BD recebe centenas a milhares de atualizações na mesma tabela a cada minuto.

  innodb_thread_concurrency: O número de cores / 2. Se for um servidor apenas para Interspire ou só magento, deixe igual o número de cores. No nosso caso ficou em 4.

  Nosso novo my.cnf:

  key_buffer_size=196M 
  sort_buffer=1M
  join_buffer=1M
  max_join_size=2M
  
  thread_concurrency=16
  thread_cache=5
  
  table_cache=2000
  
  interactive_timeout=45
  wait_timeout=45
  
  
  max_heap_table_size=128M
  tmp_table_size=2G
  
  
  query_cache_limit=1M
  query_cache_size=512M
  
  max_connections=500
  max_user_connections=30
  
  
  innodb_buffer_pool_size=128M
  innodb_file_per_table=1
  innodb_log_file_size=5M
  innodb_thread_concurrency=4
  
  
  
  

• Por fim...

  O novo resultado do Mysqltuner:

   >>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
   >>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
   >>  Run with '--help' for additional options and output filtering
  
  -------- General Statistics --------------------------------------------------
  [--] Skipped version check for MySQLTuner script
  [OK] Currently running supported MySQL version 5.1.68-cll
  [OK] Operating on 64-bit architecture
  
  -------- Storage Engine Statistics -------------------------------------------
  [--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
  [--] Data in MyISAM tables: 81M (Tables: 1461)
  [--] Data in InnoDB tables: 79M (Tables: 1202)
  [--] Data in MEMORY tables: 0B (Tables: 79)
  [!!] Total fragmented tables: 111
  
  -------- Security Recommendations  -------------------------------------------
  [OK] All database users have passwords assigned
  
  -------- Performance Metrics -------------------------------------------------
  [--] Up for: 4m 36s (6K q [21.913 qps], 123 conn, TX: 21M, RX: 1M)
  [--] Reads / Writes: 86% / 14%
  [--] Total buffers: 966.0M global + 2.6M per thread (500 max threads)
  [OK] Maximum possible memory usage: 2.2G (38% of installed RAM)
  [OK] Slow queries: 0% (1/6K)
  [OK] Highest usage of available connections: 1% (5/500)
  [OK] Key buffer size / total MyISAM indexes: 196.0M/25.2M
  [OK] Key buffer hit rate: 98.9% (263K cached / 2K reads)
  [OK] Query cache efficiency: 55.5% (2K cached / 4K selects)
  [OK] Query cache prunes per day: 0
  [OK] Sorts requiring temporary tables: 3% (16 temp sorts / 492 sorts)
  [!!] Joins performed without indexes: 51
  [!!] Temporary tables created on disk: 40% (349 on disk / 870 total)
  [OK] Thread cache hit rate: 95% (5 created / 123 connections)
  [OK] Table cache hit rate: 24% (2K open / 8K opened)
  [OK] Open file limit used: 50% (2K/4K)
  [OK] Table locks acquired immediately: 99% (4K immediate / 4K locks)
  [OK] InnoDB data size / buffer pool: 79.3M/128.0M
  
  -------- Recommendations -----------------------------------------------------
  General recommendations:
      Run OPTIMIZE TABLE to defragment tables for better performance
      MySQL started within last 24 hours - recommendations may be inaccurate
      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
  Variables to adjust:
      join_buffer_size (> 1.0M, or always use indexes with joins)
      tmp_table_size (> 2G)
      max_heap_table_size (> 128M)
  

  Veja algo interessante:

  1. nosso consumo de Ram despencou de 59% para 38%!!! UAL.

  2. Cada conexão usava antes 3,6MB, agora usam 2,6MB.

   

• Joããããããoooo! Ainda tem coisas para ajustar!

  Nem tudo o que está escrito é verdade.

  Que tal isto (lembrando que colocamos fizemos o reparo no dia 27/05):

  

  É isso senhores!

[Pedro Sodre]

Show de bola o tutorial, parabéns Grande João :P 

[chuvadenovembro]

hehehe vou ler com calma + tarde e certamente vou utilizar.

 

Obrigado pela dica :D

[MichelGomide]

Sem comentários.... maravilha de tutorial, muito bem explicado ! Parabéns

[redirect]

parabéns pelo tutorial, vou favoritá-lo aqui!

[Andre Juliano]

Parabéns pelo tuto João. Mais tarde irei usa-lo em meu servidor.  :)

[Peace]

João agradeço pelo seu tutorial, meu mysql já melhorou bastante porem ainda recebo erros mesmo tendo setado o valor correto.

Processador: 2xE5-2690 (32 threads)
Memoria: 64GB
Discos: 4x1TB 10K raid 10 por Hardware
Centos 6 x64 + Cpanel

my.cnf

[mysqld]
datadir="/var/lib/mysql"
socket="/var/lib/mysql/mysql.sock"
log-slow-queries
long_query_time=1
log-queries-not-using-indexes
read_buffer_size=8M
key_buffer_size=196M 
sort_buffer=1M
join_buffer=3M
max_join_size=2M
thread_concurrency=16
thread_cache=10
table_cache=3000
table_definition_cache=2000
interactive_timeout=45
wait_timeout=45
max_heap_table_size=128M
tmp_table_size=2G
query_cache_limit=1M
query_cache_size=512M
max_connections=300
max_user_connections=30
open_files_limit=8192
low_priority_updates=1
concurrent_insert=ALWAYS
 
[innodb]
innodb_buffer_pool_size=256M
innodb_file_per_table=1
innodb_log_file_size=5M
innodb_thread_concurrency=4

mysqltunner.pl

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.32-cll
[OK] Operating on 64-bit architecture
 
-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 779M (Tables: 1109)
[--] Data in InnoDB tables: 158M (Tables: 154)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 162
 
-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned
 
-------- Performance Metrics -------------------------------------------------
[--] Up for: 9m 31s (626K q [1K qps], 41K conn, TX: 6B, RX: 71M)
[--] Reads / Writes: 92% / 8%
[--] Total buffers: 980.0M global + 12.5M per thread (300 max threads)
[OK] Maximum possible memory usage: 4.6G (7% of installed RAM)
[!!] Slow queries: 10% (64K/626K)
[OK] Highest usage of available connections: 5% (17/300)
[OK] Key buffer size / total MyISAM indexes: 196.0M/380.4M
[OK] Key buffer hit rate: 99.9% (15M cached / 16K reads)
[OK] Query cache efficiency: 66.7% (323K cached / 485K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 1% (647 temp sorts / 44K sorts)
[!!] Joins performed without indexes: 3321
[OK] Temporary tables created on disk: 16% (6K on disk / 38K total)
[OK] Thread cache hit rate: 99% (306 created / 41K connections)
[OK] Table cache hit rate: 51% (1K open / 2K opened)
[OK] Open file limit used: 28% (2K/8K)
[OK] Table locks acquired immediately: 99% (226K immediate / 227K locks)
[!!] InnoDB data size / buffer pool: 158.6M/128.0M
 
-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Adjust your join queries to always utilize indexes
Variables to adjust:
    join_buffer_size (> 3.0M, or always use indexes with joins)
    innodb_buffer_pool_size (>= 158M)

[joaopaulo]

Hmmmm,. vc leu todo meu POST? Pois seu My.cnf esta quase 100% divergente de tudo que expliquei.

[abobre]

Vejam o meu my.cnf, só tem essas configurações ! Alguma coisa esta estranha.
 

root@srv [~]# cat /etc/my.cnf

[mysqld]innodb_file_per_table=1
open_files_limit=4442
root@srv [~]#

[Sales Mendes]

esse tutorial e perfeito, impossível usar ele no meu cliente que tem 1 cloud server gerenciado na locaweb

 

foda que ele ama a locaweb ai nem com reza brava ele sai dessa empresa dor de cabeça