Esta é uma mensagem de erro que acontece em muitos dos servidores de bancos de dados MySQL instalados aí pelo mundo e muitos dos usuários se vêem em uma situação que talvez não tenha solução. Há bastante tempo eu tenho respondido à fóruns de discussão que tratam do tema que é simples de resolver. A minha esperança é que o google indexe logo o título deste post para que tal informação de como se livrar da mensagem MySQL server has gone away em meio à operações de carga de dados, restore de um banco ou mesmo, em meio às interações do aplicação com o servidor MySQL.

Existe uma variável de ambiente no MySQL que controla este comportamento e também, o tamanho máximo dos pacotes que podem trafegar nas threads do MySQL. Você deve saber que cada thread é um conexão e você poderá ter informações sobre elas através do comando SHOW PROCESSLIST. O tamanho inicial é configurado na variável net_buffer_lentgh e o tamanho máximo é configurado em max_allowed_packet – esta variável que poderá ter um valor pequeno para o sistema que já se tornou grande.

Por exemplo, no início deste ano iniciei os trabalhos com um cliente no Brasil e precisamos na quele momento fazer a carga de grande de quantidade de dados em XML, que é um tipo de log que o cliente armazena para devidos fins. Ao iniciar as cargas com os arquivos de mais ou menos 300GB por vez, nos deparamos com o “MySQL server has gone away” ou em Português, “O MySQL foi embora”. Não é para mim uma mensagem e nem um comportamento novo, e foi somente fazer alguns ajustes no my.cnf, mais precisamente, na variável max_allowed_packet e tudo se resolveu:

[root@motoserver189 ~]# mysql -u root -p imoin_package < /files/log1765390.dump
ERROR 2006 (HY000) at line 59: MySQL server has gone away

# Alteramos o valor de max_allowed_packet para comportar pacotes maiores nas threads do MySQL

[mysqld]
max_allowed_packet=1024M

# Reiniciamos o servidor de bancos de dados MySQL para que as alterações passam a valer

[root@motoserver189 ~]# service mysql restart
Starting MySQL....................................... SUCCESS!

# Tentamos novamente e como agora vai dar tudo certo, embrulhamos o comando de restore no nohup que passa a execução do processo para o processo do Linux para se caso nossa conexão com o servidor seja fechada, o processo de restore não sofrerá nenhum impacto.

[root@motoserver189 ~]# nohup mysql -u root -p imoin_package < /files/log1765390.dump &
[1] 26303
[root@bd14 mysql]# nohup: appending output to `nohup.out'

Até…

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


Essa semana tive um problema grave em um cliente que precisava escalar o números de conexões simultâneas no MySQL de forma que estas conexões superassem o número de 2000. Vários problemas foram analisados, desde pontos básicos como configurações do próprio servidor de bancos de dados MySQL como alguns pontos relacionados ao Kernel. Somente para situar o leitor em relação ao que foi analisado, no MySQL, temos duas variáveis muito importantes que determinam a quantidade de usuários que podem se conectar ao servidor de bancos de dados e também o números de tais conexões que podem acontecer em um mesmo momento, ou seja, simultâneas.

1. max_connections – esse é o parâmetro que controla a quantidade de usuários que podem se conectar ao MySQL;
2. max_user_connections – esse é o parâmetro utilizado para configurar a quantidade de conexões simultâneas que podem acontecer durante o funcionamento do MySQL – segundo o manual, manter o valor desta variável como zero, é o mesmo que dizer que a coisa é ilimitada;

Interessante sabermos que, além das variáveis de ambiente, que são utilizadas para configurar os vários aspectos do MySQL e é com elas que realizamos o tuning, temos também as variáveis de status que cobrem todo o funcionamento do MySQL e é através delas que consultamos o que está acontecendo com o servidor de bancos de dados. Com as questões relacionadas com usuários não é diferente, veja só:

mysql> show status like '%conn%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         |    0  |
| Connections              | 1387  |
| Max_used_connections     |  645  |
| Ssl_client_connects      |    0  |
| Ssl_connect_renegotiates |    0  |
| Ssl_finished_connects    |    0  |
| Threads_connected        |  581  |
+--------------------------+-------+

Sem pensar nas variáveis SSL que foram retornadas na consulta acima, temos três variáveis bastante importantes: Aborted_connects, Connections, Max_used_connections. Cada uma delas tem um significado ligado diretamente às conexões de clientes/usuários com o servidor de bancos de dados.

