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

AIX、UNIX、Linux

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
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. - ma...

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

# ./mysqltuner.pl

>>  MySQLTuner 1.2.0 - Major Hayden <major@mhtx.net>
>>  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)</major@mhtx.net>

気になったのはテーブルが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を実施してみたのですが、解消していませんでした。
引き続き調査して対応してみようかと思います。

エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
エキスパートのためのMySQL[運用+管理]トラブルシューティングガイド
(2010/06/12)
奥野 幹也

 

商品詳細を見る

MySQL徹底入門 第3版 ~5.5新機能対応~
MySQL徹底入門 第3版 ~5.5新機能対応~
(2011/08/26)
遠藤 俊裕、坂井 恵 他

 

商品詳細を見る

この記事を書いた人

kometchtech

うつ病を患いながら、IT業界の末席にいるおっさんエンジニア。科学計算をしたことがないのに、HPC分野にお邪魔している。興味のある分野で学習したことをblogにまとめつつ、うつ病の経過症状のメモも置いておく日々。じつはRouterboard User Group JPの中の人でもある。 Amazon欲しいものリスト / Arm板を恵んでくれる人募集中

AD / 広告
いつか、そのとき、あの場所で。rev.2
タイトルとURLをコピーしました