またまた次の投稿の続きです。今回はテーブルの断片化を最適化です♪
ポイント
# テーブルの照合順序、エンジンなどを確認 SHOW TABLE STATUS FROM testdb; # MyISAM のテーブルを最適化 OPTIMIZE TABLE testtable;
MySQLTuner でのストレージエンジン分析結果（再）
先日の投稿からもう一度 MySQLTuner の診断をしたところ、次の様になっていました。4つのテーブルが断片化しているよ！だそうです。
-------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 10M (Tables: 12) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [!!] Total fragmented tables: 4
今回は、これを解消したいと思います。
実際に MySQL のテーブルを最適化して断片化を解消した記録
まずは MySQL にログインです。
[root@oki2a24 ~]# mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 9721 Server version: 5.5.31 MySQL Community Server (GPL) by Remi Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved. Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners. Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
そして本ブログのデータベースを指定します。
mysql> use wordpressdb; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql>
「Database changed」の前のヒントみたいなテキストは何なのでしょう？わたくし、気になります。
最適化、、、の前に、テーブルのエンジンが MyISAM だったと思うので、確かめます。
mysql> SHOW TABLE STATUS FROM wordpressdb; +-------------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-------------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ | wordpress_commentmeta | MyISAM | 10 | Dynamic | 646 | 207 | 134180 | 281474976710655 | 28672 | 0 | 983 | 2013-02-07 22:05:13 | 2013-05-13 21:25:14 | 2013-05-13 21:25:14 | utf8_general_ci | NULL | | | | wordpress_comments | MyISAM | 10 | Dynamic | 209 | 442 | 92424 | 281474976710655 | 23552 | 0 | 257 | 2013-02-07 22:05:13 | 2013-05-13 10:59:16 | 2013-05-13 21:25:14 | utf8_general_ci | NULL | | | | wordpress_links | MyISAM | 10 | Dynamic | 7 | 77 | 540 | 281474976710655 | 3072 | 0 | 8 | 2013-02-07 22:05:13 | 2013-05-09 23:16:32 | 2013-05-13 21:25:14 | utf8_general_ci | NULL | | | | wordpress_options | MyISAM | 10 | Dynamic | 631 | 1056 | 666872 | 281474976710655 | 31744 | 0 | 222815 | 2013-02-07 22:05:13 | 2013-05-13 21:31:48 | 2013-05-13 21:25:14 | utf8_general_ci | NULL | | | | wordpress_postmeta | MyISAM | 10 | Dynamic | 1276 | 48 | 61788 | 281474976710655 | 55296 | 0 | 3769 | 2013-02-07 22:05:13 | 2013-05-13 21:32:18 | 2013-05-13 21:25:14 | utf8_general_ci | NULL | | | | wordpress_posts | MyISAM | 10 | Dynamic | 1656 | 5703 | 9445568 | 281474976710655 | 4046848 | 0 | 3965 | 2013-02-07 22:05:13 | 2013-05-13 21:25:14 | 2013-05-13 21:25:19 | utf8_general_ci | NULL | | | | wordpress_term_relationships | MyISAM | 10 | Fixed | 1514 | 21 | 31794 | 5910974510923775 | 59392 | 0 | NULL | 2013-02-07 22:05:18 | 2013-05-11 22:32:06 | 2013-05-13 21:25:19 | utf8_general_ci | NULL | | | | wordpress_term_taxonomy | MyISAM | 10 | Dynamic | 360 | 41 | 14864 | 281474976710655 | 16384 | 0 | 389 | 2013-02-07 22:05:18 | 2013-05-13 10:00:09 | 2013-05-13 21:25:19 | utf8_general_ci | NULL | | | | wordpress_terms | MyISAM | 10 | Dynamic | 360 | 49 | 17700 | 281474976710655 | 40960 | 0 | 389 | 2013-02-07 22:05:18 | 2013-05-11 20:03:02 | 2013-05-13 21:25:19 | utf8_general_ci | NULL | | | | wordpress_usermeta | MyISAM | 10 | Dynamic | 42 | 78 | 3312 | 281474976710655 | 10240 | 0 | 43 | 2013-02-07 22:05:18 | 2013-05-13 21:25:19 | 2013-05-13 21:25:19 | utf8_general_ci | NULL | | | | wordpress_users | MyISAM | 10 | Dynamic | 1 | 128 | 128 | 281474976710655 | 4096 | 0 | 2 | 2013-02-07 22:05:18 | 2013-05-09 23:16:37 | 2013-05-13 21:25:19 | utf8_general_ci | NULL | | | | wordpress_yarpp_related_cache | MyISAM | 10 | Fixed | 2214 | 25 | 55350 | 7036874417766399 | 120832 | 0 | NULL | 2013-02-07 22:05:18 | 2013-05-13 12:09:15 | 2013-05-13 21:25:19 | utf8_general_ci | NULL | | | +-------------------------------+--------+---------+------------+------+----------------+-------------+------------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-----------------+----------+----------------+---------+ 12 rows in set (0.00 sec) mysql>
2つのテーブルのみ「Row_format」の種類が「Fixed」です。他のものは「Dynamic」です。これは一体何なのでしょうか？わたくし、気になります。
それはともかく、テーブルの「Engine」は「MyISAM」です。これから断片化解消のための SQL を作成しますが、テーブル名をひとつひとつ手で打ちたくありませんので、テーブル名一覧を表示して、それから Vim で編集しようと思います。
mysql> show tables; +-------------------------------+ | Tables_in_wordpressdb | +-------------------------------+ | wordpress_commentmeta | | wordpress_comments | | wordpress_links | | wordpress_options | | wordpress_postmeta | | wordpress_posts | | wordpress_term_relationships | | wordpress_term_taxonomy | | wordpress_terms | | wordpress_usermeta | | wordpress_users | | wordpress_yarpp_related_cache | +-------------------------------+ 12 rows in set (0.00 sec) mysql>
Vim での編集の詳細は省きます。「|」と半角スペース「 」を置換して削除し、行頭に「OPTIMIZE TABLE 」、行末に「;」を追加しただけです。
できた SQL 文を実行しますと、次のようになりました。
mysql> OPTIMIZE TABLE wordpress_commentmeta; +-----------------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_commentmeta | optimize | status | Table is already up to date | +-----------------------------------+----------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_comments; +--------------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_comments | optimize | status | Table is already up to date | +--------------------------------+----------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_links; +-----------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_links | optimize | status | Table is already up to date | +-----------------------------+----------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_options; +-------------------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +-------------------------------+----------+----------+----------+ | wordpressdb.wordpress_options | optimize | status | OK | +-------------------------------+----------+----------+----------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_postmeta; +--------------------------------+----------+----------+----------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+----------+----------+----------+ | wordpressdb.wordpress_postmeta | optimize | status | OK | +--------------------------------+----------+----------+----------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_posts; +-----------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_posts | optimize | status | Table is already up to date | +-----------------------------+----------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_term_relationships; +------------------------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +------------------------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_term_relationships | optimize | status | Table is already up to date | +------------------------------------------+----------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_term_taxonomy; +-------------------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_term_taxonomy | optimize | status | Table is already up to date | +-------------------------------------+----------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_terms; +-----------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_terms | optimize | status | Table is already up to date | +-----------------------------+----------+----------+-----------------------------+ 1 row in set (0.01 sec) mysql> OPTIMIZE TABLE wordpress_usermeta; +--------------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +--------------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_usermeta | optimize | status | Table is already up to date | +--------------------------------+----------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_users; +-----------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-----------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_users | optimize | status | Table is already up to date | +-----------------------------+----------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql> OPTIMIZE TABLE wordpress_yarpp_related_cache; +-------------------------------------------+----------+----------+-----------------------------+ | Table | Op | Msg_type | Msg_text | +-------------------------------------------+----------+----------+-----------------------------+ | wordpressdb.wordpress_yarpp_related_cache | optimize | status | Table is already up to date | +-------------------------------------------+----------+----------+-----------------------------+ 1 row in set (0.00 sec) mysql>
「wordpress_options」と「wordpress_postmeta」が「OK」と表示されており、おそらくこれらが最適化され断片化が解消されたのだと思います。
ただ、気になる点がございます。。。MySQLTuner の診断結果では、4つのテーブルが断片化との結果でしたが、今回「OPTIMIZE TABLE」で最適化されたと思われるテーブルは2つです。
どういうことなのでしょうか？
再び MySQLTuner の診断結果。果たして断片化は解消されたのでしょうか？
-------- Storage Engine Statistics ------------------------------------------- [--] Status: +Archive -BDB -Federated -InnoDB -ISAM -NDBCluster [--] Data in MyISAM tables: 10M (Tables: 12) [--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17) [OK] Total fragmented tables: 0
断片化は解消されました！嬉しいです♪
おわりに
SQL を作るにあたって、次の本家ページが参考なりました。ありがとうございます！
また、Vim での編集では次のページが参考なりました。実際によく行うことですので、しっかり身につけたいです。ありがとうございます♪
以上です。