MySQL/MariaDB: тюнинг производительности #2: innodb_buffer_pool

Продолжение серии MySQL/MariaDB: тюнинг производительности.

Размер innodb_buffer_pool_size

Один из самых спорных вопросов, это “Какое значение указывать в innodb_buffer_pool_size“.

Хорошая статья на эту тему есть тут>>>.

innodb_buffer_pool_size определяет размер памяти, называемой buffer pool, которая будет использоваться для хранения данных и индексов таблиц с типом InnoDB.

Просмотреть все доступные типы подсистем можно так:

MariaDB [mysql]> show engines;
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                                    | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
| CSV                | YES     | CSV storage engine                                                         | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                                      | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables                  | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears)             | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                                      | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| ARCHIVE            | YES     | Archive storage engine                                                     | NO           | NO   | NO         |
| FEDERATED          | YES     | FederatedX pluggable storage engine                                        | YES          | NO   | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                                         | NO           | NO   | NO         |
| Aria               | YES     | Crash-safe tables with MyISAM heritage                                     | NO           | NO   | NO         |
+--------------------+---------+----------------------------------------------------------------------------+--------------+------+------------+
10 rows in set (0.00 sec)

Найти все таблицы InnoDB можно таким запросом:

MariaDB [mysql]> select table_name from information_schema.tables where engine='innodb';
...
| usrgrp                |
| valuemaps             |
+-----------------------+
134 rows in set (0.00 sec)

А только для определённой базы – так:

MariaDB [mysql]> select table_name from information_schema.tables where table_schema='rtfm_db1' and engine='InnoDB';
+--------------------+
| table_name         |
+--------------------+
| db1_chat_log       |
| db1_chat_message   |
| db1_cntctfrm_field |
| db1_pTC_logs       |
| db1_wp_rp_tags     |
+--------------------+
5 rows in set (0.00 sec)

Можно выполнить вот такой запрос, что бы получить рекомендуемое значение для innodb_buffer_pool_size:

MariaDB [mysql]> SELECT CONCAT(CEILING(RIBPS/POWER(1024,pw)),SUBSTR(' KMGT',pw+1,1)) Recommended_InnoDB_Buffer_Pool_Size FROM (SELECT RIBPS,FLOOR(LOG(RIBPS)/LOG(1024)) pw FROM (SELECT SUM(data_length+index_length)*1.1*growth RIBPS FROM information_schema.tables AAA, (SELECT 1 growth) BBB WHERE ENGINE='InnoDB') AA) A;
+-------------------------------------+
| Recommended_InnoDB_Buffer_Pool_Size |
+-------------------------------------+
| 57M                                 |
+-------------------------------------+
1 row in set (2.65 sec)

Узнать размер одной базы можно с помощью такого запроса:

MariaDB [(none)]> select table_schema, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where table_schema like 'zabbix' and engine like 'innodb' group by table_schema;
+--------------+---------------+
| table_schema | total_size_mb |
+--------------+---------------+
| zabbix       |         33.25 |
+--------------+---------------+
1 row in set (0.02 sec)

А всех баз и таблиц – так (если базы более 1 гигабайта):

MariaDB [mysql]> SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024*1024),2),'G') data, concat(round(sum(index_length)/(1024*1024*1024),2),'G') idx, concat(round(sum(data_length+index_length)/(1024*1024*1024),2),'G') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES;
+--------+-------+-------+-------+------------+---------+
| tables | rows | data | idx | total_size | idxfrac |
+--------+-------+-------+-------+------------+---------+
| 260    |0.52M | 0.07G|0.02G| 0.09G      | 0.27    |
+--------+-------+-------+-------+------------+---------+
1 row in set (0.38 sec)

Или так, если базы небольшие, и удобнее вывести в мегабайтах:

MariaDB [mysql]> SELECT count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024),2),'MB') data, concat(round(sum(index_length)/(1024*1024),2),'MB') idx, concat(round(sum(data_length+index_length)/(1024*1024),2),'MB') total_size, round(sum(index_length)/sum(data_length),2) idxfrac FROM information_schema.TABLES;
+--------+-------+---------+---------+------------+---------+
| tables | rows | data | idx | total_size | idxfrac |
+--------+-------+---------+---------+------------+---------+
| 260    | 0.52M| 68.66MB | 18.52MB | 87.18MB | 0.27 |
+--------+-------+---------+---------+------------+---------+
1 row in set (0.17 sec)

Подсчитать все базы с таблицами типа InnoDB и их размер можно так:

MariaDB [(none)]> select count(*) tables, concat(round(sum(table_rows)/1000000,2),'M') rows, concat(round(sum(data_length)/(1024*1024),2),'MB') data, concat(round(sum(index_length)/(1024*1024),2),'MB') idx, concat(round(sum(data_length+index_length)/(1024*1024),2),'MB') total_size, round(sum(index_length)/sum(data_length),2) idxfrac from information_schema.tables where engine='innodb';
+--------+-------+---------+---------+------------+---------+
| tables | rows  | data    | idx     | total_size | idxfrac |
+--------+-------+---------+---------+------------+---------+
|    134 | 0.38M | 32.34MB | 16.88MB | 49.22MB    |    0.52 |
+--------+-------+---------+---------+------------+---------+
1 row in set (0.15 sec)

А посмотреть отдельно по базам – так:

MariaDB [(none)]> select table_schema, sum(round(data_length/1024/1024,2)) as total_size_mb from information_schema.tables where engine like 'innodb' group by table_schema;
+-------------------+---------------+
| table_schema      | total_size_mb |
+-------------------+---------------+
| autocomtestdb     |          0.02 |
| roundcube         |          0.28 |
| rtfm_db1          |          0.97 |
| setevoy_money_db1 |          0.08 |
| vexim             |          0.04 |
| zabbix            |         31.51 |
+-------------------+---------------+
6 rows in set (0.09 sec)

Теперь значение Recommended_InnoDB_Buffer_Pool_Size выглядит более понятным.

Учитывая, что базы будут расти – можно добавить 15-20% сверх того, что подсказал первый запрос (т.е., например – не 57, а 65 МБ).

Хотя в Recommended_InnoDB_Buffer_Pool_Size указано 57 МБ, следует учесть, что:

This of course assumes your database is large so you need large buffer pool, if not – setting buffer pool a bit larger than your database size will be enough.

Т.е., рекомендуется устанавливать (в идеале) размер innodb_buffer_pool_size немного больше, чем размер всех таблиц InnoDB на сервере. Источник тут>>>.

Перевод можно найти тут>>>.

Задаём в my.cnf такой параметр: