Hatena::Groupdann

dann's blog このページをアンテナに追加 RSSフィード

Fork me on GitHub

2011-08-14

DevOpsに優しいSQLの管理方法

DevOpsに優しいSQLの管理方法  - dann's blog を含むブックマーク はてなブックマーク - DevOpsに優しいSQLの管理方法  - dann's blog DevOpsに優しいSQLの管理方法  - dann's blog のブックマークコメント

性能チューニングは、Hardware, OS, Middlewareに対する理解があるかないかで大分かわってくるところがありますが、Hardware, OS, Middlewareといったインフラに詳しいだけでは、踏み込んだチューニングは行えません。それにも関わらず、AP開発と運用は比較的分離されていることが多いため、DevとOpsが協力できるような体制を築くことが必要になります。最近はDevOpsという流れもあり、インフラに詳しいDevOpsを担う人がチューニングを行うケースもよくあるのではないかと思います。ここでは、インフラ側に軸足を置く開発者をDevOpsという用語で書いていくことにします。

DevOps側からみたときに、DBアクセスが中心のAPの場合、テーブル構造、データのライフサイクル、実際のSQLSQLの実行計画を見れば大概のことはわかるので、SQLを中心にした管理にすることで多くの性能チューニングが可能になります。従って、SQLを中心にした管理が重要になります。そのためには、以下の3点が重要になります。

  • SQLを中心にしたDBアクセスラッパ/ORマッパ を使用する
  • SQLには管理コメントをつけ、どこで使われる物なのかがわかるようにする
  • ER図/CRUD図を用意し、SQLでのデータの使われ方がわかるようにする

SQLを中心にしたDBアクセスラッパ/ORマッパ を使用する

よくあるのは、ORマッパを使用しているが、どこでそのSQLが生成されているのかを特定するのに時間がかかるというケースではないかと思います。さらに、場所が特定できた場合にも、特定のSQLをORマッパでどのように生成ればいいのかというのを、ORマッパが生成するSQLを確認しながらデバッグをするという本末転倒な状況が割とおきがちです。これは割と色々なところで見られるよくあるORマッパ使用のアンチパターンなケースです。

従って、どの部分でどのSQLが発行されうるのかを、誰が見ても一目瞭然にしておく必要があります。そのためには、SQLを一元管理でき、発行されるSQLを管理ができるようにすることが重要です。これを実現するためには、色々な選択肢がありえますが、大体以下の3つの選択肢または組み合わせに集約されるのではないかと思います。

  • 外部ファイルにSQLを書いてSQLの一元管理をできるORマッパ/DBアクセスラッパを使う
  • ストアドプロシージャを使う
  • シンプルなSQLはORマッパにまかせて、その他のクエリはORマッパから直接SQLの発行をできるようにする

SQLに管理コメントをつける

どこが発行したものなのかをわかるように規則をもうけて、SQL文そのものにコメントを埋め込みます。

こうすることで、発行されたSQLの箇所がわかるようになるため、StatspackなどでSQLを見た時にどの部位が発行した物なのかがわかるようになります。こうしておくと、問題が起きた時にはDevOps側がStatspackなどのレポートを見てどの部位で問題が起きたかがわかるようになるため原因を追及することが出来、DevOps側はAPのドメインの言葉でAP開発者とコミュニケーションがとれるようになります。

ER図/CRUD図を用意し、データの使われ方がわかるようにする

データのライフサイクルがわかるようにすると、どこでどのようにデータが使われるかが分かり、APの分析が容易になります。ER図はリバースしておこすこともできますが、CRUD図はSQLとテーブル定義を突き合わせる必要があるため、文書としてあったほうがDevとOpsでのコミュニケーションはしやすく、文書として残しておくのがおすすめです。(トランザクション量の分析も事前に出来るとよいですが、それは統計データからも分析可能なので、無くても性能分析のためのコミュニケーションは成立します。)

まとめ

