MySQL 5.7 初心者向けセミナー　～チューニング基礎編、SQLチューニング編～ 20170814

まみー @mamy1326

前々回はうざいくらいにTweetした記憶がありますが、今回は @surumegohan さんに任せた！ 手元でレポート書くだけで手一杯でござります… #mysql_jp

しょー（show） @surumegohan

コネクションに関して。 コネクションをはるとコネクション&スレッドを使いまわしてコネクションプールとして使いまわす。 MySQLはマルチスレッドモデルなのでコネクション生成の負荷が低いDBではある。それでも最適に無駄な処理をしないように調整しましょう #mysql_jp

しょー（show） @surumegohan

thread_cache_sizeでスレッドをコネクションの切断後にもキャッシュしていく数を指定できる。 Thread_createdにある値が時間をおいて確認して、どんどん増えているならばスレッドプールの値を大きくした方がいい #mysql_jp

しょー（show） @surumegohan

Threads_createdの値の上昇が大きいなら、それだけスレッドプールの値も大きくした方がよいでしょう #mysql_jp

しょー（show） @surumegohan

コネクション数が多すぎるとメモリを消費しきる可能性がある。DB側で制御するかWeb側で制御するかという話もある。 #mysql_jp

しょー（show） @surumegohan

コネクション数（同時実行ユーザ数）が多い場合に有効な設定が「商用版」にはあります！MySQL Enterprise Scalability（スレッドプール）。 過剰に負荷をかけた場合（同時実行ユーザ数）が増えてもほぼ性能が落ちない。（らしい） #mysql_jp

しょー（show） @surumegohan

コネクションスレッドごとのバッファ。メモリ領域の話。大半のバッファはデフォルトの設定で問題ない。sort_buffer_size（ソート処理を行うためのメモリサイズ）はチューニング対象にあがりやすい。 ソート処理はできる限りメモリ上で処理した方がよいため。 #mysql_jp

しょー（show） @surumegohan

ソート処理を確認できる変数。「Sort_merge_passes」がある。ファイルを利用したマージソートのパス数。 そもそもソートが多いSQLをチューニングするアプローチもある。 #mysql_jp

しょー（show） @surumegohan

クエリキャッシュ。実行結果そのものをキャッシュしておくキャッシュ。クエリキャッシュが有効に使える前提はデータが変更されてなくてSQLも同じであること。 古いMySQLではデフォルトON。5.6や5.7はデフォルトでOFF。 #mysql_jp

しょー（show） @surumegohan

DEMANDという設定で任意のクエリだけキャッシュ可能にすることもできる。使用頻度の高いSQLだけDEMANDにするというアプローチもある。 #mysql_jp

まみー @mamy1326

クエリキャッシュは四月にこんなエントリーを書いてた。見直したいなー qiita.com/mamy1326/items… #mysql_jp

しょー（show） @surumegohan

クエリキャッシュが有効かどうか確かめる変数。 Qcache_hits（クエリキャッシュから返した数）,Qcache_inserts（新しくキャッシュにのせた数）、Qcache_lowmem_prunesがある。 どれくらいが適正なのかはシステムによる。 #mysql_jp

しょー（show） @surumegohan

クエリキャッシュは8.0ではサポートしない。その理由はクエリキャッシュのメンテナンスコストより、他の機能の開発に開発者のリソースを割いた方が有効であろうという判断。 #mysql_jp

まみー @mamy1326

そもそもクエリをキャッシュに持たせなくていい理由、つまりサポートしない理由からの、ではどう実装すべきかはあとで質問したいです #mysql_jp

しょー（show） @surumegohan

InnoDBパフォーマンスTips。5.5からデフォルトのInnoDB。トランザクションはInnoDBしか使えない。ストレージエンジンの開発はInnoDBに注力している。 #mysql_jp

しょー（show） @surumegohan

innodb_buffer_pool_sizeが重要！実データとIndexがこいつに載ってればそれ以上は大きくする必要はない。資料にある「80％」は頻繁にアクセスするデータがのっていれば問題ないという意味が含まれる。 #mysql_jp

しょー（show） @surumegohan

SHOW ENGINE INNODB STATUS;で直近の何秒間でキャッシュからデータをとれたのか、キャッシュが有効に使われるのか確認することができる。 #mysql_jp

しょー（show） @surumegohan

innodb_log_file_size。ibファイルは0番と1番の繰り返しをしている。innodbのログファイルはクラッシュリカバリのために使われる。 InnoDBはデータを変更した際にディスクではなくコミットのタイミングでログにだけ吐き出す。 #mysql_jp

しょー（show） @surumegohan

チェックポイントが発生すると、ディスクに書き出す。 なので、チェックポイントが発生する前にクラッシュすると困る。その際にログファイルを呼び出して適用して障害発生直前の状態に戻す。 これをクラッシュリカバリという。 #mysql_jp

しょー（show） @surumegohan

チェックポイントが発生すればログファイルは上書きできるので循環してぐるぐるできる。更新が多いとチェックポイント待ちが大きくなる。だからその場合はログファイルサイズを大きくするというアプローチがある。 #mysql_jp

まみー @mamy1326

innodb_file_per_tableってMySQL 5.6.6でデフォルトONみたいです。 dev.mysql.com/doc/refman/5.6… #mysql_jp

しょー（show） @surumegohan

innodb_file_per_tableはデフォルトでONです。ONを推奨。テーブル単位でファイルを分割する設定。テーブル名.ibdができる。OFFにすると共通表領域に全データが全部入る。しかしibデータはシュリンクできない。 #mysql_jp

しょー（show） @surumegohan

innodb_flush_methodはO_DIRECTにするとLinux上で余計にキャッシュをもたない。 #mysql_jp

しょー（show） @surumegohan

innodb_flush_log_at_trx_commit。スレーブに障害が起きても良いなら、このパラメータの値を変えても良い。基本的にきちんと理解しないと変更してはいけないパラメータ。ACIDが崩れるため。 #mysql_jp

しょー（show） @surumegohan

innodb_io_capacityのデフォルトはストライピングされた2本のディスクを想定している。 システムに依存するパラメータなので、運用して最適地を探していくパラメータになる。 #mysql_jp