MySQL/MariaDB: преобразование производительности (часть 2)

Весьма насущным вопросом при формировании успешных показателей производительности является величина значения в 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

Comments

comments