とある日

この記事はMySQL Advent Calendar 2023の18日目の記事です。

パフォーマンススキーマが素晴らしいので布教するための記事です。

パフォーマンススキーマとは

パフォーマンススキーマとは、MySQLサーバーの状態をモニタリングすることができます。

MySQL パフォーマンススキーマは低レベルで MySQL サーバーの実行をモニタリングするための機能です。

パフォーマンススキーマは、サーバーパフォーマンスに与える影響を最小にしながら、サーバー実行に関する有益な情報へのアクセスを提供することを目的としています。実装はこれらの設計目標に従います。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 27 MySQL パフォーマンススキーマ

特徴

パフォーマンススキーマは、実行時にサーバーの内部実行を検査する方法を提供します。それは、PERFORMANCE_SCHEMA ストレージエンジンおよび performance_schema データベースを使用して実装されます。パフォーマンススキーマは、主にパフォーマンスデータに焦点を合わせています。これは、メタデータの検査に使用される INFORMATION_SCHEMA と異なります。
- performance_schema データベースでパフォーマンスデータを見れます
パフォーマンススキーマはサーバーイベントをモニターします。「イベント」は、時間がかかり、タイミング情報を収集できるようにインストゥルメントされた、サーバーが実行するすべてのことです。一般に、イベントは、関数呼び出し、オペレーティングシステムの待機、解析やソートなどの SQL ステートメント実行のステージ、またはステートメント全体やステートメントのグループなどになります。イベント収集を使用すると、サーバーやいくつかのストレージエンジンの同期呼び出し (相互排他ロックなど) ファイルやテーブルの I/O,テーブルロックに関する情報にアクセスできます。
- イベント情報から様々な情報を拾うことができます
サーバーのモニタリングはごくわずかなオーバーヘッドで、継続的かつ目立たずに行われます。パフォーマンススキーマのアクティブ化によって、サーバーが使用不能になりません。
- モニタリングツール特有の最小のオーバーヘッドで利用できます
パフォーマンススキーマ内のテーブルは、永続的なディスク上のストレージを使用しないインメモリーテーブルです。コンテンツは、サーバーの起動時から再移入され、サーバーの停止時に破棄されます。
- 永続的にデータを閲覧するためにはそれようの仕組みが必要です

ユースケース

後述しますが、よく使うユースケースとしてはSQLのパフォーマンスモニタリングです。

MySQL :: MySQL 8.0 リファレンスマニュアル :: 27.12.4 パフォーマンススキーマ待機イベントテーブル

どのSQLがどこでボトルネックになっているのかを分析しています。

使い方

パフォーマンススキーマはデフォルトで有効になっています。それを明示的に有効または無効にするには、performance_schema 変数を適切な値に設定して、サーバーを起動します。たとえば、サーバー my.cnf ファイルで次の行を使用します:

[mysqld]
performance_schema=ON

MySQL :: MySQL 8.0 リファレンスマニュアル :: 27.1 パフォーマンススキーマクイックスタート

最初に、すべてのインストゥルメントとコンシューマが有効にされていないため、パフォーマンススキーマはすべてのイベントを収集しません。これらのすべてをオンにし、イベントタイミングを有効にするには、2 つのステートメントを実行します (行のカウントは MySQL バージョンによって異なることがあります)。

(root@localhost) [performance_schema] 8.0.33 > UPDATE performance_schema.setup_instruments
    ->        SET ENABLED = 'YES', TIMED = 'YES';
Query OK, 960 rows affected (0.00 sec)
Rows matched: 1245  Changed: 960  Warnings: 0

適当にSQLを実行します。

select s.emp_no, s.salary   from test.salaries s   where     s.from_date <= '2000-01-01' and '2000-01-01' < s.to_date   and s.emp_no in (select emp_no from test.employees where hire_date >= '1989-01-08') order by s.salary desc, s.emp_no   limit 10\G; 
select s.emp_no, s.salary   from test.salaries s ;

performance_schemaデータベースからイベントを確認します。

(root@localhost) [performance_schema] 8.0.33 > select EVENT_NAME, SOURCE, TIMER_WAIT from performance_schema.events_stages_history\G;
*************************** 1. row ***************************
EVENT_NAME: stage/sql/Opening tables
    SOURCE: sql_base.cc:5795
TIMER_WAIT: 25600000
*************************** 2. row ***************************
EVENT_NAME: stage/sql/init
    SOURCE: sql_select.cc:556
TIMER_WAIT: 1900000
*************************** 3. row ***************************
EVENT_NAME: stage/sql/System lock
    SOURCE: lock.cc:331
TIMER_WAIT: 4000000
*************************** 4. row ***************************
EVENT_NAME: stage/sql/optimizing
    SOURCE: sql_optimizer.cc:353
TIMER_WAIT: 1400000
*************************** 5. row ***************************
EVENT_NAME: stage/sql/statistics
    SOURCE: sql_optimizer.cc:693
TIMER_WAIT: 7600000
*************************** 6. row ***************************
EVENT_NAME: stage/sql/preparing
    SOURCE: sql_optimizer.cc:777
TIMER_WAIT: 9900000
*************************** 7. row ***************************
EVENT_NAME: stage/sql/freeing items
    SOURCE: sql_parse.cc:5405
TIMER_WAIT: 25900000
*************************** 8. row ***************************
EVENT_NAME: stage/sql/cleaning up
    SOURCE: sql_parse.cc:2484
TIMER_WAIT: 600000
*************************** 9. row ***************************
EVENT_NAME: stage/sql/starting
    SOURCE: init_net_server_extension.cc:110
TIMER_WAIT: 89800000
*************************** 10. row ***************************
EVENT_NAME: stage/sql/checking permissions
    SOURCE: sql_authorization.cc:2145
TIMER_WAIT: 5400000
10 rows in set (0.01 sec)