InnoDBでも所詮統計情報なので完全にアテになる訳じゃないのはお約束。
テスト。
mysql57> CREATE TABLE t1 (val char(250)); Query OK, 0 rows affected (0.02 sec) mysql57> INSERT INTO t1 SET val= ''; Query OK, 1 row affected (0.00 sec) mysql57> INSERT INTO t1 SELECT * FROM t1; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 .. mysql57> INSERT INTO t1 SELECT * FROM t1; Query OK, 131072 rows affected (1.67 sec) Records: 131072 Duplicates: 0 Warnings: 0 ysql57> ANALYZE TABLE t1; +-------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------+---------+----------+----------+ | d1.t1 | analyze | status | OK | +-------+---------+----------+----------+ 1 row in set (0.00 sec) mysql57> SHOW TABLE STATUS\G *************************** 1. row *************************** Name: t1 Engine: InnoDB Version: 10 Row_format: Compact Rows: 257368 Avg_row_length: 312 Data_length: 80330752 Max_data_length: 0 Index_length: 0 Data_free: 4194304 Auto_increment: NULL Create_time: 2016-03-30 19:14:45 Update_time: 2016-03-30 19:24:46 Check_time: NULL Collation: utf8mb4_general_ci Checksum: NULL Create_options: row_format=Dynamic Comment: 1 row in set (0.01 sec) mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1'); +------------+-------------+--------------+-----------+ | table_rows | data_length | index_length | data_free | +------------+-------------+--------------+-----------+ | 257368 | 76.61 MiB | 0 bytes | 4.00 MiB | +------------+-------------+--------------+-----------+ 1 row in set (0.01 sec)
SHOW TABLE STATUSだと見にくいのでi_s使って取ってみる。
innodb_autoextend_incrementはシステムテーブルスペース(ibdata1)の自動拡張単位で、.ibdファイルの場合は最大で4MB(らしい)
https://dev.mysql.com/doc/refman/5.6/ja/innodb-parameters.html#sysvar_innodb_autoextend_increment
100行ずつINSERTしながら100万行くらいまで様子を見てみたけど、Data_freeはだいたい4MB~7MBの間を彷徨っている様子。4MBまでは利用可能領域を使って、それを割るとautoextendするっぽい。
行を半分くらい消してみる。
mysql57> DELETE FROM t1 WHERE rand() < 0.5; Query OK, 130891 rows affected (1.00 sec) mysql57> ANALYZE TABLE t1; +-------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------+---------+----------+----------+ | d1.t1 | analyze | status | OK | +-------+---------+----------+----------+ 1 row in set (0.00 sec) mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1'); +------------+-------------+--------------+-----------+ | table_rows | data_length | index_length | data_free | +------------+-------------+--------------+-----------+ | 131354 | 76.61 MiB | 0 bytes | 4.00 MiB | +------------+-------------+--------------+-----------+ 1 row in set (0.00 sec)
Data_lengthは変わらず、Data_freeも変わらず。.ibdファイルのサイズももちろん変わらない。
mysql57> INSERT INTO t1 SELECT * FROM t1 WHERE rand() < 0.5; Query OK, 65675 rows affected (0.77 sec) Records: 65675 Duplicates: 0 Warnings: 0 mysql57> ANALYZE TABLE t1; +-------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------+---------+----------+----------+ | d1.t1 | analyze | status | OK | +-------+---------+----------+----------+ 1 row in set (0.00 sec) mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1'); +------------+-------------+--------------+-----------+ | table_rows | data_length | index_length | data_free | +------------+-------------+--------------+-----------+ | 193161 | 76.42 MiB | 0 bytes | 4.00 MiB | +------------+-------------+--------------+-----------+ 1 row in set (0.00 sec)
消したぶんより少ない行をINSERTすると、Data_lengthは伸びない(= 空きページが再利用されている)けどData_freeも変わらない。謎い(deleteした時にdata_freeをインクリメントしてた気がするんだけど見付けられない。気のせいだったのか)
パージスレッドのご機嫌なのかなぁと思ってinnodb_fast_shutdown= 0にして再起動してみたけど変わらない。WHEREなしのDELETEでどかんと行くとData_freeに全部計上されるんだけどなぁ。
mysql57> SET GLOBAL innodb_fast_shutdown= 0; Query OK, 0 rows affected (0.00 sec) mysql57> SHUTDOWN; Query OK, 0 rows affected (0.00 sec) $ mysqld_multi start 57
Index_lengthも同じ様子。
mysql57> ALTER TABLE t1 ADD KEY (val); Query OK, 0 rows affected (9.73 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql57> ANALYZE TABLE t1; +-------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------+---------+----------+----------+ | d1.t1 | analyze | status | OK | +-------+---------+----------+----------+ 1 row in set (0.01 sec) mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1'); +------------+-------------+--------------+-----------+ | table_rows | data_length | index_length | data_free | +------------+-------------+--------------+-----------+ | 191498 | 76.44 MiB | 58.98 MiB | 4.00 MiB | +------------+-------------+--------------+-----------+ 1 row in set (0.00 sec) mysql57> DELETE FROM t1 WHERE rand() < 0.5; Query OK, 98257 rows affected (1.94 sec) mysql57> ANALYZE TABLE t1; +-------+---------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------+---------+----------+----------+ | d1.t1 | analyze | status | OK | +-------+---------+----------+----------+ 1 row in set (0.02 sec) mysql57> SELECT table_rows, sys.format_bytes(data_length) AS data_length, sys.format_bytes(index_length) AS index_length, sys.format_bytes(data_free) AS data_free FROM information_schema.tables WHERE (table_schema, table_name)= ('d1', 't1'); +------------+-------------+--------------+-----------+ | table_rows | data_length | index_length | data_free | +------------+-------------+--------------+-----------+ | 92680 | 76.44 MiB | 58.98 MiB | 4.00 MiB | +------------+-------------+--------------+-----------+ 1 row in set (0.00 sec)
大体、storage/innobase/handler/i_s.ccから上手く見つけられないんだけどどこから見つければいいんだっけ。。:(;゙゚'ω゚'):
0 件のコメント :
コメントを投稿