mysqlのinformation_schema.tablesによるテーブルサイズの表示 - 改

ポイントは、data_free というデフラグ要な領域

InnoDBなmysqlでは data_free という領域があり、 tableのデータを削除しても、ディスク容量は減らない。

ディスク容量を減らすには、postgresのvacuumや、hddのデフラグに該当する 1) 「OPTIMIZE TABLE $table_name;」または2)「ALTER TABLE $table_name ENGINE INNODB;」の実行が必要。

https://end0tknr.hateblo.jp/entry/20120729/1343513960

↑こちらの以前のエントリの再編集。

AWS RDSで表示されるディスク使用量と、information_schema.tables による使用量が 乖離していたことがきっかけ。

1)の実行には、事前にmysqlの設定が必要らしく、私の場合、2)を実行。

select  
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,  
floor((data_length+index_length+data_free)/1024/1024) as all_MB,
floor((data_length)/1024/1024)  as data_MB,
floor((index_length)/1024/1024) as index_MB,
floor((data_free)/1024/1024) as data_free_MB
from information_schema.tables  
where table_schema=database()  
order by (data_length+index_length) desc;
+-----------------------------+--------+----------+------+-------+-------+------+---------------+
| table_name                  | engine | tbl_rows | rlen | allMB | dMB   | iMB  | data_free_iMB |
+-----------------------------+--------+----------+------+-------+-------+------+---------------+
| csv_price                   | InnoDB | 27940304 |  511 | 15018 | 13624 | 1393 |           154 |
| csv_electricity             | InnoDB | 29248104 |  493 | 13765 | 13765 |    0 |          4888 |
| csv_battery_data            | InnoDB | 13357768 |  312 |  4391 |  3975 |  415 |             7 |
| csv_electricity_yesterday   | InnoDB |  1157275 | 3552 |  3921 |  3921 |    0 |         13200 |
| monthly_data                | InnoDB |  1555381 | 2538 |  3764 |  3764 |    0 |            12 |
| csv_price_yesterday         | InnoDB |   902017 | 4057 |  3551 |  3490 |   61 |          9505 |
| csv_electricity_hour        | InnoDB |  1386656 |  531 |   703 |   703 |    0 |             6 |

mysql> ALTER TABLE csv_price ENGINE INNODB;

と実行すると、↓この通り

select  
table_name, engine, table_rows as tbl_rows, avg_row_length as rlen,  
floor((data_length+index_length+data_free)/1024/1024) as all_MB,
floor((data_length)/1024/1024)  as data_MB,
floor((index_length)/1024/1024) as index_MB,
floor((data_free)/1024/1024) as data_free_MB
from information_schema.tables  
where table_schema=database()  
order by (data_length+index_length) desc;
+-----------------------------+--------+----------+------+-------+-------+------+---------------+
| table_name                  | engine | tbl_rows | rlen | allMB | dMB   | iMB  | data_free_iMB |
+-----------------------------+--------+----------+------+-------+-------+------+---------------+
| csv_price                   | InnoDB | 27940304 |  511 | 15018 | 13624 | 1393 |             0 |
| csv_electricity             | InnoDB | 29248104 |  493 | 13765 | 13765 |    0 |             0 |
| csv_battery_data            | InnoDB | 13357768 |  312 |  4391 |  3975 |  415 |             0 |
| csv_electricity_yesterday   | InnoDB |  1157275 | 3552 |  3921 |  3921 |    0 |             0 |
| monthly_data                | InnoDB |  1555381 | 2538 |  3764 |  3764 |    0 |             0 |
| csv_price_yesterday         | InnoDB |   902017 | 4057 |  3551 |  3490 |   61 |             0 |
| csv_electricity_hour        | InnoDB |  1386656 |  531 |   703 |   703 |    0 |             0 |