Zenn
レバテック開発部
⛅

TiDBにおけるパフォーマンス検証の進め方とつまづきポイント

かわうそ
2024/03/11に公開
MySQL
負荷テスト
TiDB
パフォーマンステスト
tech

TL;DR

  • TiDBにおけるパフォーマンス検証をどうやって行ったか
  • パフォーマンス検証を行ったときにつまづいた問題とその対応策
  • TiDBの仕様やアーキテクチャなどの話はありません

前提

  • 対象のDBはAmazon Auroraで稼働中
    • DBエンジンはMySQL
  • TiDBに移行できないかPoCを実施
    • DB周りにいろんな課題があり、TiDBで解決できないか検証
  • TiDB Cloudで検証
    • 本番運用を想定してTiDB Dedicatedを利用

先にお伝えしたいこと

TiDB導入したいとか言う前に、今使っているRDBで発生しているスロークエリとかIndex設計を見直した方が良いです笑

理由はこの記事を見てもらえるとわかると思いますｗ

パフォーマンス検証の進め方

1. パフォーマンス検証に利用するクエリを洗い出す

観点としては以下の2つ

  • 実行される頻度が高いSQL
  • 実行速度が遅いSQL（スロークエリ）

APMも設定してあったので、Datadog（今はNew Relicだけど）から確認して対象のクエリを抽出しました。
また、1つのクエリだけだと比較不足にかけると判断して、頻度上位5つと速度下位5つのクエリを対象にしました。

補足として、実行速度が遅いSQL（スロークエリ）の特徴は以下の2つ

  • %keyword%のようなLIKEの中間一致が含まれている
  • Indexが貼られていないカラムに対してフルスキャンが走っている

2. TiDBのクラスターと比較検証に扱うAuroraのクラスターを作成

担当の方に相談して以下のスペックで比較することにしました。

  • TiDB
    • TiDB
      • 2 Nodes
      • 4 vCPU, 16 GiB
    • TiKV
      • 3 Nodes
      • 4 vCPU, 32 GiB
      • 500 GiB Storage
  • Aurora
    • db.t3.small
      • 2 vCPU, 2GiB

3. TiDBにデータを取り込む

TiDBのデフォルトの文字セットがutf8mb4になるので、スキーマ作成時に差分が起きないよう、対象DBの文字セットを事前に確認。

取り込む際は対象のDBからdumpファイルを取得し、TiDBにImportしました。

Importする際にTiDBにおいてMySQLでサポートされていない機能がないかチェックする

実際にImportする際に問題となったのは以下の2つ

  • ストアドプロシージャと関数
  • トリガー

結局、↑↑↑の機能は調査したら削除して問題なかったので、dump時に取り除きました。
（つまり、使われてなかったってことです笑）

4. パフォーマンス検証に利用するツールの選定

MySQLでパフォーマンス検証が実施できるのは以下のツールがありました（調べたら他にもあると思います）

今回は導入が簡単で実行もお手軽にできるmysqlslapを採用。

mysqlslapの準備にあたり参考にした記事は以下の２つです。

5. パフォーマンス評価する際の数値周りを整理

対象DBに紐づくサービスのSLI/SLOを参考にしました（一部抜粋）

  • 5分間で3000リクエストを全て200（OK）

ここから負荷をかける目安を以下に設定

  • シミュレートするクライアントの数：5
  • 実行するテストの回数：600

6. 実施

用意したSQLとか実施するmysqlslapのコマンドとかはGitHub上で管理。
環境変数とか簡単に切り替えられるようにしたかったので、結果的に以下のようにしました。

