Microsoft PowerPoint - SQL パフォーマンスチューニング.pptx

Size: px

Start display at page:

Download "Microsoft PowerPoint - SQL パフォーマンスチューニング.pptx"

つねたけふじつぐ
4 years ago
Views:

1 Caché テクニカルコンサルタント上中進太郎インターシステムズジャパン Agenda Caché SQL 概要データインデックスグローバルの構造埋め込み SQL 動的 SQL が処理される仕組みクエリのロジック SQL パフォーマンスチューニングクエリプランチューニング TuneTable Index 追加クエリキーワード SQL パフォーマンス計測 %SYS.PTools ユーティリティ 2 1

2 Caché SQL 概要 Caché のSQL はどうやって動いてる? サンプルクラス定義グローバル構造 ( データインデックス ) 埋め込みSQL の仕組み動的 SQL の仕組み SQL ロジックの基本的な考え 3 Caché SQL 概要サンプルクラス定義 Class Symposia.Person Extends %Persistent { } Property RegID As %Integer; Property Name As %String; Property CompanyName As %String; Index RegIDIndex On RegID [U Unique ]; Index CoNameIndex On CompanyName [ Type = bitmap ]; 4 2

3 Caché SQL 概要データグローバル ^Symposia.PersonD=10 IDを採番する ^Symposia.PersonD(1)=$lb(,774, 太郎 "," ロイヤル薬品証券 ") ^Symposia.PersonD(2)=$lb(,894, 鈴花, サイバー技研興業 ) データは $List 形式で格納されている $List の構造 2N T 12TInterSystems InterSystems5T t 5TTokyo 各データの前にデータデータ型がバイナリ形式で格納されている 5 Caché SQL 概要インデックスグローバル標準インデックス ^Symposia.PersonI("RegIDIndex", ("RegIDIndex",774,1)=" ^Symposia.PersonI Symposia.PersonI("RegIDIndex", ("RegIDIndex",894 " 894,2)=" グローバル名 ( インデックス名, 対象プロパティ 1,,ID) =$LB( インデックスデータ ) ビットマップインデックス ^Symposia.PersonI( ( CoNameIndex, ロイヤル薬品証券,1) =$c(150,1,1,0,1,0)/*$bit(01)*/ )*/ グローバル名 ( インデックス名, 対象データ,chunk) = chunkに最 64000bit 6 3

クエリキャッシュができるクエリキャッシュをクラス等で参照するには SMP 構成 SQL 設定般

%ResultSet API の場合 %sqlcq.[ns name].[n].mac 新しい %SQL.

4 クエリを実したらどのように処理されているのか? Caché SQL 概要埋め込みSQL クラスクエリはそのルーチンやクラスにクエリが展開される SQL1.mac SQL1.int 7 クエリを実したらどのように処理されているのか? Caché SQL 概要動的 SQL や外部からの xdbc 接続ではクエリキャッシュができるクエリキャッシュをクラス等で参照するには SMP 構成 SQL 設定般 SQL 設定クエリキャッシュ- ソース保存 = はいに変更 xdbc 経由のSQLや %ResultSet API の場合 %sqlcq.[ns name].[n].mac 新しい %SQL.Statement APIの場合 %sqlcq.[ns name].cls[n].cls がクエリキャッシュとして成される * 使しているバージョンによって異なるクエリキャッシュ名となる可能性がある SQL2mac 8 4

5 9 Caché SQL 概要クエリのロジックを予想してみる Select RegID from Symposia.Person where CompanyName = ʻAAAʼ Order by Name ^Symposia.PersonD=93 ^Symposia.PersonD(1)=$lb(,774, 太郎 "," ロイヤル薬品証券 ") ^Symposia.PersonD(2)=$lb(,894, 鈴花, サイバー技研興業 ) ^Symposia.PersonI("CoNameIndex"," AAA",1) =$bit( ) ^Symposia.PersonI("CoNameIndex"," BBB",1) =$bit(00001) CompanyNameもIndex 指定してあるので ^Symposia.PersonI( CoNameIndex, AAA,n) を参照して該当するデータグローバルのID Name 取得しその後ソートのためにNameをキーにテンポラリグローバル ^CacheTemp(Name,id)=RegIDに度格納してから最終的にデータを取りだすパフォーマンス調査の第歩 10 いちいちロジックを予想したりルーチンをたりはしていられないのでクエリプランをみよう! 管理ポータルのSQL SQL の実クエリプランの表クエリを実しなくても表できる管理ポータルの SQL スキーマクエリキャッシュ動的 SQL xdbc クエリのプランが確認できる STUDIO でクエリを選択して右クリック SQL に対するプラン表 5

6 クエリプランの具体的な英語で処理内容を表記次ページでキーワード紹介相対コストとは同クエリ同で較できる相対的なコスト主にインデックス追加前後の効果を測定できる相対コストが低いプランのほうが良い異なるクエリ同のコスト較は意味がない 11 クエリプランキーワード Call module n この表記がある場合はそのmodule 部分を先に呼び出している 12 6

7 クエリプランキーワードキーワード Read master map 意味データグローバルを参照 Read index map using the given yyy looping on xxx with a %STARTSWITH range condition インデックスグローバルを参照主にクエリのパラメータとして与えられたyyyを使してインデックス or データ本体の値を取得 xxxでインデックス or データ本体をループ前致条件でループ 13 クエリプランキーワードキーワード Add ID bit to bitmap temp-file A 意味各モジュールでの検索結果をテンポラリ領域にビットマップ形式で保存 Add a row to temp-file A, subscripted by %SQLSTRING(AAA) and ID,with node data of BBB. 各モジュールの検索結果をテンポラリ領域にサブスクリプトにAAAとID を配列形式でデータ部にBBBを保存例 : ^temp(aaa,id)=bbb Accumulate the max(xxx). xxx を計算する Max の場合は較 Sumの場合はし算など (((index map INDEXNAME) UNION (bitmap temp-file A)) UNION (bitmap temp-file B)) 14 INDEX あるいはテンポラリ領域の複数の結果を UNION 処理 7

8 クエリプランで特に注意する点 Read master map Symposia.Table1.IDKEY, looping on ID. これは全データグローバルをループして参照しているということなのでデータ件数の多いテーブルの場合はよくない Read master map Symposia.Table1.IDKEY, using the given idkey value. は問題ない Add a row to temp-file A テンポラリグローバルに書いているということなので件数にもよりますがあまりよくないこの内容がインデックス追加のヒントになることもあります 15 クエリチューニングまずは TuneTable! 管理ポータル SQL SQL スキーマを参照スキーマを表してスキーマ全体のチューニングマ全体のチテーブルを個別に表してテーブルのチューニング $SYSTEM.SQL.TuneTable(tablename) TuneTable は以下のつを計測している選択性 (SELECTIVITY) エクステントサイズ (EXTENTSIZE) 16 8

9 選択性 (SELECTIVITY) 選択性はデータの分散度合いを抽出して計測している N % 1 ユニークであることを表している 17 * 性別のフィールドの選択性は 50% になる実際のデータ分布は関係ない例えばデータ上は男性 70% 性 30% であったとしても男性性というデータが抽出されたの性というデータが抽出されたのであれば 50% となる 99% のデータが 0 の削除フラグでも削除フラグ =1 のデータが抽出されると 50% となる管理ポータルから値を指定することも可能 18 エクステントサイズテーブルの総データ件数を計測エクステントサイズは複数テーブル間のJOIN が発する場合にどのテーブルから絞り込みを開始するか決定するのに使される SELECT count(*) FROM Symposia.Table2,Symposia.Table3,Symposia.Table4 y p, y p WHERE T2Key=T3Key AND T2Key=T4Key AND T2Value %STARTSWITH 'A' AND T3Value %STARTSWITH 'B' AND T4Value %STARTSWITH 'C' 9

10 TuneTable はいつう? システム稼働前に実し空のテーブルについては予測値をれておく通常は定期的にう必要はないパフォパフォーマンスに問題が発していない場合は特にう必要がない各テーブルの選択性やエクステントサイズのテーブル間の率がきく変わった場合は効果あり注意点 TuneTable 実中はシステムに負荷がかかります TuneTable 実直後関連するテーブルのすべてのクエリキャッシュがパージされます次回実時に新たに成されるため負荷がかかります稼働直後稼働 1 年後トラン B トラン A マスタ 2 マスタ 1 19 インデックスを追加しようどんなインデックスを追加する? Q. 標準インデックス? ビットマップ? A. 対象プロパティの個別値個別値が個を超える場合には標準インデックスを使したほうが良いデータの分布を考えてインデックスを選択するとさらに良い 10 万件に1 件しかない削除フラグ =1 を検出したい標準条件に AND 検索や OR 検索が多いビットマップ COUNT() クエリを速化したいビットマップ条件なしCount() の速化エクステントビットマップ 20 10

11 インデックスを追加しよう対象テーブルにフィールドが多い場合はインデックスデータを活ある特定のクエリに対して専インデックスを設定してク速化したい標準インデックスクエリプランの Add a row to temp-file X をヒントにしてみる SELECT P1 FROM Symposia.Table1 WHERE P2 = 'V2411ʼ ORDER BY P3,P4 Index SpecialIndex On (P2, P3, P4) [Data = P1]; インデックスを使わせるクエリプロセッサがインデックスを使わない法を選択することがあるレコード件数が少ないのでインデックスをるよりデータを直接たほうが早いと判断する恣意的にインデックスを使わせたい場合は選択性エクステントサイズなどを変更する条件となるフィールドが関数を使しているとインデックスを使できない例 :TimeStamp 型フィールド (SalesDate) を付 ( ) と較 SELECT * FROM Symposia.Table5 where SalesDate > ʻ :00:00ʼ OK SELECT * FROM Symposia.Table5 where {fn CONVERT(SalesDate,SQL_DATE)} > ʻ ʼ01ʼ NG SELECT * FROM Symposia.Table5 where SalesDate > {fn CONVERT(ʻ ʼ,SQL_TIMESTAMP)} 01ʼ,SQL_TIMESTAMP)} OK 11

インデックスを使わせる最近実際にあったケース Oracle からデータを SQL Gateway で移 Oracle の DATE 型は時間情報まで含むので Caché は TIMESTAMP 型に変換クエリは付を TO_DATE 関数で指定しているのでインデックスを使わない! 解決策案 1.

12 インデックスを使わせる最近実際にあったケース Oracle からデータを SQL Gateway で移 Oracle の DATE 型は時間情報まで含むので Caché は TIMESTAMP 型に変換クエリは付を TO_DATE 関数で指定しているのでインデックスを使わない! 解決策案 1. 指定を TO_DATEではなく TO_TIMESTAMPに変更 Where visitdate = TO_DATE(ʻ2011/06/03ʼ,ʼyyyy/mm/ddʼ) Where visitdate = TO_TIMESTAMP(ʻ2011/06/03ʼ,ʼyyyy/mm/ (ʻ2011/06/03ʼ,ʼyyyy/mm/ddʼ) 案 2. 時間情報はいらない場合移プログラムを作成して Caché 側でも DATE 型を利する 23 ストアド関数をうまく使う複雑な条件をもつクエリをストアド関数を使って簡略化する SELECT count(*) FROM Symposia.Table2 where T2Value like '%12%' or T2Value like '%23%' or T2Value like '%34%' or T2Value like '%45%' or T2Value like '%56%' or T2Value like '%67%' or T2Value like '%78%' or T2Value like '%89%' or T2Value like '%90%ʻ 条件に合致するストアド関数を意する SELECT count(*) FROM Symposia.Table2 where Symposia.Table2_MyFunction1(T2Value)=1 24 * ストアド関数を使う場合相対コストは余りあてにならない 12

13 25 クエリキーワード 1 SQLにキーワードを付与して動作をコントロールすることができます INSERT/UPDATE/DELETE キーワード %NOLOCK データ追加更新時にレコードのロックを実しないレコードの競合が発しないことが分かっている場合に使可能例 : 深夜のオンラインユーザ切断中のバッチ処理など %NOCHECK 外部キーの参照整合性チェックやデータの妥当性検証をわない例 : 外部システムや H/Wから出されたデータの追加など %NOINDEX インデックスを成しない全レコード追加後にまとめて %BuildIndices() メソッド等を使って分で成する必要あり %NOTRIGGER トリガを実しない 26 クエリキーワード 2 SELECT キーワード (FROM 句の後につける ) %IGNOREINDICES 指定されたインデックスを使しない SELECT * FROM %IGNOREINDICES Symposia.Table6.FlagIndex Symposia.Table6 where Flag=0 and P1 ='Aʻ ほとんどのデータでFlag=0 なのでインデックスを参照しないように指定する JOINするテーブルの順序をコントロールするキーワード %FULL %INORDER %STARTTABLE 各クエリ最適化オプションを実しないように指定するキーワード %NOFLATTEN %NOMERGE %NOSVSO %NOTOPOPT %NOUNIONOROPT 13

14 SQL パフォーマンス計測パフォーマンスを計測しよう %SYS.PTools.SQLStats クエリの実時間を測定しデータベースに保存する有効にするとクエリキャッシュ内に計測のコードが埋め込まれる設定法 Do $SYSTEM.SQL.SetSQLStats(n) 0 : 計測コード成を無効にする 1 : 計測コードを成するが測定はわない 2 : 計測コードを成しクエリの開始終了を測定する 3 : 計測コードを成しクエリの開始終了モジュール毎の時間も測定する 27 SQL パフォーマンス計測測定する項結果確認測定する項グローバル参照数コードの実数クエリモジュールの呼び出された回数クエリモジュールの実時間クエリによって返された数結果の確認 SQLStatsクラス内に定義されている View [%SYS_PTools.SQLStatsView SYS_PTools.SQLStatsView] SQLStatsView] を使するのが良い利する可能性がある場合は $SYSTEM.SQL.SetSQLStats(1) を設定しておくとよい PTools 未設定状態から有効にする場合はクエリキャッシュをパージする必要あり 28 14

SQL パフォーマンス計測結果確認クエリ 1 SELECT RoutineName, ModuleName, ModuleCount, GlobalRefs, LinesOfCode, TotalTime, RowCount, QueryType, StartTime, QueryText FROM %SYS_PTools.

15 SQL パフォーマンス計測結果確認クエリ 1 SELECT RoutineName, ModuleName, ModuleCount, GlobalRefs, LinesOfCode, TotalTime, RowCount, QueryType, StartTime, QueryText FROM %SYS_PTools.SQLStatsView WHERE Namespace= 'SYMPOSIA' 29 SQL パフォーマンス計測結果確認クエリ 2 SELECT RoutineName as クエリキャッシュ名, ModuleName as モジュール名, SUM(ModuleCount) AS クエリ実回数, AVG(TotalTime TotalTime) AS 平均実時間, SUM(TotalTime) AS 合計実時間, AVG(GlobalRefs GlobalRefs) AS 平均グローバル参照数, AVG(LinesOfCode LinesOfCode) AS 平均コード実数, QueryText as クエリテキスト FROM %SYS_PTools.SQLStatsView WHERE NameSpace = ʻSYMPOSIA' GROUP BY RoutineName, ModuleName ORDER BY 合計実時間 DESC * クエリ単位で集計し合計実時間でソートすることでシステムへの影響がきいクエリを洗い出すことができる 30 15

16 SQL パフォーマンス計測 SQLStats の注意点 Select 句の場合クエリの開始から Fetchの終了までを計測していますクライアントで Fetch 毎に処理をしている場合はその時間も含まれます = サーバでかかった時間を計測しているわけではありません 31 システムワイドの設定です 1 度クエリを実するごとに複数回レコードの更新が実されますシステムに負荷がかかります SQL パフォーマンス計測クエリパラメータが必要な場合同じクエリのパフォーマンスにきな差がある場合はクエリパラメータにパフォーマンスが依存していることが考えられますクエリパラメータは View [%SYS_PTools.Query_With_Parameters ] を参照して取得することができます (2011.1~ ~) 直接の参照関係はないので上記 View の RunTime と SQLStatsView の StartTime を較します 32 16

17 SQL パフォーマンス計測 SQL ベンチマークを実する収集したクエリ情報クエリパラメータを利して再度 SQL を順番に実することができます (2011.1~) 1 ##class(%sys.ptools.sqlbenchmarkqueries).run(ns,1) 実結果の確認 SELECT QueryId->QueryText QueryText,* FROM 33 %SYS_PTools.SQLBenchMarkResults WHERE QueryId->NameSpace='SYMPOSIAʻ * クエリ毎に結果を集計してまとめているわけではないので注意クエリ毎にるには QueryId->QueryTextQueryText 等での Group by が必要 17

平成20年度成果報告書

平成20年度成果報告書ベンチマークレポート - データグリッド Caché 編 - 平成 22 年 9 月グリッド協議会先端金融テクノロジー研究会ベンチマーク WG - i - 目次 1. CACHÉ (INTERSYSTEMS)... 1 1.1 Caché の機能概要... 1 1.2 Caché の評価結果... 2 1.2.1 ベンチマーク実行環境... 2 1.2.2 評価シナリオ: 事前テスト... 3 -