シンプルでシステマチックな Oracle Database, Exadata 性能分析

シンプルでシステマチックな Oracle Database, Exadata 性能分析 By AWR and OS/ExaWatcher 畔勝洋平コンサルティングサービス事業統括クラウド・テクノロジーコンサルティング事業本部 DBコアテクノロジー 2017.02.13

2 出典: https://www.slideshare.net/khailey/history-of-database-monitoring

3 出典: https://www.slideshare.net/khailey/history-of-database-monitoring Simple can be harder than complex. You have to work hard to get your thinking clean to make it simple.

自己紹介 4 • 畔勝洋平（あぜかつようへい） • 2010年10月中途入社（7年目）ネットベンチャー→フリーランス→ドワンゴ→日本オラクル • Webデザイナー（HTML・JSコーダー）としてキャリアをスタート • DBコンサルとしてミッションクリティカルシステムを支援 • トラブルシューティングではOSカーネル（Linux/商用 UNIXなど）に Deep Dive することも特技：オードリー春日のモノマネ

5 Twitter: @yoheia Blog: http://d.hatena.ne.jp/yohei-a/ 著書（共著）：絵で見てわかるITインフラの仕組み JPOUG(Japan Oracle User Group)の運営に関わってます自己紹介カバーを裏返すとシステムの全貌がわかる解剖図 2013年ジュンク堂池袋本店コンピュータ書売上冊数ランキング第5位 http://compbook.g.hatena.ne.jp/compbook/20140107/p1

アジェンダこの勉強会で何がわかるようになるか Oracle Database 性能分析の歴史システムアーキテクチャと性能分析の考え方性能分析メソッド性能分析レポートの書き方 Exadata の性能分析項目性能分析TIPS集まとめ 1 2 3 4 5 6 6 7 8

この勉強会で何がわかるようになるかコンピュータの中で何が起きているかイメージする 7

8 この勉強会で何がわかるようになるか（目標） • AWRレポートなどの性能統計値からコンピュータの中で何が起きているか“リアル”にイメージできる • システムのボトルネックを特定し、改善案を提示できるメモリ CPU Disk NIC usr sys wa idle1. ボトルネックを見つけるプロセス1 プロセス2 2.ドリルダウンして原因特定 3. 原因を取り除く I/Oスケジューラプロセススケジューラメモリ管理

9 なぜこの勉強会を開催したか • オラクルに入社する前はStatspack/AWRレポートの見方がわかららなくて調べていた頃がありました • 今思うのは「Statspack/AWRレポートの見方」が分からないじゃなかった • コンピュータや Oracle Database のアーキテクチャとパフォーマンス分析の考え方、統計の見方が分かれば分析できる → 今はDB以外もあらゆるソフトウェアの性能分析ができる • 7年前の自分、当時の自分と同じような方に贈りたい

Oracle Database 性能分析の歴史 Oracle Database の性能分析機能は世界一 10

11 Oracle Database 性能分析の歴史草創期のチューニングメソッド時間ベースのチューニングメソッド https://www.ogh.nl/downloads/ogh20080410_graham_wood.pdf Graham Wood Oracle Real World Performance Group

12 Oracle Database 性能分析の歴史 https://www.ogh.nl/downloads/ogh20080410_graham_wood.pdf Method-R By Cary Millsap ASH & AWR Kyle Hailey ORTA by Craig DB Time By Gaja? YAPP By Anjo Kolk パフォーマンス分析メソッドで参考にしている先人たちパフォーマンス分析やチューニングの文献を調べる際の参考に ISBN-10:1590593871

13 Oracle Database 性能分析の歴史 http://www.slideshare.net/khailey/history-of-database-monitoring Kyle Hailey AWRやASHの設計に関わった人

14 Oracle Database 性能分析の歴史 http://www.slideshare.net/khailey/history-of-database-monitoring Kyle Hailey

15 Oracle Database 性能分析の歴史 https://aws.amazon.com/jp/blogs/aws/amazon-aurora-update-postgresql-compatibility/ • EMそっくりの画面 • DB Time や Wait Interface を持つRDBMSは少ない

