1TPS以下のAPIを400TPSから500TPSで動作するようにパフォーマンスチューニングをおこなったので、その際の方法についてメモ書きします。
要件と構成
APIで使用する言語はGo。
インフラはAWSのALB、ECS、RDS(Aurora MySQL)を利用。
APIの仕様は、実行したユーザーに商品券のシリアルコードを配るといったもの。
商品券のシリアルコードはDBの商品券テーブルに格納しており、APIのリクエスト毎に1件返す。
商品券の配布枚数は1日でXXX枚までといったように配布上限がある。
必要なインフラスペック
APIで200TPSを出すためには最低以下のスペックが必要。
-
RDS(Aurora MySQL)：db.r6g.xlargeのインスタンスが1つ
ECS：cpu: 512、memory: 1024のタスクがそれぞれ4つ
RDSについては可用性と負荷分散の観点から実際は2~3つのインスタンスを作成する。
TPSの観点だけで考えればインスタンスは1つで十分。
db.t4g.mediumだとバースト中は高TPSを実現できるが、すぐにクレジットが切れて200TPS以下の性能になるので実用的ではない。
ECSは1つのサービス内で4つのタスクを起動し負荷分散させる。
1つでも停止させると処理速度が下がる。
必要なチューニング
主に、APIのコード、DBのインデックスとSQLを見直すことで処理速度が向上する。
APIの場合はfor文で処理速度が落ちることが多く、アルゴリズムの見直しや並列処理の導入(goroutine)で処理速度が向上する。
ただ、DBの読み書きが多いAPIでは、殆どの場合DB周りの処理でボトルネックが発生していることが多い。
今回作成したAPIもDB周りのチューニングで処理速度大幅に向上したため、次からはDB周りのチューニングのポイントを記載します。
インデックス
基本ではありますが、きちんとインデックスを張っているかいないかではSELCTE文の実行速度に大きな差が発生します。
思ったようなパフォーマンスがでない時はまずインデックスの見直しを行いましょう。
たとえば次のような商品券テーブル(gift_certificates)があるとします。
商品券テーブル
列 型 コメント
-------------------------------------------------------------------------------
id bigint unsigned 連番
gift_certificate_code varchar(1024)
used_flag tinyint unsigned [0]
used_date varchar(8) NULL
索引
PRIMARY 対象カラム インデックス名前
-------------------------------------------------------------------------------
INDEX used_flag idx_used_flag
INDEX used_date idx_used_date
used_flagとused_dateを抽出条件にとする次のようなSQLではINDEXを付けると処理速度が向上します。
SELECT id FROM gift_certificates WHERE used_flag = True AND used_date = "20231227"
これでも処理速度は向上するのですが、まだ改善の余地があります。
上記SQLの実行計画を見てみましょう。
MySQLで実行計画を確認するにはSQL文の先頭にEXPLAINをつけて実行します。
EXPLAIN SELECT id FROM gift_certificates WHERE used_flag = True AND used_date = "20231227"
----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | gift_certificates | NULL | index_merge | idx_used_flag, idx_used_date | idx_used_flag, idx_used_date | 1 | | | | |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
実行計画を見ると、typeの項目がindex_mergeになっています。
index_mergeはSELECTの実行時に既存のINDEXをMySQL側で結合することによって発生します。
処理毎にINDEXの結合が発生するのは無駄なので、上記の商品券テーブルのインデックスは次のように修正します。
索引（修正前）
PRIMARY 対象カラム インデックス名前
-------------------------------------------------------------------------------
INDEX used_flag idx_used_flag
INDEX used_date idx_used_date
索引（修正後）
PRIMARY 対象カラム インデックス名前
-------------------------------------------------------------------------------
INDEX used_flag, used_date idx_used_flag_and_date
EXPLAIN SELECT id FROM gift_certificates WHERE used_flag = True AND used_date = "20231227"
----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| 1 | SIMPLE | gift_certificates | NULL | ref | idx_used_flag_and_date | idx_used_flag_and_date | 1 | | | | |
+----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
typeの項目がrefになっています。
refはインデックスは通常のインデックスを使った検索となるので、index_mergeより効率的です。
余談ですが、typeの項目がconstの場合はプライマリキーやユニークキーを利用して検索しているという意味になります。
更新系処理とインデックス
更新系のSQL（UPDATE、INSETE、DELETE）は一般的にインデックスが少ないほど処理速度が向上すると言われています。
あくまで私の体感ですがインデックスを多く定義しても1件レコードを更新するだけであれば3ms程度で処理が完了することが多いです。
インデックスを定義したことによって更新系のパフォーマンスが下がるよりも、参照系（SELECT）のパフォーマンスの恩恵の方が大きいので、インデックスは積極的に定義しても良いのかなと思っています。
ロックされたレコードにアクセスしないようにする
商品券テーブルはAPI実行ごとに在庫がある（used_flagカラムの値がFalse）ことを確認し、在庫があればそのリクエストに対して商品券のシリアルコード（gift_certificate_codeカラムの値）を返してused_flagカラムの値をTrueにします。
ここでは在庫があることを確認するSELECT文とused_flagとused_dateをTrueと処理日に更新するUPDATE分をトランザクション内で実行することになります。
処理としては次のイメージとなります。
# トランザクション開始
Beginx
# 在庫があることを確認
SELECT id FROM gift_certificates WHERE used_flag = False AND used_date = "20231227"
FOR UPDATE
# 上記で取得したidをひとつgiftCertificatesIDに格納する
# 在庫があればgift_certificatesテーブルの対象idレコードのused_flagとused_dateカラムの値を更新する
UPDATE gift_certificates
SET used_flag = 1 ,used_date = :today
WHERE id = :giftCertificatesID
# DBにデータを登録、SELECTで抽出した行のロック解除
Commit
FOR UPDATEはSELECTで参照した行に排他ロックをかけるSQL文です。
対象行に排他ロックがかかると他のトランザクションから参照も更新も受付なくなります。
詳しくはこちらにまとめています。
【MySQL 8.0】SQLのロック仕様について(行、テーブル、共有、排他ロック)
いまのSQLだと在庫のあるレコードを全てロックしてしまい、現在の処理がcommitされない限り、後続の処理が在庫のあるレコードを取得できません。
そこで次のようにSELECT文を修正します。
# 在庫があることを確認
SELECT id FROM gift_certificates WHERE used_flag = False AND used_date = "20231227"
LIMIT 1 FOR UPDATE
LIMIT 1を追加することで、在庫レコードを1件だけ抽出するので、抽出した行以外のレコードはロックされません。しかしこれではまだ不十分です。
APIへのリクエストが同時に複数件来た場合、最初のリクエストのトランザクションが終わる前に上記SELECTを実行することになるので、結局デッドロックが発生し処理速度は遅いままです。
それを回避するには、さらに次のようにSQLを修正します。
# 在庫があることを確認
SELECT id FROM gift_certificates WHERE used_flag = False AND used_date = "20231227"
LIMIT 1 FOR UPDATE SKIP LOCKED
SKIP LOCKED文を追加することによって、クエリーを実行した時にロックの獲得を待たず、すでにロックされている行は結果セットから削除するようになります。
これによって前のリクエストのcommitを待たずとも在庫のあるレコードを取得できるようになり、APIの処理速度が大幅に向上します。
countを使用しないようにする
countは件数が増えれば増えるほど遅くなる。
1000件程度なら1msでクエリが完了するが、インデックスを利用していても10万件ともなると約25msもかかる。
軽いAPIなら40~100msでレスポンスが返ってくるので、SQL一回で25msつらい。
10万件以上のデータをcountするとなると処理時間がさらに増えるのでパフォーマンス重視のAPIにはcountは利用しない方が良いでしょう。
しかし、APIの実行回数を数えたいなど、countを利用したくなる場合がある。
そういった場合は集計用のテーブルを新たに作成してそこにAPI実行ごとに1ずつ加算(UPDATE)しておけば良い。
UPDATE文の実行により3ms程行ロックがかかるが、それによってAPIのパフォーマンスが損なわれることはほとんどない。(500TPS以上は影響があるかも？)
正確な処理件数を集計するにはやはりcountが必要
「１日の商品券の配布枚数が1万件を超えていた場合、商品券のシリアルコードを発行しない」といった制御をしたい場合について。
集計用のテーブルにAPI実行件数を加算(UPDAET)して、その結果をSELECTして配布上限を確認したケースを考える。
その場合、同時に何百件もAPIが実行されているとSELECTで結果を確認するまえにAPI実行件数が配布上限を超えてしまいます。
なので、集計用テーブルに加算していった値を見るだけでは「配布枚数を超えていた場合、商品券のシリアルコードを発行しない」という制御ができません。
そこで次のように処理させることで、処理速度の犠牲を最小限にしつつ、正確に配布枚数上限を確認できるようにしました。
-
集計用テーブルが上限に達していない場合は集計用テーブルの値で配布枚数の上限を確認する
集計用テーブルが上限に達していた場合は商品券テーブルのused_flag=Trueとused_date="対象日"を条件に、排他ロックをかけた上でcountする
要するににハイブリット方式です。
集計用テーブルが上限に達していない間は行ロックやcountを行わないので高速で処理が行われます。
集計用テーブルが上限に達した数秒間に関しては正確な配布上限数を測定するため、行ロックとcountを行うため低速となります。
商品券テーブルを確認して上限に達していたこを確認できたら、集計用テーブルの対象日レコードに対して配布上限数に達したというフラグを立て、以後商品券テーブルの参照を行わないようにすれば高TPSを維持できます。
