MariaDBにおけるPerformance Schema：SQLステートメントと構造の深い考察

Performance Schemaは、80種類以上のテーブルで構成されています。これらのテーブルには、次の情報が含まれています。

メモリ使用量
データベースによって使用されるメモリの量に関する情報。これには、バッファプールの使用量、キーキャッシュの使用量、スレッドによって使用されるメモリの量などが含まれます。
スレッドの状態
各スレッドの状態に関する情報。これには、スレッドが実行中、待機中、またはスリープ中であるかどうかなどが含まれます。
SQLステートメントの実行
実行されたSQLステートメントに関する情報。これには、ステートメントのテキスト、実行時間、影響を受けた行数などが含まれます。

Performance Schemaのテーブルは、通常のSQLステートメントを使用してクエリできます。たとえば、次のクエリは、データベースで最後に実行された10件のSQLステートメントを表示します。

SELECT * FROM events_statements_summary_by_digest ORDER BY last_seen DESC LIMIT 10;

Performance Schemaは、MariaDBのパフォーマンスを監視するための強力なツールです。SQLステートメントの実行、スレッドの状態、メモリ使用量などに関する情報を収集できます。この情報は、問題を特定し、データベースのパフォーマンスをチューニングするために使用できます。

Performance SchemaとSQLステートメントと構造に関するプログラミング

Performance Schemaを使用して、SQLステートメントのパフォーマンスを監視および分析するプログラムを作成できます。たとえば、次のプログラムは、データベースで実行される実行時間が最も長い10件のSQLステートメントを特定します。

import MySQLdb

# データベースに接続する
db = MySQLdb.connect(host="localhost", user="username", password="password", database="database")

# events_statements_summary_by_digestテーブルからクエリを実行する
cursor = db.cursor()
cursor.execute("SELECT * FROM events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;")

# 結果をフェッチする
results = cursor.fetchall()

# 各結果を印刷する
for row in results:
    print("schema_name:", row[0])
    print("digest:", row[1])
    print("sum_timer_wait:", row[2])
    print("avg_timer_wait:", row[3])
    print("max_timer_wait:", row[4])
    print("sum_rows_affected:", row[5])
    print("avg_rows_affected:", row[6])
    print("min_rows_affected:", row[7])
    print("max_rows_affected:", row[8])
    print("sum_errors:", row[9])
    print("avg_errors:", row[10])
    print("min_errors:", row[11])
    print("max_errors:", row[12])
    print("first_seen:", row[13])
    print("last_seen:", row[14])

# データベース接続を閉じる
db.close()

このプログラムは、Performance Schemaのevents_statements_summary_by_digestテーブルを使用して、実行時間が最も長いSQLステートメントを特定します。このテーブルには、次の列が含まれています。

avg_errors:
sum_errors: ステートメントの実行中に発生したエラーの合計数
max_rows_affected: ステートメントによって影響を受けた行の最大数
min_rows_affected: ステートメントによって影響を受けた行の最小数
avg_rows_affected: ステートメントによって影響を受けた行の平均数
sum_rows_affected: ステートメントによって影響を受けた行の合計数
max_timer_wait: ステートメントの実行に費やされた最大時間
avg_timer_wait: ステートメントの実行に費やされた平均時間
sum_timer_wait: ステートメントの実行に費やされた合計時間
digest: ステートメントのダイジェスト
schema_name: ステートメントが実行されたスキーマの名前

import MySQLdb

# データベースに接続する
db = MySQLdb.connect(host="localhost", user="username", password="password", database="database")

# events_statements_summary_by_digestテーブルからクエリを実行する
cursor = db.cursor()
cursor.execute("SELECT * FROM events_statements_summary_by_digest ORDER BY sum_timer_wait DESC LIMIT 10;")

# 結果をフェッチする
results = cursor.fetchall()

# 各結果を印刷する
for row in results:
    print("スキーマ名:", row[0])
    print("ダイジェスト:", row[1])
    print("合計待機時間:", row[2])
    print("平均待機時間:", row[3])
    print("最大待機時間:", row[4])
    print("影響を受けた行の合計数:", row[5])
    print("影響を受けた行の平均数:", row[6])
    print("影響を受けた行の最小数:", row[7])
    print("影響を受けた行の最大数:", row[8])
    print("合計エラー数:", row[9])
    print("平均エラー数:", row[10])
    print("最小エラー数:", row[11])
    print("最大エラー数:", row[12])
    print("最初に検出された日時:", row[13])
    print("最後に検出された日時:", row[14])

# データベース接続を閉じる
db.close()

このコードは、以下のことを行います。

