Весьма насущным вопросом при формировании успешных показателей производительности является величина значения в innodb_buffer_pool_size.
Данный параметр присваивает объём памяти, что называется buffer pool, что применяется для сохранения данных типа InnoDB.
Для того, чтобы увидеть всевозможные типы подсистем, открытые к доступу, необходимо прописать следующий блок:
1 | MariaDB [mysql]> show engines; |
2 | +———————+———+—————————————————————————-+—————+——+————+ |
3 | | Engine | Support | Comment | Transactions | XA | Savepoints | |
4 | +———————+———+—————————————————————————-+—————+——+————+ |
5 | | CSV | YES | CSV storage engine | NO | NO | NO | |
6 | | MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO | |
7 | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO | |
8 | | BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO | |
9 | | MyISAM | YES | MyISAM storage engine | NO | NO | NO | |
10 | | InnoDB | DEFAULT | Percona-XtraDB, Supports transactions, row-level locking, and foreign keys | YES | YES | YES | |
11 | | ARCHIVE | YES | Archive storage engine | NO | NO | NO | |
12 | | FEDERATED | YES | FederatedX pluggable storage engine | YES | NO | YES | |
13 | | PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO | |
14 | | Aria | YES | Crash-safe tables with MyISAM heritage | NO | NO | NO | |
15 | +———————+———+—————————————————————————-+—————+——+————+ |
16 | 10 rows in set (0.00 sec) |
Для того чтобы запустить поиск таблиц типа InnoDB используйте данную команду:
1 | MariaDB [mysql]> select table_name from information_schema.tables where engine=’innodb’; |
2 | … |
3 | | usrgrp | |
4 | | valuemaps | |
5 | +————————+ |
6 | 134 rows in set (0.00 sec) |
Если же вы работаете исключительно с операционной базой, то вам необходимо применять данный алгоритм:
1 | MariaDB [mysql]> select table_name from information_schema.tables where table_schema=’rtfm_db1′ and engine=’InnoDB’; |
2 | +———————+ |
3 | | table_name | |
4 | +———————+ |
5 | | db1_chat_log | |
6 | | db1_chat_message | |
7 | | db1_cntctfrm_field | |
8 | | db1_pTC_logs | |
9 | | db1_wp_rp_tags | |
10 | +———————+ |
11 | 5 rows in set (0.00 sec) |
Для получения рекомендуемого значения в innodb_buffer_pool_size, возможно применить нижеуказанный запрос:
1 | 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; |
2 | +————————————-+ |
3 | | Recommended_InnoDB_Buffer_Pool_Size | |
4 | +————————————-+ |
5 | | 57M | |
6 | +————————————-+ |
7 | 1 row in set (2.65 sec) |
Если же вам понадобилась информация об объёме конкретной базы, формирование запросов необходимо выполнять следующим образом:
1 | 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; |
2 | +—————+—————+ |
3 | | table_schema | total_size_mb | |
4 | +—————+—————+ |
5 | | zabbix | 33.25 | |
6 | +—————+—————+ |
7 | 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. |
2 | +———+——-+——-+——-+————+———+ |
3 | | tables | rows | data | idx | total_size | idxfrac | |
4 | +———+——-+——-+——-+————+———+ |
5 | | 260 |0.52M | 0.07G|0.02G| 0.09G | 0.27 | |
6 | +———+——-+——-+——-+————+———+ |
7 | 1 row in set (0.38 sec) |
В случае необходимости выведения информации в мегабайтах, вам понадобится другая комбинация:
1 | 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; |
2 | +———+——-+———+———+————+———+ |
3 | | tables | rows | data | idx | total_size | idxfrac | |
4 | +———+——-+———+———+————+———+ |
5 | | 260 | 0.52M| 68.66MB | 18.52MB | 87.18MB | 0.27 | |
6 | +———+——-+———+———+————+———+ |
7 | 1 row in set (0.17 sec) |
Если вы решили осуществить подсчёт всех баз с таблицами типа InnoDB и их объём, поступайте таким образом:
1 | 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’; |
2 | +———+——-+———+———+————+———+ |
3 | | tables | rows | data | idx | total_size | idxfrac | |
4 | +———+——-+———+———+————+———+ |
5 | | 134 | 0.38M | 32.34MB | 16.88MB | 49.22MB | 0.52 | |
6 | +———+——-+———+———+————+———+ |
7 | 1 row in set (0.15 sec) |
Иногда информацию необходимо просмотреть диферинцированно по базам. В данном случае запускаете следующий процес:
01 | 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; |
02 | +——————-+—————+ |
03 | | table_schema | total_size_mb | |
04 | +——————-+—————+ |
05 | | autocomtestdb | 0.02 | |
06 | | roundcube | 0.28 | |
07 | | rtfm_db1 | 0.97 | |
08 | | setevoy_money_db1 | 0.08 | |
09 | | vexim | 0.04 | |
10 | | zabbix | 31.51 | |
11 | +——————-+—————+ |
12 | 6 rows in set (0.09 sec) |
Вследствие преобразований в Recommended_InnoDB_Buffer_Pool_Size данные выглядят более понятным.
Необходимо также учитывать возможность роста массивов данных. Для этого оставляем зазор памяти в 15-20% сверху.
Существенно важным моментом в процессе настройки innodb_buffer_pool_size является блокировка “двойной буферизации” как операционной системой, так и системой InnoDB. В данном вопросе стоит воспользоваться параметром:
1 | innodb_flush_method=O_DIRECT |