MySQL High Performance Tuning Guide in Udemy Section 2

MySQL High Performance Tuning Guide in Udemy Section 2

6. Example Database

ローカルにサンプルデータベースを作る章。

window関数を使うので8系を入れる必要がある。

homebrew formulaeを見てインストールコマンドを打つ。
https://formulae.brew.sh/formula/mysql

$ brew install mysql
$ brew services restart mysql

7. Maging use of Performance

• どこで時間がかかっているのか測定する必要がある
• パフォーマンススキーマはクエリの性能の情報を持っている

mysql> use performance_schema;
mysql> show tables;

• よく使うのが events_statements_summary_by_digest
• このテーブルには、テーブルがリセット(主にMySQLの再起動による)されてからの全てのクエリに関する基本的なレポートがある
• 普通にselect * しても見づらいので、total timeでソートするほうが良い

mysql> select * from events_statements_summary_by_digest order by sum_timer_wait desc limit 10;

• これでもっとも実行コストの高かったクエリを見つけられる
• events_statements_summary_by_digestの 各カラムの解説
  • DIGEST_TEXT:標準化されたクエリ
    • whereに指定したパラメタが?に置換され、同様のクエリとグルーピングされているということ
  • COUNT_STAR: クエリの実行された回数
  • SUM_TIMER_WAIT: クエリ実行回数(COUNT_STAR) * 平均実行時間(AVG_TIMER_WAIT)
  • SUM_LOCK_TIME: テーブルロックを待った時間の合計
  • 他のカラムも最適化のヒントになる
    • 例えばテンポラリテーブルがメモリを喰ってればSUM_CREATED_TMP_DISKとSUM_CREATED_TMP_TABLESがヒントになるかも
    • が、状況がわからなければとりあえず SUM_TIMER_WAITのTOP10を見ると良い

英語

• lead to ~
  • ~を引き起こす、~に通じる
  • This leads to an important principle.
    • これは重要な原則に通じる
• reliably
  • 期待通りに
  • We cannot reliably optimize it.
    • 期待通りに最適化できない
• eliminate
  • 削除する、排除する
• whatever ~
  • ~なものはすべて
• plethora
  • 過多
• out of ~
  • ~の中から
  • the challenge is to make sense out of plethora of tables and metrics.
    • 過剰なテーブルとメトリクスから理解していくのは困難だ。
• the trick is to ~
  • コツは ~ することです
• what qualifies as important is ...
  • (直訳)重要なものとしてふさわしいことは ...
  • 「重要性」とでも訳せば良さそう
  • What qualifies as important depends on your situation.
    • 重要かどうかは状況による

8. Top 10 Time-Consuming Queries

• 単純なselectだと重要な情報を取りずらいから以下のクエリを用意して、コピペしちゃうと楽

SELECT
	(100 * SUM_TIMER_WAIT / sum(SUM_TIMER_WAIT) OVER()) as percent,
	SUM_TIMER_WAIT AS tootal,
	COUNT_STAR AS calls,
	AVG_TIMER_WAIT AS mean,
	substring(DIGEST_TEXT, 1, 75)
FROM
	performance_schema.events_statements_summary_by_digest
ORDER BY
	SUM_TIMER_WAIT DESC
LIMIT 10

• sys.statement_analysis などのSys SchemaはPerformance Schemaを加工して作られているが、ほしい情報が無い場合があるので、その場合はperformance_schema.events_statements_summary_by_digest でドリルダウンするのがよい
• タイムプロファイリングは原因を特定するのに役立つけど、答えまで教えてくれるわけではない
• 基本的にはCPUで時間かかってるけど、IOの可能性もあり、その分析方法は次のセクションでやる

英語

• Therefore
  • したがって
• make use of ...
  • ...を利用する
• relevant ...
  • 関連がある、適切な、妥当な
    • 関連があるで覚えいていたけど、コンテキストに対して関連がある、という意味っぽい。重要な〜みたいな意味でとっても良さそう。

9. Whey Are Queries Slow?

• クエリが遅い理由を理解するには、クエリのシーケンスを理解して、クエリのライフタイムを考える必要がある
  • クエリは、クライアントからサーバーに送られ、実行計画を建てられて、実行されて、クライアントに戻っていく一連のシーケンスを考えろと言ってるぽい
• クエリの実行が最も重要で、ストレージエンジンを検索するためにたくさんの呼び出しを行っていて、これがメモリ・CPU・IOを消費している
• 過度なストレージエンジンの呼び出しによりクエリが遅くなる

英語

• accomplish
  • 成し遂げる、達成する、完遂する
• excessive
  • 過度の

10. Finding Candidate Queries for Optimization

• performance_schema.events_statements_summary_by_digest に戻って最適化候補のクエリを調査する
• SUM_ROWS_EXAMINED の値がかなり大きいやつをSUM_ROWS_SENTの値と比較してギャップがある場合
  • 調査した行がクライアントに返した数よりも大きいという意味
  • 貧弱なインデックスを使っていないか確認する
• SUM_SELECT_FULL_JOIN が大きい場合
  • ジョインするときにフルテーブルスキャンしているという意味
  • ジョインの条件にインデックスが必要
• SUM_SELECT_RANGE_CHECK が大きい場合
  • セカンダリインデックスを使っているという意味
  • インデックスを考え直したほうがいい
• SUM_CREATED_TMP_DISK_TABLES が大きい場合
  • ソートやグルーピングを処理するときのテンポラリテーブルをディスクに書き込む場合が多いという意味
  • ソートやグルーピングに最適なインデックスを使うようにして、テンポラリテーブルの容量が大きくならないようにしたほうがよい
  • テンポラリテーブルに許可しているメモリの容量を増やして、書き込みの遅いディスクを使われるのを避けたほうがよい
• SUM_SORT_MERGE_PASSES が大きい場合
  • sort bufferを大きくしたほうが良い
• これらの項目は他の項目とjoinして使うことが一般的

英語

• to justify further investigations
  • さらなる調査を正当化すること
    • もっと調査したほうが良い、という意味っぽい

11. Table I/O and File I/O for Selects

• Table I/Oを調べるにはtable_io_waits_summary_by_index_usageが役に立つ
• 例えば、worldというdbからcityというテーブルの情報を見るには以下のクエリを叩く

SELECT
	OBJECT_TYPE,
	OBJECT_SCHEMA,
	OBJECT_NAME<
	INDEX_NAME,
	COUNT_STAR
FROM
	performance_schema.table_io_waits_summary_by_index_usage
WHERE
	OBJECT_SCHEMA = 'world'
	AND OBJECT_NAME = 'city'\G

• indexの一覧が得られて、COUNT_STARが小さければindexとして使われていないということ

英語

• insiginificant
  • 無意味な
    • significantの反対

12. Table I/O and File I/O for Updates and Deletes

• indexの使われないselect文を実行すると performance_schema.table_io_waits_summary_by_index_usage のCOUNT_STARTが跳ね上がる様子がわかる
• selectだけでなくupdate,deleteも同様の様子が見える。つまりupdateやdeleteも、データの場所を特定するためにselectと同様に読取りが発生する。
• クエリがどのくらい行をフェッチしてくるかは考えたほうがよい

13. The Error Summary Tables

• ロックタイムアウトやデッドロックが起きると待ち時間が発生するからクエリが遅くなる
• そのようなエラーは events_errors_summary_by_account_by_error などのテーブルで見ることができる

14. Conclusion

セクション2のまとめ。省略。