ISUCON本の内容をまとめてみた DB編

はじめに

この記事はisucon本の5章の内容をまとめたものになります。要点を抑えている(つもり)なので是非ご覧ください

DBのパフォーマンスチューニング方法

主に以下のことをします

• DBに負荷がかかっていることを確認する
• スロークエリログを解析
• クエリの確認
• クエリと実行計画の確認

DBに負荷がかかっていることを確認する

まずはそもそもDBがボトルネックになっているのかどうかを確認します。DBがボトルネックになっていないにも関わらずチューニングをしてもその効果が薄かったり逆効果であるためです。DBに負荷がかかっているかどうかを確認するにはtopコマンドを用います。

topコマンドの出力例

top - 15:32:45 up 10 days,  3:45,  2 users,  load average: 6.25, 5.87, 5.76
Tasks: 209 total,   2 running, 207 sleeping,   0 stopped,   0 zombie
%Cpu(s):  95.7 us,  2.3 sy,  0.0 ni,  2.0 id,  0.0 wa,  0.0 hi,  0.0 si,  0.0 st
MiB Mem :  32000.0 total,   1000.0 free,  25000.0 used,   6000.0 buff/cache
MiB Swap:   8000.0 total,   7000.0 free,   1000.0 used.   4000.0 avail Mem 

  PID USER      PR  NI    VIRT    RES    SHR S  %CPU  %MEM     TIME+ COMMAND
  1234 mysql     20   0   5000m  4500m   120m S   800   14.1 102:45.34 mysqld
  5678 root      20   0   4000m   500m   300m S    20    1.5   5:23.22 someprocess
  6789 apache    20   0   2000m   250m   150m S    15    0.8   2:12.11 httpd
  7890 user      20   0   1500m   300m   200m R    10    0.9   1:34.56 python

この例ではmysqlのCPUの使用率が95.7%、mysqlの使用率が800%となっています。(複数のCPUコアを持つ場合、CPU使用率は各コアの使用率の合計値と、100%が上限となる値があるので注意)
このことからmysqlに高負荷がかかっていることが確認できたのでDB周りのパフォーマンスチューニングに効果があることを期待できます。

スロークエリログを解析

スロークエリログとは、設定した閾値より実行にかかった時間が長くなったクエリを、かかった秒数や処理した行数とともに出力したログです。
(isucon本より引用)

このログをもとに高負荷がかかっているクエリを探し出します。
スロークエリログを出力させるにはDBの設定が必要なので注意してください。

pt-query-digest

スロークエリログの解析ツールの例としてpt-query-digestを紹介します。pt-query-digestはPercona Toolkitに含まれるツールの1つでMySQLの運用や監視、分析などに使うツールを集めたパッケージです。

# 1.1s user time, 10ms system time, 34.47M rss, 49.08M vsz
# Current date: Tue Dec  1 15:45:32 2024
# Hostname: db-server
# Files: /var/log/mysql-slow.log
# Overall: 10 total, 5 unique, 3.22 QPS, 0.45x concurrency _______
# Time range: 2024-12-01 15:00:00 to 2024-12-01 15:05:00
# Attribute    total     min       max       avg       95% stddev  median
# ============ ======= ======= ======== ======= ======= ======= =======
# Exec time   3.45s   200ms     500ms    345ms    400ms    50ms   350ms
# Lock time   100ms    10ms      20ms     15ms     18ms     2ms    15ms
# Rows sent   450k     100k      120k     110k     115k     5k    110k
# Rows examine 2.5M     500k      800k     700k     750k     50k   700k
# Query size  15k       1k        4k       2k       3k       0.5k  2k

# Profile
# Rank Query ID                       Response time Calls R/Call Apdx V/M
# ==== ============================== ============= ===== ====== ===== ====
#    1 0xBA8E8AC9A012376B09A00D0B...  1.45s         5     290ms  1.50 0.95
#    2 0xAD9C7D3B455453B378D5F39B...  1.00s         2     500ms  1.30 0.80
#    3 0x5AFD6B984234C9A123456DEF...  0.90s         3     300ms  1.10 0.70

