Início / dba / Perguntas / 56494
Accepted
Safado
Asked: 2014-01-10 13:30:59 +0800 CST

Entendendo as estatísticas do buffer pool do INNODB

  • 772

Depois de ler esta página na documentação do mysql , tentei entender nosso uso atual do InnoDB. Atualmente, alocamos 6 GB de RAM para o pool de buffers. O tamanho do nosso banco de dados é aproximadamente o mesmo. Aqui está a saída de show engine innodb status\G(estamos executando a v5.5)

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 6593445888; in additional pool allocated 0
Dictionary memory allocated 1758417
Buffer pool size   393215
Free buffers       853
Database pages     360515
Old database pages 133060
Modified db pages  300
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 7365790, not young 23099457
0.00 youngs/s, 0.00 non-youngs/s
Pages read 1094342, created 185628, written 543182148
0.00 reads/s, 0.00 creates/s, 37.32 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 360515, unzip_LRU len: 0
I/O sum[2571]:cur[0], unzip sum[0]:cur[0]

Eu queria saber o quão bem estamos utilizando o cache de buffer. Depois de olhar inicialmente para a saída, parecia que estávamos realmente usando, com base no Pages made younge not youngtem números neles e Buffer pool hit rate is 1000 / 10000(o que eu vi em outros lugares na web que isso significa que está sendo muito usado. Verdade?)

O que está me jogando em um loop é por que young-making rateand notestão em 0/1000 e os acessos young/sand non-young/sestão em 0. Isso tudo indica que não está sendo usado, certo?

Alguém pode ajudar a entender isso?

mysql innodb

4 respostas

  1. The Buffer pool size 393215Isso está em páginas e não em bytes.

    Para ver o tamanho do Buffer Pool em GB, execute isto:

    SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

    Database pages 360515Este é o número de páginas com dados dentro do Buffer Pool

    Para ver a quantidade de dados no tamanho do Buffer Pool em GB, execute isto:

    SELECT FORMAT(BufferPoolPages*PageSize/POWER(1024,3),2) BufferPoolDataGB FROM