DevとOpsが共同で性能改善に取り組んでいくためには、SQLを中心にした管理が重要です。また、AP開発者とDevOpsの連携を促進するために、SQL管理を容易にするためのSQLの管理コメントの導入のすすめSQLの使われ方を示すためのER図/CRUD図の作成 をすることにより、AP開発者とDevOpsのコミュニケーションを容易にすることが重要であるということになります。

OracleのSQLのチューニングに使うツール

OracleのSQLのチューニングに使うツール  - dann's blog を含むブックマーク はてなブックマーク - OracleのSQLのチューニングに使うツール  - dann's blog OracleのSQLのチューニングに使うツール  - dann's blog のブックマークコメント

Oracleでは性能に関連するデータが色々と取れるため、性能問題の解決がかなり容易になっています。OracleSQLのチューニングによく使うツールと使い方のポイントをまとめてみました。

性能状況の統計的分析 (Statspack)

問題箇所の全体に占める割合によって性能改善の効果が決まるため、測定されたデータから統計的に性能比率を分析し、チューニングすべき箇所を見つけるのは、SQLレベルでのチューニングではなくてもよくやることだと思います。このために使うのはStatspackです。

ある期間における統計をとれるので、全体の中から問題になるSQLを発見することができます。Level7でStatspackを取得すれば大体チューニングに必要な情報が取れます。Standard EditionでもEnterprise Editionでも利用できるため、よく使われているツールです。

では早速、Statspackをセクション別に簡単にポイントを説明していきます。

  • Load Profile
    • Hard Parseが多発してないか、Physical Readが多発してIOPS不足になってないか、生成されるREDOサイズなど基礎的な状況の確認などをします
  • Instance Efficiency
    • バッファキャッシュのヒット率が低くないか、Hard Parse回数が多くないかなど
    • Load ProfileやInstance Efficencyのセクションで問題になるケースは、殆どのケースでDBA不在といっても過言ではないです。
    • 大概がサーバーパラメータファイルの設定で解決できるので、このレベルでは少し知っている人がみれば解決できることが殆どで、通常はあまり問題にはなりません
  • Top 5 Timed Events
    • どの待機イベントがTopか
    • CPU Timeが一番上に来ているか(CPUを使えているか)、上位の待機イベントが何か(何で待ってしまっているか?)などを見ます
    • CPU Timeが長過ぎたり、待機イベントが長過ぎたりするという場合は問題になります。待機イベントの合計時間である程度の問題の推測ができます。CPU Timeについては、SQL orderedのセクションをみないと詳細はわからないので、CPU Timeが長過ぎる場合にはSQL orderedを見て問題を見つけていきます
  • SQL ordered(SQL ordered by CPU Time, Elapsed Time, Executionsなど)
    • CPU時間がかかりすぎているもの, Elapsed Timeが長すぎるもの(待機イベントが殆ど)、Executionsが多すぎるもの(実行回数が多いもの)はここを見ればわかります。全体の中でどれだけのCPU Timeを占めているかなどの割合がわかるので、どこを改善すればどの程度性能向上の余地があるのかがわかるという点で、何をチューニングする必要があるのかがわかるので、とても貴重です。
    • CPU TimeやElapsed Timeが長い場合
      • CPU実行時間やElapsed Timeが長いSQLについては、Old Hash Valueの値を元にStatspack SQLレポートでSQLの実行計画を確認します。実行計画に問題がある場合には、SQLだけでなく統計情報の確認もします。
    • Executionsで実行回数が多い場合
      • 1回あたりのCPU Timeは短いけれど、実行回数が多く性能が劣化しているケースはよくあります。良くあるのはN+1問題などで、無駄なSQLの発行回数が増えてしまっていることはあり、そのような問題を見つけやすいとはいえます。
  • Segments
    • どの表、どの索引の負荷が問題になっているかがわかります。

このように、Statspackによる統計データは、問題の全体像、問題箇所、問題の割合を明確にできるという点でとても貴重なレポートです。しかし、Statspackで問題の大体の推測は出来ても、ある一時点での問題や複数の待機イベントの時間が突出して問題が混在している場合などには、Statspackでは完全に問題の原因を突き止めるのは難しいケースがあります。

