[未解決][CentOS][MySQL] MySQL のフラグメント化しているテーブルに対処してみた。

How To Find and Optimize Fragmented Tables in a MySQL Database | kmsm /
http://kmsm.ca/2010/how-to-find-and-optimize-fragmented-tables-in-a-mysql-database/

検証環境：

# cat /etc/redhat-release
CentOS release 6.5 (Final)
# mysql --version
mysql  Ver 14.14 Distrib 5.6.15, for Linux (x86_64) using  EditLine wrapper

GitHub - major/MySQLTuner-perl: MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for increased performance and stability.

MySQLTuner is a script written in Perl that will assist you with your MySQL configuration and make recommendations for i...

MySQLのチューニング指標に使用することができる、MySQLTunerですが、こちらを使用して自宅環境のMySQLについて調査してみたところ、気になる点が幾つか出ました。

# ./mysqltuner.pl

>>  MySQLTuner 1.2.0 - Major Hayden <[email protected]>
>>  Bug reports, feature requests, and downloads at http://mysqltuner.com/
>>  Run with '--help' for additional options and output filtering
Please enter your MySQL administrative login: root
Please enter your MySQL administrative password:
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.6.15
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
Warning: Using a password on the command line interface can be insecure.
Warning: Using a password on the command line interface can be insecure.
[--] Data in InnoDB tables: 722M (Tables: 108)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 52)
[!!] Total fragmented tables: 10

-------- Security Recommendations  -------------------------------------------
Warning: Using a password on the command line interface can be insecure.
[OK] All database users have passwords assigned
Warning: Using a password on the command line interface can be insecure.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 5h 10m 1s (112K q [6.052 qps], 1K conn, TX: 105M, RX: 995M)
[--] Reads / Writes: 79% / 21%
[--] Total buffers: 2.1G global + 2.8M per thread (300 max threads)
[OK] Maximum possible memory usage: 2.9G (76% of installed RAM)
[OK] Slow queries: 0% (29/112K)
[OK] Highest usage of available connections: 11% (33/300)
[OK] Key buffer size / total MyISAM indexes: 8.0M/97.0K
[OK] Key buffer hit rate: 99.7% (1K cached / 4 reads)
[!!] Query cache is disabled
[OK] Sorts requiring temporary tables: 0% (1 temp sorts / 4K sorts)
[OK] Temporary tables created on disk: 2% (342 on disk / 11K total)
[OK] Thread cache hit rate: 97% (33 created / 1K connections)
[!!] Table cache hit rate: 14% (224 open / 1K opened)
[OK] Open file limit used: 0% (18/5K)
[OK] Table locks acquired immediately: 100% (227K immediate / 227K locks)
[OK] InnoDB data size / buffer pool: 722.7M/2.0G

-------- Recommendations -----------------------------------------------------
General recommendations:
Run OPTIMIZE TABLE to defragment tables for better performance
MySQL started within last 24 hours - recommendations may be inaccurate
Increase table_cache gradually to avoid file descriptor limits
Variables to adjust:
query_cache_size (>= 8M)
table_cache (> 1024)</[email protected]>

気になったのはテーブルが10個フラグメントを起こしている点でした。MyISAMベースのテーブルであればOptimize tableなどのコマンドが使用できるようですが、InnoDBベースのテーブルについては使用できないようです。

最初は、該当のテーブルが入っているデータベースごとmysqldumpを実行し、再度インポートするという手段が、どこかに情報があったので試してみたのですが、残念ながら解消せずでした。

そこで、以下のコマンドを実行し、該当のテーブルを抽出してみました。

mysql> select TABLE_NAME,Data_free from information_schema.TABLES where TABLE_SCHEMA NOT IN ('information_schema','mysql') and Data_free > 0;
+--------------+-----------+
| TABLE_NAME   | Data_free |
+--------------+-----------+
| events       |   5242880 |
| history      |   4718592 |
| history_log  |   2621440 |
| history_str  |   2097152 |
| history_text |   2621440 |
| history_uint |   2097152 |
| images       |   4194304 |
| items        |   2097152 |
| trends       |   3670016 |
| trends_uint  |   4718592 |
+--------------+-----------+

抽出したテーブルに対してALTER TABLE hogehoge ENGINE=InnoDB;を実行することで、テーブルの再構築が行われ、デフラグメントを行ったのと同じ効果が得られるということらしいので、実際に実施してみました。

mysql> alter table events engine=innodb;
Query OK, 560088 rows affected (26.68 sec)
Records: 560088  Duplicates: 0  Warnings: 0

で、再度MySQL Tunerを実施してみたのですが、解消していませんでした。
引き続き調査して対応してみようかと思います。