Os exemplos deste artigo contam com uma instala\u00e7\u00e3o completamente nova do MySQL, na vers\u00e3o 5.5.18, rodando em CentOS 6.0, conforme\u00a0exibido\u00a0abaixo:<\/p>\n
[root@mgm01 ~]# rpm -ivh MySQL-server-5.5.18-1.rhel5.i386.rpm
\nPreparing... \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ################################# [100%]
\n1:MySQL-server \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ################################# [100%]
\n*** PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER ***
\n[root@mgm01 ~]# rpm -ivh MySQL-client-5.5.18-1.rhel5.i386.rpm
\n<\/span>Preparing... \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ################################# [100%]
\n<\/span>\u00a0 \u00a01:MySQL-client \u00a0 \u00a0 \u00a0 \u00a0################################# [100%]
\n<\/span>[root@mgm01 ~]# rpm -ivh MySQL-shared-5.5.18-1.rhel5.i386.rpm
\n<\/span>Preparing... \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ################################# [100%]
\n<\/span>\u00a0 \u00a01:MySQL-shared \u00a0 \u00a0 \u00a0 \u00a0################################# [100%]
\n<\/span>[root@mgm01 ~]# cp \/usr\/share\/mysql\/my-large.cnf \/etc\/my.cnf
\n<\/span>[root@mgm01 ~]# service mysql start
\n<\/span>Starting MySQL..... \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0[ \u00a0OK \u00a0]<\/span><\/code>\u00a0<\/span><\/h5>\nMuito se tem comentado e discutido sobre a utiliza\u00e7\u00e3o do InnoDB desde que a Oracle colocou no mercado a vers\u00e3o 5.5 do MySQL com o InnoDB Plugin, agora sendo este o Storage Engine padr\u00e3o do MySQL. Antes disso, talvez fosse mais c\u00f4modo somente criar um banco de dados e um bando de tabelas e iniciar os projetos, mas agora, ser\u00e1 necess\u00e1rio entender bem como funcionam algumas estruturas do InnoDB, j\u00e1 que as tabelas que voc\u00ea criava antes, agora ser\u00e3o controladas por um motor mais robusto, com integridade referencial, logs para suporte \u00e0 transa\u00e7\u00e3o, n\u00edveis de isolamento e muitos outros recursos que coloca o MySQL como uma op\u00e7\u00e3o robusta para ambientes de miss\u00e3o cr\u00edtica. Neste post, vou tratar de explicar somente o funcionamento do Buffer Pool, \u00e1rea de mem\u00f3ria criada e controlada pelo InnoDB, aonde s\u00e3o armazenados dados e \u00edndices de tabelas. Quanto mais dados destes tipos armazenados em mem\u00f3ria, mais\u00a0in-memory<\/em>\u00a0ser\u00e1 o banco de dados e mais\u00a0r\u00e1pido\u00a0ser\u00e1 o trato com informa\u00e7\u00f5es, seja para recupera\u00e7\u00e3o quanto para inser\u00e7\u00e3o\/atualiza\u00e7\u00e3o de informa\u00e7\u00e3o.<\/p>\nEm poucas palavras, o InnoDB Buffer Pool \u00e9 uma estrutura que pode ser configurada atrav\u00e9s da vari\u00e1vel\u00a0innodb_buffer_pool_size<\/em>\u00a0e a quantidade de mem\u00f3ria atribu\u00edda pode chegar a um n\u00famero entre 70 e 80% da mem\u00f3ria de um host. Na configura\u00e7\u00e3o de tal vari\u00e1vel de ambiente, um cuidado dever\u00e1 ser tomado para que esta \u00e1rea n\u00e3o fique grande demais e ent\u00e3o seja mal aproveitada pelos dados que podem fragmentar internamente.<\/p>\nAlguns recursos valiosos para evitar tal despropor\u00e7\u00e3o ao configurar o InnoDB Buffer Pool s\u00e3o as\u00a0vari\u00e1veis de status<\/strong>\u00a0e tamb\u00e9m a sa\u00edda do comando\u00a0SHOW ENGINE INNODB STATUS<\/strong>. Tanto um quanto o outro poder\u00e1 orientar o administrador de bancos de dados a ajustar melhor o Buffer Pool. Abaixo, mostro uma parte muito importante da sa\u00edda do comando\u00a0SHOW ENGINE INNODB STATUS<\/strong>, que reporta toda a aloca\u00e7\u00e3o de mem\u00f3ria atual pelo InnoDB.<\/p>\n----------------------
\nBUFFER POOL AND MEMORY
\n----------------------
\nTotal memory allocated 136806400; in additional pool allocated 0
\nDictionary memory allocated 22706
\nBuffer pool size \u00a0 8191
\nFree buffers \u00a0 \u00a0 \u00a0 7884
\nDatabase pages \u00a0 \u00a0 306
\nOld database pages 0
\nModified db pages \u00a00
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 0, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 0, created 306, written 316
\n0.00 reads\/s, 0.00 creates\/s, 0.00 writes\/s
\nNo buffer pool page gets since the last printout
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 306, unzip_LRU len: 0
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]<\/code><\/p>\nPercebam que temos um dicion\u00e1rio de dados de 22706 bytes, 306 p\u00e1ginas de dados dentro do buffer que somam o tamanho total de 8191 bytes de dados e \u00edndices, n\u00e3o temos p\u00e1ginas modificadas e nem p\u00e1ginas antigas para serem despejadas (processo de “evicted”, veremos mais \u00e0 frente). Al\u00e9m disso, n\u00e3o existem escritas pendentes, a LRU atual \u00e9 tamb\u00e9m 0 e os contadores de read-ahead, o que tamb\u00e9m veremos \u00e0 frente, est\u00e3o zerados.\u00a0Vou modificar um pouco estes dados, promovendo algum workload no InnoDB para motivar a sua percep\u00e7\u00e3o e vou pedir para que voc\u00ea interprete os resultados abaixo:<\/p>\n
----------------------
\nBUFFER POOL AND MEMORY
\n----------------------
\nTotal memory allocated 136806400; in additional pool allocated 0
\nDictionary memory allocated 25579
\nBuffer pool size \u00a0 8191
\nFree buffers \u00a0 \u00a0 \u00a0 7735
\nDatabase pages \u00a0 \u00a0 455
\nOld database pages 0
\nModified db pages \u00a00
\nPending reads 0
\nPending writes: LRU 0, flush list 0, single page 0
\nPages made young 0, not young 0
\n0.00 youngs\/s, 0.00 non-youngs\/s
\nPages read 0, created 455, written 650
\n0.00 reads\/s, 0.00 creates\/s, 0.00 writes\/s
\nNo buffer pool page gets since the last printout
\nPages read ahead 0.00\/s, evicted without access 0.00\/s, Random read ahead 0.00\/s
\nLRU len: 455, unzip_LRU len: 0
\nI\/O sum[0]:cur[0], unzip sum[0]:cur[0]<\/code><\/p>\nAinda falando a estrutura de armazenamento dos dados e \u00edndices de tabelas InnoDB em mem\u00f3ria, internamente o Buffer Pool gerencia uma lista baseada no\u00a0algor\u00edtimo\u00a0LRU ou Least Recently Used (recentemente menos utilizado). Isso faz com os dados mais novos (chamados de “new” ou “young” sublist) sejam colocados na cabe\u00e7a da lista e os dados mais antigos, e por consequ\u00eancia mais antigos, sejam posicionados na cauda (old sublist) – assim, os dados que n\u00e3o estiverem de acordo com essa l\u00f3gica ser\u00e3o despejados da mem\u00f3ria, cedendo espa\u00e7o no Buffer Pool para novas entradas.<\/div>\n<\/div>\n\nO registro da quantidade de p\u00e1ginas que foram despejadas sem serem utilizadas est\u00e3o acess\u00edveis atrav\u00e9s da vari\u00e1vel de status Innodb_buffer_pool_read_ahead_evicted.
\n<\/code><\/p>\n[root@mgm01 ~]# mysql -u root -p -e \"show status like 'Innodb_buffer_pool_read_ahead%'\\G\"
\nEnter password:
\n*************************** 3. row ***************************
\nVariable_name: Innodb_buffer_pool_read_ahead_evicted
\nValue: 167<\/code><\/p>\nSegundo o manual online, 3\/8 do Buffer Pool \u00e9 destinado aos dados que pertencem \u00e0 sublista de dados mais antigos, quando um novo dado chega ao buffer pool, el \u00e9 inserido em um ponto denominado “midpoint” que \u00e9 localizado na cabe\u00e7a da sublista da cauda – isso \u00e9 interessante pois uma operal\u00e7ao qualquer iniciada pelo usu\u00e1rio poder\u00e1 ler tal dado de maneira sequencial chamada read-ahead, que automaticamente realizada pelo InnoDB – o read-ahead \u00e9 um tipo de leitura que poder\u00e1 ser rand\u00f4mica, caso grande parte dos dados do tabelspace estiverem em mem\u00f3ria ou sequencial quando o mecanismo descobre que os dados dentro de um mesmo segmento podem ser lidos todos de uma vez para a mem\u00f3ria. Tal recurso de read-ahead poder\u00e1 ser configurado atrav\u00e9s da vari\u00e1vel global\u00a0innodb_read_ahead_threshold.As p\u00e1ginas de dados que s\u00e3o modificados em mem\u00f3ria s\u00e3o 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\u00f3ria, ou seja, tudo que foi modificado dentro do buffer pool, agora ser\u00e1 gravado em disco. Este comportamento \u00e9 gerenciado pelo InnoDB com base no valor configurado na vari\u00e1vel de ambiente innodb_flush_log_at_trx_commit que tem como seus pos\u00edveis valores, os seguintes:<\/p>\n
\n- 0, os logs em mem\u00f3ria s\u00e3o escritos em nos arquivos em disco uma vez a cada segundo, mas nada \u00e9 feito no momento do COMMIT (este que \u00e9 registrado no transaction log ao final de cada transa\u00e7\u00e3o realizada com sucesso);<\/li>\n
- 1, os logs em mem\u00f3ria s\u00e3o escritos nos arquivos em disco a cada COMMIT;<\/li>\n
- 2, os logs s\u00e3o escritos para os arquivos de log em disco a cada segundo e a cada COMMIT.<\/li>\n<\/ul>\n
Em um ambiente de replica\u00e7\u00e3o, recomenda-se que que a vari\u00e1vel\u00a0innodb_flush_log_at_trx_commit seja configurada com o valor 1 e tamb\u00e9m sync_binlog seja igual a 1. Isso far\u00e1 com que as altera\u00e7\u00f5es estejam armazenadas no log bin\u00e1rio o mais breve poss\u00edvel para que esta seja entregue ao servidor SLAVE. Um outro fato que se deve tomar bastante cuidado \u00e9 que, caso se configure tal vari\u00e1vel igual o 0, dados poder\u00e3o ser perdidos caso o sistema tenha um “crash” antes do pr\u00f3ximo “flush”. Problemas poder\u00e3o ser notados\u00a0com o SHOW PROCESSLIST\u00a0em transa\u00e7\u00f5es que demoram para ser comitadas \u00a0dependendo da maneira como seu sistema foi implementado, a configura\u00e7\u00e3o desta vari\u00e1vel igual a 2 e o MySQL Query Cache ativado – caso tenha um problema similar, al\u00e9m 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:<\/p>\n
mysql> SET profiling =1;
\nQuery OK, 0 rows affected (0.08 sec)
\nmysql> SHOW PROFILES;
\n+----------+------------+-------------------------------------------------------------------------+
\n| Query_ID | Duration \u00a0 | Query \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 |
\n+----------+------------+-------------------------------------------------------------------------+
\n| \u00a0 \u00a0 \u00a0 \u00a01 | 0.00096100 | select name,id,competitions from olympic_games where host_city='Sydney' |
\n| \u00a0 \u00a0 \u00a0 \u00a02 | 0.00029700 | SET GLOBAL query_cache_size=1024*1024*16 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0|
\n| \u00a0 \u00a0 \u00a0 \u00a03 | 0.00837900 | select name,id,competitions from olympic_games where host_city='Sydney' |
\n| \u00a0 \u00a0 \u00a0 \u00a04 | 0.00009500 | select name,id,competitions from olympic_games where host_city='Sydney' |
\n+----------+------------+-------------------------------------------------------------------------+<\/code><\/p>\nO flush \u00e9 um processo que conta tamb\u00e9m com um m\u00e9todo, que \u00e9 controlado pela vari\u00e1vel innodb_flush_method, que poder\u00e1 ser configurada com o valor O_DSYNC ou O_DIRECT, este \u00faltimo que \u00e9 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 \u00e9 bom para realiza\u00e7\u00e3o de processos de restore, mas o swap poder\u00e1 aumentar muito utilizando este m\u00e9todo. Para medir o aumento do swap, voc\u00ea pode utilizar tanto o primo rico do top, o HTop ou o vmstat.<\/p>\n
O Buffer Pool poder\u00e1 ser configurado com o valor de mais de 4GB no caso de m\u00e1quinas 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\u00e3o voc\u00ea poder\u00e1 criar, por exemplo, a seguinte configura\u00e7\u00e3o:<\/p>\n
[mysqld]
\ninnodb_buffer_pool_size = 64G # configurado em uma m\u00e1quina com RAM total = 80GB
\ninnodb_buffer_pool_instances = 10<\/code><\/p>\nSendo assim, uma inst\u00e2ncia de MySQL com a configura\u00e7\u00e3o acima contar\u00e1 com 10 inst\u00e2ncias de Buffer Pool em mem\u00f3ria, possibilitando armazenar o mesmo conjunto de dados, mas este conjunto dividido em pequenos subconjuntos que agiliza as opera\u00e7\u00f5es com dados, sendo que cada uma das inst\u00e2ncias ter\u00e1 um tamanho de 6554MB ou 6.4GB.<\/p>\n
Conclus\u00e3o<\/strong><\/p>\nFoi um artigo bem r\u00e1pido, mas, \u00e9 interessante falarmos sobre o mecanismo de buffer de dados do InnoDB, este que favorece as opera\u00e7\u00f5es com dados j\u00e1 que mant\u00e9m tudo ou quase tudo, na maioria dos casos, em mem\u00f3ria. Uma vez que os seus dados forem armazenados na mem\u00f3ria, a coisa j\u00e1 funcionar\u00e1 melhor.<\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
Os exemplos deste artigo contam com uma instala\u00e7\u00e3o completamente nova do MySQL, na vers\u00e3o 5.5.18, rodando em CentOS 6.0, conforme\u00a0exibido\u00a0abaixo: [root@mgm01 ~]# rpm -ivh MySQL-server-5.5.18-1.rhel5.i386.rpm Preparing… \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ################################# [100%] 1:MySQL-server \u00a0 \u00a0 \u00a0 \u00a0 \u00a0 ################################# [100%] *** PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER *** […]<\/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\/194"}],"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=194"}],"version-history":[{"count":10,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/194\/revisions"}],"predecessor-version":[{"id":205,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=\/wp\/v2\/posts\/194\/revisions\/205"}],"wp:attachment":[{"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=194"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=194"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/wagnerbianchi.com\/blog\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=194"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}