# Query 1: 29% of total time
# Rank: 1 (Query ID: 0xBA8E8AC9A012376B09A00D0B...)
# Time range: 2024-12-01 15:00:00 to 2024-12-01 15:05:00
# Exec time 1.45s
# Lock time 30ms
# Rows sent 200k
# Rows examine 1.2M
# Query size 3k
# InnoDB buffer pool hit rate: 99%
# EXPLAIN for: SELECT * FROM orders WHERE user_id = ? AND status = ? LIMIT 100

# Query 2: 20% of total time
# Rank: 2 (Query ID: 0xAD9C7D3B455453B378D5F39B...)
# Time range: 2024-12-01 15:00:00 to 2024-12-01 15:05:00
# Exec time 1.00s
# Lock time 20ms
# Rows sent 150k
# Rows examine 800k
# Query size 2k
# EXPLAIN for: SELECT COUNT(*) FROM users WHERE status = 'active'

# Query 3: 18% of total time
# Rank: 3 (Query ID: 0x5AFD6B984234C9A123456DEF...)
# Time range: 2024-12-01 15:00:00 to 2024-12-01 15:05:00
# Exec time 0.90s
# Lock time 10ms
# Rows sent 100k
# Rows examine 500k
# Query size 1k
# EXPLAIN for: SELECT name, age FROM employees WHERE department = ?

出力の見方

• 全体統計
  • 実行時間 (Exec time)、ロック時間 (Lock time)、送信された行数 (Rows sent)、クエリサイズ (Query size) などが記載されます。
• クエリごとの詳細
  • Query ID: クエリをハッシュ化したID（セキュリティのため元のクエリを伏せる）。
  • Response time: クエリの応答時間。
  • Calls: 実行回数。
  • R/Call: 1回あたりの応答時間。
• EXPLAIN
  各クエリの実行計画（EXPLAINコマンド）が記載されます。

pt-query-digestでは似たクエリをまとめた上で高負荷なクエリを上から順に表示してくれます。EXPLAIN for: SELECT * FROM orders WHERE user_id = ? AND status = ? LIMIT 100のように実行計画を出力するコマンドも表示してくれます。このような高負荷がかかるクエリには一回のクエリの負荷が高いものと沢山呼び出された結果高負荷になっているものがそれぞれ表示されますが、その両方がチューニング対象です。

クエリと実行計画の確認

ここからは実際にコードからクエリを確認していきます。クエリを見ただけで問題点があきらかな場合はその場で修正してもいいですし、問題点が一見見たらない場合は実行計画を確認します。実行計画とはクエリがDB内において実際どのように処理されているのか確認できるものです。

SELECT * FROM orders WHERE user_id = ? AND status = ? LIMIT 100の実行計画例

+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key        | key_len | ref   | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | orders | NULL       | ref  | user_id_idx,  | user_id_idx| 8       | const | 1000 |    50.00 | Using where |
|    |             |        |            |      | status_idx    |            |         |       |      |          | Using index |
+----+-------------+--------+------------+------+---------------+------------+---------+-------+------+----------+-------------+

各カラムの説明

