Nota: Este artigo foi publicado originalmente em 2014. Alguns passos, comandos ou versões de software podem ter mudado. Consulte a documentação atual do MySQL para obter as informações mais recentes.
Neste guia passo a passo, você aprenderá a instalar e usar o MySQLTuner para otimizar a configuração / desempenho do MySQL. O MySQL é um sistema de gerenciamento de banco de dados relacional de código aberto amplamente utilizado em aplicações web, particularmente com PHP e WordPress.
Como: Instalar e usar o MySQLTuner para Otimizar a configuração / desempenho do MySQL
Estou escrevendo este post principalmente porque existe, na minha opinião, uma maneira muito melhor de instalar o MySQLTuner na sua máquina do que o usual apt-get install MySQLTuner. Como muitos de vocês provavelmente já sabem, o MySQLTuner é uma ferramenta bem conhecida e amplamente utilizada. Ele fornece um resumo muito bom de como sua configuração está performando e aponta onde você pode fazer melhorias para obter um desempenho ainda melhor do seu servidor de banco de dados. Eu geralmente recomendo que as pessoas instalem o Percona (que é um substituto direto para o MySQL). O Percona é baseado no código do MySQL, mas é mais interessante (eles adicionam mais recursos de desempenho que você só encontraria na versão comercial do MySQL e fazem um trabalho melhor em se manter atualizados do que a Oracle). Por causa disso, eu até escrevi no artigo: (</databases/mysql/install-mysql-ubuntu-server/> “How to: Install MySQL in an Ubuntu Server”) instruções sobre como instalar o Percona em vez do MySQL no final como uma alternativa sugerida.
De qualquer forma, como mencionei anteriormente, a principal razão para este artigo é focar em uma alternativa ao simples apt-get install MySQLTuner. E por que isso? Bem, vamos ver o que executar esse comando tem a dizer em uma das minhas máquinas servidoras:
apt-get install MySQLTuner Reading package lists… Done Building dependency tree Reading state information… Done The following packages were automatically installed and are no longer required : libperconaserverclient18.1 percona-server-common-5.6 Use ‘apt-get autoremove’ to remove them. The following extra packages will be installed : libhtml-template-perl libterm-readkey-perl mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 Suggested packages: libipc-sharedcache-perl tinyca mailx The following packages will be REMOVED : percona-server-client-5.6 percona-server-server-5.6 The following NEW packages will be installed: libhtml-template-perl libterm-readkey-perl mysql-client-5.5 mysql-client-core-5.5 mysql-common mysql-server mysql-server-5.5 mysql-server-core-5.5 mysqltuner 0 upgraded, 9 newly installed, 2 to remove and 0 not upgraded. Need to get 24.8 MB/24.8 MB of archives. After this operation, 20.4 MB disk space will be freed. Do you want to continue ? N Abort.
Como destacado em negrito, o instalador tentará remover o servidor Percona, instalará o mysql no lugar e uma série de outras bibliotecas. Na maioria das vezes isso é aceitável, mas muitas vezes você descobrirá que só quer a ferramenta e está satisfeito com o Percona, assim como eu. Então, o que fazer?
Felizmente, o MySQLTuner é uma ferramenta escrita em Perl e requer apenas bibliotecas de cliente para se conectar ao MySQL, o que não é um grande problema (o cliente Percona está disponível). Simplesmente acesse: https://github.com/major/MySQLTuner-perl e baixe o arquivo mysqltuner.pl para obter a versão mais recente (faça um wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/mysqltuner.pl). Depois de baixar o script, tudo o que você precisa fazer é ir ao local onde o salvou e executar perl mysqltuner.pl e pronto!
Agora, se você está procurando algumas dicas sobre como usar esta ferramenta e mudanças recomendadas, aqui estão alguns destaques:
I. Se você está usando apenas PHP (pense no WordPress), então definitivamente altere o wait_timeout.
Por padrão, ele é de 8 horas, mas se por algum motivo a aplicação não fechar a conexão, ela pode ficar lá parada por até 8 horas (então você começa a ter muitas conexões abertas em estado de espera que consomem recursos).
wait_timeout = 30 #default is 28800
Você pode deixar o interactive timeout como está. Este geralmente é um valor mais alto que o wait_timeout e, como você deve imaginar, requer interação para permanecer em estado de espera.
II. Altere o número máximo de conexões.
Na minha instalação era 500! Cada conexão usa um pouco de recursos também, mesmo que não esteja em uso. O MySQLTuner pode ajudá-lo a ver ao longo do tempo qual é o número máximo de conexões que você acaba usando para que você possa ajustar de acordo. Por exemplo, se você está usando PHP-FPM, provavelmente não usará mais conexões do que o número máximo de processos filhos que você tem. Considere isso e examine a saída do MySQLTuner para decidir qual é o melhor número. Deixe seu número máximo de conexões (depois de ter o MySQL rodando por pelo menos 24 horas, preferencialmente 7 dias para obter métricas de uma semana inteira) em cerca de 80% (então aumente o uso máximo em 25% ao configurar esta variável)
max_connections = 50 #default is 500
III. Execute o MySQLTuner
Vamos examinar alguma saída:
perl mysqltuner.pl >> MySQLTuner 1.3.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 -------- Security Recommendations ------------------------------------------- All database users have passwords assigned -------- Performance Metrics ------------------------------------------------- Up for: 4h 51m 32s (354K q , 3K conn, TX: 2B, RX: 45M) Reads / Writes: 90% / 10% Total buffers: 420.0M global + 1.1M per thread (26 max threads) Maximum possible memory usage: 447.6M (14% of installed RAM) Slow queries: 0% (56/354K) Highest usage of available connections: 42% (11/26) Key buffer size / total MyISAM indexes: 16.0M/100.0K Key buffer hit rate: 99.0% (100 cached / 1 reads) Query cache efficiency: 78.9% (260K cached / 330K selects) Query cache prunes per day: 0 Sorts requiring temporary tables: 0% (0 temp sorts / 6K sorts) Temporary tables created on disk: 36% (3K on disk / 8K total) Thread cache hit rate: 99% (11 created / 3K connections) Table cache hit rate: 28% (363 open / 1K opened) Open file limit used: 0% (48/65K) Table locks acquired immediately: 100% (85K immediate / 85K locks) InnoDB buffer pool / data size: 128.0M/49.3M InnoDB log waits: 0 -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance MySQL started within last 24 hours - recommendations may be inaccurate When making adjustments, make tmp_table_size/max_heap_table_size equal Reduce your SELECT DISTINCT queries without LIMIT clauses Variables to adjust: tmp_table_size (> 30M) max_heap_table_size (> 30M)
Como mencionei anteriormente, você quer executar isso contra seu servidor pelo menos 24 horas depois de ter iniciado o MySQL para que as recomendações sejam mais precisas (eu recomendaria executar após 7 dias, 30 dias, etc. novamente, pois você pode obter mais insights quanto mais uso o servidor recebe). Mas por favor não ache que não obterá nenhuma informação importante se executar, digamos, uma hora ou imediatamente após iniciar. Imediatamente após iniciar, você pode ver se estragou algo com a memória. Enquanto que após uma hora pode ter informações valiosas se você está apenas começando a otimizar sua configuração. Por exemplo, aqui eu já cheguei a usar todo o espaço para minhas tabelas temporárias, então esse erro provavelmente não vai desaparecer independentemente de quanto tempo eu execute o MySQL. Com o tempo, você aprenderá quais recomendações deve esperar para implementar e quais pode implementar imediatamente.
Fazendo bom uso dessa recomendação, note também a anterior: “Ao fazer ajustes, faça tmp_table_size e max_heap_table_size iguais”.
Todas as alterações no seu /etc/mysql/my.cnf geralmente requerem uma reinicialização do serviço. Eu tentei o parâmetro reload, mas o mysqltuner parece não notar a mudança.
Meu conselho final é focar nas métricas de desempenho e na seção de recomendações:
- Se você está compartilhando sua máquina servidora com outras aplicações, tente manter o uso máximo possível de memória dentro das suas estimativas de quanta RAM você está disposto a dedicar ao MySQL
- Como mencionei, certifique-se de que seu Número Máximo de Conexões esteja em cerca de 80% das disponíveis. Execute o MySQL por um tempo e adicione 25% mais conexões ao máximo que você obtiver.
- Configure as Slow Queries. Indique quantos segundos constituem uma consulta lenta e veja se você obtém poucas ou muitas.
- Certifique-se de que todos os buffers tenham RAM suficiente.
- Configure seu swappiness para um valor baixo. Fazer swap de RAM tira um pouco de desempenho de um servidor MySQL. Lembre-se de que você está usando a maioria desses buffers / cache para evitar ter que ir ao disco para obter informações. Para mais informações sobre como configurar a agressividade do swap, veja este post: (</linux/ubuntu/configure-swappiness-ubuntu/> “How to: Configure Swappiness in Ubuntu”)
- Mantenha a calma e continue melhorando sua configuração ;). Você descobrirá que, diretamente da instalação, nenhum servidor é igual. Com o tempo e olhando as métricas, você verá se predominantemente escreve ou lê, sua atividade e necessidades de memória, e dimensionará melhor o servidor e ajustará os parâmetros para a carga que está recebendo. O Percona tem uma ferramenta para ajudá-lo a definir alguns valores iniciais em: https://tools.percona.com/wizard. Tenha em mente que você deve entendê-los e aplicá-los talvez individualmente em vez de simplesmente copiar e colar tudo. Se seu servidor foi usado antes, algumas das novas configurações podem fazê-lo travar, pois ele criou tabelas com configurações diferentes. Além disso, usar alguns parâmetros pode quebrar aplicações como o WordPress.
(http://img.zemanta.com/zemified_h.png?x-id=c89eacd2-c247-4ece-ba8a-028aabcefe70)](http://www.zemanta.com/?px “Enhanced by Zemanta”)
Resumo
Você aprendeu com sucesso a instalar e usar o MySQLTuner para otimizar a configuração / desempenho do MySQL. Se encontrar algum problema, verifique novamente os pré-requisitos e certifique-se de que seu ambiente MySQL está configurado corretamente.