(SELECT variable_value BufferPoolPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

    Para ver a porcentagem do Buffer Pool em uso, execute isto:

    SELECT CONCAT(FORMAT(DataPages*100.0/TotalPages,2),' %') BufferPoolDataPercentage FROM
(SELECT variable_value DataPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_data') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

    Modified db pages 300Este é o número de páginas no Buffer Pool que precisam ser gravadas de volta no banco de dados. Eles também são chamados de páginas sujas.

    Para ver o espaço ocupado por páginas sujas, execute isto:

    SELECT FORMAT(DirtyPages*PageSize/POWER(1024,3),2) BufferPoolDirtyGB FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value PageSize FROM information_schema.global_status
WHERE variable_name = 'Innodb_page_size') B;

    Para ver a porcentagem de páginas sujas, execute isto:

    SELECT CONCAT(FORMAT(DirtyPages*100.0/TotalPages,2),' %') BufferPoolDirtyPercentage FROM
(SELECT variable_value DirtyPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_dirty') A,
(SELECT variable_value TotalPages FROM information_schema.global_status
WHERE variable_name = 'Innodb_buffer_pool_pages_total') B;

    Quanto às outras coisas na tela, execute isto:

    SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

    Você verá todas as variáveis ​​de status do Buffer Pool. Você pode aplicar as mesmas consultas em tudo o que precisa examinar.

    ATUALIZAÇÃO 2021-10-01 11:41

    Aqui está um script que funcionará no MySQL 5.6, 5.7 e 8.0

    SET @SCH = IF(VERSION()<'5.7','information_schema','performance_schema');

SET @SQLSTMT=CONCAT("SELECT variable_value INTO @HOSTNAME        FROM ",@SCH,".global_variables WHERE variable_name='hostname'");
PREPARE s FROM @SQLSTMT; EXECUTE s ; DEALLOCATE PREPARE s;

SET @SQLSTMT=CONCAT("SELECT variable_value INTO @IBP_SIZE        FROM ",@SCH,".global_variables WHERE variable_name='innodb_buffer_pool_size'");
PREPARE s FROM @SQLSTMT; EXECUTE s ; DEALLOCATE PREPARE s;

SET @SQLSTMT=CONCAT("SELECT variable_value INTO @IBP_PAGES_DATA  FROM ",@SCH,".global_status    WHERE variable_name='Innodb_buffer_pool_pages_data'");
PREPARE s FROM @SQLSTMT; EXECUTE s ; DEALLOCATE PREPARE s;

SET @SQLSTMT=CONCAT("SELECT variable_value INTO @IBP_PAGES_FREE  FROM ",@SCH,".global_status    WHERE variable_name='Innodb_buffer_pool_pages_free'");
PREPARE s FROM @SQLSTMT; EXECUTE s ; DEALLOCATE PREPARE s;

SET @SQLSTMT=CONCAT("SELECT variable_value INTO @IBP_PAGES_MISC  FROM ",@SCH,".global_status    WHERE variable_name='Innodb_buffer_pool_pages_misc'");
PREPARE s FROM @SQLSTMT; EXECUTE s ; DEALLOCATE PREPARE s;

SET @SQLSTMT=CONCAT("SELECT variable_value INTO @IBP_PAGES_TOTAL FROM ",@SCH,".global_status    WHERE variable_name='Innodb_buffer_pool_pages_total'");
PREPARE s FROM @SQLSTMT; EXECUTE s ; DEALLOCATE PREPARE s;

SET @SQLSTMT=CONCAT("SELECT variable_value INTO @IBP_PAGE_SIZE   FROM ",@SCH,".global_status    WHERE variable_name='Innodb_page_size'");
PREPARE s FROM @SQLSTMT; EXECUTE s ; DEALLOCATE PREPARE s;

SET @IBP_PCT_DATA = 100.00 * @IBP_PAGES_DATA / @IBP_PAGES_TOTAL;
SET @IBP_PCT_FREE = 100.00 * @IBP_PAGES_FREE / @IBP_PAGES_TOTAL;
SET @IBP_PCT_MISC = 100.00 * @IBP_PAGES_MISC / @IBP_PAGES_TOTAL;
SET @IBP_PCT_FULL = 100.00 * (@IBP_PAGES_TOTAL - @IBP_PAGES_FREE) / @IBP_PAGES_TOTAL;

SET @initpad = 19;
SET @padding = IF(LENGTH(@HOSTNAME)>@initpad,LENGTH(@HOSTNAME),@initpad);
SET @decimal_places = 5; SET @KB = 1024; SET @MB = POWER(1024,2); SET @GB = POWER(1024,3);

SELECT       'innodb_buffer_pool_size' as 'Option',LPAD(FORMAT(@IBP_SIZE,0),@padding,' ') Value
UNION SELECT 'innodb_buffer_pool_size GB',LPAD(FORMAT(@IBP_SIZE / @GB,0),@padding,' ');

SELECT       'Hostname' Status                ,LPAD(@HOSTNAME,@padding,' ') Value
UNION SELECT 'This Moment'                    ,NOW()
UNION SELECT 'Innodb_page_size'               ,LPAD(FORMAT(@IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_pages_data'  ,LPAD(FORMAT(@IBP_PAGES_DATA ,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_pages_free'  ,LPAD(FORMAT(@IBP_PAGES_FREE ,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_pages_misc'  ,LPAD(FORMAT(@IBP_PAGES_MISC ,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_pages_total' ,LPAD(FORMAT(@IBP_PAGES_TOTAL,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_bytes_data'  ,LPAD(FORMAT(@IBP_PAGES_DATA  * @IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_bytes_free'  ,LPAD(FORMAT(@IBP_PAGES_FREE  * @IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_bytes_misc'  ,LPAD(FORMAT(@IBP_PAGES_MISC  * @IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_bytes_total' ,LPAD(FORMAT(@IBP_PAGES_TOTAL * @IBP_PAGE_SIZE,0),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_data GB'     ,LPAD(FORMAT(@IBP_PAGES_DATA  * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_free KB'     ,LPAD(FORMAT(@IBP_PAGES_FREE  * @IBP_PAGE_SIZE / @KB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_free MB'     ,LPAD(FORMAT(@IBP_PAGES_FREE  * @IBP_PAGE_SIZE / @MB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_free GB'     ,LPAD(FORMAT(@IBP_PAGES_FREE  * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_free GB'     ,LPAD(FORMAT(@IBP_PAGES_FREE  * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_misc KB'     ,LPAD(FORMAT(@IBP_PAGES_MISC  * @IBP_PAGE_SIZE / @KB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_misc MB'     ,LPAD(FORMAT(@IBP_PAGES_MISC  * @IBP_PAGE_SIZE / @MB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_misc GB'     ,LPAD(FORMAT(@IBP_PAGES_MISC  * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Innodb_buffer_pool_total GB'    ,LPAD(FORMAT(@IBP_PAGES_TOTAL * @IBP_PAGE_SIZE / @GB,@decimal_places),@padding,' ')
UNION SELECT 'Percentage Data'                ,LPAD(CONCAT(FORMAT(@IBP_PCT_DATA,2),' %'),@padding,' ')
UNION SELECT 'Percentage Free'                ,LPAD(CONCAT(FORMAT(@IBP_PCT_FREE,2),' %'),@padding,' ')
UNION SELECT 'Percentage Misc'                ,LPAD(CONCAT(FORMAT(@IBP_PCT_MISC,2),' %'),@padding,' ')
UNION SELECT 'Percentage Used'                ,LPAD(CONCAT(FORMAT(@IBP_PCT_FULL,2),' %'),@padding,' ')
;

    Aqui está um exemplo de saída

    $ mysql --table < ibp_review.sql 
+----------------------------+---------------------+
| Option                     | Value               |
+----------------------------+---------------------+
| innodb_buffer_pool_size    |      10,737,418,240 |
| innodb_buffer_pool_size GB |                  10 |
+----------------------------+---------------------+
+--------------------------------+---------------------+
| Status                         | Value               |
+--------------------------------+---------------------+
| Hostname                       |        somehostname |
| This Moment                    | 2021-10-01 11:35:12 |
| Innodb_page_size               |              16,384 |
| Innodb_buffer_pool_pages_data  |             130,061 |
| Innodb_buffer_pool_pages_free  |             524,379 |
| Innodb_buffer_pool_pages_misc  |                 840 |
| Innodb_buffer_pool_pages_total |             655,280 |
| Innodb_buffer_pool_bytes_data  |       2,130,919,424 |
| Innodb_buffer_pool_bytes_free  |       8,591,425,536 |
| Innodb_buffer_pool_bytes_misc  |          13,762,560 |
| Innodb_buffer_pool_bytes_total |      10,736,107,520 |
| Innodb_buffer_pool_data GB     |             1.98457 |
| Innodb_buffer_pool_free KB     |     8,390,064.00000 |
| Innodb_buffer_pool_free MB     |         8,193.42188 |
| Innodb_buffer_pool_free GB     |             8.00139 |
| Innodb_buffer_pool_misc KB     |        13,440.00000 |
| Innodb_buffer_pool_misc MB     |            13.12500 |
| Innodb_buffer_pool_misc GB     |             0.01282 |
| Innodb_buffer_pool_total GB    |             9.99878 |
| Percentage Data                |             19.85 % |
| Percentage Free                |             80.02 % |
| Percentage Misc                |              0.13 % |
| Percentage Used                |             19.98 % |
+--------------------------------+---------------------+
$
    • 30
  2. Best Answer
     Buffer pool hit rate is 1000 / 1000

    Este é o único valor realmente significativo na situação em que você está... e essa situação é que você tem a sorte de ter um buffer pool com uma taxa de acerto perfeita de 100%. Não analise demais o resto, porque não há nada que você precise alterar, a menos que o sistema operacional do servidor esteja com pouca memória, causando troca.

    Os valores jovens/não jovens não são interessantes em um caso em que há pressão zero no buffer pool. O InnoDB está usando, não faz nada sem ele. Se o pool for muito pequeno, as páginas são despejadas e novas páginas são lidas e as outras estatísticas ajudam você a entender isso... mas esse é um problema que você não parece ter.

    O espaço livre "não utilizado" no pool nunca será negligenciado ou deixado ocioso pelo InnoDB se for necessário por qualquer motivo, portanto, o fato de ser gratuito significa apenas que você tem algum espaço para expandir conforme o tamanho do seu trabalho conjunto de dados cresce.

    Isso é tudo o que significa, a menos, é claro, que você reiniciou recentemente o servidor, nesse caso, ele está incompleto. O servidor precisa ser executado por um período completo de uso "normal" (incluindo backups completos) antes que as estatísticas contem toda a história ... seja uma hora, um dia, uma semana, um mês ou um ano, depende da sua aplicação.

    • 18

  3. Eu discordo da avaliação de que "você tem a sorte de ter um buffer pool com uma taxa de acerto perfeita de 100%"

    No topo da saída (que é cortada), há uma linha algo como:

    Per second averages calculated from the last 16 seconds

    Isso me diz que nenhuma leitura aconteceu nos últimos 16 segundos, assim (artificialmente) dando a você uma pontuação perfeita de '1000/1000'.

    0.00 reads/s, 0.00 creates/s, 37.32 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000

    Enquanto isso, houve algumas gravações. Essas foram possivelmente gravações adiadas para liberar páginas 'sujas' ou limpar índices do 'buffer de alteração'.

    Provavelmente também não houve atividade na área jovem/quente nos últimos 16 segundos.

    • 6

  4. O buffer pool é dividido em duas partes, uma lista jovem e uma lista não jovem. A taxa de criação mostra quantas páginas nos conjuntos de buffers estão sendo embaralhadas entre as duas listas.

    Páginas não-jovens são páginas não-jovens sendo feitas (ou seja, sendo lidas do cache. Páginas não-jovens são páginas movidas da lista de jovens porque são muito antigas ou porque a lista de jovens está cheia.

    A taxa em que as páginas são movidas entre os dois depende de quanto do pool de buffers está sendo usado no momento versus o tamanho do pool jovem. Definir em zero significa que seu conjunto ativo (as páginas que você está usando) é menor que o conjunto jovem.

    • 0

relate perguntas