🐼

Generated Columnを使った話

2023/12/19に公開

MySQL

tech

シンプルフォームでエンジニアをやっている中山@h7kayama と申します。
本記事は SimpleForm Advent Calendar 2023 の 19 日目の記事です。

本日は、最近実施したパフォーマンス改善の話をしたいと思います。

背景

ある日、CS メンバから弊社サービスの管理画面が激重で業務に支障をきたしているという報告を受けました。

screenshot_01

前々から管理画面が重くなっているという事象は把握していたものの、なかなか優先度が上がらず後回しになっている状況でしたが、これはヤバいと思い、パフォーマンス改善に着手しました。

原因調査

NewRelic を確認したところ、特定のエンドポイントで数秒〜数十秒掛かっている状況でした。

screenshot_02

NewRelic 上で対象のエンドポイントのトレースを確認したところ、DB のアクセスで36秒も掛かっていました。
screenshot_03

対象のクエリのプロファイリングを確認したところ Sending data（読み込みと絞り込みに掛かっている時間）が支配的な状況であることが判明しました。

starting	0.000201
checking permissions	0.000007
checking permissions	0.000006
Opening tables	0.000023
init	0.000053
System lock	0.000039
optimizing	0.000018
statistics	0.003108
preparing	0.000029
Creating tmp table	0.000032
Sorting result	0.000008
executing	0.000003
Sending data	49.265025
Creating sort index	0.003125
end	0.000018
query end	0.000012
removing tmp table	0.000168
query end	0.000006
closing tables	0.000016
freeing items	0.000271
cleaned up	0.000023
logging slow query	0.000042
cleaning up	0.000024

対象のクエリには JSON 型のカラムが含まれており、その中には巨大な JSON の値が格納されていました。このカラムはインデックスも適用されていないため、データの読み込みにかなりの時間が掛かっていることが考えられます。

パフォーマンス改善

MySQL の Generated Column を導入しました。

Generated Column は、テーブルのカラム定義で宣言した式に従って値を自動で生成し、カラムとして扱えるようになる機能です。この機能を使うことで、JSON 構造のカラムの値から、特定のキーの値を抽出し、カラムとして扱う事ができ、インデックスも適用することができます。なお、宣言した式で参照している値が更新されると、自動で再計算されます。

Generated Column には、計算した値をカラムに保持する STORED カラムと、読み取り時に再計算する VIRTUAL カラムの2種類が存在します。
処理時間を少しでも短くしたかったこともあり、今回は STORED カラムを採用しました。

ただ、STORED カラムの DDL を実行する際の注意点として「同時 DML の許可」はサポートされていません。そのため、弊社の場合、メンテナンスを入れて対応しました。ちなみに、STORED カラムを13個追加したところ、約5時間程掛かりました...
screenshot_04

結果

Generated Column を使用するように対象のクエリを変更したことで、49秒程掛かっていた Sending Data が 0.0002秒まで改善することができました。

starting	0.000154
checking permissions	0.000006
checking permissions	0.000007
Opening tables	0.000106
init	0.000049
System lock	0.000013
optimizing	0.000021
statistics	0.000101
preparing	0.000022
Sorting result	0.000007
executing	0.000003
Sending data	0.000242
end	0.000005
query end	0.000007
closing tables	0.000023
freeing items	0.000217
cleaned up	0.000016
cleaning up	0.000005

さいごに

今回は Generated Column を使用してパフォーマンス改善を実施しました。
当初はテーブル設計を見直すことも視野に入れていましたが、 Generated Column を使用する事でお手軽にチューニングする事ができました。

弊社のサービスは、ローンチして1年半とまだ若いサービスではありますが、多くの企業様に導入して頂けていることもあり、データが日に日に増加している状況であり、まだまだパフォーマンス面の課題は残っています。また、弊社サービスの DB は MySQL 5.7 の AWS Aurora を使用しており、来年サポートが切れることもあり、MySQL 8.0 へアップグレードするか、もしくは TiDB への移行を検討しています。

もし、この辺りに興味あるエンジニアの方がいましたら、是非カジュアルにお話しさせてください！
SimpleFormカジュアル面談お申込みフォーム

まだまだシンプルフォーム株式会社のアドベントカレンダーは続きます。明日もお楽しみに！

背景

原因調査

パフォーマンス改善

結果

さいごに

Discussion