地方エンジニアの学習日記

興味ある技術の雑なメモだったりを書いてくブログ。たまに日記とガジェット紹介。

Performance InsightsとPerformance Schema

docs.aws.amazon.com

RDS for MySQLでも使えるPerformance InsightsだがPerformance Schemaを有効化している状態だとソースレベルの処理時間が取れないということがわかった。オンにしているとこんな感じでイベント単位の処理時間しか見えない。もちろんこれだけでも十分有用な値は取れているがPerformance Schema自体をオフに設定しておくことで低レベルの処理時間まで取得できるようになる。

え、オフでどうやってメトリクス取るの?って思ったがsystemの設定上はOFFにしているというだけでMySQLに接続するとONになっている。そもそもperformance_schema パラメータが 0 に設定されているかつソース は system に設定されている場合と言っているだけで実は0=OFFではないっぽい。インストゥルメントでNOになっているものを見ると少なくて結構取っているっぽい。p_sって結構メモリ食うからオフにしたいケースもあると思うんだけどその場合はPerformance_insightsごと切らないとダメっぽいな。そういうものなのか。

+----------------------------------------------------------------------------+---------+-------+------------+-------+------------+---------------+
| NAME                                                                       | ENABLED | TIMED | PROPERTIES | FLAGS | VOLATILITY | DOCUMENTATION |
+----------------------------------------------------------------------------+---------+-------+------------+-------+------------+---------------+
| stage/sql/After create                                                     | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/preparing for alter table                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/altering table                                                   | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/committing alter table to storage engine                         | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Changing master                                                  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Checking master version                                          | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/checking permissions                                             | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/cleaning up                                                      | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/closing tables                                                   | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Compressing gtid_executed table                                  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Connecting to master                                             | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/converting HEAP to ondisk                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/creating table                                                   | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Creating tmp table                                               | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/deleting from main table                                         | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/deleting from reference tables                                   | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/discard_or_import_tablespace                                     | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/end                                                              | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/executing                                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Execution of init_command                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/explaining                                                       | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Finished reading one binlog; switching to next binlog            | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Flushing relay log and master info repository.                   | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Flushing relay-log info file.                                    | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/freeing items                                                    | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/FULLTEXT initialization                                          | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/init                                                             | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Killing slave                                                    | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/logging slow query                                               | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Making temporary file (append) before replaying LOAD DATA INFILE | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/manage keys                                                      | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Master has sent all binlog to slave; waiting for more updates    | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Opening tables                                                   | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/optimizing                                                       | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/preparing                                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Purging old relay logs                                           | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/query end                                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Queueing master event to the relay log                           | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Reading event from the relay log                                 | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Registering slave on master                                      | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/removing tmp table                                               | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/rename                                                           | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/rename result table                                              | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Requesting binlog dump                                           | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Searching rows for update                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Sending binlog event to slave                                    | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/setup                                                            | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Slave has read all relay log; waiting for more updates           | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Reconnecting after a failed binlog dump request                  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Reconnecting after a failed master event read                    | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Reconnecting after a failed registration on master               | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for an event from Coordinator                            | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for slave workers to process their queues                | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for Slave Worker queue                                   | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting to reconnect after a failed binlog dump request          | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting to reconnect after a failed master event read            | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting to reconnect after a failed registration on master       | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for Slave Workers to free pending events                 | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for Slave Worker to release partition                    | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for workers to exit                                      | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting until MASTER_DELAY seconds after master executed event   | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/statistics                                                       | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/System lock                                                      | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/update                                                           | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/updating                                                         | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/updating main table                                              | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/updating reference tables                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/User sleep                                                       | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/verifying table                                                  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for GTID to be committed                                 | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/waiting for handler commit                                       | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for master to send event                                 | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for master update                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for the slave SQL thread to free enough relay log space  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for slave mutex on exit                                  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for slave thread to start                                | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for table flush                                          | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for the next event in relay log                          | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for the slave SQL thread to advance position             | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting to finalize termination                                  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for preceding transaction to commit                      | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for dependent transaction to commit                      | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Suspending                                                       | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/starting                                                         | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for no channel reference.                                | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Executing hook on transaction begin.                             | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for disk space                                           | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Compressing transaction changes.                                 | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Decompressing transaction changes.                               | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Fetching source member details from connected source             | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Updating fetched source member details on receiver               | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Wait before trying to fetch next membership changes from source  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Connection delegated to Group Replication                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for Binlog Group Commit ticket                           | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/mysys/Waiting for table level lock                                   | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting on empty queue                                           | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for next activation                                      | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for the scheduler to stop                                | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for global read lock                                     | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for backup lock                                          | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for tablespace metadata lock                             | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for schema metadata lock                                 | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for table metadata lock                                  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for stored function metadata lock                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for stored procedure metadata lock                       | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for trigger metadata lock                                | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for event metadata lock                                  | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for commit lock                                          | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/User lock                                                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for locking service lock                                 | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for spatial reference system lock                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for acl cache lock                                       | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for column statistics lock                               | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for resource groups metadata lock                        | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for foreign key metadata lock                            | NO      | NO    |            | NULL  |          0 | NULL          |
| stage/sql/Waiting for check constraint metadata lock                       | NO      | NO    |            | NULL  |          0 | NULL          |
+----------------------------------------------------------------------------+---------+-------+------------+-------+------------+---------------+