Hoje, os desenvolvedores continuam a desenvolver e implantar aplicativos usando a arquitetura LAMP (Linux®, Apache, MySQL e PHP/Perl). No entanto, os administradores de servidores geralmente têm pouco controle sobre os próprios aplicativos porque outra pessoa os escreveu. Esta série de três partes discute vários problemas de configuração de servidor que podem afetar o desempenho do aplicativo. Este artigo, a terceira e última parte desta série, focará no ajuste da camada de banco de dados para máxima eficiência.
Em relação ao ajuste do MySQL,
existem três métodos para acelerar a velocidade de execução do servidor MySQL. A ordem de eficiência de baixa para alta é:
Substitua o hardware problemático. Ajuste as configurações do processo MySQL. Otimize a consulta.
A substituição de hardware problemático costuma ser nossa primeira consideração, principalmente porque os bancos de dados podem consumir muitos recursos. Mas esta solução só vai até certo ponto. Na verdade, muitas vezes você pode dobrar a velocidade da sua unidade central de processamento (CPU) ou disco e aumentar a memória de 4 a 8 vezes.
O segundo método é ajustar o servidor MySQL (também conhecido como mysqld). Ajustar este processo significa alocar memória apropriadamente e informar ao mysqld a que tipo de carga ele estará sujeito. Acelerar a operação do disco não é tão importante quanto reduzir o número de acessos ao disco necessários. Da mesma forma, garantir que o processo MySQL opere corretamente significa que ele gasta mais tempo atendendo consultas do que em tarefas em segundo plano, como trabalhar com tabelas de disco temporárias ou abrir e fechar arquivos. Ajustar o mysqld é o foco deste artigo.
A melhor abordagem é garantir que a consulta foi otimizada. Isso significa que os índices apropriados são aplicados à tabela e as consultas são escritas de uma forma que aproveita ao máximo os recursos do MySQL. Embora este artigo não cubra o ajuste de consultas (um tópico abordado em muitos livros), ele configura o mysqld para relatar consultas que podem precisar de ajuste.
Embora a ordem tenha sido atribuída a essas tarefas, você ainda precisa prestar atenção às configurações de hardware e mysqld para ajustar adequadamente a consulta. Está tudo bem se a máquina estiver lenta, já vi máquinas muito rápidas falharem devido à carga pesada ao executar consultas bem projetadas porque o mysqld estava ocupado com muito trabalho e não conseguia atender a consulta.
Registrando consultas lentas
Em um servidor SQL, as tabelas de dados são armazenadas em disco. Os índices fornecem ao servidor uma maneira de localizar linhas específicas de dados em uma tabela sem precisar pesquisar a tabela inteira. Quando toda a tabela deve ser pesquisada, isso é chamado de varredura de tabela. De modo geral, você pode querer obter apenas um subconjunto dos dados na tabela, portanto, uma varredura completa da tabela desperdiçará muita E/S de disco e, portanto, muito tempo. Este problema é agravado quando os dados devem ser unidos, porque múltiplas linhas de dados em ambos os lados da junção devem ser comparadas.
É claro que as varreduras de tabelas nem sempre causam problemas; às vezes é mais eficiente ler a tabela inteira do que selecionar um subconjunto dos dados (o planejador de consultas no processo do servidor é usado para tomar essas decisões). Se o índice for usado de forma ineficiente ou não puder ser usado, ele tornará as consultas mais lentas e esse problema se tornará mais significativo à medida que a carga no servidor e o tamanho da tabela aumentarem. As consultas que demoram mais para serem executadas do que um determinado intervalo de tempo são chamadas de consultas lentas.
Você pode configurar o mysqld para registrar essas consultas lentas em um log de consultas lentas apropriadamente nomeado. Os administradores revisarão esse log para ajudá-los a determinar quais partes do aplicativo requerem investigação mais aprofundada. A Listagem 1 mostra a configuração que precisa ser feita em my.cnf para ativar o log de consulta lento.
Listagem 1. Habilitando log de consulta lenta do MySQL
[mysqld]; habilitar o log de consulta lenta, padrão 10 segundoslog-slow-queries; consultas de log que levam mais de 5 segundoslong_query_time = 5 consultas de log que não usam índices, mesmo que demorem menos que long_query_time; consultas que não usam índices
Essas três configurações são usadas juntas para registrar consultas que levam mais de 5 segundos para serem executadas e não usam índices. Observe o aviso sobre log-queries-not-using-indexes: você deve estar usando MySQL 4.1 ou superior. Os logs de consulta lenta são salvos no diretório de dados MySQL e são denominados hostname-slow.log. Se desejar usar um nome ou caminho diferente, você pode usar log-slow-queries = /new/path/to/file em my.cnf para conseguir isso.
A melhor maneira de ler logs de consultas lentas é através do comando mysqldumpslow. Ao especificar o caminho para o arquivo de log, você pode ver uma lista ordenada de consultas lentas, juntamente com o número de vezes que elas ocorrem no arquivo de log. Um recurso muito útil é que o mysqldumpslow remove quaisquer dados especificados pelo usuário antes de comparar os resultados, de modo que diferentes chamadas para a mesma consulta são contadas como uma só, o que pode ajudar a identificar a consulta que requer mais trabalho;
Consultas em cache
Muitos aplicativos LAMP dependem muito de bancos de dados, mas executam as mesmas consultas repetidamente. Cada vez que uma consulta é executada, o banco de dados deve realizar o mesmo trabalho – analisar a consulta, determinar como executá-la, carregar as informações do disco e retornar os resultados ao cliente. O MySQL possui um recurso chamado cache de consulta, que armazena os resultados da consulta (que serão usados posteriormente) na memória. Em muitos casos, isso melhorará muito o desempenho. O problema, porém, é que o cache de consulta está desabilitado por padrão.
Adicione query_cache_size = 32M a /etc/my.conf para ativar um cache de consulta de 32 MB.
Monitorando o Cache de Consultas
Depois de habilitar o cache de consultas, é importante entender se ele está sendo utilizado de forma eficaz. O MySQL possui diversas variáveis que você pode observar para entender o que está acontecendo no cache. A Listagem 2 mostra o status do cache.
Listagem 2. Exibindo estatísticas de cache de consulta
mysql> MOSTRAR STATUS LIKE 'qcache%';+----------------------------+------------+ |Nome_da_variável |+------------------------+------------+| Qcache_free_blocks | || Qcache_free_memory || Qcache_hits || Qcache_total_blocks || ------ ---+----------------+8 linhas no conjunto (0,00 seg)
A explicação desses itens está apresentada na Tabela 1.
Tabela 1. Descrição do nome da variável da variável de cache de consulta MySQL
Qcache_free_blocks O número de blocos de memória adjacentes no cache. Um número grande indica que pode haver fragmentos. FLUSH QUERY CACHE desfragmenta o cache para obter um bloco livre.
Qcache_free_memory Memória livre no cache.
Qcache_hits é incrementado cada vez que uma consulta atinge o cache.
Qcache_inserts é incrementado cada vez que uma consulta é inserida. A taxa de acertos é o número de acertos dividido pelo número de inserções, subtraia esse valor de 1 para obter a taxa de acertos. No exemplo acima, aproximadamente 87% das consultas atingiram o cache.
Qcache_lowmem_prunes O número de vezes que o cache ficou sem memória e teve que ser limpo para liberar espaço para mais consultas. Esse número é melhor visualizado durante um longo período de tempo; se o número estiver aumentando, pode indicar fragmentação grave ou falta de memória. (Os free_blocks e free_memory acima podem dizer qual é o caso).
Qcache_not_cached O número de consultas que não são adequadas para armazenamento em cache, geralmente porque não são instruções SELECT.
Qcache_queries_in_cache O número de consultas (e respostas) atualmente armazenadas em cache.
Qcache_total_blocks Número de blocos no cache.
Muitas vezes, a diferença pode ser vista exibindo essas variáveis com alguns segundos de intervalo, o que pode ajudar a determinar se o cache está sendo usado de forma eficiente. Executar FLUSH STATUS pode redefinir alguns contadores, o que pode ser muito útil se o servidor estiver em execução há algum tempo.
É muito tentador usar um cache de consulta muito grande e esperar armazenar tudo em cache. Como o mysqld deve realizar manutenção no cache, como executar a remoção quando a memória fica baixa, o servidor pode ficar atolado tentando gerenciar o cache. Via de regra, se o FLUSH QUERY CACHE demorar muito, o cache é muito grande.
Impondo Limites
Você pode impor limites no mysqld para garantir que a carga do sistema não cause esgotamento de recursos. A Listagem 3 mostra algumas configurações importantes relacionadas a recursos em my.cnf.
Listagem 3. Configurações de recursos MySQL
set-variable=max_connections=500set-variable=wait_timeout=10max_connect_errors = 100
O número máximo de conexões é gerenciado na primeira linha. Semelhante ao MaxClients no Apache, a ideia é garantir que apenas o número de conexões permitidas pelo serviço seja feito. Para determinar o número máximo de conexões atualmente estabelecidas no servidor, execute SHOW STATUS LIKE 'max_used_connections'.
A linha 2 diz ao mysqld para encerrar qualquer conexão que tenha estado inativa por mais de 10 segundos. Em um aplicativo LAMP, o tempo que leva para se conectar ao banco de dados geralmente é o tempo que o servidor web leva para processar a solicitação. Às vezes, se a carga for muito pesada, a conexão irá travar e ocupar espaço na tabela de conexão. Se você tiver vários usuários interativos ou usar conexões persistentes com o banco de dados, não é aconselhável definir esse valor para um valor mais baixo!
A última linha é um método seguro. Se um host tiver problemas para se conectar ao servidor e tentar várias vezes antes de desistir, o host será bloqueado e não poderá ser executado até depois de FLUSH HOSTS. Por padrão, 10 falhas são suficientes para causar um bloqueio. Alterar esse valor para 100 dará ao servidor tempo suficiente para se recuperar do problema. Se a conexão não puder ser estabelecida após 100 tentativas, usar um valor mais alto não ajudará muito e poderá não conectar.
Buffers e Cache
MySQL suporta mais de 100 configurações ajustáveis, mas felizmente, dominar algumas irá satisfazer a maioria das necessidades; Para encontrar os valores corretos para essas configurações, você pode visualizar as variáveis de status através do comando SHOW STATUS, que pode determinar se o mysqld está operando conforme esperado. A memória alocada para buffers e caches não pode exceder a memória disponível no sistema; portanto, o ajuste geralmente requer algum comprometimento.
As configurações ajustáveis do MySQL podem ser aplicadas a todo o processo mysqld ou a sessões individuais do cliente.
Configurações do lado do servidor
Cada tabela pode ser representada como um arquivo em disco, que deve ser aberto primeiro e depois lido. Para acelerar o processo de leitura de dados de arquivos, o mysqld armazena em cache esses arquivos abertos até um número máximo especificado por table_cache em /etc/mysqld.conf. A Listagem 4 mostra uma maneira de exibir atividades relacionadas à abertura de uma tabela.
Listagem 4. Exibindo atividades que abrem tabelas
mysql> SHOW STATUS LIKE 'open%tables';+---------------+-------+| Variable_name |+------------ -------+-------+| Tabelas_abertas || Tabelas_abertas 195 |+---------------+------- +2 linhas no conjunto (0,00 seg)
A Listagem 4 mostra que existem atualmente 5.000 tabelas abertas e 195 tabelas precisam ser abertas porque não há descritores de arquivo disponíveis no cache (como as estatísticas foram apagadas anteriormente, pode haver apenas 5.000 tabelas abertas em 195 casos de registro abertos). . Se Opened_tables aumentar rapidamente com a nova execução do comando SHOW STATUS, isso indica que a taxa de acertos do cache é insuficiente. Se Open_tables for muito menor que a configuração table_cache, o valor será muito grande (mas ter espaço para crescer nunca é uma coisa ruim). Por exemplo, use table_cache = 5000 para ajustar o cache da tabela.
Semelhante ao cache de tabela, também existe um cache para threads. mysqld gera threads conforme necessário ao receber conexões. Em um servidor ocupado onde as conexões mudam rapidamente, armazenar threads em cache para uso posterior pode acelerar a conexão inicial.
A Listagem 5 mostra como determinar se threads suficientes estão armazenados em cache.
Listagem 5. Exibindo estatísticas de uso de thread
mysql> SHOW STATUS LIKE 'threads%';+-------------------+--------+| Nome_da_variável |+---- ---------------+-------+| Threads_em cache || Threads_conectados || Threads_criados || ---------------+------------+4 linhas no conjunto (0,00 seg)
O valor importante aqui é Threads_created, este valor é incrementado toda vez que o mysqld precisa criar um novo thread. Se esse número aumentar rapidamente ao executar comandos SHOW STATUS sucessivos, você deverá tentar aumentar o cache do thread. Por exemplo, você pode usar thread_cache = 40 em my.cnf para conseguir isso.
O buffer de chave contém o bloco de índice da tabela MyISAM. Idealmente, as solicitações para esses blocos deveriam vir da memória e não do disco. A Listagem 6 mostra como determinar quantos blocos foram lidos do disco e quantos foram lidos da memória.
Listagem 6. Determinando a eficiência das palavras-chave
mysql> mostra status como '%key_read%';+-------------------+-----------+| Variable_name |+ ------------------+----------+| Key_read_requests || Key_reads |+------- -----------+-----------+2 linhas no conjunto (0,00 seg)
Key_reads representa o número de solicitações que chegam ao disco e Key_read_requests é o número total. A taxa de faltas é o número de solicitações de leitura que atingem o disco dividido pelo número total de solicitações de leitura - nesse caso, aproximadamente 0,6 faltas na memória para cada 1.000 solicitações. Se o número de ocorrências no disco exceder 1 por 1.000 solicitações, considere aumentar o buffer de palavras-chave. Por exemplo, key_buffer = 384M definirá o buffer para 384 MB.
As tabelas temporárias podem ser usadas em consultas mais avançadas onde os dados devem ser salvos em uma tabela temporária antes do processamento adicional (como uma cláusula GROUP BY, de preferência, a tabela temporária é criada na memória); Mas se a tabela temporária ficar muito grande, ela precisará ser gravada no disco. A Listagem 7 fornece estatísticas relacionadas à criação de tabelas temporárias.
Listagem 7. Determinando o uso de tabelas temporárias
mysql> SHOW STATUS LIKE 'created_tmp%';+--------------------------+-------+| Variable_name | |+-------------+-------+| Criado_tmp_tables || Criado_tmp_tables || | +---------------------+-------+3 linhas no conjunto (0,00 seg)
Criado_tmp_tables será aumentado cada vez que uma tabela temporária for usada; Criado_tmp_disk_tables também será aumentado para tabelas baseadas em disco. Não existem regras rígidas para esta relação, pois depende da consulta envolvida. Observar Created_tmp_disk_tables ao longo do tempo mostrará a proporção de tabelas de disco criadas e você poderá determinar a eficiência de sua configuração. Tanto tmp_table_size quanto max_heap_table_size controlam o tamanho máximo das tabelas temporárias, portanto, certifique-se de que ambos os valores estejam definidos em my.cnf.
Configurações por sessão
As configurações a seguir são específicas para cada sessão. Tenha muito cuidado ao definir estes números porque quando multiplicados pelo número de conexões que podem existir, estas opções representam uma grande quantidade de memória! Você pode modificar esses números em uma sessão por meio de código ou modificar essas configurações em my.cnf para todas as sessões.
Quando o MySQL precisa classificar, ele aloca um buffer de classificação para armazenar as linhas de dados à medida que são lidas do disco. Se os dados a serem classificados forem muito grandes, eles deverão ser salvos em um arquivo temporário no disco e classificados novamente. Se a variável de status sort_merge_passes for grande, isso indica atividade do disco. A Listagem 8 mostra algumas informações do contador de status relacionadas à classificação.
Listagem 8. Exibindo estatísticas de classificação
mysql> SHOW STATUS LIKE "sort%";+-------------------+---------+| Nome_da_variável |+--- --+--------+| Sort_merge_passes || Sort_range || Sort_rows || ------------------+---------+4 linhas no conjunto (0,00 seg)
Se sort_merge_passes for grande, significa que você precisa prestar atenção em sort_buffer_size. Por exemplo, sort_buffer_size = 4M define o buffer de classificação para 4 MB.
O MySQL também aloca alguma memória para ler a tabela. Idealmente, um índice fornece informações suficientes para ler apenas as linhas necessárias, mas às vezes uma consulta (mal projetada ou devido à natureza dos dados) precisa ler uma grande quantidade de dados da tabela. Para entender esse comportamento, você precisa saber quantas instruções SELECT foram executadas e quantas vezes a próxima linha de dados da tabela precisou ser lida (em vez de acessada diretamente por meio do índice). O comando para obter esta funcionalidade é mostrado na Listagem 9.
Listagem 9. Determinando a taxa de varredura da tabela
mysql> SHOW STATUS LIKE "com_select";+---------------+--------+| Variable_name |+------------ ------+--------+| Com_select |+----------+--------+1 linha no conjunto (0,00 seg) mysql> SHOW STATUS LIKE "handler_read_rnd_next";+-----------------------+----------- +| Valor_da_variável | |+-----------------------+----------+| Handler_read_rnd_next |+- ----- ------------------+----------+1 linha no conjunto (0,00 seg)
Handler_read_rnd_next / Com_select resulta em uma proporção de varredura de tabela - neste caso 521:1. Se o valor exceder 4000, você deve verificar read_buffer_size, por exemplo read_buffer_size = 4M. Se esse número ultrapassar 8 milhões, é hora de discutir o ajuste dessas consultas com os desenvolvedores!
3 Ferramentas Essenciais
Embora o comando SHOW STATUS possa ser muito útil para entender uma configuração específica, você também precisará de algumas ferramentas para interpretar as grandes quantidades de dados fornecidos pelo mysqld. Existem três ferramentas que considero essenciais; você pode encontrar links para elas na seção Recursos.
A maioria dos administradores de sistema está familiarizada com o comando top, que fornece uma visão continuamente atualizada da CPU e da memória consumida pelas tarefas. mytop emula top; fornece uma visão de todos os clientes conectados e das consultas que eles estão executando. mytop também fornece dados históricos e ao vivo sobre buffer de palavras-chave e eficiência do cache de consulta, bem como estatísticas sobre consultas em execução. Esta é uma ferramenta útil para ver o que está acontecendo em seu sistema (digamos, em 10 segundos), você pode obter uma visão das informações de integridade do servidor e mostrar quaisquer conexões que estejam causando problemas.
mysqlard é um daemon conectado ao servidor MySQL, responsável por coletar dados a cada 5 minutos e armazená-los em um banco de dados Round Robin em segundo plano. Há uma página da Web que exibe dados como uso de cache de tabela, eficiência de palavras-chave, clientes conectados e uso de tabela temporária. Embora mytop forneça um instantâneo das informações de integridade do servidor, o mysqlard fornece informações de integridade de longo prazo. Como bônus, o mysqlard usa algumas das informações coletadas para dar algumas sugestões sobre como ajustar o servidor.
Outra ferramenta para coletar informações SHOW STATUS é o mysqlreport. Seus relatórios são muito mais complexos que o mysqlard porque cada aspecto do servidor precisa ser analisado. Esta é uma ótima ferramenta para ajustar seu servidor porque realiza cálculos apropriados em variáveis de estado para ajudar a determinar quais problemas precisam ser corrigidos.
Conclusão
Este artigo apresentou alguns conhecimentos básicos sobre ajuste do MySQL e concluiu esta série de 3 partes sobre ajuste de componentes LAMP. O ajuste envolve em grande parte a compreensão de como os componentes funcionam, determinando se estão funcionando corretamente, fazendo alguns ajustes e reavaliando. Cada componente – Linux, Apache, PHP ou MySQL – possui vários requisitos. Compreender cada componente individualmente pode ajudar a reduzir gargalos que podem retardar sua aplicação.