「ちゃんとやるだけ」大規模ECサイト（その６）
さて、前回の続きですが―――
開発環境ゆえに無く
稼働環境にゆえにあったもの
開発環境と稼働環境の違いとなると、やはり「トラフィック」です。
それゆえに「その２」で記述した「アクセラレータ」「クエリキャッシュ」などで、
WebサーバやDBへの負荷を軽減するワケですが、
今回の問題において根源にあったのは―――
「クエリキャッシュ」でした。
まず、そもそも「クエリキャッシュ」についての簡単な説明を致しますと。
（あくまで今回はMySQL5に限った話です）
—————————————————
例えば―――
select * dtb_products where product_id=1;
というクエリを実行します。
すると「実行したクエリ文字列（のハッシュ？）」をキーとして、
「そのクエリの実行結果」をキャッシュとして保存します。
生成されたキャッシュに格納されているクエリ実行結果は、
「そのクエリに関するテーブル」に対して、
insert,update,delete,alter等の更新が行なわれない限りは同じ結果になります。
ゆえに次回「全く同じクエリ」が行なわれた場合、
DBへのクエリ実行を行なうことなくキャッシュより出力結果を取得することで、
DBへのアクセス負荷を軽減。
その後、いずれかのテーブルに対し何らかの更新系クエリが実行された時、
「いずれかのテーブル」に関係するクエリキャッシュは全て削除する。
—————————————————
というのが「クエリキャッシュ」の概要です。
で、今回ボトルネックとなっていたのは「購入処理」の後の「商品一覧」でして。
素のEC-CUBEでは一覧で表示する商品を検索する際、
「vw_products_allclass」を閲覧してますがmysqlの場合DBのビューを使用しているのではなく
「vw_products_allclassを構成するクエリ」に変換した上で、クエリ実行を行なっています。
そして「vw_products_allclassを構成するクエリ」では、
「dt_products,dtb_products_class,dtb_category」を参照しているワケですが。
その中で「dtb_products_class」のカラム「stock」には「在庫数」が入ってます。
そして、その「在庫数」は商品購入が行なわれるたびに買った分だけ減算。
要するに「dtb_products_class」に対するupdateが実行されます。
つまり―――
購入処理が起こるたびに、
商品一覧に関する（dtb_products_classが絡む）キャッシュが、
全て削除され新たに作りなおし。
と、なってしまいます。
特に「商品一覧」はECサイト内で最も表示頻度の高いページかつ検索条件も多岐に渡り、
同時に「生成されるキャッシュの種類（＝クエリの種類）」も膨大な数になります。
キャッシュが消えた瞬間も同時に大量（むしろ多種）のアクセスがあり、
殺到するクエリ実行要求によるDBへの負荷が発生。
その時のDBサーバはCPU使用率95%以上が５分以上継続。
結果httpリクエストが滞りサイト全体が重くなってしまった…
といったところでしょうか。
開発環境でもクエリキャッシュON/OFFで確認したところ、
ようやく再現性が確認されました。
次に稼働環境でも検証してみようとしましたが、
「DB再起動」は何かと弊害が多い為、該当クエリに「SQL_NO_CACHE」を追記。
確かに「購入直後の負荷増加」は著しく緩和されたものの、
それ以外の負荷状況が平均的に上昇。（予想通りと言えば予想通り）
と、なると―――
・クエリキャッシュを使わなくて済むくらいクエリ最適化
・購入時のクエリ削除が影響しないよう商品一覧取得クエリの修正
って辺りが思いつくんですが緊急を要する内容の為、後者で対応。
対処の方向性は単純。
購入処理で「dtb_products_class」が更新されることでキャッシュがクリアされるのなら、
商品一覧取得処理のｸｴﾘにおいて「dtb_products」と「dtb_products_class」を分けてしまう。
検索条件の多くは「dtb_products」のカラムを参照しており、
「関連するクエリキャッシュの種類」に関わるのは「dtb_products」であって、
コチラが無事ならDBに対するクエリ実行要求の数は従来ほど多くならないのではないか。
そこまで判れば、あとは取得結果について、
「修正前との整合性」を確認しつつ作ってしまえば済む話。
で、実装してみましたところDBに対する負荷は、
以前は購入のたびにCPU使用率95%以上が数分間継続していたのが、
深夜のピーク時でもCPU使用率は60%程度。
それに伴いhttpリクエストの方も滞ることはなくなりましたとさ。
めでたしめでたし―――
と思っていたら、また１点。
オチから申し上げますと自分の凡ミスだったワケですが、
これも「ちゃんと」注意して作っておけば回避できた問題でした。
次回はまた「基本的な話」になります。
開発環境ゆえに無く
稼働環境にゆえにあったもの
開発環境と稼働環境の違いとなると、やはり「トラフィック」です。
それゆえに「その２」で記述した「アクセラレータ」「クエリキャッシュ」などで、
WebサーバやDBへの負荷を軽減するワケですが、
今回の問題において根源にあったのは―――
「クエリキャッシュ」でした。
まず、そもそも「クエリキャッシュ」についての簡単な説明を致しますと。
（あくまで今回はMySQL5に限った話です）
—————————————————
例えば―――
select * dtb_products where product_id=1;
というクエリを実行します。
すると「実行したクエリ文字列（のハッシュ？）」をキーとして、
「そのクエリの実行結果」をキャッシュとして保存します。
生成されたキャッシュに格納されているクエリ実行結果は、
「そのクエリに関するテーブル」に対して、
insert,update,delete,alter等の更新が行なわれない限りは同じ結果になります。
ゆえに次回「全く同じクエリ」が行なわれた場合、
DBへのクエリ実行を行なうことなくキャッシュより出力結果を取得することで、
DBへのアクセス負荷を軽減。
その後、いずれかのテーブルに対し何らかの更新系クエリが実行された時、
「いずれかのテーブル」に関係するクエリキャッシュは全て削除する。
—————————————————
というのが「クエリキャッシュ」の概要です。
で、今回ボトルネックとなっていたのは「購入処理」の後の「商品一覧」でして。
素のEC-CUBEでは一覧で表示する商品を検索する際、
「vw_products_allclass」を閲覧してますがmysqlの場合DBのビューを使用しているのではなく
「vw_products_allclassを構成するクエリ」に変換した上で、クエリ実行を行なっています。
そして「vw_products_allclassを構成するクエリ」では、
「dt_products,dtb_products_class,dtb_category」を参照しているワケですが。
その中で「dtb_products_class」のカラム「stock」には「在庫数」が入ってます。
そして、その「在庫数」は商品購入が行なわれるたびに買った分だけ減算。
要するに「dtb_products_class」に対するupdateが実行されます。
つまり―――
購入処理が起こるたびに、
商品一覧に関する（dtb_products_classが絡む）キャッシュが、
全て削除され新たに作りなおし。
と、なってしまいます。
特に「商品一覧」はECサイト内で最も表示頻度の高いページかつ検索条件も多岐に渡り、
同時に「生成されるキャッシュの種類（＝クエリの種類）」も膨大な数になります。
キャッシュが消えた瞬間も同時に大量（むしろ多種）のアクセスがあり、
殺到するクエリ実行要求によるDBへの負荷が発生。
その時のDBサーバはCPU使用率95%以上が５分以上継続。
結果httpリクエストが滞りサイト全体が重くなってしまった…
といったところでしょうか。
開発環境でもクエリキャッシュON/OFFで確認したところ、
ようやく再現性が確認されました。
次に稼働環境でも検証してみようとしましたが、
「DB再起動」は何かと弊害が多い為、該当クエリに「SQL_NO_CACHE」を追記。
確かに「購入直後の負荷増加」は著しく緩和されたものの、
それ以外の負荷状況が平均的に上昇。（予想通りと言えば予想通り）
と、なると―――
・クエリキャッシュを使わなくて済むくらいクエリ最適化
・購入時のクエリ削除が影響しないよう商品一覧取得クエリの修正
って辺りが思いつくんですが緊急を要する内容の為、後者で対応。
対処の方向性は単純。
購入処理で「dtb_products_class」が更新されることでキャッシュがクリアされるのなら、
商品一覧取得処理のｸｴﾘにおいて「dtb_products」と「dtb_products_class」を分けてしまう。
検索条件の多くは「dtb_products」のカラムを参照しており、
「関連するクエリキャッシュの種類」に関わるのは「dtb_products」であって、
コチラが無事ならDBに対するクエリ実行要求の数は従来ほど多くならないのではないか。
そこまで判れば、あとは取得結果について、
「修正前との整合性」を確認しつつ作ってしまえば済む話。
で、実装してみましたところDBに対する負荷は、
以前は購入のたびにCPU使用率95%以上が数分間継続していたのが、
深夜のピーク時でもCPU使用率は60%程度。
それに伴いhttpリクエストの方も滞ることはなくなりましたとさ。
めでたしめでたし―――
と思っていたら、また１点。
オチから申し上げますと自分の凡ミスだったワケですが、
これも「ちゃんと」注意して作っておけば回避できた問題でした。
次回はまた「基本的な話」になります。
