InnoDB Buffer Pool

Os exemplos deste artigo contam com uma instalação completamente nova do MySQL, na versão 5.5.18, rodando em CentOS 6.0, conforme exibido abaixo:

[root@mgm01 ~]# rpm -ivh MySQL-server-5.5.18-1.rhel5.i386.rpm
Preparing...             ################################# [100%]
1:MySQL-server           ################################# [100%]
*** PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ***
[root@mgm01 ~]# rpm -ivh MySQL-client-5.5.18-1.rhel5.i386.rpm
Preparing...             ################################# [100%]
   1:MySQL-client        ################################# [100%]
[root@mgm01 ~]# rpm -ivh MySQL-shared-5.5.18-1.rhel5.i386.rpm
Preparing...             ################################# [100%]
   1:MySQL-shared        ################################# [100%]
[root@mgm01 ~]# cp /usr/share/mysql/my-large.cnf /etc/my.cnf
[root@mgm01 ~]# service mysql start
Starting MySQL.....                                      [  OK  ]
 

Muito se tem comentado e discutido sobre a utilização do InnoDB desde que a Oracle colocou no mercado a versão 5.5 do MySQL com o InnoDB Plugin, agora sendo este o Storage Engine padrão do MySQL. Antes disso, talvez fosse mais cômodo somente criar um banco de dados e um bando de tabelas e iniciar os projetos, mas agora, será necessário entender bem como funcionam algumas estruturas do InnoDB, já que as tabelas que você criava antes, agora serão controladas por um motor mais robusto, com integridade referencial, logs para suporte à transação, níveis de isolamento e muitos outros recursos que coloca o MySQL como uma opção robusta para ambientes de missão crítica. Neste post, vou tratar de explicar somente o funcionamento do Buffer Pool, área de memória criada e controlada pelo InnoDB, aonde são armazenados dados e índices de tabelas. Quanto mais dados destes tipos armazenados em memória, mais in-memory será o banco de dados e mais rápido será o trato com informações, seja para recuperação quanto para inserção/atualização de informação.

Em poucas palavras, o InnoDB Buffer Pool é uma estrutura que pode ser configurada através da variável innodb_buffer_pool_size e a quantidade de memória atribuída pode chegar a um número entre 70 e 80% da memória de um host. Na configuração de tal variável de ambiente, um cuidado deverá ser tomado para que esta área não fique grande demais e então seja mal aproveitada pelos dados que podem fragmentar internamente.

Alguns recursos valiosos para evitar tal desproporção ao configurar o InnoDB Buffer Pool são as variáveis de status e também a saída do comando SHOW ENGINE INNODB STATUS. Tanto um quanto o outro poderá orientar o administrador de bancos de dados a ajustar melhor o Buffer Pool. Abaixo, mostro uma parte muito importante da saída do comando SHOW ENGINE INNODB STATUS, que reporta toda a alocação de memória atual pelo InnoDB.

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 136806400; in additional pool allocated 0
Dictionary memory allocated 22706
Buffer pool size   8191
Free buffers       7884
Database pages     306
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 306, written 316
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 306, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Percebam que temos um dicionário de dados de 22706 bytes, 306 páginas de dados dentro do buffer que somam o tamanho total de 8191 bytes de dados e índices, não temos páginas modificadas e nem páginas antigas para serem despejadas (processo de “evicted”, veremos mais à frente). Além disso, não existem escritas pendentes, a LRU atual é também 0 e os contadores de read-ahead, o que também veremos à frente, estão zerados. Vou modificar um pouco estes dados, promovendo algum workload no InnoDB para motivar a sua percepção e vou pedir para que você interprete os resultados abaixo:

----------------------
BUFFER POOL AND MEMORY
----------------------
Total memory allocated 136806400; in additional pool allocated 0
Dictionary memory allocated 25579
Buffer pool size   8191
Free buffers       7735
Database pages     455
Old database pages 0
Modified db pages  0
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 0, not young 0
0.00 youngs/s, 0.00 non-youngs/s
Pages read 0, created 455, written 650
0.00 reads/s, 0.00 creates/s, 0.00 writes/s
No buffer pool page gets since the last printout
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 455, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

Ainda falando a estrutura de armazenamento dos dados e índices de tabelas InnoDB em memória, internamente o Buffer Pool gerencia uma lista baseada no algorítimo LRU ou Least Recently Used (recentemente menos utilizado). Isso faz com os dados mais novos (chamados de “new” ou “young” sublist) sejam colocados na cabeça da lista e os dados mais antigos, e por consequência mais antigos, sejam posicionados na cauda (old sublist) – assim, os dados que não estiverem de acordo com essa lógica serão despejados da memória, cedendo espaço no Buffer Pool para novas entradas.

O registro da quantidade de páginas que foram despejadas sem serem utilizadas estão acessíveis através da variável de status Innodb_buffer_pool_read_ahead_evicted.

[root@mgm01 ~]# mysql -u root -p -e "show status like 'Innodb_buffer_pool_read_ahead%'\G"
Enter password:
*************************** 3. row ***************************
Variable_name: Innodb_buffer_pool_read_ahead_evicted
Value: 167

