TiDB on AWS EKS 〜DMM動画のPoCレポート〜
はじめに
DMMグループ Advent Calendar 2021 12日目の記事になります。DMM.com動画配信事業部の杜志剛、後藤良彦、高木潤が担当します。
DMM動画ではAPI層のマイクロサービス化やFront層のWebコンポーネント化等様々な改善が進んでいる一方、まだデータベース層は改善方法を検討中の状態です。検討した中で今回は、TiDBでのDB改善のPoCについて共有します。
DMM動画のDBの課題
運用コスト
弊事業部のデータベース構成です。
機能分割のため、このような構成のクラスターが複数存在しています。オンプレで運用していることで、リソースの調達やネットワーク構成の管理など運用しづらい部分があります。運用コスト削減と環境の集約が期待できたので、NewSQLの利用を検討しました。
スケーラビリティと一貫性
ライブ配信やキャンペーンを行う際に、当初の想定以上のユーザーが来ると、DBへの接続数や書き込み量が増えます。DBのインスタンスでCPU負荷のスパイクや、レプリケーションの遅延などの現象が発生する可能性があります。特に購入周りでレプリケーションの遅延が発生すると、利用ユーザーから「購入したのに、購入済み画面に反映されない」などの問い合わせが来るかもしれないため好ましくありません。
NewSQL時代のDB技術選定
DB技術選定には色々選択肢があります。2021年現在は NewSQL 時代だと言われているため、時代遅れな技術を採用しないように注意しました。もう1つ限定条件として、今回のDB技術選定は既存システム(PHP+MySQL)の改善のため、RDB以外の技術は選択肢から外しました。
適切ではない技術を採用しない
NewSQL製品とは
WikipediaではNewSQLの定義 を3つの要素で説明しています。
- 関係データベースである
- 従来の関係データベースシステムのACID保証
- (NoSQLシステムのような) スケーラビリティ性
分かりづらいかもしれませんが、①と②は従来の関係データベースの特徴であり、③はNoSQLシステムの特徴です。下記の表を見たらイメージつけやすいでしょうか。
ー | MySQL シングルホスト | MySQLレプリケーションクラスター | MongoDB | NewSQL |
---|---|---|---|---|
関係データベース(SQL) | ✅ | ✅ | ❌ | ✅ |
ACID保証 | ✅ | ❌(結果整合性) | ❌(結果整合性に近い) | ✅ |
スケーラビリティ性 | ❌ | ❌(readのみ) | ✅ | ✅ |
NewSQL 技術とはどのような技術なのか、疑問が色々出てきました。
では、調べた結果をまとめていきます。
NewSQL 技術って本当に信頼できるか
新しい技術を採用する時に「新しすぎ」でリスクがあるという心配がありました。まず、NewSQL技術自体に関して「 What’s Really New with NewSQL?」という論文を読みました。すると、「NewSQLの技術成熟度は低くない」と受け取れる記載がありました。
また、Google Cloud Spanner と TiDB は既に多くの企業に使われているので、NewSQL技術は「新しすぎ」な技術ではないことがわかりました。
NewSQL は銀の弾丸であるか
NewSQL製品のNoSQL製品より良いところとして、SQLとACIDのサポートがあげられるため、「完璧でいいところばかりなのか」と思う方がいるかもしれません。しかし、完璧な設計は世の中に存在せず、「設計」が必ず Trade Off されます。つまり「これ」を選ぶと「あれ」が選ばれない状況は避けられません。
この点は、NewSQL製品でもあてはまり、「スケーラビリティ」か「レイテンシー」かどちらを選ぶか、Trade Offしたのです。「スケーラビリティ」を選ぶと、シングルサーバではなく分散システムで構成されることでサーバ間の通信が発生し、「レイテンシー」が高くなります。代わりに、「スケーラビリティ」の特性で「スループット」が無限に拡張できます。
ということで NewSQL は銀の弾丸とはいえません。TiDBを例に上げると、トランザクションのレイテンシーが1ms以上の値が許容できなければTiDBを使わないほうが良さそうです。
※ 勘違いされませんよう:従来の DB から TiDB になるとすべてのクエリのレイテンシーが長くなるわけではありません。1つのレコード結果のクエリや書き込みトランザクションの場合に、シングルサーバから分散システムになるため、通信分のレイテンシーは増加します。ですが、複数行の結果のクエリは複数のサーバで並行で処理される可能性があるため、従来のDBよりレイテンシーが短いかもしれません。更にデータ分析用クエリのシナリオなら、TiDBのカラムストア(TiFlash)を適切に使えば、クエリが大幅に改善される可能性が高いです。
NewSQL の製品をどう選ぶか
下記のNewSQL製品を調べてみました。
Feature | Amazon Aurora | Google Cloud Spanner | YugaByteDB | CockroachDB | TiDB |
---|---|---|---|---|---|
Elastic scalability (Both read and write) | ❌ | ✅ | ✅ | ✅ | ✅ |
Automated failover and high availability | ✅ | ✅ | ✅ | ✅ | ✅ |
Distributed ACID transactions | ✅ | ✅ | ✅ | ✅ | ✅ |
SQL compatibility and protocol | MySQL and PostgreSQL | Proprietary | PostgreSQL | PostgreSQL | MySQL |
Open Source License | ❌ | ❌ | Apache 2.0 | BSL and CCL | Apache 2.0 |
Open Source Contributor Count | ❌ | ❌ | 100+ | 300+ | 500+ |
HTAP | ❌ | ❌ | ❌ | ❌ | ✅ |
※ この表はPingCAP株式会社の ホームページ から
Amazon Aurora
Amazon Aurora の 論文によると、Auroraのストレージ層は分散式データシステムではありますが、計算ノード能力が従来の MySQL server と似ていて、シングルサーバのように pool buffer が ノードのメモリに制限されています。特に書き込みを担当しているノードがMasterノードのみなので、上の表の通り「Both read and write」のスケーラビリティ性がないと思います。
Google Cloud Spanner
Google Cloud Spanner はGoogleで開発されたNewSQL製品であり、スケーラビリティや実運用の経験も問題ありません。ただし、MySQL プロトコルはサポートされていません。今回の PoC は既存システム(PHP+MySQL)の改善のお試しのため、割愛します。新サービス開発なら有力な選択肢だと思います。
YugaByteDB & CockroachDB
YugaByteDB と CockroachDB は Google Cloud Spanner に似ているNewSQL製品のイメージです。スケーラビリティ面は問題がありません。ただし、PostgreSQL プロトコルのみサポートされているので、上記と同じ理由で今回 PoC に選びませんでした。
TiDB
TiDB は NewSQL の概念を超えた分散データベース製品です。NewSQLの製品はよくある特徴は、① 水平方向のスケーラビリティ、② 強力な一貫性(ACID保証)、③ 高可用性、④ 関係データベース 等です。TiDB は HTAP (Hybrid Transactional and Analytical Processing)という特徴も持ち、リアルタイム分析(ビッグデータ)の用途にも使えます。詳しく知りたい方はこばさんの こちらの文章をおすすめします。
弊事業部での今回の PoC の課題に対して、検証したいDB製品の「人物像」を下記のようにまとめたところ、TiDBはすべてあてはまりました。
TiDB PoC のゴール
弊事業部のシステムは大きいモノリシックシステムであり、ソースコードが多く存在し、複数のDBを利用しています。機能もDBも分離ができていない現状です。DBをまるごとTiDBに切り替えることは現実的に難しいため、チームで相談して、TiDBのPoCのゴールと期限を設定することになりました。
環境構築できること
TiDB の構築手段はいくつかありますが、今回 AWS EKS で構築して検証します。TiDB のドキュメントによると Production環境のハードウェア要件 が厳しそうなので、AWS での構築は問題がないかどうか試します。
検証対象機能は購入機能のみ
既存システムのすべて機能を検証することではなく、「購入」の部分のみを検証します。購入のフローに関するページは3つのみ(バスケット・購入・購入済みページ)で、これらのページに関する全てのSQL互換性とパフォーマンスを検証します。
スケーラビリティ・書き込みパフォーマンス
現状の本番環境の購入TPSの経験値が50
ぐらいなので、TiDBを使うと5倍
にスケーリングできるか、つまりTPS を250
に上げることができるかを検証します。また、TiDBのスケールアウト・スケールインの運用を検証するため、最初に最小限のサーバ数で構築し、スケールアウトの運用手段で簡単に台数を増やして、TPSを上げることが実現できるか、検証します。
懸念する課題
TiDB の導入にあたって下記の課題に対して調査します:
ということで、上記のゴール定義に対し、TiDB PoC の内容は下記のようになりました:
AWS EKS で TiDB を構築
まず、TiDB環境の構築です。弊事業部では開発環境をAWS上で構築しているため、TiDBも同じくAWS環境で構築します。TiDB の構築方法は主に2つです。① TiUPで vm にデプロイする ② tidb-operator で Kubenetes にデプロイする。AWS EKS を使うと、vm の管理は EKS node pool に任せられます。また、CloudNativeな技術を触りたいため、今回のPoCの構築は、AWS EKS + tidb-operator の方法になりました。
構成図はこちらです:
耐障害性のため、AWSの東京リージョンでマルチAZを使っています。
DBの安全性のため、使う側(アプリケーション側) のVPCとDBのVPCを完全に分離して、アプリケーションはVPC peering の形で DB VPC 側の 内部VLBに繋ぎます。また、DB VPCの中のサーバのほとんどは(インタネットゲートウェイ以外) Private subnet に置きます。
TiDB クラスターの内部には、いくつかのコンポーネントがあります:
それぞれのコンポーネントの特性が違うため、リソースの要求も違います。AWS EKS の node-group で分けて、最適なリソースを与えます。
Infrastructure as Code(IaC)を考慮していたため、下記のように実現しました。
Terraform で VPC / subnet を設定する
元々eksctl
が VPC と subnet を一括に作ってくれるので簡単ですが、 弊社のAWS VPC の IP Range は統一管理されており、VPC 自体は配られたものなので、eksctl の yaml に vpc id や subnet id を指定しないといけません。このような場合、EKS に必要となる subnet の自動構築ができなくなるため、Terraform で細かく subnet/route/nat を設定しないといけません。少し手間がかかります。
eksctlで AWS EKS cluster を構築する
TiDB のコンポーネントはいくつかあり、EKSにそれぞれのKubenetes Podになります。各コンポーネントはリソースの要件も違い、違うスペックのEKS Node Groupに置きたかったため、EKS Node Groupは下記のように計画しました。
EKS Node Group | TiDB コンポーネント | Spec 要件 |
---|---|---|
admin | 監視系 (grafana , prometheus) | 普通 |
pd | スケジューリング (pd-server) | SSDディスク |
tidb | コンピューティング (tidb-server) | メモリ、CPU、ネットワーク |
tidb | ストレージ (tikv-server) | メモリ、CPU、ネットワーク、SSDディスク |
どのように各コンポーネント(K8S Pod)を正しい EKS Node Group に配置したのかということですが、Kubenetesの「TaintとToleration」を利用することにしました。 それぞれの Node Group にtrintsを入れて、この後 tidb cluster の定義に各 Pod の toleration に配置先の Node Group のtrints を指定します。
元々 Terraform のみで IaC を実現しようとしましたが、その当時 terraform-provider-aws は trints がサポートされませんでした。そのために、公式の eksctl を選んで AWS EKS を構築しました。
※ 2021年12月現時点では terraform-provider-aws が trints サポートできるようになったよう です。
eksctl
の設定内容はyamlで定義されており、非常にシンプルです:
---apiVersion: eksctl.io/v1alpha5kind: ClusterConfigmetadata:name: tidb-clusterregion: ap-northeast-1vpc: # 既存VPCを使わない場合に不要id: "vpc-hoge" subnets:private: # a/c/d 3つの AZ...public: # a/c/d 3つの AZ...nodeGroups:- name: admin # 省略- name: pd # 省略- name: tidb # 省略- name: tikv instanceType: c5.4xlarge desiredCapacity: 3 labels:dedicated: tikv taints:dedicated: tikv:NoSchedule # tikv pod を tikv node group に配置する記法 ssh:allow: truepublicKeyPath: ./config/id\_rsa.pub privateNetworking: true
IP address が足りない問題の解決手段弊社の検証用 VPC の IP Range は /24 でした。AWS EKS CNI のデフォルト設定では IP addressの消費量が多いので、IP address が足りなくなりやすいです。しかし、下記の設定を変更すれば、問題が解決できます。
# 参考URL: https://docs.aws.amazon.com/eks/latest/userguide/cni-env-vars.htmlkubectl set env ds aws-node -n kube-system AWS_VPC_K8S_CNI_CUSTOM_NETWORK_CFG=truekubectl set env ds aws-node -n kube-system WARM_IP_TARGET=3# 確認kubectl describe daemonset aws-node -n kube-system | grep -A5 Environment
TiDB-operator で tidb cluster を構築する
シンプルな手順です。
確認手順
kubectl get svc の出力に${ClusterName}-tidb
というサービス TiDB のエントリポイントが出力です。EXTERNAL-IP は AWS VLB のドメインであり、アプリケーション向けの TiDB のエントリポイントになります。
既存の mysql cliを使って直接 TiDB に繋いて確認することがもきます (port-forwardが必要)。
kubectl port-forward -n ${Namespace} svc/${ClusterName}-tidb 4000 > /tmp/pf4000.out &mysql -h 127.0.0.1 -P 4000 -u rootmysql> select tidb_version()\G
Grafana
kubectl get svc の出力に cluster名-grafana というサービスは、Grafana です。ブラウザでアクセスができます(ユーザー名・パスワードは tidb-monitor.yaml で設定可能)。監視に有益なダッシュボードが既に整備されてある状態で、日常的な監視や問題発生時の調査の役に立ちます。
TiDB Dashboard
TiDB の PD サーバに TiDB Dashboard も用意されています。kubectl port-forward をすると監視画面にブラウザでアクセスができます。
kubectl port-forward svc/${ClusterName}-discovery -n ${Namespace} 10262:10262 &>/tmp/portforward-pd.log &
ブラウザで localhost:10262 にアクセスできます。多くの機能がありますが、PoCに役に立った機能は主に「 SQL Statements」 と 「Slow Queries」 です。
AWS EKS パフォーマンスの確認
TiDB のドキュメントによると Production環境のハードウェア要件 が厳しそうでした。
主な理由はTiDBのストレージエンジンがRocksDBであり、データ構造の LSM-tree で実装されているためです。LSM-treeは、「ランダム書き込み」が「シーケンシャル書き込み」になり、高速化できることが特徴ですが、この代わりに、読み込み・書き込みの量が多くなるそうです。原因としては下記です:
TiDBでは、RocksDB上にRaftに基づくMulti-Regionの技術で、冗長化の為のレプリカ数を設定可能となっており、デフォルトで3つとなっています。もし、冗長性を上げたい場合はレプリカ数を増やす事が可能です。容量に関しては、データ圧縮するアーキテクチャーが採用されている為、容量効率も通常DBよりも高い場合があります。デフォルトで3つのコピーを持っている為、シングルのDBに比べて容量を使いますが、レプリケーション等を考慮している設計の場合は、MySQLなどの製品であるフルクローンでなく分散の仕組みによりケースによっては非常に高い容量効率も見込めます。また、ネットワーク帯域は、十分なネットワーク帯域幅が要求されますが、hybernete regionという技術によりアクセスが少なくなっているデータレプリカの通信を減らす形をとっております。データ永続化の為には、他のDBと違う点は、実データ書き込み前にRaftLogという中間のデータ書き込みが発生しますので、その点のI/Oの考慮が必要となります。
公式サイトによると、特に I/O 周り、ネットワークには 10 Gbps の帯域幅が要求されて、TiKV サーバのディスクがPCI-E SSDが推奨されています。
上記の要件を合わせて、aws c5 シリーズの下記のサーバを選びました。
サーバの種類 | instanceType | 台数 | vCPU | Memory GB | ネットワーク帯域幅 |
---|---|---|---|---|---|
pd | c5.xlarge | 3 | 4 | 8 | 10GBps |
tikv | c5.4xlarge | 3 | 16 | 32 | 10GBps |
tidb | c5.4xlarge | 2 | 16 | 32 | 10GBps |
monitor | c5.2xlarge | 1 | 8 | 16 | 10GBps |
検討で決まったことはこちらです:
インスタンスのEBS I/O性能とネットワーク帯域幅
インスタンスの中で直接動くのではなく、Kubenetes Pod の中で動くため、AWS EBS が Kubenetes 永続ボリューム(PV) になり、ネットワークトラフィックも Kubenetes CNI になります。実際に性能の劣化現象があるかどうか、確かめました。この2つの点に対してAWS EKS でパフォーマンステストをした結果問題はありませんでした。
iperf3
というツールでc5.4xlarge インスタンス同士のネットワーク帯域幅をテストしたこれで、AWS EKS 自体のパフォーマンス確認はできました。
購入シミュレーションの実装
弊事業部のシステムは、webサーバ(Apache,PHP) + MySQL の構成になっています。今回主に TiDB の MySQL 互換性のこととスケーラビリティ性を検証したいため、webサーバ(Apache+ PHP) は現行のサービスと同じバージョンで試験します。検証対象は「ユーザーが商品を購入する機能」のみなので、この部分のコードだけをテストしたいのですが、全体の実装はモノリシックであって、購入部分のコードが複雑で綺麗に抽出できませんでした。
既存の購入処理を元にして下記の試験方法を開発しました:
これで既存の複雑なコードを弄らなくて良く、単純に MySQL general log playback を実装することになり、作業時間が短縮できました。DB検証の目的の視点で見ても問題ありませんでした。
パラメーター化した SQL General log は下記のイメージになります:
210527 10:58:22 48 Connectslave 48 Init DBslave_db 48 Query SELECT * from hoge WHERE `user_id` = '__USER_ID__' 49 Connectmaster 49 Init DBmaster_db 49 Query INSERT INTO fuga (user_id)VALUES ('__USER_ID__') 49 Query SELECT LAST_INSERT_ID() 49 PHPExec setParam('FUGA_ID', getLastResultOneValue()) 49 Query SELECT * fuga where id = '__FUGA_ID__'
元々購入処理は数本のHTTP通信で分けられますが、この方法で1つのURLにまとめることができて、パフォーマンステストする際に Apache Bench が簡単に利用できます。
パフォーマンステストとスケールアウト
本番規模の数億レコードのデータをテスト環境にインポートして、パフォーマンステストに準備しました。パフォーマンステストは、「購入負荷」と「ノイズ負荷」二種類を分けて並行にかける形です。理由は、「購入TPS」を正しく測定するため、DB の Read:Write の比を本番に近い状況を作ります。
スケールアウト
最初にtidb-server
が2
台で、tikv-server
が3
台で、購入TPSの限界値 が110
です。この数字は本番の MySQL の経験値を超えましたが、更に約2倍を実現したいため、スケールアウトをします。手順は非常にシンプルです。
まず EKS Nodegroup を スケールアウトします。
# tidb node 数 : 2 -> 7eksctl scale nodegroup --cluster=dig-ec-tidb-cluster --nodes=7 --nodes-min=7 --nodes-max=7 tidb# tidb node 数 : 3 -> 5eksctl scale nodegroup --cluster=dig-ec-tidb-cluster --nodes=5 --nodes-min=5 --nodes-max=5 tikv
そして、tidb-cluster.yaml を編集して、反映します。
tikv:baseImage: pingcap/tikv- replicas: 3+ replicas: 5tidb:baseImage: pingcap/tidb- replicas: 2+ replicas: 7
apply
kubectl apply -f config/tidb/tidb-cluster.yaml -n tidb-cluster
これでtidb-server
とtikv-server
の pod が増やされます。tidb-server
は Stateless 的なサーバであり、増やされたらすぐにサービスに投入されます。tikv-server
は永続ボリューム(PV) を持ているサーバなので、クラスターに追加されると、データが徐々にこのサーバに移行されます。 PDサーバがこのスケジューリングを担当します。Grafana の PD ダッシュボードに下記のグラフのように、Leader と Region が移動されている状況が見られます。
※ Region はデータセグメントです。同じデータセグメントはレプリカを3つ以上を持ち、Raft-Group になります。Leader は Raft-Group の Write/Read を担当するノードであり、他のノードは Follwer です。PDサーバは各 tikv-server に Region と Leader の量をバランス良く配置します。
tidb-server
が7
台、tikv-server
が5
台にスケールアウトして、購入TPS が目標値の250
に達成しました。スケールアウトのポイントはこちらです:
これでパフォーマンステストが終わりました。
スケールイン
パフォーマンステストが終わった後に、スケールインしてサーバを回収しました。スケールインの操作は少し複雑です。理由は2つです:
一台の tikv-server をスケールインする例を見てみます。まず停止したい tikv pod を選んで、 下記の手順で 1 podづつ繰り返します
# kubectl で 止めたい tikv pod 名を確認する# tidb cluster json の中に tikv pod 名から tikv の store id を調べるID=$(kubectl get tc ${CLUSTER_NAME} -n ${Namespace} -ojson | jq -r ".status.tikv.stores | .[] | select ( .podName == \"${POD_NAME}\" ) | .id")# pd-ctl で tikv ノードをオフラインするkubectl port-forward -n ${Namespace} svs/${CLUSTER_NAME}-pd 2379:2379pd-ctl store delete ${ID} # 安心してください。ここの削除は一気にデータを削除することではなく、データを別のノードに移行してから安全に削除するコマンドである# store 状態(state_name)が `Tombstone` 状態になるまで待つwatch pd-ctl store ${ID}# TiKV と PV の binding を解除# Pod の PesistentVolumeClaim を調べるPVC_NAME=$(kubectl get -n ${Namespace} pod ${POD_NAME} -ojson | jq -r '.spec.volumes | .[] | select (.name == "tikv") | .persistentVolumeClaim.claimName')echo $PVC_NAME# 削除 PesistentVolumeClaim:kubectl delete -n ${Namespace} pvc ${PVC_NAME} --wait=false# TiKV pod 自体を削除するkubectl delete -n ${Namespace} pod ${POD_NAME}
上記の手順で TiDB Cluster のオフライン作業が終わります。そして下記の手順で、AWS インスタンスを回収します:
懸念する課題の解決
auto increment id の課題
TiDB の使い方として、auto increment id は基本推奨していません。 TiDB に auto increment id を下手に使うと、2種類の問題が発生します。
業務ロジックの都合で auto increment id の利用が避けられない場合に、2021年12月現在 TiDB v5.0+ の対処法は簡単になりました。NONCLUSTERED
テーブル を使うのです。
CREATE TABLE t( id int PRIMARY KEY NONCLUSTERED AUTO_INCREMENT, /* NONCLUSTERED : テーブルのデータセグメントは、この id カラムと関係ない */ c int)AUTO_ID_CACHE=100/* 採番キャッシュを小さく */SHARD_ROW_ID_BITS = 4/* 内部 rowid に 4 bits のランダム bits を入れて、HOTSPOT 問題を避ける */PRE_SPLIT_REGIONS=3 /* 事前に テーブルのデータセグメントを 8つ に分ける (2^3) */;
我々はその当時 TiDB v4.x を使っていたので、上記の簡単な方法が使えませんでした。頑張って上記の方法と同じ効果に目的達成できましたが、もう古い知識になったので、割愛します。興味がある方 db tech showcase 2021 > TiDBではじめるNewSQL 既存MySQL環境からの移行検証 の講演を見てください。
データ移行の課題
TiDB の echosystem に いろんなツールが提供されています。データ移行ツールだけでも TiDB Lightning や TiDB Data Migration と Dumpling があります。TiDB から MySQL まで逆にデータ同期ツールも TiDB Binlog と TiCDC 2つがあります。それぞれツールがあるので迷いやすく、弊事業部の要件に適切な案はどれなのか、調査しました。また、一括に切り替えるか、dual-write の方法で SQL の互換性を十分に検証してから切り替えるか、大事なことなので決めなければなりません。
echosystem に色々データ移行ツールの使い分け方は、こちらです:
弊事業部の状況を合わせて検討した移行プランです:
移行の検証が PoC に含まれないため、あくまで検討の結果です。ご注意ください。
MySQL との互換性の課題
TiDB は MySQL プロトコルをサーポートします。PHP + MySQLのソースコードをそのまま MySQL エンドポイントから TiDB のエンドポイントに変更すると、ほどんど問題なく動くはずです。ただし、TiDB の MySQL 互換性は 100% ではありません。公式サイトによると互換性のない部分の まとめドキュメントがありますが、やはり 100% ではないとのことなので、機能をしっかりテストする必要があります。
今回の検証には、購入機能に関する SQL 文が約 140 件、その中に修正必要になる SQL 文は8つしかありません。それを3種類にまとめました:
NO_ZERO_DATE/NO_ZERO_IN_DATE sqlmode に関する SQL が 2 件
create table の時に使う default 値にDEFAULT '0000-00-00 00:00:00'
のような文法は、 MySQL 5.6.17 以降非推奨に なりました。 TiDB が MySQL 5.7 の互換性のため、同じく非推奨になっています。
ONLY_FULL_GROUP_BY sqlmode に関する SQL が 3 件
上記と似ている状況ですが、現行システムの MySQL @sql_mode にONLY_FULL_GROUP_BY
が入っていない、TiDB ではデフォルトで使う sqlmode です。
SLOW SQL に関する SQL が 3 件
パフォーマンステストがうまくいっていない時に、TiDB Dashboard の 「 SQL Statements」 と 「Slow Queries」 画面を見て Slow SQL に気づきました。3つの Slow SQL のパータンは同じでした。シンプルにまとめると、下記になります。
テーブル構造:
CREATE TABLE `orders` (`id` int(11) NOT NULL PRIMARY KEY NONCLUSTERED AUTO_INCREMENT,`user_id`varchar(20) NOT NULL,KEY `idx_user_id`(`user_id`)) AUTO_ID_CACHE=100 SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 ;CREATE TABLE `order_items` (`id` bigint(20) NOT NULL PRIMARY KEY NONCLUSTERED AUTO_INCREMENT,`price` int(11) NOT NULL,`order_id` int(11) NOT NULL,`state` enum('active', 'disabled') NOT NULL,KEY `idx_order_id`(`order_id`)) AUTO_ID_CACHE=100 SHARD_ROW_ID_BITS=4 PRE_SPLIT_REGIONS=3 ;
数分間かかる SELECT 文:
SELECT i.id, i.order_id, i.price FROM orders AS o, order_items AS iWHERE o.id = i.order_id and i.state = 'active' and o.user_id = '?' ORDER BY i.id DESC;
SQL の目的は親テーブルで user_id で絞って、親テーブルに属している子テーブルを出すものです。ただし子テーブルのデータをstate = 'active'
の条件で絞ります。実行計画は下記になります:
explain SELECT i.id, i.order_id, i.price FROM orders AS o, order_items AS iWHERE o.id = i.order_id and i.state = 'active' and o.user_id = '?' ORDER BY i.id DESC;+--------------------------------------+----------+-----------+-------------------------------------+------------------------------------------------------------------------+| id| estRows| task| access object| operator info |+--------------------------------------+----------+-----------+-------------------------------------+------------------------------------------------------------------------+| Sort_8 | 12.50 | root| | test.order_items.id:desc|| └─Projection_10 | 12.50 | root| | test.order_items.id, test.order_items.order_id, test.order_items.price ||└─HashJoin_40 | 12.50 | root| | inner join, equal:[eq(test.orders.id, test.order_items.order_id)]||├─TableReader_60(Build) | 10.00 | root| | data:Selection_59||│ └─Selection_59| 10.00 | cop[tikv] | | eq(test.order_items.state, "active")||│└─TableFullScan_58| 10000.00 | cop[tikv] | table:i| keep order:false, stats:pseudo||└─IndexLookUp_57(Probe) | 10.00 | root| ||| ├─IndexRangeScan_55(Build)| 10.00 | cop[tikv] | table:o, index:idx_user_id(user_id) | range:["?","?"], keep order:false, stats:pseudo|| └─TableRowIDScan_56(Probe)| 10.00 | cop[tikv] | table:o| keep order:false, stats:pseudo|+--------------------------------------+----------+-----------+-------------------------------------+------------------------------------------------------------------------+
実行計画の結果は、MySQL の explain 結果より見やすくなりました。特にid
列の部分がツリー構造になっていて、分散式的な実行ステップが一目瞭然です。 ちなみにtask
列に2種類の値があります。root
というのはtidb-server
で動くタスクで、cop[tikv]
はtikv-server
で動くタスクです。
この実行結果の意味は
table:o
)HashJoin_40
で操作して、上記の2つの中間結果をorders.id=order_items.order_id
の条件で join する問題点は、子テーブルのTableFullScan_58
とSelection_59
です。効率の早い方法は、 full scan ではなく、親テーブルのデータを先に集計して、order_id で子テーブルにデータを探すことです。
SQL Hint で HashJoin を IndexJoin に指定してもうまく行かなかったのですが、PingCAP 社さんのサポートで解決できました。解決の考え方は、子テーブルの条件i.state = 'active'
がなければ、親子テーブルの join が IndexJoin になるので、子テーブルのフィルター操作をcop[tikv]
からroot
(つまり tidb-server) に持てばいいです。子テーブルのi.state = 'active'
の条件を 親/子テーブルの列を両方使う条件( i.state = 'active' or o.user_id = '' )
に変換して、下記の実行計画になりました:
explain SELECT i.id, i.order_id, i.price FROM orders AS o, order_items AS iWHERE o.id = i.order_idand ( i.state = 'active' or o.user_id = '' )and o.user_id = '?' ORDER BY i.id DESC;+----------------------------------------+---------+-----------+-------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| id | estRows | task| access object| operator info |+----------------------------------------+---------+-----------+-------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Sort_8| 12.50| root| | test.order_items.id:desc|| └─Projection_10| 12.50| root| | test.order_items.id, test.order_items.order_id, test.order_items.price ||└─IndexJoin_15| 12.50| root| | inner join, inner:IndexLookUp_14, outer key:test.orders.id, inner key:test.order_items.order_id, equal cond:eq(test.orders.id, test.order_items.order_id), other cond:or(eq(test.order_items.state, "active"), eq(test.orders.user_id, "")) ||├─IndexLookUp_54(Build)| 10.00| root| |||│ ├─IndexRangeScan_52(Build) | 10.00| cop[tikv] | table:o, index:idx_user_id(user_id) | range:["?","?"], keep order:false, stats:pseudo||│ └─TableRowIDScan_53(Probe) | 10.00| cop[tikv] | table:o| keep order:false, stats:pseudo||└─IndexLookUp_14(Probe)| 1.25 | root| ||| ├─IndexRangeScan_12(Build) | 1.25 | cop[tikv] | table:i, index:order_id(order_id)| range: decided by [eq(test.order_items.order_id, test.orders.id)], keep order:false, stats:pseudo || └─TableRowIDScan_13(Probe) | 1.25 | cop[tikv] | table:i| keep order:false, stats:pseudo|+----------------------------------------+---------+-----------+-------------------------------------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
これで完璧に解決できました。
MySQL プロトコル & MySQL client
TiDB は MySQL 5.7+ の互換性を持つそうで、弊事業部の既存テスト環境では MySQL client も PHP + MySQL library も特に問題ありません。ソースコードやミドルウェア等は変更無しでいけました。
やりたかったこと
PoC は検証内容と期間を決めて無事に目標達成することができました。TiDB の他の色々な面白いところも検証したかったのですが、割愛しました。次回もっと検証する際には、下記のポイントを検証したいかなあと思っています。
まとめ
今回の TiDB PoC は、弊事業部のDB要件に対して、DB技術選定を行い、NewSQL製品の方向性に決まりました。NewSQL製品の中で、既存プログラムとの融和性が高い TiDB を選びました。そして、検証対象を絞り、AWS EKS での TiDB Cluster 構築と購入機能のパフォーマンステストを行いました。主に書き込みのスケーラビリティに対して検証し、既存システムの購入TPSが50
で、TiDB Cluster で5倍の目標が達成できました。ついでに、オンラインで迅速にスケールアウト・スケールインも検証できました。
最後に、PingCAP の方々、AWS の方々に、感謝を申し上げます。サポートありがとうございました。
DMMでは様々な技術改善が進んでいます。最新なDB技術の検証ができる職場で、他の技術の検証・選定ももちろん提案しやすいと思います。もしご興味がありましたら、ぜひ下記のポジションにご応募ください。
なお、PHPエンジニアの他にも新しいプロジェクトでGoのエンジニアも募集しています。