システムアーキテクチャと性能分析の考え方コンピュータのアーキテクチャと待ち行率理論 16

17 コンピュータのアーキテクチャは70年間変わっていない • コンピュータは70年経っても、アーキテクチャは変わっていない • CPU、メモリ、ディスク、NICなどがバスで接続されている第二次世界大戦頃に科学者ノイマンが考案したため、「ノイマン型コンピュータ」と呼ばれ、そのアーキテクチャは現代も変わっていない出典:Systems Performance: Enterprise and the Cloud [ISBN-10:0133390098]

18 CPUなどのコンポーネントにはキューが存在する • CPU、メモリ、ディスク、NICなどのコンポーネントで処理が行われ、混んでいるとキューで待たされる CPU、メモリ、ディスクなどのコンポーネント出典:Systems Performance: Enterprise and the Cloud [ISBN-10:0133390098]

19 レスポンスタイム = サービスタイム + キュータイム • デバイスで処理につかった時間がサービスタイム、キュー待ち時間がキュータイム CPU なら ON CPUCPUならランキュー出典:Systems Performance: Enterprise and the Cloud [ISBN-10:0133390098]

20 処理量が増えるとキュータイムが伸びる • 処理量が増えるとキュータイムが伸び、レスポンスが遅くなる秒間の処理要求が上がると、キュー待ち時間が延び、レスポンスが遅くなる出典:Oracle Performance Firefighting [ISBN-10:0984102302]

21 使用率↑→キュー待ち↑→エラー • 使用率が上がると、キュー待ち時間が長くなり、最終的にタイムアウトなどでエラーが発生する Errors 出典： Systems Performance: Enterprise and the Cloud [ISBN-10: 0133390098] http://www.brendangregg.com/usemethod.html

22 ソフトウェアスタック • データベースはユーザープロセスの一つデータベースユーザープロセスがハードウェアデバイスにアクセスするにはシステムコールを経由する必要がある出典:Systems Performance: Enterprise and the Cloud [ISBN-10:0133390098]

23 待機はシステムコールや他のプロセス待ち時間 • ユーザーモードで ON CPU 以外のシステムコール発行後のカーネルモード、スリープの時に待機時間 SP SP BP ハードウェア OSカーネルユーザープロセスシステムコール割込みハンドラ • Enqueue • Latch • Mutex • log file sync … • SQL*Net message from client• db file sequential/scattered read • direct path read 待機時間待機時間待機時間 ON CPU

24 OSから見ると待機は Sleep / Disk Sleep の2種類 D OR S ON CPU Sleep Disk Sleep Runnable •ON CPU の時間が DB CPU •システムコール発行後のカーネルモードで ON CPU の時間は待機時間に入る •Latch、mutex などのスピンロックは ON CPU でスピンしている時間は DB CPU に計上される •TCP/IP通信で、ソケットバッファに書いて送信後のクライアントからのパケット到着待ちのときは Sleep する •latch、mutex などの待機時間はスピンでタイムアウト後に Sleep した時間 •I/Oシステムコール発効後はカーネルモードにコンテキストスイッチ後、割込不可でスリープする(CPUは使っていない) •CPUが使われてなくてこの状態のプロセスがいると、iowat に計上される •ランキュー待ち時間は DB CPU には含まれない db file sequential/scattered read direct path read SQL*Net message to client SQL*Net message from client

性能分析観点 3-Circle Analysis / USE メソッド / DB Time 25

26 処理時間 / 仕事量 = 単価、リソース使用量 • ベースラインとの比較 • DB Time が伸びていないか • 伸びた場合、業務処理量が増えたのか、単価が増えたのか • リソース使用量は業務処理量に対して想定内か • 業務処理量が増加している場合、リソース増強が必要か判断 • 単価が増えた場合、異常がないか深堀調査出典:Systems Performance: Enterprise and the Cloud [ISBN-10:0133390098]

