読者です 読者をやめる 読者になる 読者になる

CentOS 6.6 - MySQLTunerでMySQLのチューニングをしてみた。

MySQLをチューニングやってますか??やったほうがいいですよ。

MySQLTunerのダウンロード方法と使用方法を紹介します。

 

作業環境

- さくらのVPS/2G
- CentOS 6.6
- MySQL 5.1

 

MySQLTunerをダウンロードする

MySQLTunerをダウンロードします。

# wget -O /usr/local/src/MySQLTuner.zip https://github.com/rackerhacker/MySQLTuner-perl/archive/master.zip 

 

ダウンロードしたMySQLTunerのZIPファイルを解凍します。

# unzip /usr/local/src/MySQLTuner.zip -d /usr/local/src/
# rm /usr/local/src/MySQLTuner.zip

MySQLTunerは、ダウンロードしてすぐに使えます。

 

MySQLTunerを使う

MySQLTunerを実行

MySQLTunerの実行には、MySQLのrootユーザとパスワードが必要です。

# /usr/local/src/MySQLTuner/mysqltuner.pl --user root --pass <MySQLのrootパスワード>

 >>  MySQLTuner 1.3.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
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV +InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 3M (Tables: 12)
[!!] InnoDB is enabled but isn't being used
[!!] Total fragmented tables: 6

-------- Security Recommendations  -------------------------------------------
[!!] User 'root@127.0.0.1' has no password set.

-------- Performance Metrics -------------------------------------------------
[--] Up for: 3h 16m 26s (460 q [0.039 qps], 116 conn, TX: 3M, RX: 21K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 66.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 481.2M (11% of installed RAM)
[OK] Slow queries: 0% (0/460)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 8.0M/284.0K
[!!] Key buffer hit rate: 73.8% (61 cached / 16 reads)
[!!] Query cache efficiency: 0.0% (0 cached / 86 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 4 sorts)
[!!] Temporary tables created on disk: 36% (68 on disk / 188 total)
[OK] Thread cache hit rate: 99% (1 created / 116 connections)
[OK] Table cache hit rate: 90% (68 open / 75 opened)
[OK] Open file limit used: 10% (103/1K)
[OK] Table locks acquired immediately: 100% (52 immediate / 52 locks)
[!!] Connections aborted: 86%

-------- Recommendations -----------------------------------------------------
General recommendations:
    Add skip-innodb to MySQL configuration to disable InnoDB
    Run OPTIMIZE TABLE to defragment tables for better performance
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 8M, or use smaller result sets)
    tmp_table_size (> 32M)
    max_heap_table_size (> 32M)

先頭に[!!]が表示された項目をチューニングしましょう。

 

チューニングしてみましょう

 

1.チューニング

InnoDBが有効だが利用されていない。

[!!] InnoDB is enabled but isn't being used

 

InnoDBを無効にするには、my.confに設定を追加します。

[mysqld]
skip-innodb
default-storage-engine=MyISAM

 

2.チューニング

6つのテーブルが断片化している。

[!!] Total fragmented tables: 6

 

まず、データベースを確認します。

# mysql -e "show databases;" -p
Enter password: <MySQLのrootパスワード>
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| wordpress          |
+--------------------+

 

次にデータベース(wordpress)のテーブルとストレージエンジンを確認します。

# mysql -u root -Ns -e "show table status from WordPress;" -p | awk '{print "DB NAME:"$1"\n""  -> Engine:"$2}'
Enter password:
DB NAME:wp_commentmeta
  -> Engine:MyISAM
DB NAME:wp_comments
  -> Engine:MyISAM
DB NAME:wp_links
  -> Engine:MyISAM
DB NAME:wp_options
  -> Engine:MyISAM
DB NAME:wp_postmeta
  -> Engine:MyISAM
DB NAME:wp_posts
  -> Engine:MyISAM
DB NAME:wp_term_relationships
  -> Engine:MyISAM
DB NAME:wp_term_taxonomy
  -> Engine:MyISAM
DB NAME:wp_terms
  -> Engine:MyISAM
DB NAME:wp_usermeta
  -> Engine:MyISAM
DB NAME:wp_users
  -> Engine:MyISAM

 

使われているストレージエンジンは、全てMyISAMでした。ストレージエンジンがMyISAMの場合、「optimize」コマンドを使って断片化を解消します。

# mysql -p
Enter password: <MySQLのrootパスワード>
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 17
Server version: 5.1.73 Source distribution

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> use wordpress
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> optimize table wp_commentmeta;
+--------------------------+----------+----------+----------+
| Table                    | Op       | Msg_type | Msg_text |
+--------------------------+----------+----------+----------+
| wordpress.wp_commentmeta | optimize | status   | OK       |
+--------------------------+----------+----------+----------+
1 row in set (0.04 sec)

mysql> optimize table wp_comments;
+-----------------------+----------+----------+----------+
| Table                 | Op       | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| wordpress.wp_comments | optimize | status   | OK       |
+-----------------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> optimize table wp_links;
+--------------------+----------+----------+-----------------------------+
| Table              | Op       | Msg_type | Msg_text                    |
+--------------------+----------+----------+-----------------------------+
| wordpress.wp_links | optimize | status   | Table is already up to date |
+--------------------+----------+----------+-----------------------------+
1 row in set (0.00 sec)

