Copyright © 2012, Oracle and/or its affiliates. All rights
by user
Comments
Transcript
Copyright © 2012, Oracle and/or its affiliates. All rights
1 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. データベースの症状から アプリの問題点を見つける パフォーマンス・ドクター 日本オラクル株式会社 テクノロジー製品事業統括本部 ソリューション本部 基盤技術部 プリンシパルエンジニア 柴田竜典 以下の事項は、弊社の一般的な製品の方向性に関する概要を説明するものです。 また、情報提供を唯一の目的とするものであり、いかなる契約にも組み込むことは できません。以下の事項は、マテリアルやコード、機能を提供することをコミットメン ト(確約)するものではないため、購買決定を行う際の判断材料になさらないで下さ い。オラクル製品に関して記載されている機能の開発、リリースおよび時期につい ては、弊社の裁量により決定されます。 OracleとJavaは、Oracle Corporation 及びその子会社、関連会社の米国及びその他の国における登録商標です。 文中の社名、商品名等は各社の商標または登録商標である場合があります。 3 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. シバタツって誰? 日本オラクル株式会社 テクノロジー製品事業統括本部 ソリューション本部 基盤技術部 プリンシパルエンジニア 柴田竜典 Oracle Technology Network にて全5回で 『シバタツ流! DWHチューニングの極意』を連載 – これを読めばDWHデザインを間違わない! – すべての回に似顔絵がついている! シバタツ 4 検索 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. もう少しプロフィール Oracle Exadata リリース当初から、お客様のSQLやデータを使用した PoC (Proof of Concept) を実施 – 本番稼働しているたくさんのシステムのパフォーマンス・チューニングを経験 2010年には米オラクルの開発部門の一員として サンフランシスコのヘッド・クォーターで勤務 – 米国のお客様のPoCを実施しつつ、そこから見えてきた Oracle Database のパフォーマンス課題の解決に取り組む 5 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. アプリケーション・コードが システム・パフォーマンスに 与える影響 6 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. アプリケーション・コードが システム・パフォーマンスに与える影響 アプリケーション・コードがシステム・パフォーマンスに どのくらい影響するかは無視できない – この事実は昔からよく知られている – 一方でこの事実は昔から無視されている 悪いアプリケーション・コードはハードウェアやソフトウェアへの 投資を無意味にする Developerは悪いアプリケーション・コードを書かない DBAは悪いアプリケーション・コードを素早く見つけることが重要 7 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 本日紹介するパフォーマンス・ドクターの診断症例 症例その1: 負荷が掛からない 症例その2: 共有プールの具合が良くない 症例その3: カーソルがいくつあっても足りない 症例その4: しばらくすると負荷が止まりはじめる 症例その5: 突然のサービス停止 8 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない 9 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない 症状 このアプリケーションではまったく負荷が掛からない レスポンス時間もスループットも良くない DBAはコネクション数を増やしたが改善しない – 「データベースがバグってるんじゃないの?」 冬のボーナス商戦に向けて、このアプリケーションは悪くても レスポンス時間5ms、スループット3万tpsを実現しないといけない 10 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない 診察 レスポンス時間: 150ms スループット: 300tps CPU使用率のuserとsysが 同じくらい出ている 11 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない 原因: 多発性ログイン=ログアウト症 病名はフィクションです 12 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない 原因 for (i = 0; i < 1000000; i++) { conn = ds.getConnection(userid, password); pstmt = conn.prepareStatement("SELECT name FROM employees WHERE id = " + i); rset = pstmt.executeQuery(); ...... conn.close(); } 13 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. SQL文実行のたびに ログイン / ログアウトしている 症例その1: 負荷が掛からない 処方箋 conn = ds.getConnection(userid, password); for (i = 0; i < 1000000; i++) { { pstmt = conn.prepareStatement("SELECT name FROM employees WHERE id = " + i); rset = pstmt.executeQuery(); ...... } conn.close(); 14 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 必要最小限の ログイン / ログアウト 症例その1: 負荷が掛からない 症状 多発性ログイン=ログアウト症が完治したことで レスポンス時間とスループットは多少良くなったが 目標にはだいぶ遠い Concurrencyの待機イベントがたくさん出ており 共有プールの具合が良くない 15 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない 診察 レスポンス時間: 20ms スループット: 3,000tps latch: shared pool 16 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない 原因: 慢性ハード・パース病 病名はフィクションです 17 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない 原因 conn = ds.getConnection(userid, password); for (i = 0; i < 1000000; i++) { { pstmt = conn.prepareStatement("SELECT name FROM employees WHERE id = " + i); rset = pstmt.executeQuery(); ...... } conn.close(); 18 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 毎回ハード・パースしている 症例その1: 負荷が掛からない 処方箋 conn = ds.getConnection(userid, password); for (i = 0; i < 1000000; i++) { { pstmt = conn.prepareStatement("SELECT name FROM employees WHERE id = ?"); pstmt.setInt(1, i); rset = pstmt.executeQuery(); ...... } conn.close(); 19 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. プレース・ホルダーを使うことで 毎回ハード・パースしない 症例その1: 負荷が掛からない 診察 レスポンス時間: 1ms スループット: 25,000tps 競合が解消したことで CPUがよく動いてる 20 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない アドバイス conn = ds.getConnection(userid, password); pstmt = conn.prepareStatement("SELECT name FROM employees WHERE id = ?"); for (i = 0; i < 1000000; i++) { { pstmt.setInt(1, i); rset = pstmt.executeQuery(); ...... } conn.close(); 21 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. ソフト・パースすらも毎回しない 症例その1: 負荷が掛からない 診察 レスポンス時間: 1ms スループット: 34,000tps スループットがさらに向上 22 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その1: 負荷が掛からない 160 140 120 100 80 60 40 20 0 Logon/Transaction Hard Parse Transaction Rate x 1000 23 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Soft Parse Response time(ms) No Parse 症例その2: 共有プールの具合が良くない 24 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その2: 共有プールの具合が良くない 症状 本番環境同等の検証環境を持っている 検証環境での性能が本番環境だとまったく出ない DBAは共有プール競合が多発していることを見つけ アプリのソース・コードをくまなく確認したが 不必要な構文解析は起きていない Developerは本番環境とテスト環境で 全く同じコードが動いていると主張している 25 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その2: 共有プールの具合が良くない 診察 スループットが検証環境に比べて 大幅に悪い 共有プール競合が多発 V$SYSSTATビューの parse count (failures) が 増えていないかどうか確認 26 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その2: 共有プールの具合が良くない 診察 SQL> SELECT name, value FROM v$sysstat 2> WHERE name = 'parse count (failures)'; NAME VALUE ------------------------------ ---------parse count (failures) 25173781 27 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その2: 共有プールの具合が良くない 原因: 構文解析障害症候群 病名はフィクションです 28 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その2: 共有プールの具合が良くない 原因 本番環境でだけ、ページ再読込み時のトリガーが 存在しない隠しパラメーターをセットしようとしていた – ALTER SESSION SET _INSTANT_TUNING = AUTO; すべてのユーザーがこのSQLを構文解析しようとして すべてのセッションが共有プールでシリアライズされていた SQL*Net break/reset to client 待機イベントの数からも 診断することができる 29 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その3: カーソルがいくつあっても 足りない 30 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その3: カーソルがいくつあっても足りない 症状 「ORA-01000: 最大オープン・カーソル数を超えました」が発生し アプリケーションが動作しなくなり、アプリケーションの再起動が必要になる DeveloperはDBAにカーソル数を増やすことを依頼し DBAは初期化パラメータのOPEN_CURSORに 十分と思われる30000をセットした エラーが出るまでの時間は延びたが、結局しばらくすると ORA-01000が発生してしまう 31 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その3: カーソルがいくつあっても足りない 診察 エラーが発生すると処理が停止 SQL*Net break/reset to client が多発 オープン中のカーソルを確認 32 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その3: カーソルがいくつあっても足りない 診察 回数が1より大きいものは 正しく閉じられていない可能性 33 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その3: カーソルがいくつあっても足りない 原因: カーソル閉鎖機能障害 [三大リーク病のひとつ] 病名はフィクションです 34 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その3: カーソルがいくつあっても足りない 原因 try { ...... rset = pstmt.executeQuery(); ...... rset.close(); pstmt.close(); } catch (SQLException e) { e.printStackTrace(); } 35 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 例外処理に行ってしまうと pstmtをクローズしていない 症例その3: カーソルがいくつあっても足りない 処方箋 try { finallyで必ず ...... pstmtをクローズする rset = pstmt.executeQuery(); ...... rset.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if (pstmt != null) try {pstmt.close();} catch (SQLException e) {...} } 36 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その4: しばらくすると 負荷が止まりはじめる 37 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その4: しばらくすると負荷が止まりはじめる 症状 アプリケーションを動かしてからしばらく経つと スループットが突然落ち始める アプリケーションのログではタイムアウトが記録されている DBAはデータベースに問題がなく、アプリケーションからの接続を 増やして負荷を高めることをDeveloperに要求 – 初期化パラメータのPROCESSESはすでに20000で十分 38 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その4: しばらくすると負荷が止まりはじめる 診察 スループットがゼロに近づいていく 負荷もゼロに近づいていく OSのリソース使用量も ゼロに近づいていく 39 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その4: しばらくすると負荷が止まりはじめる 原因: セッション閉鎖機能障害 [三大リーク病のひとつ] 病名はフィクションです 40 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その4: しばらくすると負荷が止まりはじめる 原因 try { ...... rset = pstmt.executeQuery(); ...... rset.close(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } 41 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 例外処理に行ってしまうと connをクローズしていない 症例その4: しばらくすると負荷が止まりはじめる 処方箋 try { finallyで必ず ...... connをクローズする rset = pstmt.executeQuery(); ...... rset.close(); } catch (SQLException e) { e.printStackTrace(); } finally { if (pstmt != null) try {pstmt.close();} catch (SQLException e) {...} if (conn != null) try {conn.close();} catch (SQLException e) {...} } 42 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その5: 突然のサービス停止 43 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その5: 突然のサービス停止 症状 何の前触れもなく、データベースがハングしたような状態になり 同時にアプリケーションもタイムアウトしはじめた DBAは、アクティブではない1個のセッションが取得しているロックを 残りすべてのセッションが待っていることを確認した この問題が起きるたびに、DBAはロックを取得しているセッションを killすることでシステムを再稼働している 44 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その5: 突然のサービス停止 診察 ロック待ちにより すべてのセッションが待機 45 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その5: 突然のサービス停止 診察 1個のセッションが 残りすべてのロック待ちを 引き起こしていることが 確認できる 46 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その5: 突然のサービス停止 原因: ロック解放機能障害 [三大リーク病のひとつ] 病名はフィクションです 47 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その5: 突然のサービス停止 原因 ロック・リークはセッション・リークと同時に起きやすく 例外処理内でのCOMMIT / ROLLBACKの処理に問題があることが多い コネクション・プーリングをしていると、リークしているセッションは ロックを獲得したままコネクションを返却してしまい データベースはCOMMITもROLLBACKもされないまま放置される 48 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 症例その5: 突然のサービス停止 原因 try { ...... rset = pstmt.executeUpdate(); ...... conn.commit(); pstmt.close(); conn.close(); } catch (SQLException e) { e.printStackTrace(); } 49 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 例外処理に行ってしまうと COMMITしていない しかもセッション・リーク している 症例その5: 突然のサービス停止 処方箋 try { 失敗したときには ...... ROLLBACK rset = pstmt.executUpdate(); ...... conn.commit(); } catch (SQLException e) { if (conn != null ) try {conn.rollback();} catch (SQLException e) {...} e.printStackTrace(); } finally { if (pstmt != null) try {pstmt.close();} catch (SQLException e) {...} if (conn != null) try {conn.close();} catch (SQLException e) {...} } 50 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 本セッションのまとめ 51 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 本セッションのまとめ アプリケーション・コードがシステム・パフォーマンスに与える影響 これらの原因の多くは一般的によく知られている問題である しかし無視されていることが現実には多い これらの問題が引き起こす影響はDBAの視点からは不可解 _ 人人人人人人 _ > 問題の長期化 <  ̄Y^Y^Y^Y^Y^Y ̄ Developerは悪いアプリケーション・コードを書かない DBAは悪いアプリケーション・コードを素早く見つけることが重要 52 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. Database Oracle Database 11g : SQLチューニング・ワークショップ Oracle Database 11gでSQLを効率的にチューニングするための知識とノウハウを習得! 本コースではチューニング方法のみならず、チューニングの方法論までも理解できます。使用するOracleの診断ツールおよび機能は、自動 SQLチューニング・コンポーネント、EXPLAIN、SQL TraceおよびTKPROF、SQL*Plus AUTOTRACEです。また、物理的なスキーマおよび SQL構文を変更することによるオプティマイザの動作への影響についても習得できます。 本コース修了時には、以前のリリースで行っていた手動でのチューニング方法と比較した上で、現在のリリースで提供される自動SQLチュー ニング機能を使用できるようになります。受講者は、Oracleオプティマイザの概念を理解し、体系化されたマシン演習を通じて、講義内容をよ り深く理解できます。 コース内容 ■Oracle Databaseアーキテクチャの検討 ■SQLチューニングの概要 ■オプティマイザの概要 ■オプティマイザ演算子 ■オプティマイザ・ヒントの使用 ■SQLチューニングの自動化 対象者 ・データベースアプリケーション開発者 受講料 定価¥218,295(税込) (2012年9月現在) 集合研修 ■実行計画の解釈 ■ケース・スタディ: スター型変換 ■オプティマイザ統計 ■バインド変数の使用 ■アプリケーション・トレース ※Oracle PartnerNetwork会員様は、パートナー割引価格で受講いただけます。 教室での集合研修も絶賛開催中。 集合研修の日程は http://education.oracle.co.jp/ 左上コース検索にて、「チューニング」で検索頂けます お申込み・お問合せ http://www.oracle.com/jp/education オラクルユニバーシティ Tel: 0120-155-092 53 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. ToD トレーニングオ ンデマンドにて 提供中 54 Copyright © 2012, Oracle and/or its affiliates. All rights reserved. 55 Copyright © 2012, Oracle and/or its affiliates. All rights reserved.