27 3-Circle Analysis • OS、DBインスタンス、高負荷SQLの3つの観点で分析 OSWatcher, ExaWatcher を利用し、USE の観点で分析 AWRレポートの SQL ordered by セクションを合計と1実行当りで分析 AWRレポートを処理時間 / 仕事量 = 単価の観点で分析出典: http://www.orapub.com/ppts-2015-ausoug-stop-guessing-use-oracle-time-based-analysis

28 OS は USE メソッドで分析 • CPU、メモリ、ストレージなどのコンポーネントをUtilization、 Saturation、Errorsの3つでボトルネックが発生していないか出典:Systems Performance: Enterprise and the Cloud [ISBN-10:0133390098]

29 DBインスタンスは時間ベースで分析 • DBで時間を使っているか • DB Time の内訳をドリルダウン出典:Optimizing Oracle Performance By: Cary Millsap, Jeff Holt(ISBN-13: 978-0596005276) Introduction to Time-Based Analysis: Stop the Guessing!(Craig A. Shallahamer, OraPub,inc.) DB Time AP Time Figure 1-1

30 チューニング効果の高いSQLをリストアップ • Elapsed、CPU Time、Gets、Reads でマトリックス化 • チューニング効果の高いSQLをリストアップ DB Time に占める割合 •SQL ordered by Elapsed time / CPU Time / Gets で 1位 •SQLチューニングで Buffer Gets を減らすとCPU使用率が下がる •インスタンス全体の 11.7% のため効果は高い出典: http://www.orapub.com/ppts-2015-ausoug-stop-guessing-use-oracle-time-based-analysis

性能分析レポートの書き方 3-Circle Analysis をそのまま目次に 31

32 3-Circle Analysis を分析レポートの目次に OS分析 DBインスタンス分析高負荷SQL分析サマリ推奨事項出典: http://www.orapub.com/ppts-2015-ausoug-stop-guessing-use-oracle-time-based-analysis

33 OS分析項目例中項目小項目チェック内容分析対象データ分析対象項目 CPU CPU使用率・100%に達している時間帯がないか・sys が 40% を超えていないか OSWatcher [vmstat]-[usr + sys + st] ランキュー・CPU数×2を超えていないか OSWatcher [vmstat]-[r,b] CPU別使用率・特定のCPUで100%で張り付いている時間帯がないか・sys が 40% を超えていないか OSWatcher [mpstat]-[user + system + steal + intr + soft] メモリメモリ使用率・実質使用量が80%を超えていないか OSWatcher [meminfo]- [MemFree+Active(file)+Inactive(file)/MemTotal] メモリ使用率内訳・ページテーブルが1GBを超えていないか・スラブキャッシュが1GBを超えていないか OSWatcher [meminfo]-[PageTables,Slab] ページング発生状況・ページイン／ページアウトが発生していないか OSWatcher [vmstat]-[si, so] スワップ領域使用率・スワップが発生していないか OSWatcher [vmstat]-[swpd] ストレージ I/Oレスポンス・20ms を上回っていないか OSWatcher [iostat]-[await] I/Oサービスタイム・10msを上回っていないか OSWatcher [iostat]-[svctm] ディスクビジー率・80%を上回っていないか OSWatcher [iostat]-[%util] IOPS ・カタログスペックの80%を超えていないか OSWatcher [iostat]-[r/s. w/s] 真のメモリ使用率 I/OはI/Oレスポンスとサービスタイムを見よう山崎さんのナレッジ（ K7549 ）を参考

34 DBインスタンス分析項目例（1/3）中項目小項目チェック内容分析対象データ分析対象項目仕事量 SQL実行回数（秒間）なし AWR Report [Load Profile]-[Executes] トランザクション数（秒間）なし AWR Report [Load Profile]-[Transactions] ログオンユーザー数なし AWR Report [Key Instance Activity Stats]-[logons cumulative] REDO生成量（秒間）なし AWR Report [Load Profile]-[Redo size (bytes)] ハードパース回数（秒間）なし AWR Report [Load Profile]-[Hard parses] 物理読込量（秒間）なし AWR Report [Load Profile]-[Physical read ] 論理読込量（秒間）なし AWR Report [Load Profile]-[Logical read] インターコネクト通信量（秒間）なし AWR Report [Load Profile]-[Global Cache blocks received] / [Global Cache blocks served]

