2009.10.28 現場で役立つ oracle dbのパフォーマンスチューニング
- 1. おら! オラ! Oracle - どっぷり検証生活 現場で役立つ Oracle DBのパフォーマンス チューニング 株式会社 ?ンサ?トテクノロジー 新久保 浩二
- 2. 今回のテーマ 1. 従来型の方法論よりシステマチックな?プローチを 2. 複雑な問題をシンプルにブレークダウン ? CPUリソースボトルネック ? Memoryリソースボトルネック ? I/Oリソースボトルネック ? 効率の悪いSQL ? 効率の悪い排他制御 ? … 3. 多分、SQLのギリギリチューニングの話は今回はしません
- 3. 注意 注意書き 本プレゼンテーションにはORACLE社による非推奨および非サポートである 内容が含まれる場合があります。 本プレゼンテーションの内容に関しては、ご自身にてリサーチを行って ください。 本プレゼンテーションの内容を実施する場合はご自身のリスクにて行って ください。
- 4. ?ジェンダ ? チューニングの意味とは ? チューニングにおける”適切なリソース”とは ? チューニングにおける”適切な処理負荷”とは ? チューニングプロセスとは ? チューニングにおいて最も重要な”測定”とは ? DBロード?ベレージとは ? ボトルネック事例とチューニング事例 ? 最後に
- 6. チューニングにおける”適切なリソース”とは ? 通常データベースで使用するリソースとは – CPUリソース – Memoryリソース – I/Oリソース – Networkリソース ? 無駄にリソースを使うのは適切なリソース配分とは言えない – ラッチ競合 – 大量なソート処理 – ひどいSQL文 – … ? リソースが使えていない状況は適切なリソース配分とは言えない – ロック競合 – H/W、DB周りの間違った設定 – …
- 7. チューニングにおける”適切な処理負荷”とは ? 何をもって”適切な処理負荷”と判断するか? – OSリソースの使用状況? – ヒット率に代表されるOracle内の統計値の比率? – 待機?ベントや統計値などのカウンタ値? – 過去との比較? ? キーワードは?クテ?ブセッション数をベースとしたロード?ベ レージ(負荷指数) – OSの場合、負荷指数としてRun QueueやCPU Load Averageなどを指標として 使いますよね – 同様にOracleでも?クテ?ブセッション数を計測することでDatabase Load Averageを測定することが可能
- 8. チューニングプロセスとは ゴールに向かって 現状の把握 分析した時点の手法にて パフォーマンスの チューニングを実施 現状の測定 善し悪しを測定 分析 チューニング ボトルネック の実施 の特定 適切なパフォーマンスとは チューニング パフォーマンスに問題が どこなのか?そのゴールを ある場合、なぜ?どうして? のゴール設定 設定する どうすれば良くなる?を分析
- 9. チューニングにおいて最も重要な”測定”とは ? 何をもって”適切な処理負荷”と判断するのか? ? そのために必要な測定項目とは何か? ? 最も有効なのはDBロード?ベレージ – 負荷指標として使用できる – チューニングのゴールを適切に設定できる – 測定と同時に多くのボトルネック分析の元データを提供してくれる 従来のヒット率/統計値を使用する指標は測定ではなく”推測”と言えるかもしれ ません。 推測をベースとしたチューニングは膨大な知識の集積を元に成り立つ場合が多 く、経験の浅いDBAでは間違った結論を出す可能性が高くなります。 * Oracle Enterprise ManagerのAAS(Average Active Sessions)と同義 (詳細は後ほど)
- 10. DBロード?ベレージとは その前に大事な概念をいくつか Database Timeの概念 最も重要な時間モデル統計はDB timeです。この統計は?ンスタンスのワークロード合計の?ン ジケータであり、データベース?コールの合計所要時間を表します。この統計は、??ドル待機 ?ベント（??ドル状態でないユーザー?セッション）で待機していない全セッションのCPUタ ?ムと待機時間を集計することで計算されます。 (Oracle Database パフォーマンス?チューニング?ガ?ドより) つまり? - データベース全体のワークロードの指標である - しかし、データベース?コール完了時にしか統計値が上がらない(長いI/Oコールで待ってい ても上手くその事象を(リ?ルタ?ムに) 捉えられない) - データベース?コールの合計であり、レスポンス?タ?ムとは異なる - 単なる指標に過ぎないがAverage Active Sessions(AAS)と組み合わせれば、見えない世界が 見えてくる
- 11. DBロード?ベレージとは Average Active Sessions(AAS) Database Timeがデータベースにおけるリソース使用時間であるのに対してAASはデータベース におけるロード?ベレージ(負荷指標)と置き換えることが可能。 AASの取得はDMA(Direct Memory Access)による高頻度なサンプリング方式。サンプリングされ るデータはv$session_waitの情報を軸とするボトルネックの原因を示す情報も含まれる。 * AASはEM(Enterprise Manager + Diag + Tuning)を使用されている方には説明不要かも知れ ません。しかし、AASの概念はデータベース共通のチューニング手法であり、DMA手法の優れ た適用分野であり、概念的にも技術的にも面白いと感じさせる部分です。 つまり? - リソース使用時間よりロード?ベレージのほうが監視?分析に適している(通常、しきい値の指標 としてOracleで使用可能な物理CPU数を使う) - OSのリソースでは多面的に相関分析する必要があるが、AASは1つの指標である程度理解できる - 情報密度が濃い - ただし、データベースに特化していることを忘れてはならない(スローダウンはデータベースの 問題のみに起因するわけではない)
- 12. DBロード?ベレージとは DBロード?ベレージ AASの場合は、Oracleカーネル自身が取得しますが、10g以上+EE+有償オプション扱いです。 また、3rdパーテ?製品などでAASを取得するような製品もあります。 (ちなみ、現在弊社で開発中のPerformance Insight 6ではAASを実装する予定) なかなか、DBロード?ベレージを体感できないじゃないか! という場合、以下のSQLを実行すると、なんとなくDBロード?ベレージの理屈を理解できます。 select decode(w.wait_time, 0, 'WAITING', -1, 'WAITING', 'ON CPU') db_resource_type ,decode(decode(w.wait_time, 0, 'WAITING', -1, 'WAITING', 'ON CPU'), 'WAITING', w.wait_class, null) wait_class ,decode(decode(w.wait_time, 0, 'WAITING', -1, 'WAITING', 'ON CPU'), 'WAITING', w.event, null) wait_event from v$session s ,v$session_wait w where s.sid != (select distinct sid from v$mystat where rownum < 2) and s.type = 'USER' and s.sid = w.sid and ((w.wait_time != 0 and s.status = 'ACTIVE' and w.wait_class != 'Idle') or (w.wait_class != 'Idle') and w.p2text != 'break?'); 本来DBロード?ベレージは非常に高いサンプリングレートで取得する必要があります。 * OSのロード?ベレージはTimer割り込み(1/100秒) のサンプリングレートで動作しています ですので、上記で示したSQL文を1/100秒に1回取得するといった行為はある意味、自殺的な行為 ですので、絶対に商用環境で実施しないでください。
- 13. DBロード?ベレージ(AAS)の?メージ Active Sessionの数をカウントする Session #1 Session #2 Session #3 Sampling interval Active Session Active Sessionの数をDB Timeとしてプロットする (これを1分程度の間隔で平均したものがAAS) DB Time 3 2 1 0
- 14. DBロード?ベレージ(AAS)の?メージ Active Sessionの数をカウントする - WaitとOn CPUを区別する Session #1 Session #2 Session #3 Sampling interval On CPU Waiting Active Sessionの数をDB Timeとしてプロットする DB Time 3 2 1 0
- 15. DBロード?ベレージ(AAS)の?メージ Active Sessionの数をカウントする – さらにWaitの内訳をみる Session #1 Session #2 Session #3 Sampling interval On CPU db file scattered read db file sequential read log file sync Active Sessionの数をDB Timeとしてプロットする DB Time 3 2 1 0
- 16. ボトルネック事例とチューニング事例 DBロード?ベレージの実力を理解するために疑似的な負荷状態を 作り、その時、OSリソースおよびDBリソースでどのように見える か確認してみる。 また、その際、DBロード?ベレージを元にどのような分析(測定) を行い、どのようなチューニングが必要なのかを探ってみる 検証に用いた環境は以下。 OS RedHat Enterprise Linux 4 (32bit) CPU 2 core * 1 Memory 2GB Oracle 11.1.0.6 * DBロード?ベレージの取得には弊社開発中のモジュールを使用 * 検証に使ったSQL等を載せていますが疑似コードです
- 17. CPUリソースボトルネック#1 準備運動を兼ねて、 最も単純な例として以下のスクリプトを同時 2セッションで実施 -- CPU runaway procedure begin while (1=1) loop null; end loop; end; / OSリソースおよびDBリソース共に100%使い切ることが容易に想 像できます。 DBリソースはWaitすることなく、常にOn CPU状態であることも 予想できます。
- 18. 颁笔鲍リソースボトルネック#1(翱厂リソース) CPU Usage 100 80 60 Wait I/O CPU 40 System CPU 20 User CPU 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 I/O Busy Ratio 100 80 60 40 sda 20 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
- 19. 颁笔鲍リソースボトルネック#1(顿叠リソース) DB LoadAvg. 物理CPUは2個 2.5 On CPU Other Wait 2 Commit Wait Network Wait 1.5 Queueing Wait Application Wait 1 Cluster Wait Scheduler Wait 0.5 Configuration Wait Administrative Wait 0 User I/O Wait System I/O Wait 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 Concurrency Wait 当然と言えば当然ですが、WaitすることなくCPUを回し続けて Wait Event Info. いることが分かります。 2.5 2 1.5 latch free 1 db file sequential read 0.5 control file sequential read 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
- 20. CPUリソースボトルネック#2 では、もう少し、現実味のあるパターンを試します。 リテラルを使用し、カーソルが再利用されないSQL文を連続で、 大量に実行します。 今回も同時実行は2セッション begin while (1=1) loop execute immediate ‘select * from emp where empno = {ここに毎回異なるリテラルが入ります}’; end loop; end; / Oracleから見れば、SQLのパース負荷(CPU負荷)が高まると共に Shared Pool周り(特にカーソルの配置、追いだし)によるラッチ 負荷(これもCPU負荷)が高まると予想されます。
- 21. 颁笔鲍リソースボトルネック#2(翱厂リソース) CPU Usage 100 80 60 Wait I/O CPU 40 System CPU 20 User CPU 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 I/O Busy Ratio 100 80 60 40 sda 20 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
- 22. 颁笔鲍リソースボトルネック#2(顿叠リソース) DB LoadAvg. 物理CPUは2個 2.5 On CPU Other Wait 2 Commit Wait Network Wait 1.5 Queueing Wait 1 DBリソースとしてはギリギリな感じです。 Application Wait Cluster Wait また、DBリソースの多くをConcurrency Waitに費やしています。 Scheduler Wait 0.5 Configuration Wait その詳細は、shared pool latchやlibrary cacheのlatch(mutex) Administrative Wait 0 となっています。11g以降library cache latchはspinからmutex User I/O Wait 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 System I/O Wait に置き換えられています。そのためmutexのSystem Callにより Concurrency Wait OSのSystem CPUが高い状態になっています。 Wait Event Info. でも実はこれは嘘です。 いくらなんでも、こんなにSystem CPUが高騰するなんて… 2.5 そもそもOracleのmutexはOSのmutexではないので… library cache: mutex X 2 System CPUの内訳を調べたらgettimeofday()とgetrusage()が library cache load lock 1.5 多くを占めていました。 latch: shared pool これはlatchのsleepやtimed_statisticsによるSystem Callですが、 1 latch: row cache objects 今回のような単純なSQLの連続実行では、System Callのオーバー 0.5 latch free ヘッドが大きく出たようです。 db file sequential read 0 昔ながらのチューニングならこのSystem CPUを何とか下げようと cursor: pin S wait on X 1 2 3 4 5 6 7 8 するのですね… 13 14 15 16 17 18 19 20 21 22 23 24 25 26 9 10 11 12
- 23. 颁笔鲍リソースボトルネック#2(チューニング例) – リテラルを排除し、バ?ンド変数化する DB LoadAvg. – CURSOR_SHARING = SIMILAR | FORCE 物理CPUは2個 2.5 On CPU Other Wait 2 Commit Wait Network Wait 1.5 Queueing Wait Application Wait 1 Cluster Wait Scheduler Wait Configuration Wait 0.5 Administrative Wait User I/O Wait 0 System I/O Wait 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Concurrency Wait チューニング前に比べて、Waitしている比率が約1.5から約0.5に Wait Event Info. 改善されています。 これにより、Database Timeとして約3倍程度のチューニング効果 2.5 があると考えられます。 2 実際に計測してみると、約3倍(1/3?)のレスポンス時間が得られま db file sequential read 1.5 した。 cursor: pin S wait on X 1 cursor: pin S control file sequential read 0.5 SQL*Net message to client 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
- 24. Memoryリソースボトルネック 今度は、Memoryリソースのボトルネックを発生させてみます。 発生させるのはいたってシンプル。 - SGAのサ?ズを可能な限り小さくする (検証時のSGA_TARGETのサ?ズは約260MB) 上記に加えてswingbenchという負荷ツール(OLTP系)を使用して 同時10セッションで一斉に負荷をかけてみます。 * swingbench(http://dominicgiles.com/swingbench.html) OSリソース的には、SGAが小さいことによりI/Oボトルネックに なることが想像できます。DBリソース的には、I/O関連のWait ?ベント(db file xxx read)が出るのでしょうか?
- 25. 惭别尘辞谤测リソースボトルネック(翱厂リソース) CPU Usage 100 80 60 Wait I/O CPU 40 System CPU 20 User CPU Memoryのチャートがないので分かりづらいですが、これだけ見る 0 とMemoryリソースのボトルネックではなく、I/Oリソースの 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 ボトルネックのようにも見えます。 経験あるOracleエンジニ?は、Buffer Cacheのヒット率を見たり I/O Busy Ratio すると思います。 これまた、ヒット率チャートを載せていませんが、この時の 100 Buffer Cacheのヒット率は93%程度で、若干悪いが、すごく悪い 80 わけでもありません… 60 40 sda 20 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
- 26. 惭别尘辞谤测リソースボトルネック(顿叠リソース) DB LoadAvg. 物理CPUは2個 2.5 On CPU Other Wait 2 Commit Wait Network Wait 1.5 Queueing Wait Application Wait 1 Cluster Wait Scheduler Wait 0.5 Configuration Wait Administrative Wait 0 User I/O Wait System I/O Wait 1 2 3 4 5 6 7 8 9I/O関連のWaitと思いきや、ConfigurationのWaitとなっていま 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Concurrency Wait す。詳細を見るとfree buffer waits(DBバッフ?ー不足)である Wait Event Info. ことが分かります。 I/O Waitはしているのだが、I/O WaitよりもDBバッフ?ーの 2.5 メンテナンスコストの方が大きいためこのような結果になっている write complete waits 2 と言えます。 latch: row cache objects 1.5 実際、本質をついた原因が報告されていると言える。 latch: cache buffers lru chain latch free 1 free buffer waits 0.5 db file sequential read 0 db file scattered read 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 buffer busy waits
- 27. 惭别尘辞谤测リソースボトルネック(チューニング例) – SGA_TARGETを大きくする DB LoadAvg. – DB_CACHE_SIZEを大きくする 2.5 On CPU Other Wait 2 Commit Wait Network Wait 1.5 Queueing Wait Application Wait 1 Cluster Wait Scheduler Wait Configuration Wait 0.5 Administrative Wait User I/O Wait 0 System I/O Wait 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 Concurrency Wait キャッシュサ?ズを大きくした瞬間から、Waitがなくなり、 Wait Event Info. On CPUのみ(ほぼ)になっていることが分かります。 2.5 それに加えて、全体として1.3程度だったロード?ベレージが write complete waits 2 一気に0.2程度に下がり、負荷が低下していることが一目瞭然 log file sync です。 library cache: mutex X 1.5 library cache load lock 1 latch free free buffer waits 0.5 db file sequential read 0 db file scattered read 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 cursor: pin S wait on X
- 28. I/Oリソースボトルネック 最後にI/O関連のボトルネックを作ってみます。 こちらも発生はいたってシンプル。(同時実行は2セッション) - 200万件程度のデータを作成 - 200万件のデータを全件検索 OSリソースとしてはI/Oボトルネック。しかし、CPUリソースは 余っている(有効活用されていない)ことが予想できます。 DBリソースとしては、I/O関連のWait?ベント(今回db file scattered read)が大量の発生し、限界状態を示していることが 予想できます。
- 29. 滨/翱リソースボトルネック(翱厂リソース) CPU Usage 100 80 60 Wait I/O CPU 40 System CPU 20 User CPU 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 I/O Busy Ratio 100 80 60 40 sda 20 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
- 30. 滨/翱リソースボトルネック(顿叠リソース) DB LoadAvg. 物理CPUは2個 2.5 On CPU Other Wait 2 Commit Wait Network Wait 1.5 Queueing Wait Application Wait 1 Cluster Wait Scheduler Wait 0.5 Configuration Wait Administrative Wait 0 User I/O Wait System I/O Wait 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 DBとしては、ほぼ全ての処理がUser I/OでWaitしていることが Concurrency Wait 分かる。 Wait Event Info. その詳細は、テーブルのフルスキャン(db file scattered read)を 2.5 原因としたバッフ?の取り合い(read by other session)である ことが分かる。 2 read by other session 1.5 latch: shared pool 1 latch: row cache objects 0.5 db file scattered read buffer busy waits 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26
- 31. I/Oリソースボトルネック(チューニング例) ? ストレージ(H/W)の変更 ? Parallel Query ? Partitioning ? ブロックサ?ズの見直し ? データ構成(テーブル構成)の見直し ? DB_FILE_MULTIBLOCK_READ_COUNTのチューニング ? データの断片化の解消 ? …
- 32. 最後に、もうちょっと現実を?メージして… これをどう見ますか? (OSリソース的?プローチ) CPU Usage レスポンスが悪いと 苦情殺到 ? 100 50 0 CPU使用率としては余裕がある? wait_io% system% user% ポ?ントされた時点はさらにCPUに余裕がある? I/O Busy Ratio デ?スク?ビジー率も高めだが、いつも通りか? 100 50 0 sda sdb sdc
- 33. これをどう见ます? (DBリソース的?プローチ) DB LoadAvg. ? 15 物理CPUは8個 10 5 0 ロック競合により上手くOSリソースを使えていない!! wait_time cpu_time 全体的にデータベース処理の半分がWait状態!! Wait Event Info. → Waitの正体は行ロックの競合でした… → ボトルネックの影響範囲と改善範囲を可視化できる!!! 10 0 db file scattered read db file sequential read enq: TX - contention enq: TX - index contention enq: TX - row lock contention
- 34. 最後に データベースはチューニングすることで快適に動作するようになります。 チューニングは、知識や経験も必要とされることが事実ですが、最も重要 なのは、コンピュータ(システム)とサービスへの深い理解です。 今、何が問題なのか? 問題を解決するゴール(目標)とは何か? ゴールにいきつく手段は何か? を理解する一助となれば幸いです。
- 35. Q&A Questions?
- 36. Thanks ORA-03113