Ir para:navegação, pesquisa

Tabela de conteúdo 1 Introdução 2 Configurando o cache nas queries 3 Limpando a cache das queries 4 Cache sob demanda 5 Alocacao de blocos e cache de queries

Introdução

Cenario Tipico

Chefe: Nosso novo website esta se arrastando!! Como isso pode estar acontecendo, se nos temos servidores novinhos - qual é o problema? Voce: Bem, nossos servidores estao blza - é o database q estah estrangulado. Chefe: O queeee? Voce me disse que o MySQL era rapido, que nos nao precisavamos d um Oracle, e agora voce diz que ele nao aguenta mais!! Como? Voce: Bem, nossos servidores estao se comportando bem, tao bem que eles estao passando um monte de queries ao mesmo tempo. Mas, soh temos um database, e temos um monte de servidores... Chefe: É muito tarde pra comprar um Oracle agora - O que vamos fazer? Chefe do chefe para o Chefe (na cabeca do chefe): Este projeto foi um desastre desde o comeco - agora voce quer que eu atrase tudo e instale um novo database, e pior, vamos gastar muito mais dinheiro!!! Voce acha que eu fabrico dinheiro??!?? Vou chamar alguem q saiba o que estah fazendo - voce estah DEMITIDO kra!! Colega de trabalho (querendo pegar sua vaga): Espere, eu sei como resolver este problema!! Entao, o que seu colega vai fazer, q vc nao saiba? Como ele vai salvar o dia e ganhar do chefe todo o credito? Nosso cenario é muito impreciso pra imaginar e existem muitas solucoes pra ele. Voce pode ler um pouco mais sobre como optimizar queries e indices (http://www.databasejournal.com/features/mysql/article.php/1382791), pode otimizar atraves da melhoria do seu hardware, e ajustando variaveis do banco de dados (http://www.databasejournal.com/features/mysql/article.php/1402311), usando um log lento das queries (http://www.databasejournal.com/features/mysql/article.php/2013631) e é claro, existem ainda os metodos de replicacao.

No entando, o MySQL 4 possui uma ferramenta que pode ser muito util, se bem usada, é uma cache para queries. Em uma situacao em que o database tem que repetir muitas vezes a mesma querie no mesmo dataset, retornando os mesmos resultados por vez, o MySQL pode fazer cache dos resultados, evitando a sobrecarga do database server. Normalmente, todo mundo usa chace no webserver, mas quando isso nao resolve o problema, um cache no database pode ajudar.

Configurando o cache nas queries

Para ter certeza que o MySQL usa a chace, existem muitas variaveis que devem ser configuradas no arquivo de configuracao dele (normalmente, my.cnf ou my.ini).

O primeiro, é o query_cache_type. Existem tres possiveis configuracoes:

0 (desligado, nao usado),

1 (ligado, cache nas queries) e

2 (sob demanda, discutido abaixo).

Para garantir que ele esta sempre ligado, deixe assim:

query_cache_type = 1

no arquivo de configuracao. Se voice iniciar o server tendo feito somente essa alteracao, voce vera as seguintes variaveis de cache configuradas:

#mysql> SHOW VARIABLES LIKE '%query_cache%';
#+-------------------+---------+
#| Variable_name | Value |
#+-------------------+---------+
#| have_query_cache | YES |
#| query_cache_limit | 1048576 |
#| query_cache_size | 0 |
#| query_cache_type | ON |
#+-------------------+---------+
#4 rows in set (0.06 sec)

Veja que essa saida é do MySQL 4.0.x - voce vera mais saidas nas versoes 4.1.x e posteriores. A query_cache_type vai estar configurada como ON ou OFF, de acordo com a distribuicao. No entanto, existem mais um parametro que pode ser configurado, é o query_cache_size. Se voce deixa ele configurado como 0 (q eh o padrao), a cache estara desabilitada. Esta variavei determina a quantidade de memoria, em bytes, usada para a cache da query. Em nosso exemplo, vamos configurar ele com 20Mb de tamanho.

query_cache_size = 20M

Esta quantidade é exibida em bytes:

#mysql> SHOW VARIABLES LIKE '%query_cache%';
#+-------------------+----------+
#| Variable_name | Value |
#+-------------------+----------+
#| have_query_cache | YES |
#| query_cache_limit | 1048576 |
#| query_cache_size | 20971520 |
#| query_cache_type | ON |
#+-------------------+----------+
#4 rows in set (0.06 sec)
<pre>

A cache da query em acao (quase)

Neste tutorial, eu usei um dump do Wikipedia, o conteudo aberto da enciclopedia (voce pode encontrar os dumps aqui http://download.wikipedia.org/. Estou usando uma maquina beeeeeeeeeeem lenta, com mais nda rodando nela, para minimizar qualquer interferencia nos resultados. Vamos rodar a mesma query duas vezes, e verificar na segunda execucao, como o tempo d resposta foi melhorado:

<pre>
#SELECT * FROM cur;
#...
#14144 rows in set (2.96 sec)
#Now we run the same query again:
#SELECT * FROM cur; 14144 rows in set (3.02 sec)

Agora, vamos rodar a mesma query novamente:

#SELECT * FROM cur; 14144 rows in set (3.02 sec)

O que aconteceu? A segunda query nao deveria ser executada em menos tempo? Vamos examinar algumas variaveis de status para ter uma ideia melhor do que aconteceu:

#mysql> SHOW STATUS LIKE '%qcache%';
#+-------------------------+----------+
#| Variable_name | Value |
#+-------------------------+----------+
#| Qcache_queries_in_cache | 0 |
#| Qcache_inserts | 2 |
#| Qcache_hits | 0 |
#| Qcache_lowmem_prunes | 0 |
#| Qcache_not_cached | 2 |
#| Qcache_free_memory | 20962720 |
#| Qcache_free_blocks | 1 |
#| Qcache_total_blocks | 1 |
#+-------------------------+----------+
#8 rows in set (0.00 sec)

As duas queries que rodamos, estao gravadas (pelo Qcache_inserts), mas nenhuma das duas foi colocada em cache. (Voce pode ter resultados diferentes se outras queries foram executadas.) O problema é que resultado é muito grande. Eu usei o dump do Wikipedia Esperanto (4Mb compactados - o dump do ingles tem 135Mb, e mesmo o meu ingles, sendo melhor que meu Esperanto, a largura de banda é beeeeem cara que no Brasil!!), e baixar algo tao grande (135 Mb) é desnecessairio pq a query_cache noa consegue alocar tanta informacao. Existem dois limites em jogo - o limite de cada cache, determinado pelo parametro query_cache_limit, que por padrao é de 1Mb. E, o limite da cache total é no parametro query_cache_size foi definido como 20Mb. Estes limites se aplicam aqui. Se o resultado é maior que 1 Mb, não é feio o cache.

A cache da query em acao (agora sim :D hehe)

Vamos tentar uma query menor:

#SELECT cur_is_new FROM cur WHERE cur_user_text > 'Y'
#...
#2336 rows in set (0.38 sec)

Vamos ver se esta foi colocada em cache:

#mysql> SHOW STATUS LIKE '%qcache%';
#+-------------------------+----------+
#| Variable_name | Value |
#+-------------------------+----------+
#| Qcache_queries_in_cache | 1 |
#| Qcache_inserts | 3 |
#| Qcache_hits | 0 |
#| Qcache_lowmem_prunes | 0 |
#| Qcache_not_cached | 2 |
#| Qcache_free_memory | 20947592 |
#| Qcache_free_blocks | 1 |
#| Qcache_total_blocks | 4 |
#+-------------------------+----------+
#8 rows in set (0.00 sec)

Agora existe uma query em cache. Se ela levou 0.38 segundos para ser executada na primeira vez, vamos ver se se na segunda execucao, notamos alguma melhoria:

#SELECT cur_is_new FROM cur WHERE cur_user_text > 'Y'
#...
#2336 rows in set (0.11 sec)
<pre>

bem melhor!!! E, olhando no status novamente:

<pre>
#mysql> SHOW STATUS LIKE '%qcache%';
#+-------------------------+----------+
#| Variable_name | Value |
#+-------------------------+----------+
#| Qcache_queries_in_cache | 1 |
#| Qcache_inserts | 3 |
#| Qcache_hits | 1 |
#| Qcache_lowmem_prunes | 0 |
#| Qcache_not_cached | 2 |
#| Qcache_free_memory | 20947592 |
#| Qcache_free_blocks | 1 |
#| Qcache_total_blocks | 4 |
#+-------------------------+----------+
#8 rows in set (0.06 sec)

A cache foi acessada uma vez. As variaveis de status acima, sao faceis de entender. A memoria disponivel para cache mudou de 20962720 para 20947592 bytes. A variavel mais util para ajustes futuros é a Qcache_lowmem_prunes. Cada vez que uma query em cache é removida da cache de queries, (porque o MySQL precisa de espaco para armazenar outras), este valor é incrementado. Se ele aumenta muito rapidamente, e voce continua com espaco de armzenamento, voce pode aumentar o query_cache_size, e se esse valor nunca aumenta, voce pode reduzir o tamanho da cache.

vamos executar a query novamente, com uma pequena diferenca na sintaxe:

#SELECT cur_is_new from cur where cur_user_text > 'Y'
#...
#2336 rows in set (0.33 sec)

Levou mais tempo que nos imaginavamos. Vamos olhar nas variaveis de status para ver o como estao as coisas:

</pre>

1. mysql> SHOW STATUS LIKE '%qcache%';
2. +-------------------------+----------+
3. | Variable_name | Value |
4. +-------------------------+----------+
5. | Qcache_queries_in_cache | 2 |
6. | Qcache_inserts | 4 |
7. | Qcache_hits | 1 |
8. | Qcache_lowmem_prunes | 0 |
9. | Qcache_not_cached | 2 |
10. | Qcache_free_memory | 20932976 |
11. | Qcache_free_blocks | 1 |
12. | Qcache_total_blocks | 6 |
13. +-------------------------+----------+

</pre>

Nao foi usada a cache nesta query - de fato, o MySQL inseriu uma nova query na cache!! O problema aqui é que a cache de queries do MySQL é case-sensitive (de fato, ela é byte sensitive). A query deve ser identica em todos os aspectos - sem espacos extras, sem mudancas no case. Por isso mesmo, a query acima foi tratada como se fosse uma query diferente. Isso por sí só é motivo o suficiente pra que voce tenha que adotar algumas convencoes, e se assegurar que todos os desenvolvedores facam uso dela. Eu uso maiusculas para minhas palavras-chave do MySQL e minusculas para tabelas e nomes de campo.

Limpando a cache das queries

A cache nao pode ficar na memoria por tempo indefinido. Com sorte, o MySQL consegue remover o cache quando vc faz alguma alteracao nas tabelas usadas em caches nas queries. Se nos inserirmos um novo registro na tabela 'cur', o MySQL vai limpar as queries afetadas (e somente as queries afetadas) da cache:

#mysql> INSERT INTO cur(cur_user_text)
# VALUES ('xxx');
#Query OK, 1 row affected (0.06 sec)

#mysql> SHOW STATUS LIKE '%qcache%';
#+-------------------------+----------+
#| Variable_name | Value |
#+-------------------------+----------+
#| Qcache_queries_in_cache | 0 |
#| Qcache_inserts | 4 |
#| Qcache_hits | 1 |
#| Qcache_lowmem_prunes | 0 |
#| Qcache_not_cached | 2 |
#| Qcache_free_memory | 20962720 |
#| Qcache_free_blocks | 1 |
#| Qcache_total_blocks | 1 |
#+-------------------------+----------+

Qualquer INSERT, UPDATE, DELETE, TRUNCATE, ALTER, DROP TABLE ou DROP DATABASE pode potencialmente remover queries da cache. Mas vc pode limpar a cache a mao com o comando RESET QUERY CACHE.

Cache sob demanda

Anteriormente dissemos que existiam tres valores para o parametro query_cache_type. On, off e sob demanda. A ultima opcao trabalha da seguinte maneira: as queries serao colocadas em cache somente se SQL_CACHE for especificado na query. Vamos reiniciar o server com a opcao 2 habilitada no arquivo de configuracao:

query_cache_type = 2

Quando voce reinicia o server, todas as variaveis sao apagadas. Vamos executar nossa query novamente:

#SELECT cur_is_new FROM cur WHERE cur_user_text > 'Y'
#...
#2336 rows in set (0.27 sec)

Ela gastou bastante tempo novamente, isso, porque a cache foi apagada.

#mysql> SHOW STATUS LIKE '%qcache%';
#+-------------------------+----------+
#| Variable_name | Value |
#+-------------------------+----------+
#| Qcache_queries_in_cache | 0 |
#| Qcache_inserts | 0 |
#| Qcache_hits | 0 |
#| Qcache_lowmem_prunes | 0 |
#| Qcache_not_cached | 1 |
#| Qcache_free_memory | 20962720 |
#| Qcache_free_blocks | 1 |
#| Qcache_total_blocks | 1 |
#+-------------------------+----------+

Nada foi gravado. Para armazenar a query no cache, devemos executar a query com SQL_CACHE, como na query a seguir:

#SELECT SQL_CACHE cur_is_new FROM cur WHERE cur_user_text > 'Y'
#...
#2336 rows in set (0.33 sec)

Desta vez ela foi armazenada no cache.

#mysql> SHOW STATUS LIKE '%qcache%';
#+-------------------------+----------+
#| Variable_name | Value |
#+-------------------------+----------+
#| Qcache_queries_in_cache | 1 |
#| Qcache_inserts | 1 |
#| Qcache_hits | 0 |
#| Qcache_lowmem_prunes | 0 |
#| Qcache_not_cached | 1 |
#| Qcache_free_memory | 20947592 |
#| Qcache_free_blocks | 1 |
#| Qcache_total_blocks | 4 |
#+-------------------------+----------+

Se o tipo de cache estver configurado como 1, algumas vezes, voce ira executar queries que nao serao repetidas, ou usadas com pouca frequencia. Nessas ocasioes, voce pode instruir o MySQL para nao gravar os resultados em cache, mesmo que existem limitacoes de tamanho, para isso, voce usa a clausula SQL_NO_CACHE no SELECT.

Alocacao de blocos e cache de queries

O MySQL aloca os resultados na cache em blocos, durante as consultas. Esta alocacao pode ser sobrecarregada (veja o tempo mais rapido para rodar uma query quando ela nao esta em cache). Voce nao deve habilitar a cache de queries a menos que voce faca bom uso dela. O numero de blocos livres (Qcache_free_blocks) pode ser uma boa indicacao de fragmentacao - um alto numero em relacao ao numero total de blocos significa que estamos desperdicando espaco. No MySQL 4.1, existe uma outra variavel de cache: query_cache_min_res_unit. Ela permite que voce defina o tamanho minimo para o bloco de alocacao. O valor default é 4KB. Se a maioria dos seus resultados é pequeino, e voce observa muita fragmentacao, vode deveria diminuir este valor. Isto se aplica a maioria dos resultados que sao grandes. Para desfragmentar a cache de queryes use FLUSH QUERY CACHE (FLUSH TABLES tam o mesmo efeito sobre a cache).

Existem situacoes em que uma query nao pode ser colocada em cache, como aquelas que retornam a hora atual, um numero aleatorio, variaveis de usuarios ou quando estamos fazendo um dump para um arquivo. As queries que fazem uso das seguintes funcoes, ou dos seguintes tipos nao devem ser colocadas em cache:

1. Funcoes definidas por usuario
2. BENCHMARK
3. CONNECTION_ID
4. CURDATE
5. CURRENT_DATE
6. CURRENT_TIME
7. CURRENT_TIMESTAMP
8. CURTIME
9. DATABASE
10. ENCRYPT (com um parametro)
11. FOUND_ROWS
12. GET_LOCK
13. LAST_INSERT_ID
14. LOAD_FILE
15. MASTER_POS_WAIT
16. NOW
17. RAND
18. RELEASE_LOCK
19. SYSDATE
20. UNIX_TIMESTAMP (sem parametros)
21. USER
22. query contem variaveis de usuario
23. query referencia um banco de dados de sistema do MySQL
24. query do form SELECT... IN SHARE MODE
25. query do form SELECT ... INTO OUTFILE ...
26. query do form SELECT ... INTO DUMPFILE ...
27. query do form SELECT * FROM AUTOINCREMENT_FIELD IS NULL
28. queries dentro de transacoes (no MySQL 4.0.x)

Usado com sabedoria, a cache de queries pode fazer uma diferenca substancial em aplicacoes com gargalos. Boa sorte!

---

Artigo de Ian Gilfillan

Traduzido por demiurgo (demiurgo at underlinux dot com dot br) Maxx Angelo