カラム名	説明
id	クエリ内での実行ステップの識別子。通常、単一クエリでは 1。複数テーブルの結合などではステップごとに番号が振られる。
select_type	クエリのタイプを表す。例: SIMPLE（単一テーブルのクエリ）、PRIMARY（サブクエリの外側）、SUBQUERY（サブクエリ）。
table	操作対象のテーブル名。
partitions	使用されたパーティション。非パーティショニングテーブルの場合は NULL が表示される。
type	テーブル結合のタイプ。効率を示す重要な指標で、以下の順で効率が良い: const > eq_ref > ref > range > index > ALL（フルスキャン）。
possible_keys	クエリで使用可能なインデックス。複数インデックスが候補になる場合、最適なものが選択される。
key	実際に使用されたインデックス。NULL が表示された場合はインデックスが使用されていないことを示す。
key_len	使用されたインデックスの長さ（バイト単位）。インデックスの効率を評価するための指標。
ref	インデックス検索で使用された値。例: const（定数値）、field1（別のカラム値）、NULL（値なし）。
rows	インデックスによってフィルタリングされた後、評価される行数の推定値。行数が多いほど負荷が高くなる可能性がある。
filtered	条件を満たすと推定される行の割合（%）。WHERE 条件が適用される割合を示す。値が小さいほど効率が良い。
Extra	実行計画に関する追加情報。例: Using where（インデックスで絞り込み後、さらに条件評価が行われる）、Using index（インデックスから直接データを取得）。

---

この場合だとuser_id = ? AND status = ?で検索をかけているのにも関わらず、user_idしかインデックスを用いていないので以下のようなインデックスの追加が考えられます。

ALTER TABLE orders ADD INDEX user_status_idx (user_id, status);

インデックスによる高速化

インデックスとは

辞書でいう索引のようなものです、これによってデータベースの中身が整理され、データの検索がしやすくなります。
例えばuserテーブルのidにインデックスを貼ったとしましょう。するとデータベース内には図のような木構造のインデックスが作成されます。

このようなインデックスがあることによってidを1から辿ることなく木の根から検索していくことでスムーズにデータを取得できます。

インデックスのデメリット

インデックスにも注意点があります。インデックスの作成、データの追加・更新があった場合のインデックス更新には、当然処理が増えるので増やしすぎるとかえって処理が重くなってしまうので注意が必要です。

N+1問題とその解消法

N+1問題とは

あるデータを取得するために
「1回のクエリ」と、関連するデータを取得するための「N回の追加クエリ」
が発生する問題のことです。
これによってクエリの実行回数が増加し、パフォーマンスに大きな影響が出てしまいます。

N+1問題のコード例(python)

# ユーザーを取得
users = db.execute("SELECT id, name FROM users")

# 各ユーザーの投稿を取得 (N回クエリが発生)
for user in users:
    posts = db.execute(f"SELECT content FROM posts WHERE user_id = {user['id']}")
    print(f"{user['name']}'s posts: {posts}")

joinを用いる

N+1問題の解消において最もベーシックな手法です。join句を用いるとテーブルを結合することができ、一回のクエリでデータ本体と関連するデータを取得できます。

SELECT users.id, users.name, posts.content
FROM users
INNER JOIN posts ON users.id = posts.user_id;

joinの方法にはINNER JOIN、LEFT JOINなど様々な結合方法があります。が、個人的には目的データと関連データが両方存在している場合のみ取得できるINNER JOINと目的データがあれば関連データがなくても取得できるLEFT JOINがあれば大体解決できると思います。

キャッシュを用いた回避

NoSQL(memcachedやRedisなど)を用いてキャッシュをしておくことによってN+1問題を回避できます。一度取得しておいたデータをキャッシュしておくことでその分のクエリ実行回数を解消できます。

キャッシュを用いた例

@app.route('/users-posts')
def users_posts():
    conn = get_db_connection()
    users = conn.execute('SELECT id, name FROM users').fetchall()

    result = []
    for user in users:
        cache_key = f"user:{user['id']}:posts"
        # Redisキャッシュから取得
        cached_posts = redis_client.get(cache_key)

        if cached_posts:
            # キャッシュヒット時
            posts = json.loads(cached_posts)
        else:
            # キャッシュミス時: データベースから取得
            posts = conn.execute('SELECT content FROM posts WHERE user_id = ?', (user['id'],)).fetchall()
            posts = [post['content'] for post in posts]
            # キャッシュに保存
            redis_client.set(cache_key, json.dumps(posts), ex=3600)  # 有効期限: 3600秒

        result.append({
            'user': user['name'],
            'posts': posts
        })

    conn.close()
    return result

