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のテーブルサイズ確認
db_size2

MYSQLデータベースのテーブル確認
mysql_db

Comments are closed.

Post Navigation