Query Cache série Mysql: Desempenho e Disponibilidade

Olá, Pessoal

Faz um tempo que não posto aqui é que o trabalho anda corrido mas agora vai vir um monte de coisa legal, a primeira delas é ganhar desempenho com o MySql®. Como todos sabem o MySql não é dos males o pior o problema é trabalhar com ele sozinho e sem algumas configurações que facilitam gigantescamente.

Vamos começar:

A primeira otimização que fazer é otimizar as buscas das querys utilizando caches, mas esta é uma via de duas mãos pode ser Ótimo e pode ser Terrível então avalie bem o que irá fazer.

Eis como funciona o Query Cache: você faz um query X, e em seguida alguém faz a mesma query X a segunda pessoa recebeu o resultado muito mais rápido magnífico certo?... nem tanto abaixo um exemplo disso.

Eu fiz a query:

mysql> select * from empresa where empresa_id = 1;
+------------+-----------------------------------+----------------+---------------------+---------------------+----------------------------------+---------------+
| empresa_id | empresa_nome                      | empresa_status | empresa_insercao    | empresa_update      | responsavel_os_responsavel_os_id | empresa_dados |
|          1 | Cliente 1  |              0 | 2007-04-04 07:00:00 | 2010-05-24 09:26:00 |                             NULL |             1 |
+------------+--------
+------------+-----------------------------------+----------------+---------------------+---------------------+----------------------------------+---------------+
---------------------------+----------------+---------------------+---------------------+----------------------------------+---------------+
1 row in set (0.14 sec)

Eu fiz a query novamente 3 segundos depois, de 0.14 sec eu tive uma melhora para 0.00 sec.

mysql> select * from empresa where empresa_id = 1;
+------------+-----------------------------------+----------------+---------------------+---------------------+----------------------------------+---------------+
| empresa_id | empresa_nome                      | empresa_status | empresa_insercao    | empresa_update      | responsavel_os_responsavel_os_id | empresa_dados |
|          1 | Cliente 1 |              0 | 2007-04-04 07:00:00 | 2010-05-24 09:26:00 |                             NULL |             1 |
+------------+---------
+------------+-----------------------------------+----------------+---------------------+---------------------+----------------------------------+---------------+
--------------------------+----------------+---------------------+---------------------+----------------------------------+---------------+
1 row in set (0.00 sec)

Ai vocês me perguntam magnífico, o que pode ser tão terrível? 

Bem se você utilizar isso em uma tabela na qual a frequência de inserção é muito grande você inutilizará o cache, o que fará o sistema ficar refazendo ele diversas vezes e você nem utilizará... ou seja sobrecarga inútil. Porém, em sites como blogs, alguns tipos de e-commerce, empresas isso fará com que você ganhe um desempenho estupendo.

Vamos começar implantar

Eu utilizei o MySql® versão 5.1 que é a que está no servidor que estou otimizando.

Primeiramente a implantação  no arquivo de configuração do myslql o my.cnf precisamos adicionar alguns parâmetros e modificar outros conforme a necessidade:

1. query_cache_limit Não efetua caches de resultados maiores que este número em bytes.
2. query_cache_min_res_unit Tamanho mínimo para blocos alocados pelo query cache em bytes.
3. query_cache_size Quantidade de memória alocada para "cachear" os resultados de query
4. query_cache_type Tipo de query cache que será utilizado (0 = desligado, 1 = ligado, 2 demanda (obrigatorio uso do SQL_CACHE ou NO_SQL_CACHE)

A configuração destas variáveis varia do tamanho dos resultados das querys utilizados, para sites pequenos os padrões abaixo resolvem muito bem.

query_cache_limit = 1MB
query_cache_min_res_unit = 4096
query_cache_min_size = 16MB
query_cache_type = 1

Lembrando que é necessário ficar atento com o query_cache_type pois em modo Ligado, ele executa cache para toda e qualquer query do seu sistema, salvo as NO_SQL_CACHE.

Após isso basta reiniciar o MySql que já esta funcionando.

Considerações:

Querys não serão enviadas para cache se contiverem algumas das funções abaixo:

BENCHMARK()	CONNECTION_ID()	CONVERT_TZ()
CURDATE()	CURRENT_DATE()	CURRENT_TIME()
CURRENT_TIMESTAMP()	CURTIME()	DATABASE()
ENCRYPT() with one parameter	FOUND_ROWS()	GET_LOCK()
LAST_INSERT_ID()	LOAD_FILE()	MASTER_POS_WAIT()
NOW()	RAND()	RELEASE_LOCK()
SLEEP()	SYSDATE()	UNIX_TIMESTAMP() with no parameters
USER()	UUID()	UUID_SHORT()

Tabela 1: query Cannot be cached from http://dev.mysql.com/doc/refman/5.1/

Bibliografia:

http://dev.mysql.com/doc/refman/5.1/en/query-cache.html

http://dev.mysql.com/doc/refman/5.1/en/server-system-variables.html#sysvar_query_cache_type