MySQL 8.0 Auto Tuning: innodb_dedicated_server

O MySQL sempre foi muito “manual” com seus parâmetros relativos a desempenho.

A versão 8.0 dá mais um passo na direção da automação, com a adição do parâmetro innodb_dedicated_server. Ao ser habilitado, são configurados os parâmetros innodb_buffer_pool_size e innodb_log_file_size de forma proporcional à quantidade de memória da máquina, e o parâmetro innodb_flush_method é alterado para O_DIRECT_NO_FSYNC.

Não chega a ser um Machine Learning, mas já ajuda.

[root@localhost ~]# mysql -u root -pNerv2018.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 9
Server version: 8.0.3-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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> show variables like 'innodb_dedicated_server';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_dedicated_server | OFF   |
+-------------------------+-------+
1 row in set (0.00 sec)

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

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

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

mysql> exit
Bye
[root@localhost ~]# echo "innodb_dedicated_server = ON" >> /etc/my.cnf
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -pNerv2018.
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 7
Server version: 8.0.3-rc-log MySQL Community Server (GPL)

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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> show variables like 'innodb_dedicated_server';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| innodb_dedicated_server | ON    |
+-------------------------+-------+
1 row in set (0.04 sec)

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

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

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

mysql>

Mais detalhes em https://dev.mysql.com/doc/refman/8.0/en/innodb-dedicated-server.html

Leave a Reply

O seu endereço de e-mail não será publicado. Campos obrigatórios são marcados com *

This site uses Akismet to reduce spam. Learn how your comment data is processed.