別クエリによるプリロードを用いたN+1の解決

in句を用いることによってクエリ回数を抑えることもできます。取得データのidを配列等でまとめてin句に入れることで2回のクエリで関連データを含めた取得ができます。

別クエリによるプリロードを用いたN+1の解決

# ユーザーを取得
users = db.execute("SELECT id, name FROM users")

# 全ユーザーの投稿を一括取得
user_ids = [user['id'] for user in users]
posts = db.execute(f"SELECT user_id, content FROM posts WHERE user_id IN ({','.join(map(str, user_ids))})")

この方法の利点としてはjoinより手軽に実現できることです。なので最初の高速化ステップとして最適です。
ただし、 IN句に渡す値の数が多過ぎると、クエリのサイズが大きくなり過ぎてエラーになったり、狙ったインデックスが使われなかったりすることに注意してください。

正規化をわざと崩す

JOINしたいものもふくめて一つのテーブルにすることによってもN+1問題は回避できます。
当然データが冗長になり、更新時のコストが高くなってしまいますので使用には注意が必要です。ですが高速化の目的がはっきりしている場面では利用可能な手法です。

N+1問題はデータベースだけに限った話ではない

N+1問題はデータベースだけに限った話ではありません。というのもAPIを用いて外部サービスにアクセスする場合にもN+1問題が生じます。

APIにおけるN+1問題の例

def fetch_user_data():
    # すべてのユーザーを取得するAPI
    users = requests.get(USER_API).json()

    result = []
    for user in users:  # N回外部APIを呼び出す
        # 各ユーザーの購入履歴を取得するAPI
        purchases = requests.get(PURCHASE_API.format(user['id'])).json()
        result.append({
            "user": user['name'],
            "purchases": purchases
        })

    return result

Webサービスのパフォーマンスに影響するようであれば、必要な情報を一括で取得するAPIを用意し、活用するなどの対策が必要です

一括で取得できるAPIを用いた場合

def fetch_user_data():
    # すべてのユーザーを取得
    users = requests.get(USER_API).json()

    # ユーザーIDをまとめて取得
    user_ids = [user['id'] for user in users]

    # バルクAPIで購入履歴を一括取得
    purchases = requests.post(BULK_PURCHASE_API, json={"user_ids": user_ids}).json()

    # 購入履歴をユーザーにマッピング
    purchases_by_user = {p['user_id']: p['items'] for p in purchases}

    result = []
    for user in users:
        result.append({
            "user": user['name'],
            "purchases": purchases_by_user.get(user['id'], [])
        })

    return result

インデックスとjoinにヒントを与える

インデックスやjoinを設定しておけば高速化できるケースが大半ですが中にはそうでもないケースもあります。というのもインデックスやjoinする順序が開発者の想定通りでない場合です。
例えば以下の例ではordersデーブルにcustomersをjoinすることを想定しています

うまくjoinをしてくれないクエリ

SELECT o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed';

想定と異なる実行計画

+----+-------------+-----------+------------+------+---------------+------------+---------+------------------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | customers | NULL       | ALL  | PRIMARY       | NULL       | NULL    | NULL             | 1000 |   100.00 | Using where |
|  1 | SIMPLE      | orders    | NULL       | ref  | status_idx    | status_idx | 10      | customers.customer_id | 100  |   50.00 | Using index |
+----+-------------+-----------+------------+------+---------------+------------+---------+------------------+------+----------+-------------+

ところが実行計画を確認すると先にcustomersテーブルが先にフルスキャンされている上に後からordersテーブルのインデックスでフィルタリングされており、うまくjoinが作用していません。

通常用いるインデックスやjoinの順番はDBが適切なものを選択してくれます。しかし実行計画を確認してみるとこちらの想定していないjoinやindexの使い方をしていて処理が思ったようにいかない場合があります。
その場合に使えるのがFORCE INDEXとSTRAIGHT_JOINです。