import-env
#!/bin/bash
for kv in $(< $1)
do
  if [[ "$kv" = ^\s*$ ]] || [[ "$kv" =~ ^# ]]; then
    continue
  fi
  export $kv
  echo $kv
done
mysqlslap-run
#!/bin/bash

. import-env .env

mysqlslap \
--user=$MYSQL_USER \
--password=$MYSQL_PASSWORD \
--host=$MYSQL_HOST \
--port=$MYSQL_PORT \
--engine=innodb \
--create-schema=xxxxx \
--concurrency=$CONCURRENCY \
--iterations=$ITERATIONS \
--query=performance-test.sql \
--delimiter=";"

つまづきポイント

全然パフォーマンスが出ない

実行される頻度が高いSQLはパフォーマンスに大きな差もなく問題もなかったですが、実行速度が遅いSQL（スロークエリ）に大きな差が出ました。

Aurora側における”実行される頻度が高いSQL”のベンチマーク

most-called-query-in-aurora
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.252 seconds
        Minimum number of seconds to run all queries: 0.203 seconds
        Maximum number of seconds to run all queries: 0.494 seconds
        Number of clients running queries: 5
        Average number of queries per client: 5

Aurora側における”実行速度が遅いSQL（スロークエリ）”のベンチマーク

slow-query-in-aurora
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.558 seconds
        Minimum number of seconds to run all queries: 0.511 seconds
        Maximum number of seconds to run all queries: 0.653 seconds
        Number of clients running queries: 5
        Average number of queries per client: 5

TiDB側における”実行される頻度が高いSQL”のベンチマーク

most-called-query-in-tidb
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 0.251 seconds
        Minimum number of seconds to run all queries: 0.209 seconds
        Maximum number of seconds to run all queries: 0.378 seconds
        Number of clients running queries: 5
        Average number of queries per client: 5

TiDB側における”実行速度が遅いSQL（スロークエリ）”のベンチマーク

slow-query-in-tidb
計測不能

なんと、TiDB側のベンチマークはもはや計測不能でした...笑
TiDB Cloud上で確認するとQuery Durationも平均15sぐらいになっていたので、これはさすがになにかがおかしいと思い、担当の人といろいろ相談。

まず、統計情報が使われていないのは？という仮説のもと、ANALYZEを設定してみる
※ データ入れた直後にクエリ流したりすると発生するが、時間おけば自動で実行されるみたいです

しかし、ANALYZEをかけてみるだけでは結果は変わらず...

次にもう少しいろいろ見てみると

  • スロークエリの実行計画を見ると、統計情報の読込が十分ではないことは確実
    • 統計情報の読込のタイムアウトを伸ばして、ちゃんと読み込めるようにしてみる
    • tidb_stats_load_sync_wait
  • %keyword%のようなLIKEの中間一致の場合、TiDBでは見積もり精度が悪く、適切な実行計画になりにくい
  • TiKVの負荷が高まったときにfollower nodeに処理を分散させるときの閾値を変更
    • この閾値はジョブの滞留時間で、この時間を短くすることで待機時間を減らし、なるべく早く処理を分散させることができる
    • tidb_load_based_replica_read_threshold

実際に以下のクエリを流してみました。

-- 統計情報のロードが十分に行えるようにタイムアウトを伸ばす（デフォルト値: 100）
SET GLOBAL tidb_stats_load_sync_wait = 200;
-- LIKEの中間一致の見積もり精度を高めるためにTopNを利用する（デフォルト値: 0.8）
SET GLOBAL tidb_default_string_match_selectivity = 0;
-- follower nodeに負荷を制限させる（デフォルト値: "1s"）
SET GLOBAL tidb_load_based_replica_read_threshold = '500ms';

すると、今度はこんなエラーが出てきました。

ERROR : runtime error: index out of range [69] with length 62

TopNの利用が良くない動作をしているようで、ひとまずtidb_default_string_match_selectivityをデフォルト値0.8に戻すことにしました。

改めて再度実行してみると結果は変わらずでした...なかなか厳しい戦い...

ここまでの事象から立てた仮説

  • EXPLAINの結果を見ると、たしかにテーブルのフルスキャンは結構発生している
    • （Indexの設計をちゃんとやろうぜはまた今度ｗ）
    • つまり、TiDBってフルスキャンの影響を結構受けやすいものなのかな？
  • じゃあ、なんでAurora MySQLではなんとかなっていたのか？
    • 読み取りのみのベンチマークだと、同じメモリに載ってる領域を複数のジョブから参照される可能性が高くなるから、そっちが性能に効いてくる可能性はある（はず）
    • まあ、インメモリにめっちゃキャッシュしてくれるってところが大きい
  • 逆に、なんでTiDBではなんとかならないのか？
    • メモリにキャッシュをあまりしない構造なので、フルスキャンの影響は大きい
      • Auroraでもメモリにのらないくらいデータが多い場合は、都度読むことになるので同じ事象になると考えられる
    • LIKEの中間一致だと最悪全テキストデータ検索なので、TiDBの方がちょい不利になることが多い

（改めて）全然パフォーマンスが出ない

上記を踏まえて、現在適応中のスペックではフルスキャンが発生したときに大きくボトルネックになってしまうはずなので、TiDBクラスターのスペックを以下に変更することにしました。

  • TiDB
    • TiDB
      • 2 Nodes
      • 4 vCPU, 16 GiB 8 vCPU, 16 GiB
    • TiKV
      • 3 Nodes
      • 4 vCPU, 32 GiB 8 vCPU, 64 GiB
      • 500 GiB Storage

上記のスペックで再度パフォーマンステストを行ってみると、、、

だめだぁぁぁ、、、メトリクスを確認するとQuery Durationの平均は4sぐらいで、さっきの結果よりは良くなってきていますが、まだまだこのパフォーマンスじゃ全然足りない、、、

もうここまで来たら、TiFlashを追加して、テーブルフルスキャンが発生した場合でも耐えられるようにしてみる。

※ ひとまず、スロークエリやIndex設計の見直しをせず移行できないか試してます笑

少なくともAuroraがインメモリでかなり頑張ってくれるDBであることは間違いないです笑

ということで、TiDBクラスターのスペックを以下のようにしました。

  • TiDB
    • TiDB
      • 2 Nodes
      • 4 vCPU, 16 GiB 8 vCPU, 16 GiB
    • TiKV
      • 3 Nodes
      • 4 vCPU, 32 GiB 8 vCPU, 64 GiB
      • 500 GiB Storage
    • TiFlash　← 追加
      • 1 Nodes
      • 8 vCPU, 64 GiB
      • 500 GiB Storage

次に、TiFlashを利用したいテーブルに対して、レプリケーションを設定（これをやらないとTiFlash側で計算してくれない）

ALTER TABLE <table名> SET TIFLASH REPLICA 1;

レプリケーションが終わったか確認する際には、以下を実行して、AVAILABLEが1になっていたらレプリケーションが完了。

SELECT * FROM information_schema.tiflash_replica WHERE TABLE_SCHEMA = '<db_name>' AND TABLE_NAME = '<table_name>';

※　今回は%keyword%のLIKEの中間一致やIndexが効いていないテーブルに対してレプリケーションを設定しました

すると、やっとベンチマークを計測することができました、、、笑

slow-query-in-tidb
Benchmark
        Running for engine innodb
        Average number of seconds to run all queries: 2.326 seconds
        Minimum number of seconds to run all queries: 2.142 seconds
        Maximum number of seconds to run all queries: 2.775 seconds
        Number of clients running queries: 5
        Average number of queries per client: 5

メトリクスはこんな感じでした。

特に、TiFlashのレプリケーションを設定したテーブルに対するクエリ（%keyword%のLIKEの中間一致）が2.8s -> 160msになったので、TiFlashの凄さに驚きました！

ただし、TiFlash本来の使い方とは異なるので、スロークエリを改善したりIndex設計を見直して、TiFlashに頼らないよう改善していきたいですね（お金というコストにも頼らないためにも）

（てか最初からIndex設計を見直せ）

あとはスロークエリを改善したりIndex設計を見直したり、TiDBの機能を駆使してチューニングすれば、パフォーマンスは改善できそうなので、この記事ではここまでにしておきます。
チューニング周りの話は、また別の機会に記事に書こうと思います。

ついでに、TiDB CloudにはTiDB ダッシュボードのスロークエリページがあるので、スロークエリの改善とかも進めやすいですね。実行計画とかも見やすくなっているのでIndex設計とかも見直しやすいです。

あと、Index Insight（Beta版）という機能もあり、Indexを効果的に使用していない遅いクエリに対してIndexの推奨を提供してくれる機能もあるので、この辺を上手く使えるとクエリのチューニングも捗りそうです。

余談：他にパフォーマンスを上げる方法

TiDBにはCoprocessor Cacheという機能があります。
TiDBがTiKVへ処理をpushdownした結果をTiDBにキャッシュすることで次回以降の処理を高速化する仕組みです。

その他にもCached Tableという機能もあります。
テーブル全体のデータがTiDBサーバーのメモリにロードされ、TiDBはTiKVにアクセスせずにメモリからテーブルデータを直接取得するため、読み取りパフォーマンスが向上する機能です。
テーブル容量が64MB未満でかつ、更新頻度が低ければ（書込みのQPSが10未満程度であれば）問題なく利用できるみたいです。

TiDBにはいろんな機能があってどうやってチューニングするか贅沢な悩みですね🤗

まとめ

  • パフォーマンス検証の進め方
    • 実行される頻度が高いSQLと実行速度が遅いSQL（スロークエリ）を利用
    • MySQLでパフォーマンス検証が実施できるツールとしてmysqlslapを採用
  • つまづきポイント
    • TiDB側で全然パフォーマンスが出なかった
      • テーブルのフルスキャンが発生すると一気にパフォーマンスが低下
      • %keyword%のLIKEの中間一致も同様
    • Auroraでなんとかなっていた理由
      • （多分）インメモリにめっちゃキャッシュしてくれるから
    • TiFlashを導入するとIndex設計を見直さなくてもなんとかなる
      • 約20倍ぐらいパフォーマンス上がった

さいごに

自戒の念を込めて、
TiDB導入するとか関係なく、まずは今発生しているスロークエリとかIndex設計を見直そう

レバテック開発部
レバテック開発部

レバテック開発部の公式テックブログです！

Discussion