Ajustando variáveis ​​de sistema do MySQL para alto desempenho

Para a maioria dos desenvolvedores de aplicativos, o banco de dados é um altar de deuses demoníacos que é melhor deixar sem abordagem. Mas não precisa ser assim!


Sendo outras coisas iguais, o nível de conforto que um desenvolvedor possui com o banco de dados subjacente define seu nível de antiguidade. Pouco banco de dados e pouca experiência em codificação = desenvolvedor júnior; pouco banco de dados e boa experiência em codificação = desenvolvedor de nível intermediário; bom banco de dados e boa experiência em codificação = desenvolvedor sênior.

É uma realidade dura que até mesmo trabalha com 6-8 anos de luta para explicar os meandros do otimizador de consultas e prefere olhar para o céu quando perguntado sobre ajuste de banco de dados.

Por quê?

Surpreendentemente, o motivo não é a preguiça (embora em parte seja).

O ponto é que os bancos de dados são uma força própria para enfrentar. Mesmo tradicionalmente, quando havia apenas os tipos relacionais de bancos de dados para lidar, dominá-los era um milagre e um plano de carreira em si; hoje em dia, temos tantos tipos de bancos de dados que é impossível esperar que uma única alma mortal domine tudo.

Dito isso, há uma boa chance de você ainda estar satisfeito com os bancos de dados relacionais ou fazer parte de uma equipe que possui um produto em execução em um banco de dados relacional satisfatoriamente por muito, muito tempo. E em nove dos dez casos, você está no MySQL (ou MariaDB). Nesses casos, mergulhar um pouco mais fundo traz benefícios enormes em aumentar o desempenho do aplicativo e vale a pena aprender.

Curioso? Vamos mergulhar!

Não está curioso? Bem, mergulhe de qualquer maneira, porque sua carreira depende disso! ��

Otimize o cache de consultas do MySQL

Quase toda otimização no campo dos computadores se resume ao cache. Por um lado, a CPU mantém vários níveis de cache para acelerar seus cálculos e, por outro, os aplicativos da Web fazem uso agressivo de soluções de cache como o Redis para fornecer resultados pré-computados aos usuários, em vez de acessar o banco de dados todas as vezes..

Mas ei, mesmo o pobre banco de dados MySQL tem seu próprio cache de consultas! Ou seja, toda vez que você consulta algo e os dados ainda estão obsoletos, o MySQL exibirá esses resultados em cache em vez de executar a consulta novamente, tornando o aplicativo ridiculamente mais rápido.

Você pode verificar se possui um cache de consulta disponível (observe, disponível, não ativado) em seu banco de dados executando esta consulta no console do banco de dados:

MariaDB [(nenhum)]> MOSTRAR VARIÁVEIS COMO ‘have_query_cache’;
+——————+——-+
| Nome da variável | Valor
+——————+——-+
| have_query_cache | SIM
+——————+——-+

Portanto, você pode ver que estou executando o MariaDB e que tenho o cache de consultas disponível para ser ativado. É extremamente improvável que você o desligue se estiver usando uma instalação padrão do MySQL.

Agora vamos ver se o cache de consultas está realmente ativado:

MariaDB [(nenhum)]> MOSTRAR VARIÁVEIS COMO ‘query_cache_type’;
+——————+——-+
| Nome da variável | Valor
+——————+——-+
| query_cache_type | ON |
+——————+——-+

Sim eu quero. Mas, caso contrário, você pode ativá-lo dizendo:

MariaDB [(nenhum)]> SET GLOBAL query_cache_type = ON;

Curiosamente, essa variável também aceita um terceiro valor que indica “sob demanda”, o que significa que o MySQL armazenará em cache apenas as consultas que solicitamos, mas não abordaremos isso aqui.

Com isso, você colocou o cache de consultas e deu o primeiro passo para uma configuração mais robusta do MySQL! Digo a primeira etapa porque, embora ativá-la seja uma grande melhoria, precisamos ajustar o cache de consultas para se adequar à nossa configuração. Então, vamos aprender a fazer isso.

A outra variável de interesse aqui é query_cache_size, cuja função é auto-explicativa:

MariaDB [(nenhum)]> MOSTRAR VARIÁVEIS COMO ‘query_cache_size’;
+——————+———-+
| Nome da variável | Valor
+——————+———-+
| query_cache_size | 16777216
+——————+———-+

Então, eu tenho um cache de consulta de cerca de 16 MB de tamanho. Observe que, mesmo que o cache da consulta esteja ativado, mas esse tamanho seja zero, o cache estará efetivamente desativado. É por isso que verificar apenas uma variável não é suficiente. Agora, você deve definir um tamanho de cache de consulta, mas quanto deve ser? Primeiro, observe que o próprio recurso de cache de consulta precisará de 4 KB para armazenar seus metadados, portanto, o que você selecionar deve estar acima desse.

Digamos que você defina o tamanho do cache da consulta como 500 KB:

MariaDB [(nenhum)]> SET GLOBAL query_cache_size = 500000;

