Pesquisar na Comunidade

[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: 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 <[email protected]> >> 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!