MySQLチューニング
- 1. MySQL パラメーターチューニングの 理屈と定石 2014/03/01 yoku0825@MyNA OSC 2014 Tokyo Spring
- 2. ＼こんにちは／ ● ● ● ● とある企業のＤＢＡ ● オラクれない ● ポスグれない ● マイエスキューエる その正体は ● 嫁の夫 ● せがれの父 日本 MySQL ユーザ会 (MyNA) のすべり担当 #mysql_jp でツッコミをいただけると幸い
- 3. おしながき ● ● MySQL のパラメーターとは何か 根性論（理屈） ● ● ● ● 実際役に立つかどうかは置いておいて、 基本的な考え方 これはきっと MySQL に限らないはず 調査に使うコマンドとか のりたまふりかけ（定石）
- 4. MySQL のパラメーターとは ● 基本的に「ハードウェアのリソースを使い切ら ないための安全弁」 ● ● ● ● ● 低すぎると性能が頭打ちされるし 高すぎると不安定になったりリソースを食い合って 性能を下げたり 基本的には「必要なぶん + 余裕度」 「必要なぶん」の最適値は環境によって違うし、見 積りがとても難しい 現状足りているか足りていないかは多少調べがつく
- 6. 根性論（理屈） ● とにかく比較するしかない ● パラメーターを { 上げ | 下げ } てみる – – ● ● 性能が上がってるならまだ足りてない 性能が下がってるなら割り当てすぎてる 2 か所以上を同時に変更してはいけない 目の前にあるものを直視する ● ● 速くしたいのは今目の前にあるアクセスパターン ベンチマークで速くなった、誰かが上手くやったパ ラメーターをそのまま適用しても、目の前のものも 速くなるとは限らない
- 7. 根性論（理屈） ● 限界値 , 定常値を知る ● ● パラメーターをどれだけ変えても、ハードウェアの 限界値を超えて性能を出すことはできない 今の状況は、まだ頑張れば伸びる ( はず ) なのか、 もう諦め時なのか – ● 1 年間チューニングして、 1ms 速くなりましたって訳に はいかないのも含む 目的を絞る ● 性能 = Σ( 速さ , 可用性 , 安定性 , 運用性 , ..) – ● 速さ = Σ( レスポンスタイム , スループット , ..) 基本的にトレードオフ。何を捨てて、何を得るの か。
- 9. 調べ方
- 10. 比較する ● 一番シンプルなのは、本番機で innotop と dstat 流しながら SET GLOBAL .. ● ● 変更前後で「ほぼ同じメモリー状態に」「ほぼ同じ クエリー」が「ほぼ同じ量」流れてくることが期待 できるので、パラメーター変更に本当に効果があっ たかどうかが一番測りやすい。 ユーザー企業でよかったと思う瞬間。
- 14. SET GLOBAL で変更するとき ● グローバルでしか存在しないもの ● ● 変更した瞬間からその値で処理が始まる グローバルもセッションも存在するもの ● ● ● グローバル値は基本的にセッション値の暗黙のデ フォルト値 セッション値が新しく作られる (= コネクション の ) タイミング以外ではグローバル値の変更は効果 を及ぼさない セッション値が実効パラメーター
- 15. SET GLOBAL で変更するとき ● 反映に再起動が必要なものは ● mysqld を再起動するとメモリー状態は盛大に変わ る – – 5.6 で InnoDB Buffer Pool Dump が入ったとはいえ キャッシュ状態が違うと、比べた結果がアテにならな かったりもする ● ● 自分でキャッシュ状態をある程度一定に保つテクニックが必要 簡単に再起動できない mysqld もある – 基本的にはこの類のパラメーターは事前に計算しておく のが必要
- 16. 目の前にあるもの ● 問題が起きている箇所を特定する ● ● スロークエリーログ innotop で Time, State を観察 – ● ● SHOW FULL PROCESSLIST でもいい SHOW GLOBAL STATUS; どのパラメーターをいじるべきか考える ● EXPLAIN で、 SQL 側の問題ではないことを確認して おく – ● クソクエリーはパラメーターじゃ速くならない profile で「どのステップで問題なのか」を調べる – I/O がイケてないのに、ソートのパラメーターを変えて もダメ
- 19. SHOW GLOBAL STATUS ● ● ● ● ● ● ● なんちゃら disk なんちゃらとか なんちゃら cache とか InnoDB なんちゃら pending なんちゃらとか sort なんちゃらとか thread なんちゃらとか table_open_cache なんちゃらとか どれがカウントアップされたらどのパラメー ターをいじるのかはリファレンスとにらめっこ ● http://dev.mysql.com/doc/refman/5.6/en/serverstatus-variables.html
- 20. SHOW ENGINE INNODB STATUS ● ● SEMAPHORES セクション TRANSACTIONS セクション ● ● FILE I/O セクション ● ● ● たまにどこのラッチがほしくて待ってるかの細かい 情報が出る Pending なんちゃら LOG セクション BUFFER POOL AND MEMORY セクション
- 22. performance_schema ● ● ● ● ● 5.6 でだいぶ取れる情報が増えた とはいえ、 performance_schema 自体の情報が 少なくて今がんばってる オーバーヘッド怖い メモリーごりごり食う ps_helper と直アクセスと使い分ける感じにな りそう
- 26. 限界値を知る ● ベンチマークソフトが良く使われる ● ● ● ストレージの速度 fio MySQL の OLTP 的なクエリーを流しまくる sysbench, tpcc-mysql カタログスペック的なイメージ ● ● 飽くまでも参考値というか、「どう頑張ってもこれ 以上は出ないだろうなー」という感じ 今の状態が、ちょっと頑張れば伸びそうなのか、頑 張れば伸びるだろうけど伸びは悪そうなのか、これ 以上はスケールするしかないのか
- 27. 定常値を知る ● グラフ化しておくとべんり ● ● ● ● ● ● Percona Monitoring Plugins for Cacti ドリルダウンして調べたい時はお手製スクリプト パラメーター変更後に効果が出たのか出なかっ たのか タイミングの問題で気付けないことが長いスパ ンの中で現れてくることも パラメーターに限らずトラブルシュート全般に 役立ちます 継続的な改善にも必須
- 28. Percona Monitoring Plugins for Cacti
- 29. パラメーターだけにこだわらない ● 飽くまで 1 手段に過ぎない ● ● ● ● ● ● ハードウェアを変える パラメーターを変える テーブル構造を変える ＳＱＬを変える バイナリーを変える OS レイヤーのパラメーターを変える
- 32. テーブル構造 ● 適切なインデックス ● ● ● ● インデックスは「ソート済みのデータの複製」 検索には ( オプティマイザーの戸惑いを除いて ) 悪 影響はなし 更新には必ずオーバーヘッドになる 大概の場合「要るなら作る」の一択だが、書き込み ボトルネックになっているなら「マスターからは消 す、スレーブには残す」とか考える – 運用的なツラみとのトレードオフ
- 33. テーブル構造 ● 適度な正規化 ● ● ● ● ● ● 基本は全力で正規化するべき 非正規化した方が GROUP BY とか当然速い 非正規化するとデータ量が増えたり、 1 トランザク ション内で更新する箇所が増えて I/O 増えたり、不 整合が発生する可能性も 非正規化はダーティーハック ダーティーハックであることを忘れてスタンダード になるとツラい ストレージエンジン ● 基本的には InnoDB 統一がいいんですが
- 34. SQL を変える ● MySQL はあんまり難しいことできない ● ● ● ● 何とかとハサミは使いよう ● ● ● ORDER BY .. ASC, .. DESC とか 相関サブクエリーとか 多段で JOIN とか、テンポラリーテーブル使ったり アプリ側でマージしたり MySQL が無理せずできることだけやらせる ループや関数演算はアプリ側に持たせてやると幸せ になることが多い ○racle DB はなんだかんだ言ってすごい ● アレを基準にすると 3 歳児レベルだと思った方が
- 35. バイナリーを変える ● ● メジャーバージョンアップで機能が強化されて たり 本家 MySQL 以外の選択肢 ● ● ● ● ● Facebook MySQL Twitter MySQL MariaDB Percona Server PostgreSQL
- 36. OS レイヤーを変える ● ● ● ● ● ● ext3 より ext4 の方が性能が良いとか マウントオプション noatime とか queue/scheduler を deadline とか noop とか numactl とか Transparent Hugepage とか 概して情報量が少ないけれど
- 37. のりたまふりかけ ● カジュアルに変更できないやつ ● ● innodb_buffer_pool_size, innodb_log_file_size, innodb_log_files_in_group, innodb_flush_log_at_trx_commit, query_cache_size( を減らす ) カジュアルに変更で対応するやつ ● max_connections, thread_cache_size, table_open_cache, max_heap_table_size, tmp_table_size, read_buffer_size, read_rnd_buffer_size, sort_buffer_size, join_buffer_size, binlog_cache_size, key_buffer_size, innodb_io_capacity, innodb_io_capacity_max
- 38. innodb_buffer_pool_size ● ● InnoDB で一番性能に直結するパラメーター バッファプールは「データ , インデックスの キャッシュ」だけではなく、「最初にデータが 書かれる場所」でもある ● ● INSERT, DELETE でも使う マニュアルのいわく、物理メモリーの 80% 以上 割り当てろ ● ● InnoDB が使うメモリーはさらに 1 割くらいオー バーヘッドが載る ( 余裕度込みで ) データが全て収まるのが理想
- 39. 机上で計算 ● データサイズ ● ● ● インデックスサイズ ● ● http://dev.mysql.com/doc/refman/5.6/en/storage -requirements.html ↑ あたりを参考に インデックスに含まれるカラムのデータサイズ + プライマリキーのサイズ とはいえ大体 varchar, text 型のサイズ * キャラクターセット が支配する ● utf8 で varchar(255) を含むテーブル => 800bytes/row 弱
- 40. バッファプール確認 ● ● mysql> SELECT SUM(data_length) AS data_length, SUM(index_length) AS index_length FROM information_schema.tables WHERE engine= 'InnoDB'; +-------------+--------------+ | data_length | index_length | +-------------+--------------+ | 156237824 | 32522240 | +-------------+--------------+ 1 row in set (0.04 sec) mysql> SHOW ENGINE INNODB STATUSG .. ---------------------BUFFER POOL AND MEMORY ---------------------Total memory allocated 38425067520; in additional pool allocated 0 Dictionary memory allocated 708200 Buffer pool size 2293759 Free buffers 2281361 Database pages 9526 Old database pages 3496 ..
- 41. バッファプール確認 ● ● mysql> SHOW ENGINE INNODB STATUSG .. ---------------------BUFFER POOL AND MEMORY ---------------------.. Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000 .. mysql> SHOW GLOBAL STATUS LIKE 'innodb_buffer_%read%'; +---------------------------------------+---------------+ | Variable_name | Value | +---------------------------------------+---------------+ | Innodb_buffer_pool_read_ahead | 5691980889 | | Innodb_buffer_pool_read_ahead_evicted | 11862757 | | Innodb_buffer_pool_read_requests | 2074130774514 | | Innodb_buffer_pool_reads | 136410489 | +---------------------------------------+---------------+ 4 rows in set (0.00 sec)
- 42. innodb_log_file_size ● ● 5.6 未満では、反映に再起動のみならず ib_logfile* の再作成が必要だった ログファイルの性能は innodb_log_file_size* innodb_log_files_in_group に比例する ● ● ● 個人的には innodb_log_files_in_group は 2 でいい ログファイルの性能は書き込み処理に影響 5.5 未満では増やしすぎるとクラッシュリカバ リーに時間がかかるらしい ● 5.5 以降なら、変更のしにくさも鑑みて予め大きめ にして良いと思う
- 43. ログファイル確認 ● ● mysql> SHOW ENGINE INNODB STATUSG .. --LOG --Log sequence number 158185651300 Log flushed up to 158185651244 Last checkpoint at 158185645072 0 pending log writes, 0 pending chkp writes .. # pt-ioprofile --cell sizes --run-time 10 Tracing process ID xxxx total pread read pwrite lseek filename .. 34304 0 0 34304 0 /data/mysql/ib_logfile1 0 0 0 0 0 /data/mysql/ib_logfile0 write fsync 0 0 0 0
- 44. innodb_flush_log_at_trx_commit ● クラッシュした時に、 * 必ず * データをバッ クアップなり何なりから戻す気があるかどうか ● あるなら = 0 で、ついでに skip-innodbdoublewrite してもいいです – ● skip-innodb-doublewrite や innodb-flush-log-at-trxcommit!= 1 は「 { 失う | 壊れる } かも知れない」ではな くて「 { 失って | 壊れて } もまったく検知できない」 とりあえず起動して試して、ダメだったら…とかい うフローにしたいなら、 = 1 で
- 45. query_cache_size ● ● ● 基本的には query_cache_type= query_cache_size= 0 で起動したい SHOW PROCESSLIST で見たときに、 "Waiting for query cache lock" が目立つなら切った方 が良い オンラインで増やすのは別にいいんだけど、減 らす時は Query Cache がまるっとロックされる ので時間が止まる
- 46. どうせ後で変えるやつら ● read_buffer_size= read_rnd_buffer_size= 2M sort_buffer_size= join_buffer_size= 8M ● ● ● ● ● ● 基本、ここから減らすこと前提 thread_cache_size= 70 table_open_cache= 2048 max_heap_table_size= tmp_table_size= 128M binlog_cache_size= 8M max_connections= 151 ● 500 とかやると何かあった時にサーバー死ぬ
- 47. あんまり変えない奴ら ● ● ● ● ● ● ● ● binlog_format= MIXED max_binlog_size= max_relay_log_size= 256M relay_log_info_repository= TABLE relay_log_recovery= 1 skip_name_resolve tmpdir は datadir の隣辺りに置いてる slave_load_tmpdir character_set_server