データベースのパフォーマンス向上に必須！MariaDBのInformation Schema PROFILINGテーブルとsys.innodb_profiling_statsビュー

MariaDBのInformation Schema PROFILINGテーブルは、実行されたSQL文に関する詳細な情報を提供します。このテーブルは、データベースのパフォーマンスを分析し、ボトルネックを特定するのに役立ちます。

テーブル構造

PROFILINGテーブルは、以下の列で構成されています。

• CHECKSUM: 行の整合性を検証するために使用されるチェックサム。
• LINE: プロファイリングされた状態が実行されたソースコード行。
• FILE: プロファイリングされた状態が実行されたソースコードファイル。
• SWAPS: スワップが発生した回数。
• PAGE_FAULTS_MINOR: マイナーページフォルトの数。
• PAGE_FAULTS_MAJOR: メジャーページフォルトの数。
• MESSAGES_OUT: 送受信された通信メッセージの数。
• MESSAGES_IN: 送受信された通信メッセージの数。
• BLOCK_OPS_OUT: 書き込み操作の数。
• BLOCK_OPS_IN: 読み取り操作の数。
• CONTEXT_INVOLUNTARY: 非自発的なコンテキストスイッチの数。
• CONTEXT_VOLUNTARY: 自発的なコンテキストスイッチの数。
• CPU_SYSTEM: システムCPU使用時間。ミリ秒単位で測定されます。
• CPU_USER: ユーザーCPU使用時間。ミリ秒単位で測定されます。
• DURATION: 実行時間。ミリ秒単位で測定されます。
• STATE: プロファイリング状態。実行、待機、スリープなどの状態があります。
• SEQ: シーケンス番号。同じQUERY_IDを持つ行の表示順序を示します。
• QUERY_ID: 識別子。各SQL文に固有の番号が割り当てられます。

使用方法

PROFILINGテーブルを使用するには、まずprofilingセッション変数を1に設定する必要があります。これにより、MariaDBは実行されたすべてのSQL文を記録し始めます。

SET profiling = 1;

その後、PROFILINGテーブルをクエリして、実行されたSQL文に関する情報を取得できます。

SELECT * FROM information_schema.profiling;

特定のSQL文に関する情報のみを取得するには、WHERE句を使用できます。

SELECT * FROM information_schema.profiling
WHERE QUERY_ID = 12345;

例

次の例では、SELECT * FROM customersクエリの実行に関する情報を取得します。

SET profiling = 1;
SELECT * FROM customers;

SELECT * FROM information_schema.profiling
WHERE QUERY_ID = (
    SELECT LAST_INSERT_ID()
);

プログラミングでの活用

PROFILINGテーブルは、データベースアプリケーションのパフォーマンスを分析するプログラムで活用できます。例えば、次のことができます。

• ボトルネックを特定し、パフォーマンスを改善する
• 特定のSQL文がデータベースのパフォーマンスに与える影響を分析する
• 実行時間が長いSQL文を特定する

---

import mysql.connector

# データベースへの接続
db = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="database_name"
)

# profilingセッションを有効にする
cursor = db.cursor()
cursor.execute("SET profiling = 1;")
db.commit()

# サンプルアプリケーションを実行する
# ...

# 実行時間を1秒を超えるSQL文を取得する
cursor.execute("SELECT * FROM information_schema.profiling WHERE DURATION > 1000;")
for row in cursor:
    print(row)

# profilingセッションを無効にする
cursor.execute("SET profiling = 0;")
db.commit()

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

例2：特定のSQL文がデータベースのパフォーマンスに与える影響を分析する

次のプログラムは、SELECT * FROM customersクエリがデータベースのパフォーマンスに与える影響を分析します。

import mysql.connector
import time

# データベースへの接続
db = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="database_name"
)

# profilingセッションを有効にする
cursor = db.cursor()
cursor.execute("SET profiling = 1;")
db.commit()

# `SELECT * FROM customers`クエリを実行する
start_time = time.time()
cursor.execute("SELECT * FROM customers;")
end_time = time.time()

# クエリの実行時間を取得する
query_time = end_time - start_time

# クエリの実行に関する情報を取得する
cursor.execute("SELECT * FROM information_schema.profiling WHERE QUERY_ID = (SELECT LAST_INSERT_ID());")
for row in cursor:
    print(row)

# profilingセッションを無効にする
cursor.execute("SET profiling = 0;")
db.commit()

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

# クエリの実行時間とプロファイリング情報を表示する
print(f"Query time: {query_time} seconds")

例3：ボトルネックを特定し、パフォーマンスを改善する

次のプログラムは、ボトルネックを特定し、パフォーマンスを改善するためのヒントを提供します。

import mysql.connector

# データベースへの接続
db = mysql.connector.connect(
    host="localhost",
    user="username",
    password="password",
    database="database_name"
)

# profilingセッションを有効にする
cursor = db.cursor()
cursor.execute("SET profiling = 1;")
db.commit()

# サンプルアプリケーションを実行する
# ...

# 実行時間を1秒を超えるSQL文を取得する
cursor.execute("SELECT * FROM information_schema.profiling WHERE DURATION > 1000;")
for row in cursor:
    # 実行時間が長いSQL文を分析し、ボトルネックを特定する
    # ...

    # パフォーマンスを改善するためのヒントを提供する
    # ...

# profilingセッションを無効にする
cursor.execute("SET profiling = 0;")
db.commit()

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

• profilingはデータベースのパフォーマンスに影響を与える可能性があるため、本番環境で使用前に十分なテストを行ってください。

---

MySQL 8.0以降では、Information Schema PROFILINGテーブルは非推奨となり、将来のリリースで削除される予定です。代わりに、以下の代替方法が推奨されています。

• MySQL EXPLAINコマンド
• sys.innodb_profiling_statsビュー
• Performance Schema

代替方法の詳細

Performance Schema

Performance Schemaは、MySQLのパフォーマンスと診断に関する包括的な情報を提供するスキーマです。PROFILINGテーブルよりも詳細な情報を提供し、実行中のすべてのセッションに関する情報を取得できます。

Performance Schemaを使用するには、まずperformance_schemaエンジンを有効にする必要があります。

SET GLOBAL performance_schema = ON;

その後、次のクエリを使用して、実行されたSQL文に関する情報を取得できます。

SELECT * FROM performance_schema.events_statements_summary_by_digest;

sys.innodb_profiling_statsビュー

sys.innodb_profiling_statsビューは、InnoDBストレージエンジンによって実行されたSQL文に関する情報を提供します。PROFILINGテーブルよりも詳細な情報を提供し、実行された各操作に関する情報を取得できます。

sys.innodb_profiling_statsビューを使用するには、まずinnodb_profiling_historyテーブルを有効にする必要があります。

SET GLOBAL innodb_profiling_history = ON;

SELECT * FROM sys.innodb_profiling_stats;

MySQL EXPLAINコマンドは、特定のSQL文の実行計画を表示します。これは、クエリのボトルネックを特定するのに役立ちます。

EXPLAINコマンドを使用するには、次のクエリを実行します。

EXPLAIN <SQL文>;

例

次の例では、SELECT * FROM customersクエリの詳細な実行計画を表示します。

EXPLAIN SELECT * FROM customers;