1. Aborted_Connects: se o número desta variável de status estiver alto você poderá estar perdendo conexões por quebra das mesmas, sua aplicação não está chamando um método de de “connection_close” antes de fechar a conexão ou mesmo, seu MySQL está evitando consultas;
2. Connections: é número total de conexões que já acontecerão desde o último restart;
3. Max_used_connections: é o número de conexões simultâneas que acontecerão desde  o último restart.

Sendo assim, já temos um norte para trabalhar questões de escala de conexões com o MySQL. Já ouvi dizer sobre escalonador de threads e parâmetros de kernel do Linux, mas, a coisa pode ser mais simples que isso. As configirações atuais de um servidor que tenho monitorado são as seguintes:

mysql> show variables like 'max_connections';
+-----------------+-------+
| Variable_name   | Value |
+-----------------+-------+
| max_connections | 6000  |
+-----------------+-------+
1 row in set (0.00 sec)

mysql> show variables like 'max_user%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_user_connections | 0     |
+----------------------+-------+
1 row in set (0.00 sec)

Com base nisso, passei a observar que quando as conexões atingiam o número de 1000 acontecendo de forma simultânea, ví que o valor da variável de status Aborted_Connects iniciava a aumentar freneticamente e  quando eu tentava acessar o MySQL via mysql client com qualuqer usuário, o seguinte mensagem de erro era enviada:

Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug

Ou seja, ou você está rodando o servidor de bancos de dados configurado com um valor de memória além daquele que o servidor tem disponível ou existe um bug no sistema operacional. Por eliminação, o servidor aonde roda este MySQL tem 64GB e está com 16GB sobrando. Então, esse problema tem havir com algo no sistema operacional. Pesquisando no internet, puder ver que outros amigos tiveram um cenário parecido e também criaram um blog sobre o assunto, como fez o amigo Dimitri em http://bit.ly/trVqL4.

Seguindo mais ou menos o que ele relatou nesse seu blog, eu tinha os memos parâmetros de ulimit para o usuário mysql (su – mysql), mas tinha um valor diferente para threads-max, um valor muito inferior ao mostrado por ele no blog, que é 2065067. Então foi assim que procedi:

1. Configurei a qtd máxima de threads: echo “2065067” > /proc/sys/kernel/threads-max
2. Configurei o arquivo “limits.conf” para as sessões dos usuários mysql e root:

mysql soft nofile 10240
mysql hard nofile 40960
mysql soft nproc 10240
mysql hard nproc 40960
root soft nofile 10240
root hard nofile 40960
root soft nproc 10240
root hard nproc 40960

A configuração número dois me pareceu muito familiar e foi bem aceita, pois, isso já é realizado quando se instala o Oracle Database. Após feito isso, foram realizados vários um testes de stress com o mysqlslap, biblioteca de benchmark do próprio MySQL e o problema persistia. Vários binários foram testados para verificar questões de escala entre uma versão e outra:

MySQL Oracle 5.5.17

mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 1135 Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
mysqlslap: Error when connecting to server: 1135 Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
mysqlslap: Error when connecting to server: 1135 Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 1135 Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
Benchmark
Average number of seconds to run all queries: 4.117 seconds
Minimum number of seconds to run all queries: 4.117 seconds
Maximum number of seconds to run all queries: 4.117 seconds
Number of clients running queries: 1200
Average number of queries per client: 0

MySQL Oracle 5.0.92

mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
Benchmark
Average number of seconds to run all queries: 3.049 seconds
Minimum number of seconds to run all queries: 3.049 seconds
Maximum number of seconds to run all queries: 3.049 seconds
Number of clients running queries: 1200
Average number of queries per client: 0

Percona Server 5.5.17

mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
mysqlslap: Error when connecting to server: 2001 Can't create UNIX socket (24)
Benchmark
Average number of seconds to run all queries: 4.137 seconds
Minimum number of seconds to run all queries: 4.137 seconds
Maximum number of seconds to run all queries: 4.137 seconds
Number of clients running queries: 1200
Average number of queries per client: 0

Os testes acima foram realizados em uma mesma máquina com um arquivo de configuração padrão, somente com o valor de max_connections=6000 e max_user_connections=o.

