MySQL-Innodb統計
11917 ワード
mysqlライブラリにinnodb_がありますindex_stats、innodb_table_statsはinnodbの情報を統計し、統計情報に基づいてテーブルの複雑さを分析し、最適化のために準備することができる. innodb_table_statsはその名の通りライブラリ全体のinnodbテーブルを統計
フィールド詳細database_nameデータベース名table_nameテーブル名last_update最終更新時間n_rowsテーブルの合計数列のデータclustered_index_size集計インデックスサイズ(データページ)sum_of_other_index_sizesその他のインデックスサイズ(データページ)
データ詳細select@@innodb_page_size; デフォルトは16 K clustered_index_sizeが2590集約インデックスに必要なディスク容量は2590 x innodb_page_size/1024=41 Mその他のインデックスに必要なディスク容量は2924 x innodb_page_size/1024=46M innodb_index_stats innodb内のすべてのインデックスを統計
フィールド詳細database_nameデータベース名table_nameテーブル名index_nameインデックス名last_update最終更新時間stat_name統計名stat_value統計sample_sizeサンプルサイズstat_description統計説明-インデックスに対応するフィールド名
データ詳細stat_name中n_diff_pfx 02は、stat_という2つのカラムがインデックスにあることを示します.descriptionのseverity,id stat_value値は118618で、severityを表し、idの2列にseverityがあり、idが異なる値を表します.2つのテーブルの構築文/usr/share/mysql/mysql_system_tables.sql中
レポートinnodb_table_statsテーブルが存在しない場合-テーブルを再構築すればよいinnodb統計を開く
mysql> desc innodb_table_stats;
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name | varchar(64) | NO | PRI | NULL | |
| table_name | varchar(64) | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| n_rows | bigint(20) unsigned | NO | | NULL | |
| clustered_index_size | bigint(20) unsigned | NO | | NULL | |
| sum_of_other_index_sizes | bigint(20) unsigned | NO | | NULL | |
+--------------------------+---------------------+------+-----+-------------------+-----------------------------+
6 rows in set (0.00 sec)
フィールド詳細database_nameデータベース名table_nameテーブル名last_update最終更新時間n_rowsテーブルの合計数列のデータclustered_index_size集計インデックスサイズ(データページ)sum_of_other_index_sizesその他のインデックスサイズ(データページ)
mysql> select * from innodb_table_stats order by n_rows desc;
+-------------------+---------------------------+---------------------+--------+----------------------+--------------------------+
| database_name | table_name | last_update | n_rows | clustered_index_size | sum_of_other_index_sizes |
+-------------------+---------------------------+---------------------+--------+----------------------+--------------------------+
| sonar | issues | 2016-06-13 17:51:10 | 117017 | 2590 | 2924 |
| sonar | project_measures | 2016-06-15 03:34:26 | 93801 | 1497 | 395 |
| sonar | resource_index | 2016-06-13 17:51:11 | 26448 | 161 | 123 |
| sonar | projects | 2016-06-13 17:51:10 | 3139 | 97 | 74 |
| sonar | snapshots | 2016-06-15 03:34:16 | 2411 | 17 | 23 |
| sonar | file_sources | 2016-06-13 17:51:09 | 1227 | 2912 | 17 |
| sonar | issue_changes | 2016-06-13 17:51:09 | 833 | 8 | 5 |
| sonar | rules | 2016-06-13 17:51:11 | 552 | 97 | 1 |
| sonar | activities | 2016-06-13 17:51:09 | 419 | 7 | 1 |
| sonar | active_rules | 2016-06-13 17:51:09 | 419 | 3 | 1 |
| sonar | schema_migrations | 2016-06-13 17:51:11 | 216 | 1 | 0 |
| sonar | metrics | 2016-06-13 17:51:10 | 136 | 3 | 1 |
データ詳細select@@innodb_page_size; デフォルトは16 K clustered_index_sizeが2590集約インデックスに必要なディスク容量は2590 x innodb_page_size/1024=41 Mその他のインデックスに必要なディスク容量は2924 x innodb_page_size/1024=46M
：
mysql> SELECT SUM(stat_value) pages, index_name, SUM(stat_value)*@@innodb_page_size size FROM mysql.innodb_index_stats WHERE table_name='issues' AND stat_name = 'size' GROUP BY index_name;
+-------+------------------------+----------+
| pages | index_name | size |
+-------+------------------------+----------+
| 2590 | PRIMARY | 42434560 |
| 151 | issues_action_plan_key | 2473984 |
| 151 | issues_assignee | 2473984 |
| 328 | issues_component_uuid | 5373952 |
| 209 | issues_creation_date | 3424256 |
| 652 | issues_kee | 10682368 |
| 329 | issues_project_uuid | 5390336 |
| 146 | issues_resolution | 2392064 |
| 271 | issues_rule_id | 4440064 |
| 212 | issues_severity | 3473408 |
| 209 | issues_status | 3424256 |
| 266 | issues_updated_at | 4358144 |
+-------+------------------------+----------+
mysql> desc innodb_index_stats;
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
| database_name | varchar(64) | NO | PRI | NULL | |
| table_name | varchar(64) | NO | PRI | NULL | |
| index_name | varchar(64) | NO | PRI | NULL | |
| last_update | timestamp | NO | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
| stat_name | varchar(64) | NO | PRI | NULL | |
| stat_value | bigint(20) unsigned | NO | | NULL | |
| sample_size | bigint(20) unsigned | YES | | NULL | |
| stat_description | varchar(1024) | NO | | NULL | |
+------------------+---------------------+------+-----+-------------------+-----------------------------+
8 rows in set (0.00 sec)
フィールド詳細database_nameデータベース名table_nameテーブル名index_nameインデックス名last_update最終更新時間stat_name統計名stat_value統計sample_sizeサンプルサイズstat_description統計説明-インデックスに対応するフィールド名
mysql> select * from innodb_index_stats order by stat_value desc;
+-------------------+---------------------------+--------------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| database_name | table_name | index_name | last_update | stat_name | stat_value | sample_size | stat_description |
+-------------------+---------------------------+--------------------------------+---------------------+--------------+------------+-------------+-----------------------------------+
| sonar | issues | issues_severity | 2016-06-13 17:51:10 | n_diff_pfx02 | 118618 | 20 | severity,id |
| sonar | issues | issues_resolution | 2016-06-13 17:51:10 | n_diff_pfx02 | 118508 | 20 | resolution,id |
| sonar | issues | issues_action_plan_key | 2016-06-13 17:51:10 | n_diff_pfx02 | 118416 | 20 | action_plan_key,id |
| sonar | issues | issues_creation_date | 2016-06-13 17:51:10 | n_diff_pfx02 | 118244 | 20 | issue_creation_date,id |
| sonar | issues | PRIMARY | 2016-06-13 17:51:10 | n_diff_pfx01 | 117017 | 20 | id |
| sonar | issues | issues_assignee | 2016-06-13 17:51:10 | n_diff_pfx02 | 116678 | 20 | assignee,id |
| sonar | issues | issues_status | 2016-06-13 17:51:10 | n_diff_pfx02 | 115722 | 20 | status,id |
| sonar | issues | issues_rule_id | 2016-06-13 17:51:10 | n_diff_pfx02 | 115692 | 20 | rule_id,id |
| sonar | issues | issues_project_uuid | 2016-06-13 17:51:10 | n_diff_pfx02 | 115544 | 20 | project_uuid,id |
| sonar | issues | issues_updated_at | 2016-06-13 17:51:10 | n_diff_pfx02 | 112466 | 20 | updated_at,id |
| sonar | issues | issues_kee | 2016-06-13 17:51:10 | n_diff_pfx01 | 107901 | 20 | kee |
| sonar | issues | issues_component_uuid | 2016-06-13 17:51:10 | n_diff_pfx02 | 105440 | 20 | component_uuid,id |
| sonar | project_measures | measures_sid_metric | 2016-06-15 03:34:26 | n_diff_pfx03 | 95257 | 20 | snapshot_id,metric_id,id |
| sonar | project_measures | PRIMARY | 2016-06-15 03:34:26 | n_diff_pfx01 | 93801 | 20 | id |
データ詳細stat_name中n_diff_pfx 02は、stat_という2つのカラムがインデックスにあることを示します.descriptionのseverity,id stat_value値は118618で、severityを表し、idの2列にseverityがあり、idが異なる値を表します.
CREATE TABLE IF NOT EXISTS innodb_table_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
n_rows BIGINT UNSIGNED NOT NULL,
clustered_index_size BIGINT UNSIGNED NOT NULL,
sum_of_other_index_sizes BIGINT UNSIGNED NOT NULL,
PRIMARY KEY (database_name, table_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
CREATE TABLE IF NOT EXISTS innodb_index_stats (
database_name VARCHAR(64) NOT NULL,
table_name VARCHAR(64) NOT NULL,
index_name VARCHAR(64) NOT NULL,
last_update TIMESTAMP NOT NULL NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
stat_name VARCHAR(64) NOT NULL,
stat_value BIGINT UNSIGNED NOT NULL,
sample_size BIGINT UNSIGNED,
stat_description VARCHAR(1024) NOT NULL,
PRIMARY KEY (database_name, table_name, index_name, stat_name)
) ENGINE=INNODB DEFAULT CHARSET=utf8 COLLATE=utf8_bin STATS_PERSISTENT=0;
レポートinnodb_table_statsテーブルが存在しない場合-テーブルを再構築すればよい
mysql> set GLOBAL innodb_stats_auto_recalc='ON';
mysql> set GLOBAL innodb_stats_persistent='ON';
mysql> show variables like 'innodb_stats_auto_recalc';
+--------------------------+-------+
| Variable_name | Value |
+--------------------------+-------+
| innodb_stats_auto_recalc | ON |
+--------------------------+-------+
1 row in set (0.00 sec)
mysql> show variables like 'innodb_stats_persistent';
+-------------------------+-------+
| Variable_name | Value |
+-------------------------+-------+
| innodb_stats_persistent | ON |
+-------------------------+-------+
1 row in set (0.00 sec)