Está fazendo isso o suficiente? Bem, não, porque o desempenho do mecanismo de consulta depende de mais algumas coisas:

  • Primeiro, a variável query_cache_size deve ser grande o suficiente para manter o resultado das suas consultas. Se for muito pequeno, nada será armazenado em cache.
  • Em segundo lugar, se query_cache_size estiver definido como um número muito alto, haverá dois tipos de problemas: 1) O mecanismo precisará executar um trabalho extra para armazenar e localizar resultados de consulta nessa área de memória massiva. 2) Se a maioria das consultas resultar em tamanhos muito menores, o cache será fragmentado e os benefícios do uso de um cache serão perdidos.

Como você sabe que o cache está ficando fragmentado? Verifique o número total de blocos no cache assim:

MariaDB [(nenhum)]> mostre status como ‘Qcache_total_blocks’;
+———————+——-+
| Nome da variável | Valor
+———————+——-+
| Qcache_total_blocks | 33
+———————+——-+

Se o número for muito alto, o cache será fragmentado e precisará ser liberado.

Portanto, para evitar esses problemas, verifique se o tamanho do query_cache_size foi escolhido com sabedoria. Se você se sente frustrado por eu não ter deixado você com um número concreto aqui, receio que seja assim que as coisas são quando você passa pelo desenvolvimento e entra na engenharia. Você deve analisar o aplicativo em execução e ver quais são os tamanhos das consultas para os resultados importantes da consulta e definir esse número. E mesmo assim você pode acabar cometendo um erro. ��

Encadeamento, conjuntos de encadeamentos, espera e tempos limite

Esta é provavelmente a parte mais interessante de como o MySQL funciona e fazer o correto significa tornar seu aplicativo várias vezes mais rápido!

Rosqueamento

O MySQL é um servidor multiencadeado. Isso significa que, toda vez que há uma nova conexão com o servidor MySQL, ele abre um novo thread com os dados da conexão e passa um identificador para o cliente (caso você esteja se perguntando o que é um thread, consulte isto) O cliente envia todas as consultas sobre esse thread e recebe resultados. Isso nos leva a fazer uma pergunta natural: quantos threads o MySQL pode girar? A resposta está na próxima seção.

Grupo de discussão

Nenhum programa em um sistema de computador pode abrir quantos threads desejar. O motivo é duplo: 1) Threads custam memória (RAM) e o sistema operacional não permite que você fique louco e consome tudo isso. 2) Gerenciar, digamos, um milhão de threads é uma tarefa enorme por si só, e se o servidor MySQL pudesse criar tantos threads, morreria tentando lidar com a sobrecarga.

Para evitar esses problemas, o MySQL vem com um pool de threads – um número fixo de threads que fazem parte de um pool no início. Novas solicitações de conexão fazem com que o MySQL escolha um desses threads e retorne os dados da conexão e, se todos os threads forem usados, novas conexões serão naturalmente recusadas. Vamos ver o tamanho do pool de threads:

ariaDB [(nenhum)]> mostre variáveis ​​como ‘thread_pool_size’;
+——————+——-+
| Nome da variável | Valor
+——————+——-+
| thread_pool_size | 4
+——————+——-+

Portanto, minha máquina permite no máximo quatro conexões ao mesmo tempo. É interessante notar que o número 4 vem do fato de eu ter um processador de quatro núcleos, o que significa que meu computador pode executar apenas quatro tarefas paralelas por vez (estou falando de tarefas verdadeiramente paralelas aqui, não de concorrentes). Idealmente, esse é o limite que deve direcionar o valor de thread_pool_size, mas em máquinas mais robustas aumentá-lo até certo ponto. Se você não quiser que todas as novas conexões esperem e não sofra algum impacto no desempenho (novamente, essa é uma área que você pode julgar melhor com base no desempenho do seu aplicativo sob carga), aumentar até 8 pode ser uma boa idéia.

No entanto, defini-lo além de 16 é uma péssima idéia, a menos que você tenha uma máquina de 32 núcleos, pois o desempenho diminui significativamente. A toca dos pools de threads no MySQL é profunda, mas se você estiver interessado, aqui está uma discussão mais detalhada.

Espera e tempos limite

Depois que um segmento for criado e anexado a um cliente, será um desperdício de recursos se o cliente não enviar consultas pelos próximos segundos (ou minutos). Como resultado, o MySQL encerra uma conexão após um período de inatividade. Isso é controlado pela variável wait_timeout:

MariaDB [(nenhum)]> mostra variáveis ​​como ‘wait%’;
+—————+——-+
| Nome da variável | Valor
+—————+——-+
| wait_timeout | 28800
+—————+——-+

O valor resultante é em segundos. Então, sim, por padrão, o MySQL está configurado para aguardar mais de 8 horas antes de cortar o cabo! Isso pode ser bom se você tiver consultas de longa duração e realmente quiser esperar por elas (mas mesmo assim, oito horas é um absurdo!), Mas terrível na maioria dos casos. Quando uma consulta é executada, esse valor é definido como 0 (ou seja, para sempre), mas geralmente deve ser definido como um valor muito baixo (5 segundos, por exemplo, ou talvez até menos) para liberar a conexão para outros processos..