[root@server mysql-coms]# my_print_defaults mysqld
--skip-external-locking
--port=3306
--socket=/var/lib/mysql/mysql.sock
--max_connections=6000
--max_user_connections=0

Uma saída lógica foi checar de mais de perto o erro “Error when connecting to server: 2001 Can’t create UNIX socket (24)” que poderia estar limitando a criação de mais threads, e por consequência mais usuários, no sistema operacional. Foi então que achei o MySQL Dojo aonde já haviam testado esses problemas e se baseavam no ulimit. Resumindo, explorar os valores configurados no ulimit, ou melhor, no arquivo limits.conf e aumentá-los até que os testes fossem satisfatórios. Então foi assim:

[root@server mysql-rpm]# ulimit -a mysql
core file size (blocks, -c) 0
data seg size (kbytes, -d) unlimited
scheduling priority (-e) 0
file size (blocks, -f) unlimited
pending signals (-i) 192031
max locked memory (kbytes, -l) 64
max memory size (kbytes, -m) unlimited
open files (-n) 90000
pipe size (512 bytes, -p) 8
POSIX message queues (bytes, -q) 819200
real-time priority (-r) 0
stack size (kbytes, -s) 90000
cpu time (seconds, -t) unlimited
max user processes (-u) 90000
virtual memory (kbytes, -v) unlimited
file locks (-x) unlimited

[root@server mysql-coms]# mysqlslap --user=root --auto-generate-sql --concurrency=1200 --number-of-queries=1
Benchmark
Average number of seconds to run all queries: 5.775 seconds
Minimum number of seconds to run all queries: 5.775 seconds
Maximum number of seconds to run all queries: 5.775 seconds
Number of clients running queries: 1200
Average number of queries per client: 0

E para provar que o MySQL está limitado somente pelo hardware ou ainda, neste caso, também pelas configurações do sistema operacional…

[root@server mysql-coms]# mysqlslap --user=root --auto-generate-sql --concurrency=2000 --number-of-queries=1
Benchmark
Average number of seconds to run all queries: 18.367 seconds
Minimum number of seconds to run all queries: 18.367 seconds
Maximum number of seconds to run all queries: 18.367 seconds
Number of clients running queries: 2000
Average number of queries per client: 0

[root@server mysql-coms]# mysqlslap --user=root --auto-generate-sql --concurrency=3000 --number-of-queries=1
Benchmark
Average number of seconds to run all queries: 41.411 seconds
Minimum number of seconds to run all queries: 41.411 seconds
Maximum number of seconds to run all queries: 41.411 seconds
Number of clients running queries: 3000
Average number of queries per client: 0

E assim, finalizo mais uma aventura com o MySQL e com missão cumprida! Até breve…

A primeira coisa a se fazer ao se trabalhar com o InnoDB é utilizar as variáveis de status para checar se a configuração atual do Buffer Pool, definida em innodb_buffer_pool_size, satisfaz as necessidades dos bancos de dados atualmente armazenados no MySQL. Como já abordei aqui no blog, em outro post, manter os dados (e índices principalmente) em memória é a melhor opção para se obter boa performance de um banco de dados e no caso do MySQL + InnoDB não é diferente…

Selecione as variáveis de status que interessa…

mysql> show status like 'innodb_buffer_pool%';
+---------------------------------------+------------+
| Variable_name                         | Value      |
+---------------------------------------+------------+
| Innodb_buffer_pool_pages_data         | 392124     |
| Innodb_buffer_pool_pages_dirty        | 1          |
| Innodb_buffer_pool_pages_flushed      | 15949040   |
| Innodb_buffer_pool_pages_free         | 0          |
| Innodb_buffer_pool_pages_misc         | 1092       |
| Innodb_buffer_pool_pages_total        | 393215     |
| Innodb_buffer_pool_read_ahead_rnd     | 0          |
| Innodb_buffer_pool_read_ahead         | 8154       |
| Innodb_buffer_pool_read_ahead_evicted | 252        |
| Innodb_buffer_pool_read_requests      | 1444481964 |
| Innodb_buffer_pool_reads              | 7502       |
| Innodb_buffer_pool_wait_free          | 0          |
| Innodb_buffer_pool_write_requests     | 148957406  |
+---------------------------------------+------------+
13 rows in set (0.00 sec)

