O título deste artigo é bastante sugestivo do ponto de vista de performance em bancos de dados. Geralmente, independente do tipo de particionamento, horizontal ou vertical, ele servirá para eliminar overheads em operações adicionais na escrita e/ou recuperação de dados. Com o InnoDB Buffer Pool, a partir da versão 5.5 não é diferente, pois, poderemos utilizar uma nova variável, aplicada somente ao InnoDB Plugin que nos possibilita dividir o Buffer Pool (área de memória que armazena índices e dados de tabelas InnoDB) em várias instâncias, sendo que cada uma das instâncias deverá ter no mínimo 1GB de espaço. Então, neste cenário, caso tenhamos um innodb_buffer_pool_size igual à 2GB, poderemos ter a variável innodb_buffer_pool_instances=2.

As principais vantagens de ser ter um Buffer Pool particionado é a possibilidade de que cada uma das instâncias poder controlar sua própria lista, que é baseada no algorítimo LRU (Least Recently Used), armazenam bem menos de dados que uma só instância, o que possibilita menos tempo para localizar um determinado dado na memória em meio à menos dados.

Uma boa analogia para a busca do entendimento é, imagine que você deixa o seu carro em um estacionamento de shopping que tem capacidade para 1000 carros. Você pára o seu carro e se você não tiver uma boa noção de espaço, quando voltar para buscá-lo poderá gastar vários minutos para achá-lo. Agora, imagine que este mesmo estacionamento agora conta com setores, algo como A1, A2, B1, B2 e etc. Neste cenário, quando você parar o carro, você saberá em qual setor o seu carro está parado, sendo que em cada setor, a lotação máxima é de somente 50 carros. Você procura seu carro em meio a um número muito menor do que se você tivesse que procurá-lo em meio à todos os carros.

As configurações (exemplo) podem ser como seguem:

[mysqld]
innodb_buffer_pool_size=16G
innodb_buffer_pool_instances=8

No exemplo acima, temos 8 instâncias do Buffer Pool, cada uma delas com 2GB de espaço para dados e índices de tabelas InnoDB. Podemos ainda monitorar o que está acontecendo com cada uma das instâncias de InnoDB Buffer Pool através do comando SHOW ENGINE INNODB STATUS, observando a seção “INDIVIDUAL BUFFER POOL INFO”:

----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size 131071
Free buffers 20999
Database pages 109854
Old database pages 40564
Modified db pages 2
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 106393, created 3461, written 70472
0.00 reads/s, 0.02 creates/s, 0.80 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: 109854, unzip_LRU len: 190
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size 131071
Free buffers 20192
Database pages 110633
Old database pages 40859
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 21, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 107355, created 3278, written 50788
0.00 reads/s, 0.00 creates/s, 0.48 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: 110633, unzip_LRU len: 219
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size 131071
Free buffers 19981
Database pages 110840
Old database pages 40935
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 107052, created 3788, written 65778
0.00 reads/s, 0.00 creates/s, 0.48 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: 110840, unzip_LRU len: 223
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size 131071
Free buffers 18616
Database pages 112208
Old database pages 41440
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 17, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108448, created 3760, written 48754
0.00 reads/s, 0.00 creates/s, 0.27 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: 112208, unzip_LRU len: 220
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size 131071
Free buffers 23980
Database pages 106849
Old database pages 39461
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 9, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 103190, created 3659, written 63331
0.00 reads/s, 0.02 creates/s, 0.70 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: 106849, unzip_LRU len: 217
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size 131071
Free buffers 19814
Database pages 111069
Old database pages 41020
Modified db pages 0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 14, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 106936, created 4133, written 85900
0.00 reads/s, 0.00 creates/s, 0.61 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: 111069, unzip_LRU len: 162
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size 131071
Free buffers 18889
Database pages 112005
Old database pages 41340
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 5, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 108175, created 3830, written 83143
0.00 reads/s, 0.00 creates/s, 0.73 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: 112005, unzip_LRU len: 149
I/O sum[0]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size 131071
Free buffers 19352
Database pages 111534
Old database pages 41189
Modified db pages 1
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 11, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 107999, created 3535, written 57687
0.00 reads/s, 0.00 creates/s, 0.41 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: 111534, unzip_LRU len: 158
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Perceba que cada instância tem o seu próprio controle de LRU, páginas jovens e velhas, assim como aquelas que se tornaram jovens por serem mais requisitadas e aquelas que se tornaram velhas por serem pouco requisitadas. Quantidade de páginas e quantidade de buffers livres podem também ser observados. Interessante notar que esta seção somente estará presente na saída do SHOW ENGINE INNODB STATUS caso innodb_buffer_pool_instances for maior que zero.

O mais interessante é, para que o InnoDB Buffer Pool funcione bem, particionado ou não, os dados precisam estar lá e para que você, manualmente efetue um “preload” dos dados no buffer, rode esta consulta e depois rode os comandos que ela gerar: 

SELECT
CONCAT('SELECT ',MIN(c.COLUMN_NAME),' FROM ',c.TABLE_NAME,' WHERE ',MIN(c.COLUMN_NAME),' IS NOT NULL')
FROM
information_schema.COLUMNS AS c
LEFT JOIN (
SELECT DISTINCT
TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
FROM
information_schema.KEY_COLUMN_USAGE
) AS k
USING
(TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
WHERE
c.TABLE_SCHEMA = 'yourDatabase'
AND k.COLUMN_NAME IS NULL
GROUP BY
c.TABLE_NAME

Enquanto roda as consultas finais para carregar os dados no Buffer Pool, você poderá utilizar uma interface gráfica qualquer para checar a diminuição do espaço configurado para innodb_buffer_pool_size ou mesmo, checar as variáveis de status que o MySQL possui para monitorar o InnoDB:

mysql> show status like 'Innodb_buffer_pool%'\G
*************************** 1. row ***************************
Variable_name: Innodb_buffer_pool_pages_data
Value: 1639
*************************** 2. row ***************************
Variable_name: Innodb_buffer_pool_pages_dirty
Value: 0
*************************** 3. row ***************************
Variable_name: Innodb_buffer_pool_pages_flushed
Value: 2352
*************************** 4. row ***************************
Variable_name: Innodb_buffer_pool_pages_free
Value: 1046928
*************************** 5. row ***************************
Variable_name: Innodb_buffer_pool_pages_misc
Value: 1
*************************** 6. row ***************************
Variable_name: Innodb_buffer_pool_pages_total
Value: 1048568


You can follow any responses to this entry through the RSS 2.0 feed. You can leave a response, or trackback from your own site.