MySQLdbモジュールを使用して、Performance Schemaを有効にしたMariaDBデータベースへの接続を確立します。
events_statements_summary_by_digestテーブルに対してSQLクエリを実行します。このクエリは、sum_timer_wait列の降順で結果を並べ替え、上位10件の結果を返します。
クエリの結果をフェッチし、各行をループ処理します。
各行について、スキーマ名、ダイジェスト、合計待機時間、平均待機時間、最大待機時間、影響を受けた行の合計数、影響を受けた行の平均数、影響を受けた行の最小数、影響を受けた行の最大数、合計エラー数、平均エラー数、最小エラー数、最大エラー数、最初に検出された日時、最後に検出された日時を印刷します。
データベース接続を閉じます。

このコードを独自のニーズに合わせて変更できます。たとえば、特定のスキーマまたはステートメントダイジェストにフィルターを適用したり、結果を異なる順序で並べ替えたりできます。

SELECT * FROM events_statements_summary_by_digest WHERE digest = 'SELECT * FROM customers';

メトリクスとログ

MySQLおよびMariaDBは、どちらもデータベースのパフォーマンスに関するメトリクスとログを生成します。これらのメトリクスとログを使用して、パフォーマンスの問題を特定し、データベースを調整できます。

長所

すべてのバージョンのMySQLとMariaDBで利用可能
セットアップと使用が簡単

短所

問題の根本原因を特定するのが難しい場合がある
Performance Schemaほど詳細な情報が得られない

サードパーティ製のツール

データベースのパフォーマンスを監視するためのサードパーティ製のツールが多数あります。これらのツールは、Performance Schemaよりも多くの機能を提供する場合があり、複雑なデータベース環境を管理するのに役立ちます。

長所

複雑なデータベース環境を管理するのに役立つ
Performance Schemaよりも多くの機能を提供する場合がある

短所

セットアップと使用が複雑な場合がある
商用ライセンスが必要なものが多い

カスタムスクリプト

独自のスクリプトを作成して、データベースのパフォーマンスを監視することもできます。これは、特定のニーズに合わせて監視をカスタマイズしたい場合に役立ちます。

長所

特定のニーズに合わせて監視をカスタマイズできる

短所

データベースの専門知識が必要
開発とメンテナンスに時間がかかる

最適な代替手段は、ニーズによって異なります。 Performance Schemaは、多くの場合、データベースのパフォーマンスを監視するための優れた出発点となります。しかし、より多くの機能が必要な場合、または複雑なデータベース環境を管理している場合は、サードパーティ製のツールまたはカスタムスクリプトを検討する必要があります。

Performance Schemaと比較した代替手段の詳細

以下に、Performance Schemaと比較した代替手段の詳細をいくつか紹介します。

メトリクスとログ

MySQLおよびMariaDBは、次のメトリクスとログを生成します。

エラー
発生したエラーの数
クエリの実行時間
クエリの実行にかかる時間
I/O使用量
ディスクとネットワークで使用されるI/Oの量
メモリ使用量
データベースによって使用されるメモリの量
CPU使用率
CPUで使用されるプロセッサ時間の割合

これらのメトリクスとログは、SHOW GLOBAL STATUSステートメントまたはmysql_innodb_stat_monitorステートメントを使用して表示できます。また、mysqldサーバーログにも記録されます。

メトリクスとログを使用して、パフォーマンスの問題を特定できます。たとえば、CPU使用率が高い場合は、データベースサーバーが過負荷になっている可能性があります。メモリ使用量が多い場合は、インデックスを追加したり、クエリを最適化したりして、メモリ使用量を削減する必要がある可能性があります。

ただし、メトリクスとログは、Performance Schemaほど詳細な情報が得られない場合があります。たとえば、特定のクエリがパフォーマンスの問題を引き起こしている原因を特定することはできません。

サードパーティ製のツール

データベースのパフォーマンスを監視するためのサードパーティ製のツールが多数あります。これらのツールには、次の機能が含まれている場合があります。

容量計画
将来のニーズを予測するのに役立つ
レポート
パフォーマンスデータの生成
アラート
パフォーマンスの問題が発生したときに通知
ダッシュボード
パフォーマンスデータの可視化

人気のサードパーティ製ツールには、次のものがあります。

Percona Monitoring Server
dbForge Profiler
Quest Spotlight
SolarWinds Database Performance Monitor

サードパーティ製のツールは、Performance Schemaよりも多くの機能を提供する場合があります。また、複雑なデータベース環境を管理するのに役立ちます。ただし、商用ライセンスが必要なものが多いことに注意してください。また、セットアップと使用が複雑な場合もあります。

カスタムスクリプト

たとえば、特定のアプリケーションで使用されるクエリのパフォーマンスを追跡するスクリプトを作成できます。または、データベースのパフォーマンスに影響を与える可能性のあるシステムイベントを追跡するスクリプトを作成できます。

カスタムスクリプトは、特定のニーズに合わせて監視をカスタマイズする優れた方法ですが、開発とメンテナンスに時間がかかることに注意してください。また、データベースの専門知識が必要となります。