Como nesta instância não estou utilizando compressão de dados, as páginas de dados do InnoDB continuam com o valor padrão que é 16KB cada. Através da variável de status Innodb_buffer_pool_pages_data temos o número total de páginas atualmente dentro do Buffer Pool. Fazendo uma aritimética simples, Innodb_buffer_pool_pages_data*16KB, temos a quantidade em KB da quantidade de dados que preenche o buffer neste momento.

mysql> select (392124*16) pages;
+---------+
| pages   |
+---------+
| 6273984 |
+---------+
1 row in set (0.02 sec)

Transforme o resultado de bytes em giga:

mysql> select 6273984/1024/1024;
+-------------------+
| 6273984/1024/1024 |
+-------------------+
| 5.98333740        |
+-------------------+
1 row in set (0.00 sec)

E então compare a efetividade entre a quantidade de dados que estão dentro do buffer e o valor configurado para aquela área de memória:

mysql> select format(6273984/1024/1024,2) 'dadosNoBuffer',
-> format(@@innodb_buffer_pool_size/1024/1024/1024,0) 'valorConfigurado';
+---------------+------------------+
| dadosNoBuffer | valorConfigurado |
+---------------+------------------+
| 5.98          | 6                |
+---------------+------------------+
1 row in set (0.00 sec)

Vimos que o Buffer Pool está todo tomado por dados e, caso Innodb_buffer_pool_reads for maior que zero e Innodb_buffer_pool_pages_free for igual a zero, considere aumentar uma pouco o tamanho do Buffer Pool, uma vez que:

mysql> show status like 'innodb_buffer_pool%';
+---------------------------------------+------------+
| Variable_name                         | Value      |
+---------------------------------------+------------+
| Innodb_buffer_pool_pages_data         | 392123     |
| Innodb_buffer_pool_pages_dirty        | 1          |
| Innodb_buffer_pool_pages_flushed      | 15949040   |
| Innodb_buffer_pool_pages_free         | 0          |
| Innodb_buffer_pool_pages_misc         | 1092       |
| Innodb_buffer_pool_pages_total        | 393215     |
| Innodb_buffer_pool_read_ahead_rnd     | 0          |
| Innodb_buffer_pool_read_ahead         | 8154       |
| Innodb_buffer_pool_read_ahead_evicted | 252        |
| Innodb_buffer_pool_read_requests      | 1444481964 |
| Innodb_buffer_pool_reads              | 7502       |
| Innodb_buffer_pool_wait_free          | 0          |
| Innodb_buffer_pool_write_requests     | 148957406  |
+---------------------------------------+------------+
13 rows in set (0.00 sec)

Innodb_buffer_pool_reads -> leitura de dados do disco que não foram satisfeitas ao tentar ler dados do Buffer Pool, ou seja, os dados não estão lá por não haver mais espaço para armazená-los;

Innodb_buffer_pool_pages_free -> quantidade de páginas ainda disponíveis para armazenar dados no Buffer Pool;

Até…

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:

Pode ser que esse post não tenha haver com o fato de este ser um blog focado em MySQL, mas, pelo contrário, tudo que está em torno de tal software de bancos de dados será abordado aqui com foco na utilidade e melhor funcionamento. Levando em conta que alguns serviços em nuvem ainda oferecem serviços muito aquém daquilo que se espera, precisamos ter pró-atividade suficiente para monitorar hardwarede um servidor e principalmente, monitorar os discos, aonde reside a parte física dos bancos de dados – focando no MySQL – arquivos de dados ou tablespaces, logs de transações, logs binários, relay logs, general logs e logs de erro.

Muito interessante que você DBA entenda que todos estes arquivos são também parte necessária para o seu trabalho diário uma vez que:

• Arquivo de Dados ou Tablespaces: como o MySQL tem sido mais utilizado ultimamente com o Storage Engine InnoDB, motor de armazenamento que possibilita a utilização de um ou mais arquivos de tablespace compartilhados com prefixo ibdataX (onde X é o número de sequência do nome do tablepace compartilhado) ou ainda com a configuração innodb_file_per_table que criará um tablepsace individual para cada tabela de um banco de dados. Uma falha dos discos poderá corromper os tablepsaces, sejam eles de quaisquer dos tipos citados acima, gerando um erro pareceido com este MySQL Bug # 18410.

