🔖
MySQLパフォーマンス向上のための調査の道のり
まずはDBのパフォーマンスを測定する。
「mysql パフォーマンス 計測」で検索して記事を探す。 よさそうな記事を発見。
上記記事からEXPLAINステートメントについての記事を発見。 問題のあるクエリが明確であればこれで良さそうだが、まだ不明確なため現時点では別の方法で調査した方が良さそう。
performance_schemaという機能を発見。検索してみる。 events_statements_summary_by_digestが便利そう。
更に検索していくとperformance_schemaを使用したスロークエリー抽出のクエリを発見。
アレンジしてクエリを実行してみる。
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