{"id":206,"date":"2011-11-24T18:21:17","date_gmt":"2011-11-24T21:21:17","guid":{"rendered":"http:\/\/wagnerbianchi.com\/blog\/?p=206"},"modified":"2011-11-24T19:11:57","modified_gmt":"2011-11-24T22:11:57","slug":"analisando-o-innodb-buffer-pool","status":"publish","type":"post","link":"http:\/\/wagnerbianchi.com\/blog\/?p=206","title":{"rendered":"Analisando o InnoDB Buffer Pool"},"content":{"rendered":"

A primeira coisa a se fazer ao se trabalhar com o InnoDB \u00e9 utilizar as vari\u00e1veis de status para checar se a configura\u00e7\u00e3o atual do Buffer Pool, definida em innodb_buffer_pool_size, satisfaz as necessidades dos bancos de dados atualmente armazenados no MySQL. Como j\u00e1 abordei aqui no blog, em outro post, manter os dados (e \u00edndices principalmente) em mem\u00f3ria \u00e9 a melhor op\u00e7\u00e3o para se obter boa performance de um banco de dados e no caso do MySQL + InnoDB n\u00e3o \u00e9 diferente…<\/p>\n

Selecione as vari\u00e1veis de status que interessa…<\/p>\n

mysql> show status like 'innodb_buffer_pool%';
\n+---------------------------------------+------------+
\n| Variable_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Value \u00a0 \u00a0 \u00a0|
\n+---------------------------------------+------------+
\n| Innodb_buffer_pool_pages_data \u00a0 \u00a0 \u00a0 \u00a0 | 392124 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_pages_dirty \u00a0 \u00a0 \u00a0 \u00a0| 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_pages_flushed \u00a0 \u00a0 \u00a0| 15949040 \u00a0 |
\n| Innodb_buffer_pool_pages_free \u00a0 \u00a0 \u00a0 \u00a0 | 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_pages_misc \u00a0 \u00a0 \u00a0 \u00a0 | 1092 \u00a0 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_pages_total \u00a0 \u00a0 \u00a0 \u00a0| 393215 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_read_ahead_rnd \u00a0 \u00a0 | 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_read_ahead \u00a0 \u00a0 \u00a0 \u00a0 | 8154 \u00a0 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_read_ahead_evicted | 252 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_read_requests \u00a0 \u00a0 \u00a0| 1444481964 |
\n| Innodb_buffer_pool_reads \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| 7502 \u00a0 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_wait_free \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_write_requests \u00a0 \u00a0 | 148957406 \u00a0|
\n+---------------------------------------+------------+
\n13 rows in set (0.00 sec)<\/code><\/p>\n

<\/code>Como nesta inst\u00e2ncia n\u00e3o estou utilizando compress\u00e3o de dados, as p\u00e1ginas de dados do InnoDB continuam com o valor padr\u00e3o que \u00e9 16KB cada. Atrav\u00e9s da vari\u00e1vel de status Innodb_buffer_pool_pages_data temos o n\u00famero total de p\u00e1ginas atualmente dentro do Buffer Pool. Fazendo uma aritim\u00e9tica simples, Innodb_buffer_pool_pages_data*16KB, temos a quantidade em KB da quantidade de dados que preenche o buffer neste momento.<\/p>\n

mysql> select (392124*16) pages;
\n+---------+
\n| pages \u00a0 |
\n+---------+
\n| 6273984 |
\n+---------+
\n1 row in set (0.02 sec)<\/code><\/p>\n

Transforme o resultado de bytes em giga:
\n<\/code><\/p>\n

mysql> select 6273984\/1024\/1024;
\n+-------------------+
\n| 6273984\/1024\/1024 |
\n+-------------------+
\n| 5.98333740 \u00a0 \u00a0 \u00a0 \u00a0|
\n+-------------------+
\n1 row in set (0.00 sec)<\/code><\/p>\n

E ent\u00e3o compare a efetividade entre a quantidade de dados que est\u00e3o dentro do buffer e o valor configurado para aquela \u00e1rea de mem\u00f3ria:<\/p>\n

mysql> select format(6273984\/1024\/1024,2) 'dadosNoBuffer',
\n-> format(@@innodb_buffer_pool_size\/1024\/1024\/1024,0) 'valorConfigurado';
\n+---------------+------------------+
\n| dadosNoBuffer | valorConfigurado |
\n+---------------+------------------+
\n| 5.98 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| 6 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n+---------------+------------------+
\n1 row in set (0.00 sec)<\/code><\/p>\n

Vimos que o Buffer Pool est\u00e1 todo tomado por dados e, caso Innodb_buffer_pool_reads for maior que zero e Innodb_buffer_pool_pages_free for igual a zero, considere aumentar uma pouco o tamanho do Buffer Pool, uma vez que:
\n<\/code><\/p>\n

mysql> show status like 'innodb_buffer_pool%';
\n+---------------------------------------+------------+
\n| Variable_name \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 | Value \u00a0 \u00a0 \u00a0|
\n+---------------------------------------+------------+
\n| Innodb_buffer_pool_pages_data \u00a0 \u00a0 \u00a0 \u00a0 | 392123 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_pages_dirty \u00a0 \u00a0 \u00a0 \u00a0| 1 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_pages_flushed \u00a0 \u00a0 \u00a0| 15949040 \u00a0 |
\n| Innodb_buffer_pool_pages_free \u00a0 \u00a0 \u00a0 \u00a0 | 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_pages_misc \u00a0 \u00a0 \u00a0 \u00a0 | 1092 \u00a0 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_pages_total \u00a0 \u00a0 \u00a0 \u00a0| 393215 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_read_ahead_rnd \u00a0 \u00a0 | 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_read_ahead \u00a0 \u00a0 \u00a0 \u00a0 | 8154 \u00a0 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_read_ahead_evicted | 252 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_read_requests \u00a0 \u00a0 \u00a0| 1444481964 |
\n| Innodb_buffer_pool_reads \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| 7502 \u00a0 \u00a0 \u00a0 |
\n| Innodb_buffer_pool_wait_free \u00a0 \u00a0 \u00a0 \u00a0 \u00a0| 0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| Innodb_buffer_pool_write_requests \u00a0 \u00a0 | 148957406 \u00a0|
\n+---------------------------------------+------------+
\n13 rows in set (0.00 sec)<\/code>
\n<\/strong><\/p>\n

Innodb_buffer_pool_reads<\/strong> -> leitura de dados do disco que n\u00e3o foram satisfeitas ao tentar ler dados do Buffer Pool, ou seja, os dados n\u00e3o est\u00e3o l\u00e1 por n\u00e3o haver mais espa\u00e7o para armazen\u00e1-los;<\/p>\n

Innodb_buffer_pool_pages_free<\/strong> -> quantidade de p\u00e1ginas ainda dispon\u00edveis para armazenar dados no Buffer Pool;<\/p>\n

At\u00e9…<\/p>\n","protected":false},"excerpt":{"rendered":"

A primeira coisa a se fazer ao se trabalhar com o InnoDB \u00e9 utilizar as vari\u00e1veis de status para checar se a configura\u00e7\u00e3o atual do Buffer Pool, definida em innodb_buffer_pool_size, satisfaz as necessidades dos bancos de dados atualmente armazenados no MySQL. Como j\u00e1 abordei aqui no blog, em outro post, manter os dados (e \u00edndices […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[17],"tags":[],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/206"}],"collection":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=206"}],"version-history":[{"count":8,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/206\/revisions"}],"predecessor-version":[{"id":212,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/206\/revisions\/212"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=206"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=206"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=206"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}