• Logs de Transações: estes arquivos (que por padrão são criados 2 após uma instalação padrão, id_logfile0 e ib_logfile1) são responsáveis por armazenar transações do InnoDB que receberam ou não um COMMIT. Utilizado principalmente para realizar, em conjunto com as áreas internas ao tablespace compartilhado (undo, redo e metadados), o processo de crash-recovery, eliminando do log todas as transações que não contam atualmente com um COMMIT e criando um checkpoint. Outros processo são realizados em conjunto (flush logs, escrita de dados…); tema para outro post.

• Logs binários: estes arquivos podem ser utilizados como uma fonte muito ágil de extração de backup incremental, já que armazena tudo (STATEMENT ou ROW) que atualiza o estado dos bancos de dados. Além de ser uma ótima fonte para tal estratégia de backup, é obrigatoriamente utilizado para implementação de topologias de replicação entre dois ou mais servidores de bancos de dados MySQL.

Não vou citar a utilidade de todos os arquivos que foram mencionados na introdução deste post para não torná-lo cansativo, pois, o intuito aqui é você ter em mente que, além dos dados, que são a parte mais importante que qualquer outra, você ainda precisa monitorar os seus discos para que não existam surpresas, por exemplo, ao extrair backups com o mysqldump e este backup não contar com todos os dados pois o tablespace de uma tabela específica (geralmente a mais importante de todo o modelo físico) está com uma parte de seus dados corrompida. Isso pode acontecer. Uma outra surpresa é receber uma mensagem de erro de Assertion Thread Failure do inode do InnoDB ao tentar efetuar o CHECKSUM dos dados e não ter conseguido -esse é um dos problemas que uma intermitência nos discos poderá gerar.

98% das falhas atualmente detectadas com tablepace do InnoDB estão relacionadas com hardware mal provisionado, banco de dados mal configurado em relação ao desempenho e a pressão sobre memória secundária, o que implica em muito mais trabalho de disco e CPU e menos da memória (quando deveria ser ao contrário). A utilização intensa de disco e CPU gera overhead e, consequentemente, lentidão.

Mas como fazer para monitorar possíveis problemas com os discos do meu servidor?

Tenho utilizado muito o S.M.A.R.T. com o smartctl e o smartd com que são respectivamente o utilitário e o daemon para verificação contínua da saúde dos discos de um servidor, seja ele crítico ou não. Indenpendente da criticidade, é muito importante que se tenha um pleno monitoramento, pois, o hardware também poderá nos deixar na mão e parar o acesso à informação.

O smartd é o daemon, sendo assim, ele precisa estar rodando para que possamos interagir com o mesmo e gerar os resultados que buscamos ao monitorar os discos de um servidor. O comando cliente é o smartctl que poderá ser utilizado das seguintes formas:

[root@redhat01 ~]# smartctl -i /dev/sda3
smartctl 5.39.1 2010-01-28 r3054 [x86_64-redhat-linux-gnu] (local build)
Copyright (C) 2002-10 by Bruce Allen, http://smartmontools.sourceforge.net

=== START OF INFORMATION SECTION ===
Device Model: WDC WD1003FBYX-01Y7B0
Serial Number: WD-WCAW32441497
Firmware Version: 01.01V01
User Capacity: 1,000,204,886,016 bytes
Device is: Not in smartctl database [for details use: -P showall]
ATA Version is: 8
ATA Standard is: Exact ATA specification draft version not indicated
Local Time is: Wed Nov 16 14:40:28 2011 CST
SMART support is: Available - device has SMART capability.
SMART support is: Enabled

O comando mostrado acima nos permite verificar o modelo e o firmware do disco /dev/hda. O SMART mantém um database com vários modelos de disco e possivelmente o seu esteja neste database será reconhecido.

Você pode verificar o conteúdo deste database com o seguinte comando (resultados suprimidos):
[root@redhat01 ~]# smartctl -P showall
...
MODEL REGEXP: QUANTUM FIREBALL EX(3.2|6.4)A
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball EX series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALL ST(3.2|4.3|4300)A
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball ST series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALL SE4.3A
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball SE series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALLP LM(10.2|15|20.[45]|30)
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball Plus LM series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALLP AS(10.2|20.5|30.0|40.0)
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball Plus AS series
ATTRIBUTE OPTIONS: None preset; no -v options are required.

MODEL REGEXP: QUANTUM FIREBALLP KX27.3
FIRMWARE REGEXP: .*
MODEL FAMILY: Quantum Fireball Plus KX series
ATTRIBUTE OPTIONS: None preset; no -v options are required.
…

