【テーブル断片化の解消】MySQL チューニングしたいです!★MySQLTuner をきっかけに♪3

スポンサードリンク


またまた次の投稿の続きです。今回はテーブルの断片化を最適化です♪

ポイント

# テーブルの照合順序、エンジンなどを確認
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 での編集では次のページが参考なりました。実際によく行うことですので、しっかり身につけたいです。ありがとうございます♪

以上です。

コメントを残す