FORCE INDEX

FORCE INDEXは用いるインデックスを強制することができます

FORCE INDEXを用いる例

SELECT o.order_id, c.customer_name
FROM orders o FORCE INDEX (status_idx)
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed';

FORCE INDEXを用いた実行計画

+----+-------------+-----------+------------+------+---------------+------------+---------+------------------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | orders    | NULL       | ref  | status_idx    | status_idx | 10      | const            | 100  |   50.00 | Using index |
|  1 | SIMPLE      | customers | NULL       | eq_ref| PRIMARY       | PRIMARY    | 10      | orders.customer_id | 1   |  100.00 | NULL        |
+----+-------------+-----------+------------+------+---------------+------------+---------+------------------+------+----------+-------------+

インデックスを強制したことによってjoinの順番も変わり、高速化できました。

STRAIGHT_JOIN

STRAIGHT_JOINはINNER JOINにおいてjoinの順序を強制することができます

STRAIGHT_JOINを用いる例

SELECT STRAIGHT_JOIN o.order_id, c.customer_name
FROM orders o
JOIN customers c ON o.customer_id = c.customer_id
WHERE o.status = 'completed';

STRAIGHT_JOINを用いた実行計画

+----+-------------+-----------+------------+------+---------------+------------+---------+------------------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key        | key_len | ref              | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------------+---------+------------------+------+----------+-------------+
|  1 | SIMPLE      | orders    | NULL       | ref  | status_idx    | status_idx | 10      | const            | 100  |   50.00 | Using index |
|  1 | SIMPLE      | customers | NULL       | eq_ref| PRIMARY       | PRIMARY    | 10      | orders.customer_id | 1   |  100.00 | NULL        |
+----+-------------+-----------+------------+------+---------------+------------+---------+------------------+------+----------+-------------+

先にordersテーブルを参照することによって高速化できました。

SELECTで必要なカラムだけを指定する

SELECTを書くときはついつい*を使いがちですが、、、

SELECT * FROM USER;

以下のように必要なカラムだけを抽出することでもパフォーマンスを向上させることができます。特に画像など重たいデータのあるカラムがある場合は要注意です。

SELECT firts_name,last_name FROM USER;

プリペアドステートメント

プリペアドステートメントとはクエリにおいて共通部分を抽出しておいて、異なる箇所だけ入れ替えることで高速に命令分を生成できる仕組みのことです。キャッシュにプリペアドステートメントを保存することによって処理を高速化するのが目的です。

プリペアドステートメントの例

-- :name はプレースホルダ
SELECT name FROM user WHERE name = :name;

しかしこれがパフォーマンス低下の原因となることもあります。webアプリケーションにおいてはクエリ数も多く、このプリペアドステートメントがうまく活用されず、その結果プリペアドステートメント作成と解放の処理の方が重くなってしまいます。設定によって無効化できるので必要に応じて無効化しましょう。

DBの最大同時接続数の設定

データベースへの接続はTCPやUnix domain socketを介して行われることが多いです。TCPの接続はコストの高い処理なのでいちいち接続していると負荷が増してしまいます。そこで一度接続したコネクションを使い回すことを考えます。
データベースでは最大同時接続数を設定できます。この場合アクティブな接続とアイドル状態(使っていない状態)の最大接続数をそれぞれ調整できます。この値も多ければ多いほどいいというわけではなく適切な値があるのでチューニングしましょう。

まとめ

以上がisucon本5章まとめです。チューニングに必須な項目を要約したので是非ご活用ください！
その他の章の記事はこちらから
ISUCON本の内容をまとめてみた DB編
ISUCON本の内容をまとめてみた リバースプロキシ nginx編
ISUCON本の内容をまとめてみた キャッシュ編
ISUCON本の内容をまとめてみた 高速化に必要なその他技術編
ISUCON本の内容をまとめてみた OS Linux編