Ajustando Tabelas Temporárias

Vamos começar com o que são tabelas temporárias no MySQL.

Suponha que tenhamos um MySQL que se parece estruturalmente com isso: TABELA A UNION (TABELA B INTERIOR JOIN C). Ou seja, estamos interessados ​​em unir as tabelas B e C e, em seguida, realizar uma união do resultado com a tabela A. Agora, o MySQL continuaria a unir as tabelas B e C, mas antes que ele possa realizar uma união, ele precisa para armazenar esses dados em algum lugar. É aqui que as tabelas temporárias entram – o MySQL as usa para armazenar dados em estágios intermediários em consultas complexas temporariamente, e assim que a consulta termina, essa tabela temporária é descartada.

Agora a pergunta é: por que devemos nos preocupar com tudo isso?

Simplesmente porque a tabela temporária, apenas um resultado de consulta, são dados que estão sendo usados ​​pelo MySQL em computação, e a velocidade de seu acesso (entre outras limitações) determinará a rapidez com que a consulta é executada. Por exemplo, armazenar a tabela temporária na RAM será várias vezes mais rápido que armazená-la no disco.

Existem duas variáveis ​​que controlam esse comportamento:

MariaDB [(nenhum)]> mostra variáveis ​​como ‘MariaDB [(none)]> mostre variáveis ​​como ‘tmp_table_size’;
+—————-+———-+

| Nome da variável | Valor

+—————-+———-+

| tmp_table_size | 16777216

+—————-+———-+
‘;
+———————+———-+
| Nome da variável | Valor
+———————+———-+
| max_heap_table_size | 16777216
+———————+———-+

MariaDB [(nenhum)]> mostre variáveis ​​como ‘tmp_table_size’;
+—————-+———-+
| Nome da variável | Valor
+—————-+———-+
| tmp_table_size | 16777216
+—————-+———-+

O primeiro, max_heap_table_size, nos diz quanta RAM pode ser usada por uma tabela MySQL (“heap” aqui se refere aqui à estrutura de dados usada na alocação e gerenciamento de RAM – leia mais aqui), enquanto o segundo, tmp_table_size, mostra qual é o tamanho máximo da tabela temporária. No meu caso, ambos são definidos para 16 MB, embora o ponto que estou tentando fazer com que apenas o aumento de tmp_table_size não funcione como um todo, o MySQL ainda seja limitado por max_table_heap_size.

Agora chega o ponto: se as tabelas temporárias que estão sendo criadas forem maiores que o limite permitido por essas variáveis, o MySQL será forçado a gravá-las no disco rígido, resultando em desempenho extremamente ruim. Nosso trabalho agora é simples: faça o possível para adivinhar o tamanho de dados mais preciso para tabelas temporárias e ajustar essas variáveis ​​para esse limite. No entanto, gostaria de advertir contra o absurdo: definir esse limite para 16 GB (supondo que você tenha muita memória RAM) quando a maioria de suas tabelas temporárias tiver menos de 24 MB de tamanho é tolice – você está simplesmente desperdiçando RAM que poderia ‘ foi usado por outras consultas ou partes do sistema (cache, por exemplo).

Conclusão

Não é possível cobrir todas as variáveis ​​do sistema em um artigo, ou mesmo todas as importantes em um artigo, quando a documentação do MySQL se estende por vários milhares de palavras. Embora tenhamos abordado algumas variáveis ​​universais aqui, recomendamos que você analise as variáveis ​​do sistema para o mecanismo que você está usando (InnoDB ou MyISAM).

Meu resultado mais desejável para escrever este artigo é que você tire três coisas:

  1. O MySQL é um software típico que funciona dentro dos limites estabelecidos pelo sistema operacional. Não é um programa misterioso que faz Deus sabe o que é impossível de domar. Além disso, felizmente, não é tão difícil entender como ele é configurado e é controlado por suas variáveis ​​de sistema.
  2.  Não existe uma configuração única que faça sua instalação do MySQL aumentar o zoom. Você não tem escolha a não ser procurar dentro de seus sistemas em execução (lembre-se, a otimização ocorre depois que o aplicativo está em produção, não antes), faz as melhores suposições e medições e convive com a realidade de que nunca será perfeito.
  3. Ajustar as variáveis ​​não é a única maneira de otimizar o MySQL – consultas de escrita eficientes são outro grande problema, mas é algo que abordarei em outro artigo. Mas o ponto é que, mesmo que você tenha feito uma análise divina e ajustado esses parâmetros da melhor forma possível, ainda é possível que você pare tudo de uma maneira estridente..

Qual é a sua variável de sistema favorita para ajuste? ��

TAG:

  • Base de dados

Jeffrey Wilson Administrator
Sorry! The Author has not filled his profile.
follow me
    Like this post? Please share to your friends:
    Adblock
    detector
    map