【Mysql】チューニング用ツール

  • 投稿者:
  • 投稿カテゴリー:Linux / MySQL

その前に

必要に応じて RESET QUERY CACHE コマンドを利用してクエリキャッシュを削除するべし

mysql> RESET QUERY CACHE;
Query OK, 0 rows affected (0.00 sec)

MySQLTuner

設定を変更するツールではなく、サーバのパフォーマンスの概観を表示し、推奨設定を提示してくれるツール

cd /usr/local/src/
wget -d https://github.com/major/MySQLTuner-perl/archive/master.zip
unzip master.zip
cd MySQLTuner-perl-master/
./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
[OK] Logged in using credentials from debian maintenance account.

-------- General Statistics --------------------------------------------------
[--] Skipped version check for MySQLTuner script
[OK] Currently running supported MySQL version 5.5.34-0ubuntu0.12.04.1-log
[OK] Operating on 64-bit architecture

-------- Storage Engine Statistics -------------------------------------------
[--] Status: +Archive -BDB -Federated +InnoDB -ISAM -NDBCluster
[--] Data in MyISAM tables: 56B (Tables: 11)
[--] Data in InnoDB tables: 4M (Tables: 100)
[--] Data in PERFORMANCE_SCHEMA tables: 0B (Tables: 17)
[!!] Total fragmented tables: 100

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

-------- Performance Metrics -------------------------------------------------
[--] Up for: 11d 22h 54m 56s (95K q [0.092 qps], 3K conn, TX: 224M, RX: 14M)
[--] Reads / Writes: 81% / 19%
[--] Total buffers: 192.0M global + 2.7M per thread (151 max threads)
[OK] Maximum possible memory usage: 597.8M (60% of installed RAM)
[OK] Slow queries: 1% (1K/95K)
[OK] Highest usage of available connections: 1% (2/151)
[OK] Key buffer size / total MyISAM indexes: 16.0M/125.0K
[!!] Key buffer hit rate: 94.6% (368 cached / 20 reads)
[OK] Query cache efficiency: 69.6% (50K cached / 71K selects)
[OK] Query cache prunes per day: 0
[OK] Sorts requiring temporary tables: 0% (0 temp sorts / 2K sorts)
[!!] Temporary tables created on disk: 34% (1K on disk / 3K total)
[OK] Thread cache hit rate: 99% (2 created / 3K connections)
[OK] Table cache hit rate: 28% (285 open / 998 opened)
[OK] Open file limit used: 10% (107/1K)
[OK] Table locks acquired immediately: 100% (29K immediate / 29K locks)
[OK] InnoDB data size / buffer pool: 4.6M/128.0M

-------- Recommendations -----------------------------------------------------
General recommendations:
    Run OPTIMIZE TABLE to defragment tables for better performance
    When making adjustments, make tmp_table_size/max_heap_table_size equal
    Reduce your SELECT DISTINCT queries without LIMIT clauses
Variables to adjust:
    tmp_table_size (> 16M)
    max_heap_table_size (> 16M)


[!!]と出ている行が警告で、チューニングの改善が必要な箇所が提示される
また--host <hostname> でリモートホストに接続することも可能

<h1>既存ツールで状況確認</h1>
MySQLの稼働総時間や開かれているテーブルの数を調べる

mysqladmin -uroot -pXXXXXXXXX status
Uptime: 1036930  Threads: 1  Questions: 96337  Slow queries: 1550  Opens: 998  Flush tables: 1  Open tables: 285  Queries per second avg: 0.092

より詳細に知りたい場合は
mysqladmin -uroot -pXXXXXXXXX extended-status | lv

mysqlslap

クライアント負荷エミュレーション
mysqlslapはテストを開始すると共にmysqlsapというデータベースとt1というテーブルを自動作成し、テストが完了すると削除する

root@hostname:/home/admin# mysqlslap --version
mysqlslap  Ver 1.0 Distrib 5.5.39-MariaDB, for debian-linux-gnu (x86_64)
root@hostname:/home/admin# mysqlslap --no-defaults --auto-generate-sql --engine=innodb --auto-generate-sql-write-number=1000 --concurrency=100 --auto-generate-sql-execute-number=100 --auto-generate-sql-load-type=read --iterations=3 --user=root --password=password
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 19.531 seconds
        Minimum number of seconds to run all queries: 19.407 seconds
        Maximum number of seconds to run all queries: 19.776 seconds
        Number of clients running queries: 100
        Average number of queries per client: 100
--no-defaultsmy.cnf に書かれたデフォルト値を無視する。このオプションは先頭に付ける必要がある
--auto-generate-sqlファイルやコマンドオプションを介して提供されていない場合、SQLステートメントを自動的に生成する
--auto-generate-sql-guid-primaryGUIDベースの主キーカラムを作成する
--engineストレージエンジンを指定する
--auto-generate-sql-write-number各スレッドで実行するINSERT回数を指定する(デフォルト100)
--no-dropで確認したところ、t1のレコード格納数
--concurrency同時接続数
--auto-generate-sql-execute-numberクライアント毎に発行するSQLクエリ数を指定する。concurrencyとの積が実行される全クエリ数
--auto-generate-sql-load-typeアクセスパターンを指定する。read (scan tables), write (insert into tables), key (read primary keys), update (update primary keys), or mixed (half inserts, half scanning selects)
--iterations実行するテストの回数
--number-int-colsテストテーブルに作る INT 型カラムの数
--number-char-colsテストテーブルに作る VARCHAR 型カラムの数
--no-dropプログラムの終了時、作成したスキーマを削除しない、どんなテーブルで検証したか確認できる

