![variable.jp [データベース,パフォーマンス,運用]](https://variable.jp/wp-content/uploads/2013/09/cropped-variable.jp_.jpg)
MYSQLのﾃｰﾌﾞﾙｻｲｽﾞを確認する為にinformation_schema.tablesを利用してみる。
運用する上で、負荷の高い原因となってるﾃｰﾌﾞﾙを確認する。
mysql> desc information_schema.tables; +-----------------+---------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-----------------+---------------------+------+-----+---------+-------+ | TABLE_CATALOG | varchar(512) | YES | | NULL | | | TABLE_SCHEMA | varchar(64) | NO | | | | | TABLE_NAME | varchar(64) | NO | | | | | TABLE_TYPE | varchar(64) | NO | | | | | ENGINE | varchar(64) | YES | | NULL | | | VERSION | bigint(21) unsigned | YES | | NULL | | | ROW_FORMAT | varchar(10) | YES | | NULL | | | TABLE_ROWS | bigint(21) unsigned | YES | | NULL | | | AVG_ROW_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | MAX_DATA_LENGTH | bigint(21) unsigned | YES | | NULL | | | INDEX_LENGTH | bigint(21) unsigned | YES | | NULL | | | DATA_FREE | bigint(21) unsigned | YES | | NULL | | | AUTO_INCREMENT | bigint(21) unsigned | YES | | NULL | | | CREATE_TIME | datetime | YES | | NULL | | | UPDATE_TIME | datetime | YES | | NULL | | | CHECK_TIME | datetime | YES | | NULL | | | TABLE_COLLATION | varchar(32) | YES | | NULL | | | CHECKSUM | bigint(21) unsigned | YES | | NULL | | | CREATE_OPTIONS | varchar(255) | YES | | NULL | | | TABLE_COMMENT | varchar(80) | NO | | | | +-----------------+---------------------+------+-----+---------+-------+ 21 rows in set (0.01 sec) mysql>
mysql> select database(); +------------+ | database() | +------------+ | STUDY | +------------+ 1 row in set (0.00 sec)
select table_name, engine, table_rows as tbl_rows, avg_row_length as rlen, floor((data_length+index_length)/1024/1024) as 'ToTal(MB)', floor((data_length)/1024/1024) as 'DATA(MB)', floor((index_length)/1024/1024) as 'INDEX(MB)' from information_schema.tables where table_schema=database() order by (data_length+index_length) desc;
MySQL5.7にて実行した内容は以下@20141218
select TABLE_SCHEMA,table_name, engine, table_rows as tbl_rows,
avg_row_length as rlen,floor((data_length+index_length)/1024/1024) as 'ToTal(MB)',
floor((data_length)/1024/1024) as 'DATA(MB)',floor((index_length)/1024/1024) as 'INDEX(MB)'
from information_schema.tables WHERE TABLE_SCHEMA NOT IN ('mysql','INFORMATION_SCHEMA','performance_schema')
AND ENGINE IS NOT NULL
order by (data_length+index_length) desc;
root@localhost [information_schema]>select TABLE_SCHEMA,table_name, engine, table_rows as tbl_rows,
-> avg_row_length as rlen,floor((data_length+index_length)/1024/1024) as 'ToTal(MB)',
-> floor((data_length)/1024/1024) as 'DATA(MB)',floor((index_length)/1024/1024) as 'INDEX(MB)'
-> from information_schema.tables WHERE TABLE_SCHEMA NOT IN ('mysql','INFORMATION_SCHEMA','performance_schema')
-> AND ENGINE IS NOT NULL
-> order by (data_length+index_length) desc;
+--------------+----------------------+--------+----------+-------+-----------+----------+-----------+
| TABLE_SCHEMA | table_name | engine | tbl_rows | rlen | ToTal(MB) | DATA(MB) | INDEX(MB) |
+--------------+----------------------+--------+----------+-------+-----------+----------+-----------+
| nyosm | nodes | InnoDB | 5226742 | 132 | 865 | 659 | 206 |
| nyosm | waytags | InnoDB | 4564009 | 57 | 602 | 250 | 351 |
| nyosm | waynodes | InnoDB | 6244646 | 50 | 429 | 301 | 127 |
| nyosm | nodetags | InnoDB | 648414 | 60 | 93 | 37 | 55 |
| nyosm | ways | InnoDB | 868452 | 88 | 91 | 73 | 17 |
| nyosm | relationtags | InnoDB | 28733 | 91 | 7 | 2 | 4 |
| nyosm | relationmembers | InnoDB | 73485 | 64 | 6 | 4 | 1 |
| sakila | rental | InnoDB | 16005 | 99 | 2 | 1 | 1 |
| sakila | payment | InnoDB | 16086 | 98 | 2 | 1 | 0 |
| nyosm | relations | InnoDB | 4748 | 89 | 0 | 0 | 0 |
| sakila | inventory | InnoDB | 4581 | 39 | 0 | 0 | 0 |
| sakila | film_actor | InnoDB | 5462 | 35 | 0 | 0 | 0 |
| sakila | film | InnoDB | 1000 | 196 | 0 | 0 | 0 |
| sakila | film_text | InnoDB | 1000 | 180 | 0 | 0 | 0 |
| sakila | customer | InnoDB | 599 | 136 | 0 | 0 | 0 |
| sakila | address | InnoDB | 603 | 163 | 0 | 0 | 0 |
| sakila | staff | InnoDB | 2 | 32768 | 0 | 0 | 0 |
| sakila | film_category | InnoDB | 1000 | 65 | 0 | 0 | 0 |
| sakila | city | InnoDB | 600 | 81 | 0 | 0 | 0 |
| sakila | store | InnoDB | 2 | 8192 | 0 | 0 | 0 |
| sakila | actor | InnoDB | 200 | 81 | 0 | 0 | 0 |
| myhttp | json_types | InnoDB | 0 | 0 | 0 | 0 | 0 |
| myhttp | hello | InnoDB | 6 | 2730 | 0 | 0 | 0 |
| sakila | country | InnoDB | 109 | 150 | 0 | 0 | 0 |
| myhttp | dojo_jsonp_fields | InnoDB | 2 | 8192 | 0 | 0 | 0 |
| myhttp | dojo_jsonp | InnoDB | 2 | 8192 | 0 | 0 | 0 |
| sakila | category | InnoDB | 16 | 1024 | 0 | 0 | 0 |
| myhttp | crud_messages | InnoDB | 0 | 0 | 0 | 0 | 0 |
| myhttp | compound_primary_key | InnoDB | 4 | 4096 | 0 | 0 | 0 |
| myhttp | blog_posts | InnoDB | 0 | 0 | 0 | 0 | 0 |
| myhttp | sql_types | InnoDB | 2 | 8192 | 0 | 0 | 0 |
| myhttp | simple | InnoDB | 20 | 819 | 0 | 0 | 0 |
| sakila | language | InnoDB | 6 | 2730 | 0 | 0 | 0 |
| myhttp | no_primary_key | InnoDB | 3 | 5461 | 0 | 0 | 0 |
+--------------+----------------------+--------+----------+-------+-----------+----------+-----------+
34 rows in set (0.02 sec)
root@localhost [information_schema]>select @@version;
+---------------------+
| @@version |
+---------------------+
| 5.7.5-labs-http-log |
+---------------------+
1 row in set (0.00 sec)
root@localhost [information_schema]>
特定DBのﾃｰﾌﾞﾙｻｲｽﾞ確認
MYSQLﾃﾞｰﾀﾍﾞｰｽのﾃｰﾌﾞﾙ確認
