Mysqlのチューニングまとめ

Mysqlのチューニングのよく使う手段をまとめる

環境

AWS RDS
mysql: 5.7.23
パフォーマンスインサイト有効にしてる

SlowQuery

設定

slow_query_log	ON	説明
slow_query_log_file	/var/lib/mysql/slow.log	logファイルのパス
log_output	TABLE	mysql.slow_logテーブルへ出力する
long_query_time	5	5秒超えると、slow_queryと判定する

本番DBで何回SQL何回実行しても遅い

index追加、アプリの改善が効きそう

本番DBでの実行は一時的遅い

lock waitを監視する

パフォーマンスインサイトでlocks周りのメトリクスでlock発生してかどうかを確認

lock発生した場合は、どのQueryかを特定する

SELECT r.trx_id waiting_trx_id,  
       r.trx_mysql_thread_id waiting_thread,
       r.trx_started waiting_started,
       r.trx_tables_in_use waiting_tables_in_use,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, 
       b.trx_mysql_thread_id blocking_thread,
       b.trx_started blocking_started,
       b.trx_tables_in_use blocking_tables_in_use,
       b.trx_query blocking_query
   FROM       information_schema.innodb_lock_waits w
   INNER JOIN information_schema.innodb_trx b  ON  
    b.trx_id = w.blocking_trx_id
  INNER JOIN information_schema.innodb_trx r  ON  
    r.trx_id = w.requesting_trx_id;

https://dev.mysql.com/doc/refman/5.6/ja/innodb-information-schema-examples.html
こちらを参考に、jenkinsかlamdaで上のSQLの結果をCSVで溜め込んで調査する

blocking_query見つかったら、そちらを改善する

buffer pool

パフォーマンスインサイトでこの二つ見る

メトリクス名	説明
innodb_buffer_pool_hit_rate	buffer poolへのヒット率、基本は99%以上ある、低い場合はbuffer poolサイズをあげた方が良い
innodb_buffer_pool_usage	buffer pool利用率、高くなると、余裕を持って、buffer poolサイズをあげた方が良い