Segundo o manual online, 3/8 do Buffer Pool é destinado aos dados que pertencem à sublista de dados mais antigos, quando um novo dado chega ao buffer pool, el é inserido em um ponto denominado “midpoint” que é localizado na cabeça da sublista da cauda – isso é interessante pois uma operalçao qualquer iniciada pelo usuário poderá ler tal dado de maneira sequencial chamada read-ahead, que automaticamente realizada pelo InnoDB – o read-ahead é um tipo de leitura que poderá ser randômica, caso grande parte dos dados do tabelspace estiverem em memória ou sequencial quando o mecanismo descobre que os dados dentro de um mesmo segmento podem ser lidos todos de uma vez para a memória. Tal recurso de read-ahead poderá ser configurado através da variável global innodb_read_ahead_threshold.As páginas de dados que são modificados em memória são registrados no log buffer pool, que de tempos em tempos realiza um processo denominado “flush” que atualiza os dados do disco com os dados da memória, ou seja, tudo que foi modificado dentro do buffer pool, agora será gravado em disco. Este comportamento é gerenciado pelo InnoDB com base no valor configurado na variável de ambiente innodb_flush_log_at_trx_commit que tem como seus posíveis valores, os seguintes:

  • 0, os logs em memória são escritos em nos arquivos em disco uma vez a cada segundo, mas nada é feito no momento do COMMIT (este que é registrado no transaction log ao final de cada transação realizada com sucesso);
  • 1, os logs em memória são escritos nos arquivos em disco a cada COMMIT;
  • 2, os logs são escritos para os arquivos de log em disco a cada segundo e a cada COMMIT.

Em um ambiente de replicação, recomenda-se que que a variável innodb_flush_log_at_trx_commit seja configurada com o valor 1 e também sync_binlog seja igual a 1. Isso fará com que as alterações estejam armazenadas no log binário o mais breve possível para que esta seja entregue ao servidor SLAVE. Um outro fato que se deve tomar bastante cuidado é que, caso se configure tal variável igual o 0, dados poderão ser perdidos caso o sistema tenha um “crash” antes do próximo “flush”. Problemas poderão ser notados com o SHOW PROCESSLIST em transações que demoram para ser comitadas  dependendo da maneira como seu sistema foi implementado, a configuração desta variável igual a 2 e o MySQL Query Cache ativado – caso tenha um problema similar, além de me deixar saber (@wagnerbianchijr), desative o MySQL Query Cache e reinicie o MySQL. Ative o MySQL Profiling para verificar o que realmente esteja acontecendo:

mysql> SET profiling =1;
Query OK, 0 rows affected (0.08 sec)
mysql> SHOW PROFILES;
+----------+------------+-------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                   |
+----------+------------+-------------------------------------------------------------------------+
|        1 | 0.00096100 | select name,id,competitions from olympic_games where host_city='Sydney' |
|        2 | 0.00029700 | SET GLOBAL query_cache_size=1024*1024*16                                |
|        3 | 0.00837900 | select name,id,competitions from olympic_games where host_city='Sydney' |
|        4 | 0.00009500 | select name,id,competitions from olympic_games where host_city='Sydney' |
+----------+------------+-------------------------------------------------------------------------+

O flush é um processo que conta também com um método, que é controlado pela variável innodb_flush_method, que poderá ser configurada com o valor O_DSYNC ou O_DIRECT, este último que é o mais indicado para ambientes com muita escrita pois evita uma escrita dupla dos dados no cache do InnoDB e no do Sistema Operacional. O_DSYNC é bom para realização de processos de restore, mas o swap poderá aumentar muito utilizando este método. Para medir o aumento do swap, você pode utilizar tanto o primo rico do top, o HTop ou o vmstat.

O Buffer Pool poderá ser configurado com o valor de mais de 4GB no caso de máquinas servidoras que tenham arquitetura 64 bits, sendo assim, uma novidade bem interessante que foi entregue com o MySQL 5.5 foi a possibilidade de particionar o Buffer Pool. A partir daquela versão você poderá criar, por exemplo, a seguinte configuração:

[mysqld]
innodb_buffer_pool_size = 64G # configurado em uma máquina com RAM total = 80GB
innodb_buffer_pool_instances = 10

Sendo assim, uma instância de MySQL com a configuração acima contará com 10 instâncias de Buffer Pool em memória, possibilitando armazenar o mesmo conjunto de dados, mas este conjunto dividido em pequenos subconjuntos que agiliza as operações com dados, sendo que cada uma das instâncias terá um tamanho de 6554MB ou 6.4GB.

Conclusão

Foi um artigo bem rápido, mas, é interessante falarmos sobre o mecanismo de buffer de dados do InnoDB, este que favorece as operações com dados já que mantém tudo ou quase tudo, na maioria dos casos, em memória. Uma vez que os seus dados forem armazenados na memória, a coisa já funcionará melhor.


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.

AddThis Social Bookmark Button

Leave a Reply