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

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

性能状況の統計的分析 (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の動向に注目したいところです。

DevOpsに優しいSQLの管理方法

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

DevOps側からみたときに、DBアクセスが中心のAPの場合、テーブル構造、データのライフサイクル、実際のSQL、SQLの実行計画を見れば大概のことはわかるので、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のコミュニケーションを容易にすることが重要であるということになります。

Hardwareの基礎性能を測定するためのツール

インフラの性能でアプリケーションの性能限界が決まってしまうため、Hardwareの基礎性能を把握しておくことはとても重要です。以下では、Hardwareの基礎性能を測るための各種ツールを紹介します。

ディスク用ツール - fio

DBサーバーなどのI/O intensiveなサーバーで、Random Read/WriteのIOPSの限界でアプリケーション性能が決まってしまうというのは良くあるのではないかと思います

ディスクの基礎性能を測定するためのツールとしては、fio があります。

このツールでは、read/write, DirectI/O, I/O sizeなど、色々なI/Oパターンを指定して測定ができます。様々なI/Oパターンで、ディスクのIOPS・転送速度を簡単に測定できるので、とても便利です。

メモリ用ツール - stream

CPUの処理性能の向上率に比べてメモリ帯域はそれほど伸びていないという現状があり、コア数が増えていくにつれてそれが問題になってきます。特にコア数が多く・memory-intensiveなアプリケーションでは、実際にどの程度のメモリ帯域があるかの目安を知っておくことは重要になります。

メモリの帯域幅を測定するためのツールとしては、「stream」があります。

このツールでは、Copy, Scaleなどいくつかのメモリのアクセスパターンに応じてメモリの帯域幅を測定することができます。
-O3、openmp使用でコンパイルし、帯域幅を使いきれる状態にして測定しましょう。

ネットワーク用ツール - nuttcp, nepim

多量のトランザクション、IMDBなどの使用、大きなサイズのファイルの転送など、ネットワークのスループット・レイテンシが重要になるケースなどは、割とあるのではないでしょうか。

スループットなどのネットワークの性能測定用のツールとしては、nuttcp, nepimがあります。

これらのツールでは、バッファサイズやキューサイズなどを変更したテストが簡単にできるため、重宝しています。

# 他に、このツール便利だよ！というのがあれば、是非教えてください！

継続的プロファイリング

Google-Wide Profiling: A Continuous Profiling Infrastructure for Data Centers
http://research.google.com/pubs/pub36575.html

継続的プロファイリングの仕組み

  • 測定はOprofileとGoogle Perf Tools
    • OprofileでのHPMのプロファイル
    • Google Perf Toolsを使ってリモートからHTTPでプロファイル結果をとれる仕組み
      • ProfilerStart(), ProfilerStop()などのAPIがあるので、C++のアプリの場合、任意の部分をリモートからプロファイルできそうですね。こういう仕組みは、一部のサーバーのデバッグ用などにもとても良さそうです。
  • 回収・解析・表示の仕組み
    • Oprofileなどの結果をCollectorで集めて、集めた結果をMapReduceで処理してProfile Serverにいれて、Web Serverから表示できるようにしています

Googleの継続的プロファイリングの面白い点

　Hardwareの違いも含めてプロファイリングを通じて最適化するための仕組みを用意しているのはとても面白いなと思いました。処理性能も、コア数、メモリのバンド幅、メモリの構成、L1, L2, L3キャッシュなどの量などの関係、さらにアプリケーションの処理特性（CPU-intensiveな処理か、Memory-intensiveな処理なのかなど）になどによって、まるで性能は変わってしまい、それを完全に予測するのは難しいのは事実です。だからこそ、ハードに近いレイヤで、それを運用を通じて測定することで、性能改善をかけられる仕組みを用意しているのはGoogleらしいと思いました。
また、最適化した結果のコスト削減結果も、‘‘dollar amount per performance change,’’ has become popular among Google engineers. と書かれているように、コスト視点のメトリクスで見られ、性能改善結果を最終的なコストに直結させている点も面白いです

perfの使い方

CPUネックのサーバーの状況をリアルタイムに知りたいケースというのは、科学計算、暗号処理、画像処理などのCPU boundなアプリケーションでは良くあるんじゃないかと思います。

perfはoprofileのリアルタイム版といった感じで、hardware event, software eventの測定をでき、その場の状況を測定できるのでとても便利です。リアルタイムに状況を知りたい場合の使い方を中心にまとめてみました。

全体の測定

まずは、最初何も状況がわからなければ。

perf top

特定関数の測定

perf top実行後、s keyを押して、perf topで表示されたfunction名を入力。
これで、特定関数が測定できる。perf topで眺めながら、問題のある関数が把握できたらその関数だけ見るといった使い方が便利。

特定プロセス・スレッドの測定

問題のあるプロセス・スレッドが特定できているという場合には、それだけをモニタリングするというのは良くやりたくなることの一つです。以下で確認できました。

特定プロセスの測定

perf stat -e cycles -p プロセスID sleep 5
  • e で指定するイベントなどは適宜。

特定スレッドの測定

特定イベントのperformance counterの取得。
ps -L axなどでスレッドIDは決めて、以下を実行。

perf stat -t <スレッドID> sleep 5
  • e で指定するイベントなどは適宜。

特定イベントの測定

問題が特定イベントに絞り込まれている場合に。これは上記のいずれのものとも組み合わせられるので、適宜使うこと。

  • イベントはperf listで確認
  • perf stat -e event1,event2 で絞り込んで測定

call chainを表示してbottleneckを発見

これは、perf top, perf statと違って記録を残して分析するための使い方なのですが、とても便利なので書いておきます。

sudo perf record -g yourprogram
sudo  perf report -g

TwitterのBlenderに近いアーキテクチャになっているMessage Pack RPCのJava版の実装を読んでみた

TwitterのBlenderはNIOの使い方として面白い使い方だなあと思っていたので、それに比較的近い実装をしているMessagePack-RPCのJava版の実装を読んでみました(MessagePack RPCのJava版でも同様にNettyを使っています）。

読んでみた感じだと、MessagePack RPCのJava版の実装のポイントは、以下の3つでした。

  • NettyによるNon Blocking I/Oサーバーのイベントループ
  • 入力と出力のMessagePackによるエンコード／デコード
  • 入力のメッセージからのRPC先の実装の決定方法(Reflection, JavaAssistの実装でのInvoker実装)

基本的なこの流れは、NettyのPiplineを使って実現しています。コードだと以下のような感じになってますね。ServlerやWSGI, PSGIなどを使っている方は理解しやすいんじゃないかと思います。

	public ChannelPipeline getPipeline() throws Exception {
		ChannelPipeline p = Channels.pipeline();
		p.addLast("msgpack-decode-stream", new MessagePackStreamDecoder());
		p.addLast("msgpack-encode", new MessagePackEncoder());
		p.addLast("message", new MessageHandler(handler));
		return p;
	}

簡単に動作をまとめると、「Nettyでイベントループをまわして、メッセージを受け取ったらMessagePackでデコードして、その後でMessageHandlerで呼び出し先を決めて(Reflection or JavaAssist)、オブジェクトのメソッドを呼び出しをして、結果はMessagePackのフォーマットで従って返す」ということになります。

Nettyはとても綺麗にNIO実装が抽象化されており、さらにPipelineやPiplineへのメッセージのエンコーダ・デコーダを切り替えられる仕組みなどを提供しており、サーバー実装に必要なものを簡単に書けるようになっているので、MesagePack RPCのJava実装もとてもシンプルな実装になってますね。MessagePack RPCのJava実装は、RPC呼び出しをJavaのメソッド呼び出しに変換する部分(Invoker)と、MessagePack形式のメッセージのエンコード／デコード部分が基本で、それをNettyにつなぐという形の実装になっています。

TwitterのBlenderでは、メッセージのエンコード／デコードにはThriftを使っているようなので同じようにProxy Layerの部分ではPiplineに、OneToOneDecoder, OneToOneEncoderを実装したThrift用のDecoder, Encoderを実装して実現しているのでしょう。また、Blenderでは、バックエンドサービスの呼び出し手順を規定したワークフローは、NettyのPipelineとして実現されているということなので実装も大分イメージはしやすいですね。シーケンシャルにしたい部分をNettyのPipelineとして実装してるんだろうなと。