ある時点での状況分析 (EM or ASH Viewer)

Statspackは統計データによる分析ですが、問題は統計だけではわからないというのはよくあります。今その瞬間でどのSQLがどの程度CPU Timeを使っていて、どの程度の待機イベントが発生しているのかを見なければ問題を特定できないことがあります。例えば、Statspackで待機イベントの待ち時間が長いものが複数存在している場合に、どのSQLがいつどの程度の割合で待機イベントで待っているかを判断しようとしてもわかりません。全体の中での統計とある一時点の状況は異なるからです。

このようなときに使うのが Enterprise Manager(or ASH Viewer) です。

Oracleがとても素晴らしいのは、どのSQLの実行時にどの待機イベントでどれだけ待っていたかというのを、Enterprise Managerを使うと時系列で見ることができます。Statspackのような期間での統計情報ではなく、まさにその遅延している状況で、特定のSQLについて待機イベントを把握することができるというのがポイントです。

発生する待機イベントの問題をどう解決するかはOracleの内部のアーキテクチャに密接に関連するため、チューニング方法は待機イベントによって異なります。しかし、Oracleのアーキテクチャをよく理解していれば、何が問題で待機イベントが発生しているかは想像がつきやすいものでもあります。

OSの性能データの分析(iostat, vmstat, mpstat, sar)

Statspackである期間の統計をとっても、ASHでみても分からないのは、OSの性能データです。

Oracleのようなミドルではなく、Hardwareのレベルでボトルネックになってしまっている場合には、ミドルレベルでいくら改善しようとしてもHardewareの性能限界からそれ以上の性能を出すことはできません。

OracleというMiddlewareの問題なのか、Hardeareの問題なのかの切り分けは、このレイヤで判断する必要があります。まずMiddleの性能を使い切れるようなHardwareを選定できているかを検証することは重要です。

Hardewareの基礎性能を測るツールについては先日のエントリに書いたので参考にしてみてください。

ディスクのIOPSレベルで問題が出ているために、待機イベントが発生しているのか、CPUリソースの不足で待機イベントが発生してしまっているのかなどは、OS側の基礎情報もつきあわせないと分からないことは、高負荷環境ではよくあるのではないかと思います。OSレベルで問題がない場合は、ASHを中心に特定のSQLでの待機イベントを中心にして、ボトルネックの推定をOracleのアーキテクチャを考量した上でしていくことになります。

基本的に、OSの性能データは、iostat, vmstat, mpstat, sarで見ます。これを時系列で記録し、EMなどと突き合わせるとどのような状況で問題がおきているかはわかりやすくなります。

ストレージのI/O周りでよくあるのは、Random Read/Write周りが限界にくることです。これはディスクの仕組みからくる物で、Random WriteだとSSDにするか、BBWCを使うか、Random ReadだとSSDにするかなど、はたまたディスク本数や回転数を上げるといったことでIOPSをあげ、Hardwareレベルでの限界値をあげておくというのはよくやるのではないかと思います。このIOPSを解決すると、CPU周りで詰まることが多い訳ですが、ここは色んなケースで詰まるのでCase by Caseです。このような場合、SQL発行回数の削減や非正規化などは、比較的よく使われるテクニックではないかと思います。

まとめ

全体の統計データと時系列データの2点を組みあせて、全体の中からどこを解決しなければいけないかを見つけた上で、問題の全体像が見えたら、問題を深追いしてボトルネックの原因を見つけるという手順になります。

それを実現するためには、OracleではStatspack + ASH + OSの基礎情報(iostat, vmstat, mpsta)を使って性能分析をします。これらのツールで詳細に性能に関する詳細データが統計的にも時系列的にも取ることが可能なため、ブラックボックスであってもある程度内部の動作の推測はできるので、ボトルネックの問題分析が容易になっています。

# 最近はMySQLもPerformance Schemaという近い物がでてきたので、もう少し時間が立つとOracleと同じような形になっていくのではないかと思います。今後のOracleの動向に注目したいところです。