mysql> optimize table wp_options;
+----------------------+----------+----------+----------+
| Table                | Op       | Msg_type | Msg_text |
+----------------------+----------+----------+----------+
| wordpress.wp_options | optimize | status   | OK       |
+----------------------+----------+----------+----------+
1 row in set (0.05 sec)

mysql> optimize table wp_postmeta;
+-----------------------+----------+----------+----------+
| Table                 | Op       | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| wordpress.wp_postmeta | optimize | status   | OK       |
+-----------------------+----------+----------+----------+
1 row in set (0.05 sec)

mysql> optimize table wp_posts;
+--------------------+----------+----------+----------+
| Table              | Op       | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| wordpress.wp_posts | optimize | status   | OK       |
+--------------------+----------+----------+----------+
1 row in set (8.77 sec)

mysql> optimize table wp_term_relationships;
+---------------------------------+----------+----------+----------+
| Table                           | Op       | Msg_type | Msg_text |
+---------------------------------+----------+----------+----------+
| wordpress.wp_term_relationships | optimize | status   | OK       |
+---------------------------------+----------+----------+----------+
1 row in set (0.82 sec)

mysql> optimize table wp_term_taxonomy;
+----------------------------+----------+----------+----------+
| Table                      | Op       | Msg_type | Msg_text |
+----------------------------+----------+----------+----------+
| wordpress.wp_term_taxonomy | optimize | status   | OK       |
+----------------------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> optimize table wp_terms;
+--------------------+----------+----------+----------+
| Table              | Op       | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| wordpress.wp_terms | optimize | status   | OK       |
+--------------------+----------+----------+----------+
1 row in set (0.03 sec)

mysql> optimize table wp_usermeta;
+-----------------------+----------+----------+----------+
| Table                 | Op       | Msg_type | Msg_text |
+-----------------------+----------+----------+----------+
| wordpress.wp_usermeta | optimize | status   | OK       |
+-----------------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> optimize table wp_users;
+--------------------+----------+----------+----------+
| Table              | Op       | Msg_type | Msg_text |
+--------------------+----------+----------+----------+
| wordpress.wp_users | optimize | status   | OK       |
+--------------------+----------+----------+----------+
1 row in set (0.00 sec)

mysql> optimize table wp_websitez_stats;
+-----------------------------+----------+----------+----------+
| Table                       | Op       | Msg_type | Msg_text |
+-----------------------------+----------+----------+----------+
| wordpress.wp_websitez_stats | optimize | status   | OK       |
+-----------------------------+----------+----------+----------+
1 row in set (0.00 sec)

 

ストレージエンジンが「InnoDB」の場合、上記の方法では断片化を解消できません。

ストレージエンジンにInnoDBを使っている場合、下記のようにします。

# ALTER TABLE <テーブル名> ENGINE=INNODB;

公式サイト:http://dev.mysql.com/doc/refman/5.1-olh/ja/innodb-file-defragmenting.html

 

3.チューニング

パスワードが設定されていない、MySQLユーザが存在する。

[!!] User 'root@127.0.0.1' has no password set.

パスワードが設定されていないユーザを削除します。

# mysql -e "delete from mysql.user where password="";" -p
Enter password: <MySQLのrootパスワード>

 

チューニングを終えたら確認してみましょう

MySQLTunerを実行してチューニングの効果を確認します。

# /usr/local/src/MySQLTuner/mysqltuner.pl --user root --pass <MySQLのrootパスワード>

 >>  MySQLTuner 1.3.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
[OK] Logged in using credentials passed on the command line
[OK] Currently running supported MySQL version 5.1.73
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +CSV -InnoDB +MRG_MYISAM
[--] Data in MyISAM tables: 2M (Tables: 12)
[OK] Total fragmented tables: 0

-------- Security Recommendations  -------------------------------------------
[OK] All database users have passwords assigned

-------- Performance Metrics -------------------------------------------------
[--] Up for: 2m 43s (37 q [0.227 qps], 18 conn, TX: 23K, RX: 2K)
[--] Reads / Writes: 100% / 0%
[--] Total buffers: 2.4G global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 2.8G (70% of installed RAM)
[OK] Slow queries: 0% (0/37)
[OK] Highest usage of available connections: 0% (1/151)
[OK] Key buffer size / total MyISAM indexes: 2.3G/236.0K
[!!] Query cache efficiency: 0.0% (0 cached / 19 selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2 sorts)
[OK] Temporary tables created on disk: 0% (0 on disk / 10 total)
[OK] Thread cache hit rate: 94% (1 created / 18 connections)
[OK] Table cache hit rate: 83% (35 open / 42 opened)
[OK] Open file limit used: 6% (70/1K)
[OK] Table locks acquired immediately: 100% (19 immediate / 19 locks)
[!!] Connections aborted: 16%

-------- Recommendations -----------------------------------------------------
General recommendations:
    MySQL started within last 24 hours - recommendations may be inaccurate
    Enable the slow query log to troubleshoot bad queries
    Your applications are not closing MySQL connections properly
Variables to adjust:
    query_cache_limit (> 16M, or use smaller result sets)

[!!]警告が減りました。こんな感じで、みなさんもチューニングしてみてください。

参考サイト http://www.e-agency.co.jp/column/20121220.html