Um Olhar Prático no Cache de Consulta do MySQL

Todos os Sistemas de Gerenciamento de Bancos de Dados (SGBD’s) relacionais robustos utilizam caching de memória para ajudar a aprimorar seu desempenho, uma vez que acessar dados de aplicativo, planos de consulta ou metadados do banco de dados em RAM é normalmente muito mais rápido do que repetitivamente recuperar esta mesma informação de disco.

Este artigo compara as estratégias de caching de memória utilizadas pelos diferentes SGBD’s com o MySQL, e então foca na cache de consulta do MySQL.

Estratégias de caching de memória de SGBD’s

Uma discussão detalhada sobre como os SGBD’s executam as suas estratégias de caching poderia facilmente resultar em um extenso trabalho. Portanto, apenas olharemos rapidamente o assunto dando uma visão geral. Existem normalmente três tipos de caches globais usados por SGBD’s relacionais:

1.     Caches de dados que contêm dados freqüentemente-consultados;

2.     Caches de códigos que retêm códigos freqüentemente consultados de SQL ou procedimentos;

3.     Caches de metadados que guardam dados de referência, tais como informações de estrutura de objeto, informações de segurança, dentre outras.

Existem mais estruturas de memória usadas pelos bancos de dados, mas essas três representam a parte principal das demandas de RAM em um SGBD relacional. Trabalhando de forma bottom-up (de baixo para cima), caches de metadados possuem um padrão similar em quase todos os SGBD’s relacionais e carregam os dados de referência do sistema. Alguns exemplos disso seriam a cache de dicionário do Oracle (contido em seu pool compartilhado), a cache de tabela do MySQL e a cache de dicionário do mecanismos de armazenamento InnoDB do MySQL.

As caches de código são usadas para, primeiramente, reduzir o impacto de atividades de "hard parse" (análise gramatical), onde códigos de declarações SQL e de stored procedures são inicialmente executados e verificados sintaticamente, e por segurança algumas vezes o processo de geração de planos de acesso é realizado. O objetivo das caches de código é reusar declarações de código SQL e de procedimentos, de forma que somente “soft parsing” (parsing sem a geração de plano de acesso, outros passos variados e carregamento em cache) é feita. A quantidade de uso de CPU economizada a partir de tais caches pode ser grande. Exemplos incluem a cache de pool/biblioteca compartilhada da Oracle e as caches de SQL/Procedimentos do SQL Server e Sybase.

Caches de dados são aquelas caches de memória mais facilmente compreendidas do banco de dados, pois os conteúdos são os dados da aplicação. Blocos de dados são lidos do disco e armazenados em memória usando cadeias LRU (menos utilizados recentemente) ou outros mecanismos avançados (como Oracle's Touch Count ou MySQL's Midpoint Insertion Strategy que é usada para blocos de índices MyISAM) para manter blocos “quentes” sempre ativos. Alguns SGBD’s permitem que um DBA divida certas áreas da cache de dados para uso específico. Por exemplo, em adição à cache de dados global, o Oracle provê um pool de buffers chamado KEEP que mantém dados de uma tabela em memória RAM e um pool de buffer RECYCLE que previne que a cache padrão seja inundada com blocos de dados pouco referenciados consultados a partir de uma varredura completa na tabela (full table scan) que podem potencialmente forçar blocos de dados freqüentemente consultados e necessitados para fora da memória. O Sybase oferece ao DBA a habilidade de definir caches de dados múltiplas e então associar especificamente certos objetos a elas. O SQL Server oferece uma função PIN (DBCC PINTABLE) que “prega” uma tabela na cache de dados global uma vez que esta é acessada pela primeira vez.

O MySQL oferece várias caches para dados que são gerenciados pelos seus mecanismos de armazenamento. A cache do MyISAM retém blocos de índices em RAM para acesso rápido, com ambas as caches padrão e personalizadas estando disponíveis. Já o mecanismo de armazenamento MySQL InnoDB oferece uma cache de buffer global que carrega ambos os blocos de dados e índices, com suporte ao AWE (Advanced Windowing Extensions) sendo disponível para instalações no Windows. O MySQL também oferece um mecanismo de armazenamento Memory/Heap, que como o nome diz, mantém o conteúdo de uma tabela (e índices) sempre em memória RAM.

O acesso de memória de dados é a panacéia do desempenho?

Alguns DBAs e analistas de desempenho acham que uma cache de dados amplos é a bala de prata para quase todas as dores de cabeça sobre desempenho. Afinal, já que o acesso de memória é muito mais veloz que buscas em discos, a chave para um desempenho excelente tem que ser armazenar tantos dados quanto for possível em memória. Depois que isso for realizado, então provavelmente não importará qual SQL seja enviado ao sistema.

Para analisar se uma cache de dados foi corretamente configurada, um conjunto de taxas foi criado que medem taxas de I/O lógicas (memória) para físicas (disco). O MySQL tem as taxas de hit da cache chave e pool de buffer de InnoDB, Oracle tem a taxa de hit de cache do buffer e Sybase e SQL Server utilizam a taxa de hit da cache de dados. Os DBAs confiam nestas taxas para ajudar a determinar se suas caches de dados eram grandes o bastante, com regras padrão de aceitação com um valor em torno de 90% ou mais necessário para a cache ser denominada “eficaz”. Se a taxa de hit da cache de dados se aproxima deste valor, então muitos DBAs “abençoam” o seu sistema como sendo de alto desempenho.

Pode chocá-lo ouvir isto, mas nada poderia ser mais distante da verdade. Enquanto é certamente verdadeiro que as caches de dados corretamente configuradas desempenham um papel importante em um sistema de banco de dados de alto desempenho, confiar em altas taxas de hit de caches de dados como o indicador único ou primário de um banco de dados de desempenho eficiente é uma péssima prática. Por quê? Considere a seguinte ilustração retirada do excelente artigo do perito em Oracle, Cary Millsap, intitulado “Porque Uma Taxa de Hit de Cache de Buffer de Banco de Dados 99%+ Não É OK":

Duas instruções SQL distintas A e B retornam um conjuntos idêntico de linhas, mas as duas instruções possuem diferentes planos de execução (ver Tabela 1). Qual instrução você preferiria ter em seu sistema?