Iago DBA

Fala pessoal, espero que todos estejam bem, no MySQL uma das primeiras informações que gostei de saber foi sobre a variável de innodb_buffer_pool_size que apesar de simples, ela faz total diferença para a performance do seu ambiente e neste artigo pretendo falar um pouco sobre como funciona o seu gerenciamento, espero que tenham uma boa leitura!

O que é essa variável?

• A innodb_buffer_pool_size é uma variável extremamente recomendada para administrarmos, principalmente em ambientes de produção.
• Ela possui um default de 128MB que irá ocupar do seu servidor (o que é muito pouco)
• O Recomendado é alterar para em media 70% da RAM total do seu ambiente ou igual ao valor de innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances, mais do que isso já passa a não ser tão recomendado, pois o MySQL irá usar a memória RAM para outras coisas que não fazem parte do buffer pool, como por exemplo para a execução de comandos SQL.
• Algumas estruturas internas do mysql precisam ficar no cache (RAM): como binary logs, relay logs, innodb transaction logs, e principalmente precisa deixar disponível memória RAM para o sistema operacional trabalhar e não fazer SWAPPING em disco, o que para a performance do servidor em geral seria péssimo.

Esse buffer pode também ser visualizado através da ferramenta do Workbench

Eu também consigo verificar a quantidade setada dessa variável no meu ambiente (em MB) com os seguintes comandos:

mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 134217728 |
+-------------------------+-----------+
1 row in set (0.00 sec)
mysql> select 134217728 / 1024 / 1024 as qtgbalocadabufferpool;
+----------------------+
| qtgbalocadabufferpool |
+----------------------+
|           128.00000000 |
+----------------------+
1 row in set (0.00 sec)
mysql>

Caso eu queira alterar esse valor para 512MB por exemplo, é possível apenas alterando o GLOBAL innodb_buffer_pool_size igual no exemplo abaixo:

mysql> SET GLOBAL innodb_buffer_pool_size=536870912;
Query OK, 0 rows affected (0.00 sec)
mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+-----------+
| Variable_name           | Value     |
+-------------------------+-----------+
| innodb_buffer_pool_size | 536870912 |
+-------------------------+-----------+
1 row in set (0.00 sec)
mysql>
mysql> select 536870912 / 1024 / 1024 as qtgbalocadabufferpool;
+----------------------+
| qtgbalocadabufferpool |
+----------------------+
|           512.00000000 |
+----------------------+
1 row in set (0.00 sec)
mysql>

E após isso, automaticamente a porcentagem de utilização do innodb já caíra no dashboard do workbench também

É importante ressaltar que essa alteração de variável só irá acontecer de forma permanente no meu MySQL após eu adicionar esse parâmetro no meu arquivo /etc/mysql/my.cnf  da seguinte forma:

[mysqld]
bind-address = 192.168.15.160
port = 3306
# Aumentando para 1G
innodb_buffer_pool_size= 1G

No exemplo acima, eu já altero o parâmetro para 1G e após isso já irei reiniciar o meu ambiente para verificar se a mudança realmente ocorreu

iago@ubuntodb:/etc/mysql$ sudo systemctl restart mysql
iago@ubuntodb:/etc/mysql$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.37-0ubuntu0.22.04.3 (Ubuntu)
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
mysql> show global variables like 'innodb_buffer_pool_size';
+-------------------------+------------+
| Variable_name           | Value      |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.00 sec)
mysql> select 1073741824 / 1024 / 1024 / 1024 as qtgbalocadabufferpool;
+----------------------+
| qtgbalocadabufferpool |
+----------------------+
|         1.0000000000 |
+----------------------+
1 row in set (0.00 sec)
mysql>

Conclusão

A variável innodb_buffer_pool_size é fundamental para a performance do seu servidor de MySQL e com certeza pode fazer a diferença no seu dia a dia se bem configurada de acordo com a capacidade do seu ambiente.