O segundo comando é o mais interessante, pois, possibilita criar um relatório mais completo que lista pontos importantes relacionados com a saúde do disco analisado. É conhecido como “Executive Summary of Disk Health” – o relatório abaixo mostra que o disco foi aprovado e não existem falhas, mas, caso o relatório te mostre o contrário, faça o backup de seus dados imediatamente.

[root@redhat01 ~]# smartctl -Hc /dev/sda3
smartctl 5.39.1 2010-01-28 r3054 [x86_64-redhat-linux-gnu] (local build)
Copyright (C) 2002-10 by Bruce Allen, http://smartmontools.sourceforge.net

General SMART Values:
Offline data collection status: (0x84) Offline data collection activity
was suspended by an interrupting command from host.
Auto Offline Data Collection: Enabled.
Self-test execution status: ( 0) The previous self-test routine completed
without error or no self-test has ever
been run.
Total time to complete Offline
data collection: (16500) seconds.
Offline data collection
capabilities: (0x7b) SMART execute Offline immediate.
Auto Offline data collection on/off support.
Suspend Offline collection upon new
command.
Offline surface scan supported.
Self-test supported.
Conveyance Self-test supported.
Selective Self-test supported.
SMART capabilities: (0x0003) Saves SMART data before entering
power-saving mode.
Supports SMART auto save timer.
Error logging capability: (0x01) Error logging supported.
General Purpose Logging supported.
Short self-test routine
recommended polling time: ( 2) minutes.
Extended self-test routine
recommended polling time: ( 170) minutes.
Conveyance self-test routine
recommended polling time: ( 5) minutes.
SCT capabilities: (0x303f) SCT Status supported.
SCT Feature Control supported.
SCT Data Table supported.

Percebam que o primeiro atributo é mesmo aquele que indica que o disco PASSOU no teste do SMART.

Referências:

• Wiki: http://en.wikipedia.org/wiki/S.M.A.R.T.]
• Source Forge: http://sourceforge.net/apps/trac/smartmontools/wiki/TocDoc]
• Linux Magazine: http://www.linuxjournal.com/magazine/monitoring-hard-disks-smart?page=0,2

Uma das tarefas mais interessantes e que envolve mais pontos a serem analisados são as rotinas de manutenção dos bancos de dados e com o MySQL não é diferente. Tais pontos vão da análise do melhor momento para se realizar tal manutenção, intervalo de tempo denominado “janela de manutenção” ou do inglês, “maintenance time frame” , o tempo que tal rotina levará para completar o trabalho e quais são os objetivos de tal ação, esta que pode resultar em vários ganhos para o ambiente, seja de performance, seja de backup ou mesmo de prevenção contra desastres.

Comummente, não é muito racional simplesmente colocar um script qualquer no cron do Linux ou agendador de tarefas do MS Windows e deixar este ser executado de acordo com o agendamento. Interessante que você, administrador de bancos de dados, saiba exatamente o que realizar em detrimento de cada objetivo que você tem em relação à manutenção dos seus bancos de dados – é uma ilusão achar que os dados não fragmentarão com o tempo, que dados não serão corrompidos nunca e que, principalmente, você não precisará de um backup, aquele que falhou esta manhã. Muito cuidado com este último tópico e lembre-se, Murphy e sua lei estão sempre aonde existem “pontos simples de falha (SPOF)”.

O MySQL nos dá várias possibilidades de se trabalhar a manutenção de tabelas de qualquer Storage Engine através do *programa-cliente mysqlcheck, que é adicionado ao PATH do sistema operacional após a instalação do MySQL Server. Tanto em sistemas Unix Like quanto em sistemas MS Windows, ao utilizar o terminal ou o prompt de comando, respectivamente, basta teclar as primeiras letras e utilizar TAB para completar o nome do aplicativo, que, logo você perceberá que vários outros aplicativos, além do mysqlcheck, também estão disponíveis. Após ser chamado na linha de comando, como se trata de um programa-cliente, você precisará apontar qual usuário/senha para se conectar ao servidor de bancos de dados MySQL (mysqld), quais bancos de dados e quais as opções, estas que são várias as disponíveis.

Sintaxe de utilização do mysqlcheck:

shell> mysqlcheck [options] db_name [tables]
shell> mysqlcheck [options] --databases DB1 [DB2 DB3...]
shell> mysqlcheck [options] --all-databases

Checagem de Erros – opção “-c”

O mysqlcheck poderá ser utilizado com a opção “-c” para checar os possíveis erros em tabelas de qualquer Storage Engine. Você pode programar algo em shell script para pegar os erros e lhe enviar por e-mail ou acompanhar a execução do programa via linha de comando. Após a checagem, caso algum erro seja reportado, é interessante verificar qual é a tabela e em qual banco de dados está para que as ações de correção possam ser realizadas, o que varia de acordo com o Storage Engine.

Checando erros em todos os bancos de dados:

shell> mysqlcheck [options] --all-databases -c

Atualizando Estatísticas – opção “-a”

Atualizar estatísticas é uma operação essencial em bancos de dados! Normalmente, é interessante que os objetos, índices e linhas de tabelas sejam estatisticamente computadas para que a primeira camada do MySQL, aonde residem os módulos de otimização de consultas (transformação e criação de plano de execução), para que a melhor rota seja traçada com base em tais estatísticas – resumidamente, eu sei quantas linhas, em índices ou não, eu tenho que percorrer de acordo com uma estratégia definida para atender a uma consulta. No caso de consultas que envolvem JOIN, a ordem das tabelas é definida através das estatísticas.

Se eu não sei a quantidade de objetos, índices e linhas que tenho em disco nos arquivos em disco, como farei essa decisão? A atualização das estatísticas garante que haja consistência entre a visão lógica (engine) e física (arquivos em disco) dos bancos de dados.

O mysqlcheck -a ou –analyze é equivalente ou ANALYZE TABLEsão comando equivalentes e ao serem executados, adquirem bloqueio de leitura (bloqueio compartilhado) e funcionando para tabelas MyISAM e InnoDB.

Analisando as tabelas em todos os bancos de dados:

shell> mysqlcheck [options] --all-databases -a

Otimizando tabelas – opção “-o”

A opção “-o” otimiza os dados em disco promovendo melhor alocação dos dados dentro de suas respectivas páginas de dados em disco, assim como promove, consequentemente, a desfragmentção de toda a informação armazenada. Além disso, atualiza as estatísticas de índices fazendo a ordenação dos mesmos (InnoDB Clustered Indexes) e o rearranjo da árvore B-TREE (MyISAM e InnoDB). Durante a operação, um bloqueio exclusivo será adquirido na tabela na qual o processo está rodando (bloqueio exclusivo = WRITE LOCK) e o poder de resposta do servidor de bancos de dados poderá diminuir muito nesse momento. Após o processo, caso seja uma base que tem alterações intensas em seus registros, poderemos perceber diminuição dos espaço consumido em disco pelos bancos de dados.

Otimizando as tabelas em todos os bancos de dados:

shell> mysqlcheck [options] --all-databases -o

A partir da versão 5.1 do MySQL, o InnoDB Plugin passou a não se entender muito bem com o OPTIMIZE TABLE ou a opção “-o” do mysqlcheck. Ao rodar um daqueles comandos contra uma tabela que é controlada pelo Storage Engine InnoDB Plugin, o seguinte erro será enviado ao usuário:

Table does not support optimize, doing recreate + analyze ...

Este tipo de problema poderá ser contornado através do recriação da tabela com o que chamamos de REBUILD, seguido por um ANALYZE, isso para todas as tabelas do banco de dados:

mysql> ALTER TABLE nome_tabela ENGINE=InnoDB;
mysql> ANALYZE TABLE nome_tabela;

Lembro que você poderá montar os comandos para todas as tabelas do banco através de scripts ou mesmo, utilizando SQL em conjunto com a tabela TABLES do dicionário de dados do MySQL, o INFORMATION_SCHEMA.

Tais operações de manutenção podem agora fazer parte do seu plano de otimização para o MySQL e você poderá desenvolver um script para isso utilizando shell ou DOS. Problemas com crash de tabelas e performance poderão ser detectados e resolvidos com o aplicativo mysqlcheck. Basta agora, conceber um plano com padrões para todas as ações que serão realizadas, estudar o padrão de nomenclatura, contar com um profissional especializado para assinar o projeto e colocar em execução. Assim, sua tecnologia tem bem menos chances de ter problemas quando mais se precisar dela.