35 DBインスタンス分析項目例（2/3）中項目小項目チェック内容分析対象データ分析対象項目 DB処理時間アクティブセッション数・アクティブセッション数が CPU_COUNTを超えていないか AWR Report [Wait Classes by Total Wait Time]-[Avg Active Sessions] 時間モデル統計なし AWR Report [Time Model Statistics] 待機クラスなし AWR Report [Foreground Wait Class] Top N 待機イベント・enqueue、latch、mutex 等の割合が高い場合は深堀調査を行う・平均待機時間が長い待機イベントがないか AWR Report [Top 10 Foreground Events by Total Wait Time] I/Oレスポンス 20ms を上回っていないか AWR Report [Foreground Wait Events] [Wait Event Histogra] [Wait Event Histogram Detai] キャッシュフュージョン平均ブロック転送時間 20ms を上回っていないか AWR Report [Global Cache and Enqueue Services - Workload Characteristics]-[Avg global cache cr/current block receive time (ms)] I/Oは合計ではなく平均やヒストグラムでレスポンスが健全か見る

36 DBインスタンス分析項目例（3/3）中項目小項目チェック内容分析対象データ分析対象項目メモリ使用状況 SGA内訳 AWR Report [SGA Breakdown Difference] 共有プール内訳 AWR Report [SGA Breakdown Difference] ラージプール内訳 AWR Report [SGA Breakdown Difference] 共有プール・ラージプールの immediate 拡張有無 AWR Report [Memory Dynamic Components] バッファキャッシュヒット率・オンラインで95%を下回っていないか・バッチで90を下回らないか AWR Report [Instance Efficiency Percentages] RACバッファキャッシュヒット率・なし AWR Report [Global Cache Efficiency Percentages (Target local+remote 100%) ] ライブラリキャッシュヒット率・オンラインで95%を下回っていないか・バッチで90を下回らないか AWR Report [Instance Efficiency Percentages] SGA Target Advisory ・バッファキャッシュ拡張によりI/O量削減の可能性が高いか AWR Report [SGA Target Advisory] BufferPoolAdvisory ・バッファキャッシュ拡張によりI/O量削減の可能性が高いか AWR Report [Buffer Pool Advisory] PGA使用量・PGA_AGGREGATE_TARGET を超えていないか AWR Report [PGA Aggr Target Stats]

37 高負荷SQL分析項目例中項目チェック内容分析対象データ分析対象項目実行回数の多いSQL ・ベースラインおよび他の期間との比較 AWR Report SQL ordered by Executions 物理読込量の多いSQL(総計/1回当り) ・時系列で右肩上がりで増えているSQLがないか AWR Report SQL ordered by Reads 論理読込量の多いSQL(総計/1回当り) ・時系列で右肩上がりで増えているSQLがないか AWR Report SQL ordered by Gets CPU時間の長いSQL(総計/1回当り) ・時系列で右肩上がりで増えているSQLがないか AWR Report SQL ordered by CPU Time クラスタ待機時間の長いSQL(総計/1回当り) ・時系列で右肩上がりで増えているSQLがないか AWR Report SQL ordered by Cluster Wait Time 共有メモリ使用量の多いSQL ・共有メモリ使用量が 100MBを超えているSQLがないか AWR Report SQL ordered by Sharable Memory Version Count の多いSQL ・時系列で右肩上がりで増えているSQLがないか AWR Report SQL ordered by Version Count 実行時間の長いSQL(総計/1回当り) ・elapsed が undo_retention を超えているSQLがないか AWR Report SQL ordered by Elapsed Time 合計と1回当りの両方で分析すると、一発で重いのか、塵も積もればで上位なのかわかりやすい

