XOOPS Brasil

 

6.9. Cache de Consultas do MySQL

A partir da versão 4.0.1, O servidor MySQL dispões do recurso Query Cache (cache de consultas). Quando em uso, o cache de consultas armazena o textop de uma consulta SELECT junto com o resultado correspondente que foi enviado para o cliente. Se uma consulta identica é recebida mais tarde, o servidor retornará o resultado da cache de consultas ao invés de analisar e executar a mesma consulta novamente.

NOTE: A cache de consulta não retornam dados antigos. Quando o dado é modificado, qualquer entrada relevante na cache de consulta é atualizado.

A cache de consultas é extremamente útil em um ambiente onde (algumas) tabelas não mudam com frequência e você tem várias consultas idênticas. Esta é uma situação típica em muitos servidores web que utilizam muito conteúdo dinâmico.

Abaixo está algumas performances de dados da cache de consultas. (Estes resultado foram gerados rodando o pacote de benchmark do MySQL em um Linux Alpha 2 x 500 MHz com 2 GB RAM e uma cache de consultas de 64 MB):

  • Se todas as consultas que você estiver realizando forem simples (tais como selecionar um registro de uma tabela com um registro); mas ainda diferente daquelas em que as consultas não são armazendas, a sobrecarga de ter a cache de consultas ativa é de 13%. Este pode ser considerado como o cenário de pior caso. No entanto, na vida real, consultas são muito mais complicadas que nosso exemplo simples, assim a sobrecarga é, normalmente, significantemente menor.

  • Buscas depois de uma linha em uma tabela de uma linha é 238% mais rápido. Isto pode ser considerado perto do mínimo de ganho a ser esperado para uma consulta que está armazenada.

  • Se você quiser disabilitar o codigo da cache de consulta defina query_cache_size=0. Disabilitando o código da cache de consultas não haverá nenhuma sobrecarga notável. (cache de consultas pode ser excluído do código com ajuda da opção de coniguração --without-query-cache)

6.9.1. Como a Cache de Consultas Opera

Consultas são comparadas antes da análise, logo

SELECT * FROM nome_tabela

e

Select * from nome_tabela

são consideradas consultas diferentes pela cache de consulta, assim consultas precisam ser exatamente a mesma (byte a byte) para serem vistas como idênticas. Além disso, uma consulta pode ser vista como diferente se, por exemplo, um cliente estiver usando um novo formato de protocolo de comunicação ou um conjunto de caracteres diferente de outro cliente.

Cansultas que utilizam banco de dados diferentes, utilizam versões de protocolos diferentes ou que usam conjunto de caracters padrão diferentes são considerados consultas diferentes e armazenadas separadamente.

A cache funciona para consultas do tipo SELECT SQL_CALC_FOUND_ROWS ... e SELECT FOUND_ROWS() ... porque o número de registros encontrados também é armazenado na cache.

Se o resultado da consulta foi retornado da cache de consultas, então o estado da variável Com_select não irá ser aumentado, mas Qcache_hits será. Veja mais informações sobre isto na Seção 6.9.4, “Estado e Manutenção da Cache de Consultas”.

Se uma tabela é alterada (INSERT, UPDATE, DELETE, TRUNCATE, ALTER ou DROP TABLE|DATABASE), então todas as caches de consulta que utilizam esta tabela (possivelmente atarvés de uma tabela MRG_MyISAM!) se torna inválida e é removida da cache.

Tabelas InnoDB transacionais que foram alteradas serão invalidadas quando um COMMIT é realizado.

No MySQL 4.0 a cache de consulta está disbilitada dentro da transação (ela não retorna resultados), mas a partir da versão 4.1.1 as caches de consultas funcionarão com tabelas InnoDB dentro da transação (ela usará o número da versão da tabela para detectar se a data é atual ou não).

Antes da versão 5.0, consultas com comentários na mesma linha não podem ser trazidas da cache (mas elas serão colocadas na cache se satisfazerem outras condições).

Uma consulta não pode ser armazenada em cache se contem uma das funções:

FunçãoFunçãoFunção
Funções Definidas por UsuariosCONNECTION_IDFOUND_ROWS
GET_LOCKRELEASE_LOCKLOAD_FILE
MASTER_POS_WAITNOWSYSDATE
CURRENT_TIMESTAMPCURDATECURRENT_DATE
CURTIMECURRENT_TIMEDATABASE
ENCRYPT (com um parâmetro)LAST_INSERT_IDRAND
UNIX_TIMESTAMP (sem parâmetros)USERBENCHMARK

Um consulta não pode ser armazenada em cache se conter variáveis, referenciar o banco de dados do sistema mysql, for da forma SELECT ... IN SHARE MODE, SELECT ... INTO OUTFILE ..., SELECT ... INTO DUMPFILE ... ou da forma SELECT * FROM AUTOINCREMENT_FIELD IS NULL (para retornar a ID da ultima inserção - ODBC contorna este problema).

No entanto, FOUND_ROWS() retornará o valor correto, mesmo se a consulta precedente foi buscada da cache.

No caso de uma consulta não utilizar qualquer tabela, ou utilizar tabelas temporárias, ou se o utilizador tiver um privilégio de coluna para qualquer tabela chamada, esta consulta não será armazenada em cache.

Antes de uma consulta ser trazida da cache de consulta, o MySQL irá verificar se o utilizador com privilégio SELECT para todos os banco de dados e tabelas envolvidos. Se este não for o caso, o resultado em cache não será usado.

6.9.2. Configuração da Cache de Consultas

