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)
[!!]警告が減りました。こんな感じで、みなさんもチューニングしてみてください。