Exadata の性能分析項目 SmartScan/Storage Index/Flash Cache のベースラインを押えておく 38

39 Exadata の I/O が速い理由 InfiniBand DBサーバストレージサーバサーバープロセス HDD CellServ HDD HDDASM SmartScan（仕事量を減らす） DBサーバからストレージサーバにWhere句の条件を渡し、ストレージサーバで返すブロックを絞込むことで速くなる InfiniBand（高速化） DBサーバとストレージサーバをレイテンシが小さく帯域が広い InfiniBandで接続 ASM（並列化）複数のディスクに分散配置し、並列I/Oにより時間が短縮される仕事量を減らす、並列化、高速化はあらゆるレイヤーで有効な考え方メモリ Storage Index（仕事量を減らす） SQLが実行されるとメモリにブロックが含むデータの範囲がキャッシュされ、ディスクI/Oを削減する出典:http://otndnld.oracle.co.jp/ondemand/ddd-2016/DD2-5.pdf

40 DD2-3 しばちょう先生の特別講義！！ストレージ管理のベストプラクティス～ASMからExadataまで～より • 処理量を減らす – Index, Partitioning, Compression, Exadata Smart Scan/Storage Index … • 高速化 – Database In-Memory, Flash Device, InfiniBand, Exafusion, … • 並列化 – Parallel Query, Multi-Core, RAC, ASM, … 時間↓ = 処理量↓ / （速度 * 並列度）↑ じかん = みちのり ÷ はやさ 1つ前のスライドの話処理を速くする３つの方法出典:http://otndnld.oracle.co.jp/ondemand/ddd-2016/DD2-5.pdf

大項目中項目小項目チェック内容分析対象データ分析対象項目仕事量 SQL実行回数なし AWR Report [Load Profile]-[Executes] トランザクション数なし AWR Report [Load Profile]-[Transactions] REDO生成量なし AWR Report [Load Profile]-[Redo size (bytes)] ハードパース回数なし AWR Report [Load Profile]-[Hard parses] 物理読込量なし AWR Report [Load Profile]-[Physical read ] 物理読込量（実質）・SmartScan / Storage Index / Flash Cache 利用状況 AWR Report [Exadata Smart Statistics]-[Smart IO] 論理読込量なし AWR Report [Load Profile]-[Logical read] キャッシュフュージョンなし AWR Report [Load Profile]-[Global Cache blocks received] / [Global Cache blocks served] 41 SmartScan の効き具合を分析項目に入れる • Exadata Smart Statistics - Smart IO で SmartScan、Storage Index、Flash Cache の効き具合を傾向分析する

42 ExaWatcher を活用する • DBサーバ、CellサーバのOS性能統計情報収集ツール • Exadata の場合、3-Circle Analysis でOS分析で ExaWatcher を活用できる • vmstat や mpstat から top、ps、/proc/meminfo、/proc/slabinfo まで詳細な情報を収集してくれる（Doc ID:2183442.1） • 保持期限はログサイズで指定されているため、あまり古いデータが残っていなかったりする（Doc ID:1769508.1）

43 Exadata X5 からCellサーバのOS性能統計がAWRに • AWRレポートに CellサーバのCPU使用率やI/OなどのOS性能統計、SmartScanなどの統計がレポートされるようになった Oracle Exadata Storage Server Software Performance Statistics in AWR Reports Exadata Storage Server configuration and performance statistics are collected in the Automatic Workload Repository (AWR), and the data is available in the AWR report. The Oracle Exadata section of the AWR report is available in HTML or active report format. The following sections are three main sections in the AWR report: • Exadata Server Configuration: Hardware model information, software versions, and storage configuration • Exadata Server Health Report: Offline disks and open alerts • Exadata Performance Statistics: Operating system statistics, storage server software statistics, smart scan statistics, and statistics by databases The AWR report provides storage level performance statistics, not restricted to a specific instance or a database. This is useful for analyzing cases where one database can affect the performance of another database. Oracle Exadata Database Machine System Overview 12c Release 1 (12.1) E51953-17 http://docs.oracle.com/cd/E50790_01/doc/doc.121/e51953/app_whatsnew.htm#DBMSO21849

