O t\u00edtulo deste artigo \u00e9 bastante sugestivo do ponto de vista de performance em bancos de dados. Geralmente, independente do tipo de particionamento, horizontal ou vertical, ele servir\u00e1 para eliminar overheads em opera\u00e7\u00f5es adicionais na escrita e\/ou recupera\u00e7\u00e3o de dados. Com o InnoDB Buffer Pool, a partir da vers\u00e3o 5.5 n\u00e3o \u00e9 diferente, pois, poderemos utilizar uma nova vari\u00e1vel, aplicada somente ao InnoDB Plugin que nos possibilita dividir o Buffer Pool (\u00e1rea de mem\u00f3ria que armazena \u00edndices e dados de tabelas InnoDB) em v\u00e1rias inst\u00e2ncias, sendo que cada uma das inst\u00e2ncias dever\u00e1 ter no m\u00ednimo 1GB de espa\u00e7o. Ent\u00e3o, neste cen\u00e1rio, caso tenhamos um innodb_buffer_pool_size igual \u00e0 2GB, poderemos ter a vari\u00e1vel innodb_buffer_pool_instances=2.<\/p>\n
As principais vantagens de ser ter um Buffer Pool particionado \u00e9 a possibilidade de que cada uma das inst\u00e2ncias poder controlar sua pr\u00f3pria lista, que \u00e9 baseada no\u00a0algor\u00edtimo\u00a0LRU (Least Recently Used), armazenam bem menos de dados que uma s\u00f3 inst\u00e2ncia, o que possibilita menos tempo para localizar um determinado dado na mem\u00f3ria em meio \u00e0 menos dados.<\/p>\n
Uma boa analogia para a busca do entendimento \u00e9, imagine que voc\u00ea deixa o seu carro em um estacionamento de shopping que tem capacidade para 1000 carros. Voc\u00ea p\u00e1ra o seu carro e se voc\u00ea n\u00e3o tiver uma boa no\u00e7\u00e3o de espa\u00e7o, quando voltar para busc\u00e1-lo poder\u00e1 gastar v\u00e1rios minutos para ach\u00e1-lo. Agora, imagine que este mesmo estacionamento agora conta com setores, algo como A1, A2, B1, B2 e etc. Neste cen\u00e1rio, quando voc\u00ea parar o carro, voc\u00ea saber\u00e1 em qual setor o seu carro est\u00e1 parado, sendo que em cada setor, a lota\u00e7\u00e3o m\u00e1xima \u00e9 de somente 50 carros. Voc\u00ea procura seu carro em meio a um n\u00famero muito menor do que se voc\u00ea tivesse que procur\u00e1-lo em meio \u00e0 todos os carros.<\/p>\n
As configura\u00e7\u00f5es (exemplo) podem ser como seguem:<\/p>\n
O mais\u00a0interessante\u00a0\u00e9, para que o InnoDB Buffer Pool funcione bem, particionado ou n\u00e3o, os dados precisam estar l\u00e1 e para que voc\u00ea, manualmente efetue um “preload” dos dados no buffer, rode esta consulta e depois rode os comandos que ela gerar:\u00a0<\/span><\/p>\n Enquanto roda as consultas finais para carregar os dados no Buffer Pool, voc\u00ea poder\u00e1 utilizar uma interface gr\u00e1fica qualquer para checar a diminui\u00e7\u00e3o do espa\u00e7o configurado para innodb_buffer_pool_size ou mesmo, checar as vari\u00e1veis de status que o MySQL possui para monitorar o InnoDB:<\/p>\n <\/p>\n","protected":false},"excerpt":{"rendered":" O t\u00edtulo deste artigo \u00e9 bastante sugestivo do ponto de vista de performance em bancos de dados. Geralmente, independente do tipo de particionamento, horizontal ou vertical, ele servir\u00e1 para eliminar overheads em opera\u00e7\u00f5es adicionais na escrita e\/ou recupera\u00e7\u00e3o de dados. Com o InnoDB Buffer Pool, a partir da vers\u00e3o 5.5 n\u00e3o \u00e9 diferente, pois, poderemos […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[17,1],"tags":[],"_links":{"self":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/264"}],"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=264"}],"version-history":[{"count":17,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/264\/revisions"}],"predecessor-version":[{"id":298,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/264\/revisions\/298"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=264"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=264"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=264"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}[mysqld]
\ninnodb_buffer_pool_size=16G
\ninnodb_buffer_pool_instances=8
\n<\/code>
\nNo exemplo acima, temos 8 inst\u00e2ncias do Buffer Pool, cada uma delas com 2GB de espa\u00e7o para dados e \u00edndices de tabelas InnoDB. Podemos ainda monitorar o que est\u00e1 acontecendo com cada uma das inst\u00e2ncias de InnoDB Buffer Pool atrav\u00e9s do comando SHOW ENGINE INNODB STATUS, observando a se\u00e7\u00e3o “INDIVIDUAL BUFFER POOL INFO”:<\/p>\n----------------------
\nINDIVIDUAL BUFFER POOL INFO
\n----------------------
\n---BUFFER POOL 0
\nBuffer pool size 131071
\nFree buffers 20999
\nDatabase pages 109854
\nOld database pages 40564
\nModified db pages 2
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 11, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 106393, created 3461, written 70472
\n0.00 reads\/s, 0.02 creates\/s, 0.80 writes\/s
\nBuffer pool hit rate 1000 \/ 1000, young-making rate 0 \/ 1000 not 0 \/ 1000
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 109854, unzip_LRU len: 190
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]
\n---BUFFER POOL 1
\nBuffer pool size 131071
\nFree buffers 20192
\nDatabase pages 110633
\nOld database pages 40859
\nModified db pages 1
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 21, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 107355, created 3278, written 50788
\n0.00 reads\/s, 0.00 creates\/s, 0.48 writes\/s
\nBuffer pool hit rate 1000 \/ 1000, young-making rate 0 \/ 1000 not 0 \/ 1000
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 110633, unzip_LRU len: 219
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]
\n---BUFFER POOL 2
\nBuffer pool size 131071
\nFree buffers 19981
\nDatabase pages 110840
\nOld database pages 40935
\nModified db pages 1
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 11, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 107052, created 3788, written 65778
\n0.00 reads\/s, 0.00 creates\/s, 0.48 writes\/s
\nBuffer pool hit rate 1000 \/ 1000, young-making rate 0 \/ 1000 not 0 \/ 1000
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 110840, unzip_LRU len: 223
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]
\n---BUFFER POOL 3
\nBuffer pool size 131071
\nFree buffers 18616
\nDatabase pages 112208
\nOld database pages 41440
\nModified db pages 1
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 17, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 108448, created 3760, written 48754
\n0.00 reads\/s, 0.00 creates\/s, 0.27 writes\/s
\nBuffer pool hit rate 1000 \/ 1000, young-making rate 0 \/ 1000 not 0 \/ 1000
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 112208, unzip_LRU len: 220
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]
\n---BUFFER POOL 4
\nBuffer pool size 131071
\nFree buffers 23980
\nDatabase pages 106849
\nOld database pages 39461
\nModified db pages 1
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 9, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 103190, created 3659, written 63331
\n0.00 reads\/s, 0.02 creates\/s, 0.70 writes\/s
\nBuffer pool hit rate 1000 \/ 1000, young-making rate 0 \/ 1000 not 0 \/ 1000
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 106849, unzip_LRU len: 217
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]
\n---BUFFER POOL 5
\nBuffer pool size 131071
\nFree buffers 19814
\nDatabase pages 111069
\nOld database pages 41020
\nModified db pages 0
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 14, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 106936, created 4133, written 85900
\n0.00 reads\/s, 0.00 creates\/s, 0.61 writes\/s
\nBuffer pool hit rate 1000 \/ 1000, young-making rate 0 \/ 1000 not 0 \/ 1000
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 111069, unzip_LRU len: 162
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]
\n---BUFFER POOL 6
\nBuffer pool size 131071
\nFree buffers 18889
\nDatabase pages 112005
\nOld database pages 41340
\nModified db pages 1
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 5, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 108175, created 3830, written 83143
\n0.00 reads\/s, 0.00 creates\/s, 0.73 writes\/s
\nBuffer pool hit rate 1000 \/ 1000, young-making rate 0 \/ 1000 not 0 \/ 1000
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 112005, unzip_LRU len: 149
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]
\n---BUFFER POOL 7
\nBuffer pool size 131071
\nFree buffers 19352
\nDatabase pages 111534
\nOld database pages 41189
\nModified db pages 1
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 11, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 107999, created 3535, written 57687
\n0.00 reads\/s, 0.00 creates\/s, 0.41 writes\/s
\nBuffer pool hit rate 1000 \/ 1000, young-making rate 0 \/ 1000 not 0 \/ 1000
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 111534, unzip_LRU len: 158
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]
\n<\/code>
\nPerceba que cada inst\u00e2ncia tem o seu pr\u00f3prio controle de LRU, p\u00e1ginas 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\u00e1ginas e quantidade de buffers livres podem tamb\u00e9m ser observados. Interessante notar que esta se\u00e7\u00e3o somente estar\u00e1 presente na sa\u00edda do SHOW ENGINE INNODB STATUS caso innodb_buffer_pool_instances for maior que zero.<\/p>\nSELECT
\nCONCAT('SELECT ',MIN(c.COLUMN_NAME),' FROM ',c.TABLE_NAME,' WHERE ',MIN(c.COLUMN_NAME),' IS NOT NULL')
\nFROM
\ninformation_schema.COLUMNS AS c
\nLEFT JOIN (
\nSELECT DISTINCT
\nTABLE_SCHEMA,TABLE_NAME,COLUMN_NAME
\nFROM
\ninformation_schema.KEY_COLUMN_USAGE
\n) AS k
\nUSING
\n(TABLE_SCHEMA,TABLE_NAME,COLUMN_NAME)
\nWHERE
\nc.TABLE_SCHEMA = 'yourDatabase'
\nAND k.COLUMN_NAME IS NULL
\nGROUP BY
\nc.TABLE_NAME<\/code><\/p>\nmysql> show status like 'Innodb_buffer_pool%'\\G
\n*************************** 1. row ***************************
\nVariable_name: Innodb_buffer_pool_pages_data
\nValue: 1639
\n*************************** 2. row ***************************
\nVariable_name: Innodb_buffer_pool_pages_dirty
\nValue: 0
\n*************************** 3. row ***************************
\nVariable_name: Innodb_buffer_pool_pages_flushed
\nValue: 2352
\n*************************** 4. row ***************************
\nVariable_name: Innodb_buffer_pool_pages_free
\nValue: 1046928
\n*************************** 5. row ***************************
\nVariable_name: Innodb_buffer_pool_pages_misc
\nValue: 1
\n*************************** 6. row ***************************
\nVariable_name: Innodb_buffer_pool_pages_total
\nValue: 1048568
\n<\/code><\/p>\n