Esquentando o InnoDB Buffer Pool

Send to Kindle

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.

Send to Kindle
This entry was posted in InnoDB, MySQL, optimização, performance. Bookmark the permalink.

5 Responses to Esquentando o InnoDB Buffer Pool

  1. Valerie Parham-Thompson says:

    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

    • marceloaltmann says:

      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 the innodb_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 AND SELECT * 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.

  2. sylar says:

    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!

Deixe uma resposta

O seu endereço de email não será publicado Campos obrigatórios são marcados *


2 + sete =

Você pode usar estas tags e atributos de HTML: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>