45 CPU時間とCPU使用率の換算 • 1時間のAWRレポートでCPU_COUNT=8の場合 • 3,600秒（1時間）* 8 = 28,800秒（4時間） • DB CPU = 28,800（4時間）→ CPU使用率100%をDBが使っている 3,600秒（1時間） 28,800秒（8時間）出典:Systems Performance: Enterprise and the Cloud [ISBN-10:0133390098]

46 DB Time < CPU Time + Wait Time • db file sequential read などのI/Oシステコールは発行してから返ってくるまでの時間のため、待機時間にカーネルモードでのCPU時間が含まれる • CPU時間は db file sequential read などの待機時間と、CPU Time にダブルカウントされる通常、極めて短時間のため目立つことはない出典:https://www.slideshare.net/jberesni/awr1page-sanity-checking-time-instrumentation-in-awr-reports

47 DB Time > CPU Time + Wait Time • AIX on POWER ではCPU時間をOSから見て ON CPU の時間ではなく、SMT でCPU内で使用した時間を算出し、ソフトウェアから見て CPU Time が実際より短く計上されるケースがある • 詳しくは以下参照 http://oracleprof.blogspot.jp/2013/02/oracle-on-aix-wheres-my-cpu-time.html 出典:https://www.slideshare.net/jberesni/awr1page-sanity-checking-time-instrumentation-in-awr-reports

48 真のメモリ使用率の見方カーネル buffer cached Page Cache 共有メモリ（System V IPC）共有メモリ（/dev/shm） Anonymous Pages free (-/+ buffers/cache) ①/proc/meminfo の MemTotal ②vmstat の used free の used（-/+ buffers/cache） ③ipcs -um → pagesresident × 4KB ④df -k → tmpfs の Used メモリ使用率(%) = (メモリ使用量(KB) / ①MemTotal(KB)) × 100 メモリ使用量(KB) = ②物理メモリ使用量(ページキャッシュ除く)(KB) + ③共有メモリ使用容量(ページ数×ページサイズ) + ④tmpfs/ramfs使用容量(KB) Huge Page は使用前はここに含まれる Huge Page はSGAとして使われている時はここに含まれる出典:シンプルでシステマチックな Linux 性能分析方法

49 RHEL/Oracle Linux 6、7 では空きメモリサイズ = /proc/meminfo の MemFree + Active(file) + Inactive(file) メモリ使用率 = 100 – 空きメモリサイズ ÷ /proc/meminfo の MemTotal 空き領域（free）カーネル空間（Slab、KernelStack、PageTablesなど）総メモリ容量（/proc/meminfo のMemTotal） buffer cached AnonPages anon file active inactive active inactive共有メモリ Shmem スワップされる（空けるとなくなるためディスクに保存する必要がある）ユーザ空間共有メモリはファイルシステム（tmpfs）として実装されているため、cached に計上されるが、バッキング・ストアがないため、ページ回収の際はスワップする必要があるため、anon のリストで管理される。参考：プロのための Linuxシステム・10年効く技術 [ISBN-10: 4774151432] http://d.hatena.ne.jp/enakai00/20110906/1315315488

51 まとめ • コンピュータはコンポーネントがバスで接続されている • あらゆるところにキューが存在する • レスポンスタイム = サービスタイム + キュータイム • 処理時間 / 仕事量 = 単価、リソース使用率 • 3-Circle Analysis で正確な分析

53 参考情報 http://d.hatena.ne.jp/yohei-a/20161205/1480913874

シンプルでシステマチックな Oracle Database, Exadata 性能分析

More Related Content

What's hot

Viewers also liked

Similar to シンプルでシステマチックな Oracle Database, Exadata 性能分析

More from Yohei Azekatsu

シンプルでシステマチックな Oracle Database, Exadata 性能分析