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データベースのテーブル確認