1. はじめに
こんにちは、クマ老師と申します。お仕事としては、ソフトウェアエンジニアをやってきました！
今回は、約10年間続けてきたITエンジニアとしての経験の中で、きわめて多くつまづいてきた、リレーショナルデータベースへのアクセス周りの注意点についてまとめてみたいと思います！
自分の経験に立ってというお題なので、ソフトウェアエンジニアとして自分から見たときの視点となります。
もし、他にこんなことに気をつけた方がいいよ！という方がいらっしゃいましたら、ぜひ、教えてください。
目次
- 1. はじめに
- 2. 執筆動機
- 3. データベース自体やクライアント側の設定周り
- 4. SQLについて
- 5. ソフトウェアの挙動
- 6. システム構成
- 7. 問題が起こっちゃったときの対応策
- 8. 補足：昔本当におこったシステム障害について
- 9. おわりに
2. 執筆動機
twitterで、大量データ取得処理が走っただけでシステムが死ぬのかという話になり、
場合によっては、死ぬこともあるかもという話になりました。
そこで、今まで出会った経験から、気をつけておくべきことをまとめようと思いました。
基本的には、以下のような構成についてのお話です。
- 業務でトランザクション処理を日々行っているリレーショナルデータベースへのアクセスである
- そこで使用しているデータに対してアクセスしたい
- 一回のSQL文で複数のテーブルや複数のデータに対してアクセスしたい
以下、カテゴリー別に述べていきます。網羅的なので、詳しいお話をここで展開することができません。参考にしたサイトを張っておきますので、リンク先などを補足でご覧になってください。
3. データベース自体やクライアント側の設定周り
いきなりなのですが、ここが一番重要だったりします。ポイントは、以下三つです。
- データベース何をつかっているのか？
- データベースによっては探索時にレコード更新を止めてしまうものがある
- データベース自体の性能
- メモリ、CPUの空きは十分か？
- そもそもコネクション張れる空きがあるのか？
- クライアント側とデータベースのセッションタイムアウト設定について
- トランザクション分離レベル
3.1. データベース何を使っているのか？
ここで、一番必要な知識は、共有ロックです。共有ロックとは何かというと、下のような内容をご確認ください。
簡単に言ってしまいますと、SELECT文を投げて、データを探索しているときにもロックがかかることがあるよ、というお話です。これは SELECT FOR UPDATE でかける明示的ロックのお話ではなく、ごく普通のSELECT文でかかるロックがあります。
多くのRDBでは、DROP TABLEなどだけを止めるのですが、一部のRDBでは、レコードのUPDATE系にもかかる場合があります。代表的な例がSQLServerです。
SQL Serverのロックについて出来る限り分かりやすく解説
ですので、RDBとして何をつかっていて、SELECT文でどのくらいロックをかけてしまうかを知ることが重要になります。
3.2 データベース自体の性能について
まずは、メモリやCPUへの影響です。特に気にするのはメモリですかね？
SELECT文の結果をキャッシュしてしまうと、メモリ上に展開されると思うので。このあたり、バッチ処理が大量データ取得がエクスポートだけだったりすると、メモリを大きくあけてないかもしれないとか不安になりますよね。
詳しくは担当のデータベースエンジニアの人に訊きに行くと思います。
また、そもそもなのですが、コネクション最大数というのがございまして、ここにあまりがないとアクセスできなくて詰まります。意外と見落としがち。実は空きが足りていなかったというのに一度出くわしたことがありますので、一応注意します。
MySQLにて、DBの最大コネクション数と現在のコネクション数を確認する
コネクションも繋げないことで待ちになり、エラーになる場合がありますので……。
3.3. クライアントとデータベースのセッションタイムアウト設定について
セッションタイムアウトなのですが、短いと大量データ取得できずに切られてしまうという欠点がありますし、長いと不用意にSELECT文を投げてずっとリソースを占有し続けてしまうことがあります。なので程々良い値になっていてもらえると嬉しい項目です。
特に気にしたいのが、自分のJDBCなどのクライアント側で設定するセッションタイムアウトよりも、DB側のセッションタイムアウトの設定です。
クライアント側の設定だけ見て、切れるつもりになっていますと、場合によってはDB側の設定が良くなく、クライアント側が切ったあともDB側でプロセスが動き続けるという場合があります。
この場合、必要なのはKeep Aliveの設定です。
MySQLのDBコネクション関連でトラブルが発生した際にやること
DBごとに設定方法違いますが、必ずあるはずなので、注意しましょう。
たまに設定がおかしく、数日謎のプロセスが動き続けている、みたいな事例を見聞きします。
3.4. トランザクション分離レベル
トランザクション分離レベルなのですが、性能問題のために複数回に分けてSQL文を発行しようとしますと、「ファジーリード/ノンリピータブルリード」「ファントムリード」が問題になってきます。
どのトランザクション分離レベルなのかは認識した上で何が起こるのかを考えておく必要があります。
お客様への説明が必要な場合がありますので。
4. SQLについて
SELECT文ですが、当然ですが短ければ短いほど良いです。なので、パフォーマンスチューニングをちゃんとするべきでしょう。
当然普通のSQL文のチューニング方法は必要です。それについては語っていると長くなるので割愛します。
4.1. インデックス利かせるべきか？
大量データ取得時に特有の問題を一つだけ挙げておきますが、それはインデックス利かせない方が場合によっては速くなる問題です。
上手く説明してくれるサイトが見つからなかったので(ｵｲ、あっさり説明しますが計算上、木構造は全データからかなりの割合のデータを一度に持ってきたい場合には、全探索した方が早いのですね。
それは木構造の探索方式の問題で、基本情報とか応用情報に出てくるやつです。
うさぎでもわかる2分探索木 前編 2分探索木の基礎（表現・追加・削除）
うさぎでもわかる2分探索木 後編 2分探索木における4つの走査方法
具体的には、全データの20%くらいが目安になります。意外と少ない……。
例えばですが、取得した顧客リストから注文データを引っ張るときに、注文テーブル内の顧客IDにインデックスがついているとします。都市部と地方との購買の違いを分析したいとなって、都市部の顧客データを引っ張ってきて、注文テーブルにアクセスすると、20%を超えてきて、途端にパフォーマンスが遅くなるということは、普通に起こります。
ですので、大量データを引っ張ってくる処理の場合、インデックス効かせた方がいいのかは思案のしどころです。
4.2. フェッチ
フェッチってソフトウェア開発者忘れがちですが、ネットワークへの負荷を減らす効果などもあるので、場合によっては有効です。社内LANが一時的に重くなって……、みたいなの割とありますよね。
また、場合によっては、Webサーバー側のメモリ節約にもなると思います。Webサーバーでデータ編集しないといけない場合には有効だったり。
5. ソフトウェアの挙動
巨大データを引っ張ってくるために取りえる方法、いろいろあると思います。例えばですが、以下のようなことです。
- 分割して取得するようにする
- DB側でjoinしないで、ソフトウェア側でjoinする
- 一覧画面のページネーションをどう表現するのか
意外に悩むのがページネーションです。例えば、100件ごとに一覧画面に表示するみたいな場合です。
トランザクション処理を日々行っているリレーショナルデータベースへのアクセスというお題なので、当然ですが、ぼやぼやしているとソート後のランキングがどんどん入れ替わってきます。ゲームアプリとかまさにそうですよね！
毎回ページがめくられるごとにアクセス結果変わってもいいのか、という確認が必要ですよね……。難しい。
一回私も、一覧画面なくしてもらったことがあったりします。一覧画面って、メモリ圧迫の問題もあるので、プログラムつくるのはたやすいのですが、本当に動くのかが怪しい時があるのですよね……。
6. システム構成
簡単に言ってしまうと、DBにアクセスする負担を減らすためにシステムの構成変えられるのか？という視点です。以下のような方法があると思います。
- リードレプリカの作成
- DWHなどをつくる
ポイントは、データの同期タイミングだと思います。
- リアルタイム同期
- 定期的な同期
更新が多く、かつDB自体が非常にリソースを逼迫している場合に、リアルタイム同期をしようとすると、それだけでリソース逼迫の原因となる可能性があります。安全性を考えると定期的にファイルエクスポートして連携する方が安全だったりするわけですね。
結局、何をしたいのですか？ということから、同期タイミングをどうしたいのか考える必要があります。
7. 問題が起こっちゃったときの対応策
ここまでは、前提知識の共有でした。以下、何か問題が起こった時に対応する方法について考えます。
どの内容でも同じなのですが、データの一貫性と同期のリアルタイム性にどこまでこだわるのかが意外と大事になります。
7.1. どんな時でも見直したらいいこと
- ソフトウェア仕様の見直し
- リアルタイム性がいるのか？
- テレアポのようなリアルタイム性が必要な要件なのか？
- マーケティングのようにリアルタイム性が必要ない要件なのか？
- 取得データ量を絞れないのか？
- 名前や電話番号などで検索かけれないのか？
- リアルタイム性がいるのか？
- システム構成の見直し
- リードレプリカの導入
- 参照での負荷が重い場合に有効
- 更新処理が頻発すると効果的ではないかも
- DWHなどでデータベースを完全に分け、同期
- 基本的にそれほどリアルタイム性にこだわらない場合は、夜間バッチによる連携などが有効
- リードレプリカの導入
7.2. 共有ロックが問題になっている場合
- SQL文でロックをかからないようにする
- SQL文内のヒントなどでロックを弱められるか検討
- 参考：SQLServer: with(nolock)ヒントでロックを確実に回避できるという認識は間違い
- 分割して取得するようにする
- IDごとに1000件とか、地域別で複数回取得するとか
- 分割する場合にはトランザクション分離レベルに注意しないと、同じレコードが複数回取れてしまうことがある
7.3. SQL文の実行時間が問題になり、コネクションの空きやDBのリソースを逼迫している
- SQL文のチューニング
- ソフトウェア側で分割して取得するようにする
- 画面からWebサーバーへのリクエストをタスクキューにするなども要検討
7.4. DBとの間の通信量の逼迫
- 分割して取得するようにする
- フェッチを使う
7.5. 途中のサーバーやクライアントの画面で負荷
- ページネーションなどで都度SQL発行する形に変更
- 画面仕様として、100件以上は取得できないようにする制限をかける
- フェッチの利用
- 画面側ではなく、Webサーバー側で一覧情報をメモリ展開してしまう
8. 補足：昔本当におこったシステム障害について
ここまで読んで、SQL文で1～2分くらいで終わっている処理だけだったら問題にならなくない？と思った方もいらっしゃると思いますが、そんなことないというお話をしたいと思います。
実際起こったシステム障害では以下のような仕様がありました。
- Webサーバー内でエラーが起こった場合に、リトライ処理が働く
- Webサーバーが何らかの理由で倒れたときは、受け取ったリクエストをバックアップサーバーが引き受けて処理を行う
- PUSH型で動くシステムであったので可能だった
- 更新や参照に伴うアクセスが継続的にかなり多い
このシステムで、一旦処理しきれずにパフォーマンスが落ちると、雪だるま式に問題が膨らんで一気にバックアップ系含めてシステムがダウンしはじめました。
ポイントは、リトライしちゃうことなのですね。リトライ処理があると、パフォーマンスに問題出て処理できない場合にも再度時間をおかずに処理しようとしてしまうので、ちょっとした目詰まりでタスクが短時間で積みあがるということがあります。
こういう複数の仕様が組み合わせて起こる障害が一番やっかいで、かつよく起こるのですね。
これをどうにかするために、コツコツいろいろなところを改善して、やっと乗り越えるみたいなのが必要になります。忍耐の勝負だったりします。
9. おわりに
ソフトウェアエンジニアであっても、かなり広範囲の知識がないと本当に動くシステムってなかなか作れないのだなと思っていただければ幸いです。
特にDBアクセス部分が問題になった場合に、DBの設定とか、画面仕様やバッチ仕様の見直しに即座に入れるかが結構重要な資質だったりしますので、全体像を養っておくの大事ですね。
他の職種の方であれば、別の観点での見直しもできるかもしれないです。
また、クラウドだと起こらない事象だけど、コストが膨大にかかるとか、別の問題が発生するパターンもありますよね。
そうした意見を挙げてもらい、みんなで強いシステムを作るにはどうしたらいいのか、考えるきっかけになっていただければと思います。
読んでいただき、ありがとうございました。
修正履歴
2024/01/07 修正
JDBCと書いていたところを、クライアント側との表記に改めました。私のこだわりなだけなので、内容には変わりはないはずです。