Cache no MySQL

7.1 Cache no lado do banco de dados

O MySQL usa algoritmos altamente escaláveis ​​ao trabalhar com tabelas, portanto, o MySQL pode ser executado mesmo com pequenas quantidades de memória. Naturalmente, para um melhor desempenho, você precisa de mais RAM.

Para visualizar as configurações atuais, conecte-se ao banco de dados

#mysq -u root -p

mysql> show variables like 'query_cache%';
+------------------------------+-----------+
| Variable_name                | Value     |
+------------------------------+-----------+
| query_cache_limit            | 1048576   |
| query_cache_min_res_unit     | 4096      |
| query_cache_size             | 134217728 |
| query_cache_type             | ON        |
| query_cache_wlock_invalidate | OFF       |
+------------------------------+-----------+
5 rows in set (0.00 sec)

Primeiro, verifique se o cache está ativado. Variável:

• query_cache_typedeve ser ON (1) ou DEMAND (2)
• query_cache_limit- determina o tamanho máximo do resultado que entrará no cache
• query_cache_sizeser diferente de zero. Ao usar DEMAND, apenas as solicitações que contêm a diretiva serão armazenadas em cacheSQL_CACHE;
• query_cache_min_res_unito tamanho mínimo do bloco de memória alocado para armazenar os resultados da consulta em cache. O MySQL não armazena o cache em um grande pedaço de memória, ao invés disso ele aloca blocos com um tamanho mínimo query_cache_min_res_unit(=4KB por padrão) sob demanda. O último desses blocos é truncado para o tamanho dos dados e a memória restante é liberada.

O efeito do cache é que, quando o servidor recebe uma solicitação, ele verifica se o hash da solicitação está no cache. Se o hash corresponder - o servidor retorna imediatamente o resultado - sem analisar a solicitação, otimizar etc. sobrecarga - acompanhada por um mecanismo de cache - navegando no cache, gravando o resultado da consulta no cache, etc.

E se você tiver muitas solicitações pequenas no cache, isso pode levar à fragmentação da memória devido a um grande número de blocos livres. E isso, por sua vez, faz com que as entradas em cache sejam excluídas devido à falta de memória. Nesse caso, faz sentido diminuir o valor de query_cache_min_res_unit. Se a maioria de suas consultas produzir resultados grandes, aumentar essa configuração pode melhorar o desempenho.

Vamos tentar avaliar o efeito. Observamos como os contadores de acertos do cache mudam (Qcahe_hits), o número de solicitações declaradas inválidas por falta de memória (Qcache_lowmem_prunes), o número total de solicitações do tipo SELECT (e somente elas são armazenadas em cache):

#mysq -u root -p
mysql> show status like 'Qcache%';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| Qcache_free_blocks      | 715       |
| Qcache_free_memory      | 130369640 |
| Qcache_hits             | 24209     |
| Qcache_inserts          | 16215     |
| Qcache_lowmem_prunes    | 0         |
| Qcache_not_cached       | 444       |
| Qcache_queries_in_cache | 1740      |
| Qcache_total_blocks     | 4225      |
+-------------------------+-----------+
8 rows in set (0.00 sec)

7.2 Status atual do cache

Para monitorar o cache de consulta é usado SHOW STATUS:

mysql> SHOW STATUS LIKE 'Qcache_%';
+-------------------------+----------+
| Variable_name           | Value    |
+-------------------------+----------+
| Qcache_free_blocks      | 10       |
| Qcache_free_memory      | 16755496 |
| Qcache_hits             | 49812    |
| Qcache_inserts          | 103999   |
| Qcache_lowmem_prunes    | 0        |
| Qcache_not_cached       | 180      |
| Qcache_queries_in_cache | 6        |
| Qcache_total_blocks     | 28       |
+-------------------------+----------+
8 rows in set (0.00 sec)

• Qcache_free_blocksmostra quantos blocos livres existem no cache (diminuem à medida que as solicitações em cache aumentam);
• Qcache_total_blocks— o número de blocos ocupados;
• Qcache_free_memory- mostra a memória livre "disponível" para cache;
• Qcache_hits- o número de solicitações cujos resultados foram retirados do cache, sem realmente acessar o banco de dados;
• Qcache_inserts- o número de solicitações que foram adicionadas ao cache;
• Qcache_lowmem_prunes- o número de solicitações que foram removidas do cache por falta de memória;
• Qcache_not_cached- o número de solicitações que não foram gravadas no cache devido ao uso de funções de gerenciamento de tempo, etc.;
• Qcache_queries_in_cache- o número de solicitações que estão no cache.

Você pode visualizar o número total de consultas SELECT:

mysql> show status like 'Com_select';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Com_select    | 16719 |
+---------------+-------+
1 row in set (0.00 sec)

7.3 Eficiência do cache

qcache_hit_ratio = qcache_hits / (qcache_hits + qcache_inserts + qcache_not_cached)

