概要
以下資料をまとめた、ありがとうございますOracle様
http://www.oracle.com/technetwork/jp/ondemand/database/mysql/mysql-perftun-1484759-ja.pdf
レスポンスタイムを高速化するために、[キューイング待ち時間(mysqlは実行前にキューに入る) + 実行時間] を短縮する
そのために仕組みを知るべし
アーキテクチャ
mysqldプロセスがクライアントからの接続を受け付けると、スレッドを生成する
その後、クライアントとスレッドが1対1で通信し、SQL構文の解析や実行計画の作成、実行などの処理を実施する
- レプリケーションのスレーブはマスタと同等以上のスペックに
- より多くのCPUコアを割り当てる (特に5.5以降)
稼働状況確認
root@hostname:/home/shimizu# mysqladmin -uroot -ppasswore status Uptime: 874 Threads: 2 Questions: 871 Slow queries: 0 Opens: 76 Flush tables: 2 Open tables: 49 Queries per second avg: 0.996 root@hostname:/home/shimizu# mysqladmin -uroot -ppasswore extended-status root@hostname:/home/shimizu# mysqladmin -uroot -ppasswore mysql> show session variables; …略 # 設定されているセッション設定を確認する mysql> show session status; …略 # セッションの統計情報を表示する
mysqladmin statusの内容
Connection Thread Pool
クライアントから1つの接続が確立されると、1つのスレッドを割り当てる
またセッション毎にさまざまな情報を保持している
mysql> show status like 'max%'; +----------------------+-------+ | Variable_name | Value | +----------------------+-------+ | Max_used_connections | 2 | # 同時に行われた同時接続のピーク値 +----------------------+-------+ 1 row in set (0.01 sec) mysql> show status like 'threads%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Threads_cached | 1 | # スレッドキャッシュ内にキャッシュされたスレッド数 | Threads_connected | 1 | # 現在接続中のクライアント数 | Threads_created | 2 | # 起動してから現在までに作成されたスレッド数 | Threads_running | 1 | +-------------------+-------+ 4 rows in set (0.01 sec) mysql> show variables like 'thread_cache%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | thread_cache_size | 128 | +-------------------+-------+ 1 row in set (0.00 sec)
max_connections：サーバが許容できるコネクション数。多すぎるとメモリを消費しきる可能性あり
また、クライアント1つにつき1つのファイルディスクリプタを利用するため注意
thread_cache_size：スレッドをコネクションの切断後にもキャッシュしておく数(max_connections/3)
スレッドのメモリ領域について
sort_buffer_size
mysql> show status like 'sort_merge%'; +-------------------+-------+ | Variable_name | Value | +-------------------+-------+ | Sort_merge_passes | 0 | # ソート処理においてテンポラリファイル上でソートされた行の結果をマージした回数 +-------------------+-------+ 1 row in set (0.00 sec)
sort_buffer_size：ソート用のメモリのサイズ。メモリに収まらない場合はテンポラリファイルを利用して実行する
temporary table (一時テーブル)
一時テーブルはgroup by や複雑なjoinなどをする時に一時的にMySQLが作るテーブル。
基本はMEMORYストレージエンジンで作成されるのでオンメモリ。ただし設定値以上になるとMyISAMで作る
tmp_table_size = 128M
max_heap_table_size = 128M
tmp file(テンポラリファイル)
こちらはソートで使うファイル。order by とgroup byで実際にソートするときに使われます。こちらはsort_buffer_size に収まりきらない場合にtmpディレクトリ上にできる
read_buffer_size
全件検索に使用されるメモリ量、indexの利用の有無は関係ない
join_buffer_size
フルジョイン(全件検索を伴うテーブルの結合)での、レコードデータのキャッシュに利用される
1回のフルジョインにつき、1つのジョインバッファが確保され、SQLの実行終了とともに解放される
Query Cache
mysqlではSELECT文をkey、結果をvalueとしてメモリに保存する
(SELECT文をハッシュ化してkeyとするため、小文字や大文字まで全く同じでないと同じと認識されない)
ただしSELECTを実行するテーブルが更新された場合は、そのキャッシュエントリは無効となる
mysql> show variables like 'query_cache_%';
+------------------------------+----------+
| Variable_name | Value |
+------------------------------+----------+
| query_cache_limit | 1048576 |
| query_cache_min_res_unit | 4096 |
| query_cache_size | 16777216 | # クエリのキャッシュサイズ
| query_cache_type | ON | # Query Cacheを許可するかどうか("2"も指定でき、そのときは明示的にSQL文にSQL_CACHE句を記載する) 更新処理が多いとパフォーマンスのオーバヘッド最悪15%
| query_cache_wlock_invalidate | OFF |
+------------------------------+----------+
5 rows in set (0.00 sec)
mysql> show status like 'Qcache_%'; # クエリキャッシュの統計情報
+-------------------------+----------+
| Variable_name | Value |
+-------------------------+----------+
| Qcache_free_blocks | 20 |
| Qcache_free_memory | 66782352 |
| Qcache_hits | 250244 | # キャッシュヒット この値によってはQueryCacheを無効にしてもよし
| Qcache_inserts | 181634 | # 登録件数
| Qcache_lowmem_prunes | 0 | # メモリ不足のためにキャッシュが削除された回数
| Qcache_not_cached | 33511 |
| Qcache_queries_in_cache | 82 |
| Qcache_total_blocks | 196 |
+-------------------------+----------+
8 rows in set (0.00 sec)
ヒット率は、Qcache_hits / ( Qcache_hits + Qcache_inserts + Qcache_not_cached ) で算出可能
クエリキャッシュを利用しない場合のSELECT文は、クエリキャッシュ無効の場合のSELECTと比較して、
キャッシュへの登録などが必要となるため、約1-2割のオーバヘッドが発生する
データベースエンジン-InnoDB-
mysql> show variables like 'innodb_%'; # 結果は中略、順不同 +-------------------------------------------+------------------------+ | Variable_name | Value | +-------------------------------------------+------------------------+ | innodb_buffer_pool_size | 268435456 | # データとインデックスの両方をキャッシュ。InnoDBはバッファプールのサイズに比例してメモリを消費(5-10%)するオブジェクトがあるため注意 | innodb_log_file_size | 5242880 | # innodb_buffer_pool_sizeの25%〜100% | innodb_flush_log_at_trx_commit | 1 | # 1:コミット時にログをフラッシュ。真のACID | innodb_flush_method | O_DIRECT | # OSのキャッシュを利用しない | innodb_buffer_pool_instances | 1 | # | innodb_io_capacity | 400 | # ディスクIOに応じて変更するべし、200だと2つのディスクストライプ | innodb_file_per_table | ON | # ファイルIOの分散に効果あり、5.6からはデフォルト | innodb_write_io_threads | 4 | # ディスクIOに応じて変更するべし、4くらいがよいパフォーマンス +-------------------------------------------+------------------------+ 100 rows in set (0.00 sec) mysql> SHOW ENGINE INNODB STATUS\G # とても長いため略 *************************** 1. row *************************** # Handler というのは MySQL のストレージエンジンのインターフェースで、 # その種類を見ることで InnoDB がファイル I/O 、ディスク I/O に近いローレベルでどういう仕事をしているのか見ることがわかるらしい mysql> show status like '%handler%'; +----------------------------+-------+ | Variable_name | Value | +----------------------------+-------+ | Handler_commit | 0 | | Handler_delete | 0 | | Handler_discover | 0 | | Handler_icp_attempts | 0 | | Handler_icp_match | 0 | | Handler_mrr_init | 0 | | Handler_mrr_key_refills | 0 | | Handler_mrr_rowid_refills | 0 | | Handler_prepare | 0 | | Handler_read_first | 0 | | Handler_read_key | 0 | | Handler_read_last | 0 | | Handler_read_next | 0 | | Handler_read_prev | 0 | | Handler_read_rnd | 0 | | Handler_read_rnd_deleted | 0 | | Handler_read_rnd_next | 0 | | Handler_rollback | 0 | | Handler_savepoint | 0 | | Handler_savepoint_rollback | 0 | | Handler_tmp_update | 0 | | Handler_tmp_write | 0 | | Handler_update | 0 | | Handler_write | 0 | +----------------------------+-------+ 24 rows in set (0.00 sec)
参考
http://www.oracle.com/technetwork/jp/ondemand/database/mysql/mysql-perftun-1484759-ja.pdf
最後に読む
http://www.atmarkit.co.jp/ait/articles/0107/24/news001.html
http://d.hatena.ne.jp/fixers/20121227/1356570495