A sobrecarga da invalidação frequente do cache de consulta vale a pena?

Você deve apenas desativar o cache de consulta com

[mysqld]
query_cache_size = 0

e reinicie o mysql. Por que eu sugeriria isso ???

O Query Cache sempre irá bater de frente com o InnoDB. Seria bom se o MVCC do InnoDB permitisse que as consultas fossem atendidas a partir do cache de consulta se as modificações não afetassem as leituras repetíveis para outras transações. Infelizmente, o InnoDB simplesmente não faz isso. Aparentemente, você tem muitas consultas que são invalidadas rapidamente e provavelmente não estão sendo reutilizadas.

Para InnoDB no MySQL 4.0, o cache de consulta foi desabilitado para transações. Para o MySQL 4.1+, o InnoDB desempenha o papel de guarda de tráfego ao permitir o acesso ao cache de consulta por tabela.

Da perspectiva da sua pergunta, eu diria que a justificativa para remover o cache de consulta não é tanto a sobrecarga, mas como o InnoDB a gerencia.

Para obter mais informações sobre como o InnoDB interage com o cache de consulta, leia as páginas 213-215 do livro "High Performance MySQL (Second Edition)" .

Se todos ou a maioria dos seus dados forem MyISAM, você pode seguir sua ideia original de usar SQL_NO_CACHE.

Se você tiver uma mistura de InnoDB e MyISAM, terá que encontrar o equilíbrio certo para seu aplicativo com base em quão alto são os erros de cache. De fato, as páginas 209-210 do mesmo livro apontam as razões para erros de cache:

• A consulta não pode ser armazenada em cache porque contém uma construção não determinística (como CURRENT_DATE) ou porque seu conjunto de resultados é muito grande para ser armazenado. Ambos os tipos de consultas sem cache incrementam a variável de status Qcache_not_cached.
• O servidor nunca viu a consulta antes, portanto, nunca teve a chance de armazenar em cache seu resultado.
• O resultado da consulta foi armazenado em cache anteriormente, mas o servidor o removeu. Isso pode acontecer porque não havia memória suficiente para mantê-lo, porque alguém instruiu o servidor a removê-lo ou porque foi invalidado

e as causas principais de faltas de cache altas com poucas consultas que não podem ser armazenadas em cache podem ser:

• O cache de consulta ainda não está aquecido. Ou seja, o servidor não teve a chance de preencher o cache com conjuntos de resultados.
• O servidor está vendo consultas que nunca viu antes. Se você não tiver muitas consultas repetidas, isso pode acontecer mesmo após o aquecimento do cache.
• Há muitas invalidações de cache.

ATUALIZAÇÃO 2012-09-06 10:10 EDT

Olhando suas últimas informações atualizadas, você query_cache_limitdefiniu para 1048576 (1M). Isso limita qualquer conjunto de resultados a 1M. Se você recuperar algo maior, simplesmente não será armazenado em cache. Embora você tenha query_cache_sizedefinido para 104857600 (100M), isso permite apenas 100 resultados em cache definidos em um mundo perfeito. Se você executar centenas de consultas, a fragmentação ocorrerá rapidamente. Você também tem 4096 (4K) como o conjunto de resultados de tamanho mínimo. Infelizmente, o mysql não possui nenhum mecanismo interno para desfragmentar o cache de consulta.

Se você precisar do cache de consulta e tiver muita RAM, poderá executar o seguinte:

SET GLOBAL query_cache_size = 0;
SELECT SLEEP(60);
SET GLOBAL query_cache_size = 1024 * 1024 * 1024;

para limpar o cache de consulta. Você perde todos os resultados armazenados em cache, portanto, execute essas linhas fora dos horários de pico.

Eu também atribuiria o seguinte:

• query_cache_size = 1G
• query_cache_limit = 8M

Isso deixa 23G de RAM. Eu levantaria o seguinte:

• innodb_buffer_pool_size = 12G
• key_buffer_size = 4G

Isso deixa 7G. Isso deve ser adequado para conexões de sistema operacional e banco de dados.

Lembre-se de que o buffer de chave armazena em cache apenas páginas de índice MyISAM, enquanto o InnoDB Buffer Pool armazena dados e índices em cache.

Mais uma recomendação: atualize para o MySQL 5.5 para poder configurar o InnoDB para várias CPUs e vários threads para E/S de leitura/gravação.

Veja minhas postagens anteriores sobre o uso do MySQL 5.5 em conjunto com o acesso a várias CPUs para InnoDB

• Nov 24, 2011: Por que mysql 5.5 é mais lento que 5.1 (linux, usando mysqlslap)
• Oct 05, 2011: A consulta é executada por muito tempo em algumas versões mais recentes do MySQL
• Sep 20, 2011: Múltiplos núcleos e desempenho do MySQL
• Jun 19, 2011: Como eu executo corretamente um Bake-off do MySQL?

ATUALIZAÇÃO 2012-09-06 14:56 EDT

Meu método para limpar o cache de consulta é bastante extremo porque ele armazena os dados em cache e forma um segmento completamente diferente da RAM. Como você apontou em seu comentário FLUSH QUERY CACHE(como você sugeriu) ou até RESET QUERY CACHEseria melhor. Para esclarecimento, quando disse "nenhum mecanismo interno", quis dizer exatamente isso. A desfragmentação é necessária e deve ser feita manualmente. Precisaria ser crontab'd .

Se você fizer DML (INSERTs, UPDATEs, DELETEs) no InnoDB com mais frequência do que no MyISAM, eu diria para remover completamente o cache de consulta, o que eu disse no começo.

RUIM: query_cache_size = 1G

Por quê? Por causa de quanto tempo levará uma descarga. Ou seja, quando ocorrer alguma escrita, todo o 1GB será escaneado para encontrar alguma referência à tabela que foi modificada. Quanto maior o QC, mais lento isso é. Eu recomendo um tamanho não superior a 50M, a menos que seus dados raramente mudem.

O QC é overhead para MyISAM e InnoDB. Ele remove um Mutex global e o remove cedo demais. Esse mutex é uma das razões pelas quais o MySQL não pode fazer uso efetivo de mais de 8 núcleos.

SQL_NO_CACHE não é percebido até que o Mutex seja bloqueado! Praticamente o único uso para esse sinalizador é para benchmarking.

Freqüentemente, é melhor dar a RAM para algum outro cache.

Posso pensar em um caso perfeito para isso, e nós o testamos exaustivamente e o executamos em produção... Eu chamo isso de estratégia de agrupamento de "pista rápida" :

Se você fizer divisão de leitura/gravação com um proxy como MaxScale, ou seu aplicativo for capaz, você pode enviar algumas das leituras para essas tabelas raramente invalidadas apenas para escravos que têm o cache de consulta ativado e o restante para outros escravos com ele desligado.

Fazemos isso e, como resultado, lidamos com 4 milhões de chamadas por minuto para o cluster durante nossos testes de carga (não benchmark... o negócio real). O aplicativo espera em master_pos_wait() por algumas coisas, por isso é limitado pelo thread de replicação e, embora o tenhamos visto com um status de espera pela invalidação do Qcache com taxa de transferência muito alta, esses níveis de taxa de transferência são mais altos do que o cluster. capaz de sem Qcache.

Isso funciona porque raramente há algo relevante no pequeno cache de consulta nessas máquinas para invalidar (essas consultas são relevantes apenas para tabelas atualizadas com pouca frequência). Essas caixas são a nossa "pista rápida". Para o restante das consultas que o aplicativo faz, eles não precisam lidar com o Qcache, pois vão para as caixas sem que ele esteja ativado.