O valor atual pode ser visto assim:

SHOW VARIABLES LIKE 'query_cache_size';

Novamente, surge a pergunta: como escolher um valor adequadoquery_cache_size?

Isso vai ajudar Qcache_lowmem_prunes. Essa variável armazena o número de solicitações que foram removidas do cache devido à necessidade de armazenar novas solicitações em cache. É necessário buscar um tamanho de cache no qual Qcache_lowmem_prunesaumentará apenas ligeiramente. Para isso, é recomendável comparar a diferença de valores Qcache_lowmem_prunespor hora e a quantidade de requisições recebidas pelo mysql na mesma hora.

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2

query_cache_size = (number of requests for 10 minutes)*(average response volume on request) * 1,2
query_cache_size = (volume de tráfego em 10 minutos) * 1,2 "

Isso armazenará solicitações em cache por 10 minutos + fornecerá 20% de memória adicional para fragmentação de cache e reserva de cache adicional

Você pode calcular o número e o volume médio de uma resposta a uma solicitação usando as variáveis ​​Bytes_sent , respectivamente

E assim query_cache_sizeaumentamos os valores, depois disso você deve ficar atento aos valores Qcache_total_blocks, Qcache_free_blockse Qcache_queries_in_cache. MySQL armazena cache em blocos. Para 1 solicitação, são necessários 2 blocos: um para o próprio texto da solicitação e o segundo para o resultado.

Se considerarmos a tabela a partir do valorQcache%

Número total de blocos de cacheQcache_total_blocks – 28

A solicitação 6 está armazenada em cache agora, o que significa que 6 * 2 = 12 blocos estão ocupados

Blocos grátis Qcache_free_blocks – 10. Quanto mais ocioso Qcache_free_blocks, maior o grau de "fragmentação" do cache.

Se a maioria das consultas tiver uma pequena quantidade de dados resultantes, vale a pena reduzir o tamanho mínimo do bloco de cache query_cache_min_res_unit, que é de 4 KB por padrão.

Se a maioria das solicitações retornar muitos dados, vale a pena aumentar o tamanho do bloco de cache.

O principal é atingir um valor mínimo Qcache_free_blocks.

Se o contador Qcache_not_cachedfor grande, você pode tentar aumentar a variável query_cache_limit- isso permitirá aumentar o limite e armazenar em cache os resultados das consultas que "não cabem".

As seguintes variáveis ​​de configuração são responsáveis ​​pelo uso do cache de consulta:

• query_cache_size— o tamanho do cache de consulta. query_cache_size = 0desabilita o uso do cache;
• query_cache_limit- o tamanho da amostra máxima armazenada no cache;
• query_cache_wlock_invalidate- determina se os dados serão retirados do cache se a tabela a qual pertencem estiver bloqueada para leitura.
=

Para ativar o cache de consulta mysql, basta adicionar as seguintes linhas a my.cnf (Seção [mysqld]):

query_cache_size = 268435456
query_cache_type =1 
query_cache_limit =1 048576

E reinicie o serviço.

7.4 Onde a cache não é utilizada

Não armazenado em cache:

• Pedidos deSQL_NO_CACHE
• consultas preparadas(Prepared statements);
• Consultas que são subconsultas da consulta externa;
• Consultas dentro de stored procedures e funções;
• Consultas que usam funções:

  BENCHMARK (), CONNECTION_ID (), CONVERT_TZ (), CURDATE (), CURRENT_DATE (), CURRENT_TIME (), CURRENT_TIMESTAMP (), CURTIME (), DATABASE (), ENCRYPT ()com um argumento, FOUND_ROWS (), GET_LOCK (), LAST_INSERT_ID (), LOAD_FILE (), MASTER_POS_WAIT (), NOW (), RAND (), RELEASE_LOCK (), SLEEP (), SYSDATE (), UNIX_TIMESTAMP ()sem argumentos, USER (),UUID ();

• Consultas usando funções armazenadas, variáveis ​​de usuário ou tabelas de referência em bancos de dados do sistema mysql ou INFORMATION_SCHEMA;
• • Solicitações nas seguintes formas:
• SELECT ... IN SHARE MODE
• SELECT ... FOR UPDATE
• SELECT ... INTO OUTFILE ...
• SELECT ... INTO DUMPFILE ...
• SELECT * FROM ... WHERE autoincrement_col IS NULL
• Consultas com tabelas temporárias ou sem uso de tabelas;
• Solicitações geradoras de warnings (warnings);

Você pode desfragmentar o cache com o comando:

mysql>flush query cache;

Limpar - comando:

mysql>flush query cache;

O mais importante

Nunca trabalhe em produção com configurações padrão. Isso resultará na não utilização da maioria dos recursos do servidor. O ajuste adequado do MySQL pode melhorar o desempenho do banco de dados várias vezes. Isso não apenas acelerará o aplicativo, mas também lidará com uma carga pesada.