Uma das mais importantes configurações para quem usa InnoDB é o innodb_buffer_pool_size, ele basicamente armazena dados e índices em memória, quando o MySQL recebe uma query e as paginas que contem o resultado da pesquisa/índice estao armazenadas no buffer, o MySQL não precisar ler essas informações do disco, o que é muito mais rápido (velocidade da memória vs velocidade do disco) .
Como estas informações estão armazenadas na memória, cada vez que o MySQL é re-iniciado, o buffer é apagado, e para “esquentar” o buffer normalmente leva-se algum tempo.
Para agilizar este processo, vamos utilizar 2 variáveis para salvar e restaurar as referencias das paginas que estão armazenadas no buffer, esta é uma nova funcionalidade adicionada no MySQL 5.6 (essa opção já pode ser encontrada em algumas versões anteriores do Percona Server e MariaDB )
Se o seu servidor já está rodando e com o buffer “quente”, vamos setar a variável innodb_buffer_pool_dump_at_shutdown para fazer efeito no próximo shutdown ou restart.
SET GLOBAL innodb_buffer_pool_dump_at_shutdown = 1;
Agora, no próximo shutdown ou restart, um arquivo chamado ib_buffer_pool será criado no datadir do MySQL (você pode alterar este nome configurando a variável innodb_buffer_pool_filename ).
Próximo passo é configurar o MySQL para que ele leia o conteúdo deste arquivo e carregue os dados de volta para o InnoDB buffer, para isso, vamos adicionar a variável innodb_buffer_pool_load_at_startup ao my.cnf (e também a variável innodb_buffer_pool_dump_at_shutdown, para que o MySQL salve o buffer pool a cada shutdown/restart):
[mysqld] innodb_buffer_pool_dump_at_shutdown = 1 innodb_buffer_pool_load_at_startup = 1
Agora podemos reiniciar o MySQL, como mencionado anteriormente, um arquivo será criado no datadir do MySQL, o conteúdo é similar ao seguinte:
[root@marcelodb data]# tail ib_buffer_pool 0,69618 0,69619 0,69620 0,69621 0,69622 0,69623 0,69624 0,69625 0,69626 0,69627
Quando reiniciamos o MySQL, veremos 2 mensagens no log:
2014-01-07 19:53:54 7fad34bc7700 InnoDB: Dumping buffer pool(s) to .//ib_buffer_pool 2014-01-07 19:53:54 7fad34bc7700 InnoDB: Buffer pool(s) dump completed at 140107 19:53:54
Isto significa que o MySQL escreveu o arquivo contendo as referencias as paginas que estavam armazenadas no InnoDB buffer poll.
2014-01-07 19:54:01 7f68eee67700 InnoDB: Buffer pool(s) load completed at 140107 19:54:01
Esta mensagem, significa que o MySQL terminou de restaurar o conteúdo do InnoDB buffer pool de volta.
Este progresso pode ser controlado através das variáveis Innodb_buffer_pool_dump_status e Innodb_buffer_pool_load_status:
mysql> SHOW STATUS LIKE 'Innodb_buffer_pool_load_status'; +--------------------------------+------------------------+ | Variable_name | Value | +--------------------------------+------------------------+ | Innodb_buffer_pool_load_status | Loaded 5121/6441 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 140108 16:55:05 | +--------------------------------+--------------------------------------------------+ 1 row in set (0.00 sec)
Case deseja, pode se salvar/restaurar o conteúdo do InnoDB buffer pool através da variáveis innodb_buffer_pool_dump_now e innodb_buffer_pool_load_now.
Caso utilize InnoDB como storage engine em alguma tabela, este processo pode trazer grandes benefícios e minimizar os impactos de um restart.
This is good stuff.
Have you tested moving this file to a new slave to warm up its buffer pool? If that works, this might be an easy way to do that with failover.
Also wonder if you have used this on a high-volume production server, and if it causes performance issues (I imagine it’s not “cost-free”).
Valerie
Hi Valerie, thanks for your questions.
I did a test but it turns out that it does not work to move the file from one instance to other, see bellow:
master [localhost] {msandbox} ((none)) > SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
| 13717 |
+----------+
1 row in set (0.22 sec)
master [localhost] {msandbox} ((none)) > SET GLOBAL innodb_buffer_pool_dump_now = 1;
Query OK, 0 rows affected (0.00 sec)
master [localhost] {msandbox} ((none)) > quit
Bye
[root@marcellodb rsandbox_5_6_15]# cp master/data/ib_buffer_pool node1/data/
cp: overwrite `node1/data/ib_buffer_pool'? y
You have mail in /var/spool/mail/root
[root@marcellodb rsandbox_5_6_15]# node1/use
slave1 [localhost] {msandbox} ((none)) > SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
| 14 |
+----------+
1 row in set (0.16 sec)
slave1 [localhost] {msandbox} ((none)) > SET GLOBAL innodb_buffer_pool_load_now=1;
Query OK, 0 rows affected (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';
+--------------------------------+--------------------------------------------------+
| Variable_name | Value |
+--------------------------------+--------------------------------------------------+
| Innodb_buffer_pool_load_status | Buffer pool(s) load completed at 140114 17:25:48 |
+--------------------------------+--------------------------------------------------+
1 row in set (0.00 sec)
slave1 [localhost] {msandbox} ((none)) > SELECT COUNT(*) FROM information_schema.INNODB_BUFFER_PAGE WHERE TABLE_NAME IS NOT NULL;
+----------+
| COUNT(*) |
+----------+
| 14 |
+----------+
1 row in set (0.12 sec)
slave1 [localhost] {msandbox} ((none)) >
Regarding to the costs, I ran some tests with a 12gb instance in a server where iowait saturation is 4.16% (based on
echo "( 1 / `cat /proc/cpuinfo | grep processor | wc -l`) * 100" | bc -l
) using theinnodb_buffer_pool_load_at_startup
variable it took 1 minute and 16 seconds the iowait was around 2%, in comparison with normal warmup (SELECT * FROM TABLE
ANDSELECT * FROM TABLE FORCE INDEX(index)
) it took 1 minute and 20 (not a big difference) but the iowait reached a max of 20%, so, yes, there is a cost (some IO), but is far less then a normal warm-up.Thanks for sharing the test outcomes.
Good job! In mysql5.5, I often execute the query “select count(*) from tb_name” for each table to warm up the innodb buffer pool by a shell script ^.^
thanks, it’s a nice pos!
Hi sylar,
Note that a simple
SELECT COUNT(*) FROM tb_name
will usually load the table data and the primary key into the buffer, if you have secondary keys you need to run aSELECT COUNT(*) FROM tb_name FORCE INDEX(key_name)
for each secondary key.yes,that’s right
is this workable to dump in master node and load in slave node to warm up slave ?
I use xtrabackup to build slave , but concerns the page_id is not exactly the same .