★queryを指定できる？
http://hack.aipo.com/archives/2959/

tcpdumpでSQL読み取り可能？

DBT2
SysBench
supesmack
mybench

シンプルにパラメーターが書かれている

http://www.powercms.jp/blog/2012/10/mysqlinnodb.html
http://thinkit.co.jp/free/article/0707/2/4/

よむべし

http://www.gashimax.com/wiki/index.php?MySQL%2F%A5%C1%A5%E5%A1%BC%A5%CB%A5%F3%A5%B0

SHOW STATUS

http://rfs.jp/sb/sql/s03/07-8.html

ORACLEが開発した負荷テストツール。現在開発終了。使う？

http://www.multiburst.net/sometime-php/2009/06/transaction-generator/

mysqldumpslow

slow queryをExPLAINで解析

slow query 出力方法

mysql> show variables like 'slow_query%';
+---------------------+---------------------------------+
| Variable_name       | Value                           |
+---------------------+---------------------------------+
| slow_query_log      | OFF                             |
| slow_query_log_file | /var/log/mysql/mariadb-slow.log |
+---------------------+---------------------------------+
2 rows in set (0.00 sec)

mysql> show variables like 'long_query%';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 | 
+-----------------+-----------+
1 row in set (0.00 sec)

mysql> show variables like 'log_queries%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| log_queries_not_using_indexes | OFF   |
+-------------------------------+-------+
1 row in set (0.00 sec)

mysql> set global slow_query_log = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> set global long_query_time = 0.01; # 実行に0.01秒かかったSQL文を出力。0を指定するとすべてのクエリが出力する
Query OK, 0 rows affected (0.00 sec)

mysql> set global log_queries_not_using_indexes = 1; # フルスキャン(全件検索(SELECT * FROM)、全索引検索(SELECT COUNT (*) FROM ))したSQL文を実行時間にかかわらず出力する
Query OK, 0 rows affected (0.00 sec)

EXPLAIN

実行計画(indexを使うか、どのindexを使うか)を確認するための構文
2014-07-22_231935

次のクエリを改善する必要がある

  • type=ALL(全件検索)のクエリ
  • type=index(全索引検索) key=PRIMARYのクエリ(InnoDBはクラスタインデックスのため)

http://nippondanji.blogspot.jp/2009/03/mysqlexplain.html
http://tech.aainc.co.jp/archives/4634

general_log(一般クエリログ)を取得

MariaDB [(none)]> SET GLOBAL general_log = 'ON'; #MySQL 5.1.12 から、ランタイムで一般クエリログを有効/無効にすることができるようになった
Query OK, 0 rows affected (0.01 sec)

MariaDB [(none)]> show variables like '%general_log%';
+------------------+---------------------+
| Variable_name    | Value               |
+------------------+---------------------+
| general_log      | ON                  |
| general_log_file | general.log         |
+------------------+---------------------+
2 rows in set (0.00 sec)

MariaDB [(none)]> quit

root@hostname:/home/admin# tail -f /var/lib/mysql/general.log # queryが流れる
140628 13:30:21  2390 Connect   wordpressuser@localhost as anonymous on
                 2390 Query     SET NAMES utf8
                 2390 Init DB   techwordpress
                 2390 Query     SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
                 2390 Query     SELECT option_value FROM wp_options WHERE option_name = 'akismet_comment_nonce' LIMIT 1
                 2390 Query     SELECT option_value FROM wp_options WHERE option_name = 'widget_pages' LIMIT 1
# 表示内容は(開始時間-スレッドID-クエリ種類-SQL内容)

テーブルに保存することも可能(mysql.general_log)

MariaDB [(none)]> SET GLOBAL log_output = "TABLE";
Query OK, 0 rows affected (0.02 sec)

MariaDB [(none)]> show variables like '%log_output%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_output    | TABLE |
+---------------+-------+
1 row in set (0.03 sec)

MariaDB [mysql]> select * from general_log limit 4\G
*************************** 1. row ***************************
  event_time: 2014-06-28 14:08:56.648096
   user_host: [wordpressuser] @ localhost []
   thread_id: 2455
   server_id: 1
command_type: Connect
    argument: wordpressuser@localhost as anonymous on
*************************** 2. row ***************************
  event_time: 2014-06-28 14:08:56.648341
   user_host: wordpressuser[wordpressuser] @ localhost []
   thread_id: 2455
   server_id: 1
command_type: Query
    argument: SET NAMES utf8
*************************** 3. row ***************************
  event_time: 2014-06-28 14:08:56.648525
   user_host: wordpressuser[wordpressuser] @ localhost []
   thread_id: 2455
   server_id: 1
command_type: Init DB
    argument: techwordpressuser
*************************** 4. row ***************************
  event_time: 2014-06-28 14:08:56.652426
   user_host: wordpressuser[wordpressuser] @ localhost []
   thread_id: 2455
   server_id: 1
command_type: Query
    argument: SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'

参考URL

http://www.e-agency.co.jp/column/20121220.html

http://dream-web.info/2014/01/29/3233/