A cache de consultas adiciona algumas variáveis do sistema MySQL para mysqld os quais podem ser definidos em um arquivo de configuração, na linha de comando ao iniciar mysqld.

  • query_cache_limit Não armazene em cache resultados que são maiores que isto. (Padrão 1M).

  • query_cache_min_res_unit

    Esta variável está presente a partir da versão 4.1.

    O resultado de uma consulta (os dados que também são enviados ao cliente) é armazenado na cache de consulta durante o recuperação do resultado. Consequentemente o dado normalmente não é tratado em um grande bloco. A cache de de conaultas aloca blocos para armazenar o dado em demanda, assim quando um bloco é preenchido, um novo bloco é alocado. Como a operação de alocação de memória é caro, a cache de consulta aloca blocos com um tamanho mínimo de query_cache_min_res_unit. Quando a consulta é executada, o último bloco do resultado é cortado para o tamanho atual do dado, assim a memória sem uso é liberada.

    • O valor padrão de query_cache_min_res_unit é 4 KB o qual deve ser adequada para a maioria dos casos.

    • Se você tiver várias consultas com resultados pequenos, o tamanho padrão do bloco pode levar a fragmentação de memória (indicado por um grande número de blocos livres (Qcache_free_blocks), que podem fazer a cache de consultas deletar consultas da cache devido a perda de memória) (Qcache_lowmem_prunes)). Neste caso você deve diminuir query_cache_min_res_unit.

    • Se você tem muitas consultas com resultados grandes (veja Qcache_total_blocks e Qcache_queries_in_cache),você pode aumentar a performance aumentadno query_cache_min_res_unit. No entanto, seja cuidadoso para não torná-lo muito grande (veja o ponto anterior).

  • query_cache_size A quantidade de memória (especificada em bytes) alocada para armazenar resultados de consultas antigas. Se ele for 0, a cache de consultas está desbilitada (padrão).

  • query_cache_type Pode ser atribuido (apenas numérico) com

    OpçãoDescrição
    0(OFF, não armazene ou retorne resultados)
    1(ON, armazene todos os resultados, exceto consultas SELECT SQL_NO_CACHE ...)
    2(DEMAND, armazene apenas cconsultas SELECT SQL_CACHE ...)

Dentro de uma thread (conexão), o comportamento da cache de consulta pode ser alterado do padrão. A sintaxe é a seguinte:

QUERY_CACHE_TYPE = OFF | ON | DEMAND QUERY_CACHE_TYPE = 0 | 1 | 2

OpçãoDescrição
0 or OFFNão armazene ou recupere resultados
1 or ONAramazene todos os resultados exceto consultas SELECT SQL_NO_CACHE ....
2 or DEMANDArmazene apenas consultas SELECT SQL_CACHE ....

6.9.3. Opções da Cache de Consultas na SELECT

Existem duas possibilidades de parâmetros relacionados a cache de consultas que podem ser especificados em uma consulta SELECT:

OpçãoDescrição
SQL_CACHESe QUERY_CACHE_TYPE é DEMAND, permite que a query seja armazenada em cache. Se QUERY_CACHE_TYPE é ON, este é o padrão. Se QUERY_CACHE_TYPE é OFF, não faz nada.
SQL_NO_CACHEFaz esta consulta não armazenável em cache, não permite que esta consulta seja armazenada em cache.

6.9.4. Estado e Manutenção da Cache de Consultas

Com o comando FLUSH QUERY CACHE você pode desfragmentar a cache de consultas para melhor utilizar a memória. Este comnado não removerá qualquer consulta da cache. FLUSH TABLES também descarrega a cache de consultas.

O camnado RESET QUERY CACHE remove todas os resultados de consultas da cache de consultas.

Você pode verificar se a cache de consltas está presente em sua versão do MySQL:

mysql> SHOW VARIABLES LIKE 'have_query_cache';
+------------------+-------+
| Variable_name | Value |
+------------------+-------+
| have_query_cache | YES |
+------------------+-------+
1 row in set (0.00 sec)

Você pode monitorar o desempenho da cache de consultas com SHOW STATUS:

VariávelDescrição
Qcache_queries_in_cacheNúmero de consultas registrada na cache.
Qcache_insertsNúmero de consultas adicionadas na cache.
Qcache_hitsNúmero de acertos da cache.
Qcache_lowmem_prunesNúmero de consultas que foram deletadas da cache devido a memória baixa.
Qcache_not_cachedN;úmero de consultas não armazenadas em cache (não armazenáveis, ou devido a QUERY_CACHE_TYPE).
Qcache_free_memoryQuantidade de memória livre para cache de consultas.
Qcache_free_blocksNúmero de blocos de memória livre na cache de consultas
Qcache_total_blocksNúmero total de blocos na cache de consultas.

Número total de consultas = Qcache_inserts + Qcache_hits + Qcache_not_cached.

A cache de consultas utiliza blocos de tamanhos variáveis, assim Qcache_total_blocks e Qcache_free_blocks podem indicar fragmentação de memória da cache de consultas. Depois de um FLUSH QUERY CACHE apenas um único (grande) bloco livre permanece.

Nota: Toda consulta precisa de um mínimo de 2 blocos (um para o texto da consulta e um ou mais para o resultado da conulta). Também, cada tabela que é usada por uma consulta precisa de um bloco, mas se duas ou mais consultas usam a mesma tabela, apenas um bloco precisa ser alocado.

Você pode utilizar a variável de estado Qcache_lowmem_prunes para ajustar o tamanho da cache de consultas. Ela conta o número de consultas que são removidas da cache para liberar memória para armazenar novas consultas. A cache de consultas utiliza uma estratégia least recently used (LRU) para decidir quais consultas serão removidas da cache.