kibanaでスロークエリの確認

拡張イベントを使ってスロークエリを探す で出力されたファイルをkibanaに取り込んでスロークエリの確認をします。

kibanaのインストールはSQLServer+probespawner+Elasticsearch+kibanaの手順で行いました。

 

probespawner のjsonは添付のように設定しました。

probespawner_sql_sample.json

{
    "input": ["DatabaseInput"],
    
    "DatabaseInput": {
    "probemodule": { "module": "cooldbprobe", "name" : "DatabaseProbe" },
    "output": ["ElasticsearchOutput"],
    "interval": 60,
    "url": "jdbc:sqlserver://[対象のDBサーバ]:1433;integratedSecurity=true;databaseName=Master",
    "driverClassName": "com.microsoft.sqlserver.jdbc.SQLServerDriver",
    "username": "",
    "password": "",
    "minIdle": "2",
    "maxIdle": "2",
    "maxAge": "86400",
    "maxCycles": 1,
    "sql": [
    {
    "statement": "select CONVERT(varchar(30), xml_data.value('(/event/@timestamp)[1]','datetime') , 127) as '@timestamp'  , @@SERVERNAME as servername  , @@SERVICENAME as servicename  , xml_data.value('(event/@name)[1]', 'varchar(50)') AS class  , case when CHARINDEX('Server 2012',@@VERSION,1) > 1              OR xml_data.value('(event/@name)[1]', 'varchar(50)') <>'rpc_completed'    then xml_data.value('(/event/data[@name=''duration'']/value)[1]','bigint')/1000000.0    else xml_data.value('(/event/data[@name=''duration'']/value)[1]','bigint')/1000.0    end duration_sec  , xml_data.value('(/event/action[@name=''sql_text'']/value)[1]','varchar(max)')  sql_text  , xml_data.value('(/event/data[@name=''statement'']/value)[1]','varchar(max)')   statement  , xml_data.value('(/event/action[@name=''client_hostname'']/value)[1]','varchar(max)') hostname  , xml_data.value('(/event/action[@name=''username'']/value)[1]','varchar(max)') loginname  from   (select object_name as event, CONVERT(xml, event_data) as xml_data  from sys.fn_xe_file_target_read_file  ('G:\work\ExtendedEvents_log\slowquery*.xel', 'G:\work\ExtendedEvents_log\slowquery*.xem', null, null)  ) v  where DATEADD(hh, 9, xml_data.value('(/event/@timestamp)[1]','datetime')) >= convert(varchar(15),dateadd(MI,-20,getdate()),120)+'0:00' and  DATEADD(hh, 9, xml_data.value('(/event/@timestamp)[1]','datetime')) < convert(varchar(15),dateadd(MI,-10,getdate()),120)+'0:00'  order by '@timestamp' desc"
    }
    ]
    },
    "ElasticsearchOutput": {
      "class": "elasticsearch",
      "outputmodule": { "module": "jelh2", "name" : "Elasticsearch" },
      "cluster" : "[設定したクラスタ名]",
      "host" : "[ElasticsearchのIPアドレス]",
      "port" : 9300,
      "bulkActions": 1000,
      "bulkSize": 107374182400,
      "indexPrefix": "sqlserver",
      "indexSuffix": "@timestamp",
      "type": "jdbc",
      "index_settings": {
        "index" : {
          "analysis" : {
            "analyzer" : {
              "ana_analyzer" : {
                "type" : "standard",
                "tokenizer": "standard",
                "filter": ["standard", "asciifolding", "lowercase"]
              },
              "sortable": {
                "tokenizer": "standard",
                "filter": ["standard", "asciifolding", "lowercase"]
              }
            }
          }
        }
      },
      "type_mapping": {
        "jdbc": { 
          "properties" : { 
            "@timestamp" : { "type" : "date" },
            "servername" :  { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
            "servicename" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
            "class" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
            "sql_text" : {
              "type" : "multi_field",
              "fields" : {
                  "sql_text" : {"type" : "string", "index" : "analyzed", "analyzer": "ana_analyzer"},
                  "disp" : {"type" : "string", "index" : "not_analyzed"}
              }
            },
            "statement" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
            "hostname" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
            "loginname" : { "type" : "string", "index" : "not_analyzed", "omit_norms": "true" },
            "value" : {"type" : "double" }
          } 
        }
      }
   }
}

 

下記の場所を環境に合わせて修正してください。

8行目
“url”: “jdbc:sqlserver://[対象のDBサーバ]:1433;integratedSecurity=true;databaseName=Master”,

※Windows認証にしましたので、バッチはDBに接続できるユーザで実行します。

 

25,26行目
“cluster” : “[設定したクラスタ名]”,
“host” : “[ElasticsearchのIPアドレス]”,

 

その他

18行目のstatement句のSQL文内にスロークエリを取得する時間を指定してあります。

where句で抽出対象時間を20分前から10分前までにしています。

あまり直近時間にすると、拡張イベントのファイル出力が非同期のため、データが落ちてない場合があります。

 

取得バッチをOSのタスクに登録して、有効/無効化を切り替えたかったので

15行目の maxCyclesを1として、処理を1回で終了するようにしています。


 

取得バッチ

G:\App\probespawner-master\test.bat

cd /d  "%~dp0"

probespawner.bat probespawner_sql_sample.json

 

バッチをタスクに登録して、定期的に実行するとよいと思います。

 


取得することができました。

kibana_01

 

sql_text をanalyzedに設定したので、ある程度の文字列検索もできるようです。

kibana_02

 

表示に利用する時は、sql_text.dispを使います。

kibana_03

まだまだ勉強中なので間違っていたり、無駄な部分があるかもしれません。。。

 


管理人 has written 30 articles