🔖

MySQLパフォーマンス向上のための調査の道のり

2022/02/14に公開約6,100字

まずはDBのパフォーマンスを測定する。

「mysql パフォーマンス 計測」で検索して記事を探す。

https://thinkit.co.jp/article/9549
よさそうな記事を発見。

上記記事からEXPLAINステートメントについての記事を発見。

https://thinkit.co.jp/article/9658
問題のあるクエリが明確であればこれで良さそうだが、まだ不明確なため現時点では別の方法で調査した方が良さそう。

performance_schemaという機能を発見。検索してみる。

https://yoku0825.blogspot.com/2015/03/mysqlperformanceschema.html
events_statements_summary_by_digestが便利そう。

更に検索していくとperformance_schemaを使用したスロークエリー抽出のクエリを発見。

https://gist.github.com/matsuu/8dde4ba0eebbeefe9db1c308b343b765

アレンジしてクエリを実行してみる。

mysql> SELECT count_star AS cnt, sum_timer_wait AS sum, min_timer_wait AS min, avg_timer_wait AS avg, max_timer_wait AS max, sum_rows_sent AS sumRows, sum_rows_sent/count_star AS avfRows, schema_name AS db, digest_text AS query FROM events_statements_summary_by_digest WHERE count_star <> 0 ORDER BY sum DESC LIMIT 10;
+-----+-------------+-------------+-------------+-------------+---------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| cnt | sum         | min         | avg         | max         | sumRows | avfRows  | db                 | query                                                                                                                                                                                                                                                                       |
+-----+-------------+-------------+-------------+-------------+---------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
|   1 | 11869900000 | 11869900000 | 11869900000 | 11869900000 |     392 | 392.0000 | performance_schema | SELECT `EVENT_NAME` , `COUNT_STAR` , `SUM_TIMER_WAIT` , `AVG_TIMER_WAIT` FROM `events_waits_summary_global_by_event_name` WHERE `event_name` != ? ORDER BY `SUM_TIMER_WAIT` DESC                                                                                            |
|   2 | 11184200000 |  4511300000 |  5592100000 |  6672900000 |     220 | 110.0000 | performance_schema | SHOW TABLES                                                                                                                                                                                                                                                                 |
|   1 | 10265700000 | 10265700000 | 10265700000 | 10265700000 |       5 |   5.0000 | NULL               | SHOW SCHEMAS                                                                                                                                                                                                                                                                |
|   1 |  9283500000 |  9283500000 |  9283500000 |  9283500000 |      10 |  10.0000 | performance_schema | SELECT `EVENT_NAME` , `COUNT_STAR` , `SUM_TIMER_WAIT` , `AVG_TIMER_WAIT` FROM `events_waits_summary_global_by_event_name` WHERE `event_name` != ? ORDER BY `SUM_TIMER_WAIT` DESC LIMIT ?                                                                                    |
|   1 |  4214400000 |  4214400000 |  4214400000 |  4214400000 |       1 |   1.0000 | NULL               | SELECT @@`version_comment` LIMIT ?                                                                                                                                                                                                                                          |
|   1 |  2421900000 |  2421900000 |  2421900000 |  2421900000 |      10 |  10.0000 | performance_schema | SELECT `DIGEST_TEXT` , `COUNT_STAR` , `FIRST_SEEN` , `LAST_SEEN` FROM `events_statements_summary_by_digest` ORDER BY `COUNT_STAR` DESC LIMIT ?                                                                                                                              |
|   2 |  2184200000 |  1024800000 |  1092100000 |  1159400000 |       8 |   4.0000 | performance_schema | SELECT * FROM `events_statements_summary_by_digest` WHERE `count_star` != ? ORDER BY `sum_timer_wait` DESC LIMIT ?                                                                                                                                                          |
|   1 |  1897600000 |  1897600000 |  1897600000 |  1897600000 |       1 |   1.0000 | performance_schema | SELECT * FROM `events_stages_summary_by_account_by_event_name` WHERE `count_star` != ? ORDER BY `sum_timer_wait` DESC LIMIT ?                                                                                                                                               |
|   1 |  1806700000 |  1806700000 |  1806700000 |  1806700000 |       5 |   5.0000 | performance_schema | SELECT `count_star` , `sum_timer_wait` , `min_timer_wait` , `avg_timer_wait` , `max_timer_wait` , `sum_rows_sent` , `sum_rows_sent` , SCHEMA_NAME , `digest_text` FROM `events_statements_summary_by_digest` WHERE `count_star` != ? ORDER BY `sum_timer_wait` DESC LIMIT ? |
|   1 |  1602800000 |  1602800000 |  1602800000 |  1602800000 |       1 |   1.0000 | NULL               | SELECT SCHEMA ( )                                                                                                                                                                                                                                                           |
+-----+-------------+-------------+-------------+-------------+---------+----------+--------------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
10 rows in set (0.00 sec)

出てきたクエリの効率化を図るのが良さそう?

つづく。

Discussion

ログインするとコメントできます