MySQL InnoDB: Estabilidade de Cache

Os bancos de dados tem um problema quando são reiniciados: subir novamente os dados mais acessados para o Cache. Até lá, os dados tem que ser acessados novamente nos discos, muitos mais lentos do que a memória. Quando trata-se de alguns poucos GBs isto não é problema, mas a diferença é sensível em instalações maiores.

O Engine InnoDB do MySQL tem uma solução simples para este problema: ao desligar o MySQL um arquivo é criado contendo quais são as páginas que estavam no Cache, para que estas páginas sejam carregadas novamente (antes que o usuário as solicite) para o Cache. Apenas os IDs das páginas é armazenado, e não a página em si, ou seja, o arquivo não será do tamanho do seu Cache (innodb_buffer_pool_size), será um pequeno arquivo. A carga de páginas para o Cache após o início do MySQL é feita por um processo background, e não atrasa o início do banco.

Para habilitar esta função, adicione estas três System Variables no arquivo de configuração (no RHEL / OEL / CentOS é o arquivo /etc/my.cnf), e reinicie o MySQL.

innodb_buffer_pool_filename = InnoDB.dump
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON

A System Variable innodb_buffer_pool_filename apenas define qual será o arquivo, innodb_buffer_pool_dump_at_shutdown é o que indica se os IDs das páginas serão gravados no arquivo, e innodb_buffer_pool_load_at_startup é o que indica se estas páginas serão carregadas após o início do MySQL.

Adicionalmente, a System Variable innodb_buffer_pool_dump_pct define qual o percentual de páginas mais utilizadas que será gravado no arquivo (o padrão na versão 5.7.7 é de 25%).

innodb_buffer_pool_dump_pct = 100

Para um controle mais fino, os IDs das páginas podem ser descarregados para o arquivo enquanto o MySQL está no ar, e estas páginas também podem ser carregadas de volta para o Cache sem reiniciar o MySQL. Isto é útil quando o DBA for fazer uma manutenção no banco ou uma carga de dados, durante a noite, que iria encher o Cache com “lixo”, e no dia seguinte o Cache não estaria mais com as páginas das tabelas de usuários, produtos, estoque, etc.

Para descarregar imediatamente as páginas para o arquivo de Dump, execute o seguinte comando:

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
+--------------------------------+------------------------------------+
| Variable_name                  | Value                              |
+--------------------------------+------------------------------------+
| Innodb_buffer_pool_dump_status | Dumping of buffer pool not started |
+--------------------------------+------------------------------------+
1 row in set (3,54 sec)

mysql> SET GLOBAL innodb_buffer_pool_dump_now = ON;
Query OK, 0 rows affected (0,02 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_dump_status | Buffer pool(s) dump completed at 170107 13:35:24 |
+--------------------------------+--------------------------------------------------+
1 row in set (0,01 sec)

mysql>

E para carrega-las com o MySQL no ar, execute o comando abaixo. Veja que esta é a operação demorada, pois realmente está lendo páginas do disco e carregando para o Cache.

mysql> SET GLOBAL innodb_buffer_pool_load_now = ON;
Query OK, 0 rows affected (0,00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+---------------------------+
| Variable_name                  | Value                     |
+--------------------------------+---------------------------+
| Innodb_buffer_pool_load_status | Loaded 40961/122273 pages |
+--------------------------------+---------------------------+
1 row in set (0,00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+----------------------------+
| Variable_name                  | Value                      |
+--------------------------------+----------------------------+
| Innodb_buffer_pool_load_status | Loaded 118785/122273 pages |
+--------------------------------+----------------------------+
1 row in set (0,00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name                  | Value                                            |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 170107 13:39:06 |
+--------------------------------+--------------------------------------------------+
1 row in set (0,00 sec)

mysql>

Se por algum motivo você quiser cancelar a carga de páginas para o Cache, pode executar o comando abaixo.

mysql> SET GLOBAL innodb_buffer_pool_load_now = ON;
Query OK, 0 rows affected (0,00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+-----------------------+
| Variable_name                  | Value                 |
+--------------------------------+-----------------------+
| Innodb_buffer_pool_load_status | Loaded 1/122273 pages |
+--------------------------------+-----------------------+
1 row in set (0,00 sec)

mysql> SET GLOBAL innodb_buffer_pool_load_abort = ON;
Query OK, 0 rows affected (0,00 sec)

mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+----------------------------------------+
| Variable_name                  | Value                                  |
+--------------------------------+----------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load aborted on request |
+--------------------------------+----------------------------------------+
1 row in set (0,00 sec)

mysql>

Lembre-se que é muito comum no MySQL que os valores padrão, forma de alteração, comportamento e valores das System Variables sejam alterados entre versões, então consulte sempre a documentação correta para a versão que você utiliza.

MySQL 5.7: Saving and Restoring the Buffer Pool State
MySQL 5.7: InnoDB Startup Options and System Variables

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

Esse site utiliza o Akismet para reduzir spam. Aprenda como seus dados de comentários são processados.