Elastic Stack を使用した SQL Server の情報取得 at SE の雑記

Elastic Stack の Logstash / Elasticsearch / Kibana を利用して、SQL Server の情報を格納 / 検索する際の基本的な構成についてまとめてみたいと思います。

いままで、やろうやろうと思いながら後回しになっていたものを最近勉強しだしたものでして。
細かな構築方法は別途まとめたいと思いますが、結構簡単に連携ができるものですね。

今回、Elastic Stack のソフトウェアは Ubuntu 16.04 にインストールをしています。
インストールについては、以下を参考にしながら apt でインストールすれば問題ないかと。
本投稿では、それぞれ 6.3 を使用しています。

これらは Java 1.8 以降を使用するため、Java についてもインストールする必要があり、SQL Server の情報を取得するために SQL Server の JDBC ドライバーについてもインストールをする必要があります。
インストールに必要なコマンドは次のような内容でしょうか。
Elasticsearch については、日本語検索を行うため、kuromoji のインストールも実施しています。
Java

apt install -y default-jre

JDBC

wget https://download.microsoft.com/download/6/C/1/6C131C1D-1F57-4014-A45A-AFFC461C19A2/sqljdbc_6.4.0.0_jpn.tar.gz
tar -xzvf sqljdbc_6.4.0.0_jpn.tar.gz
cd ./sqljdbc_6.4/jpn
cp mssql-jdbc-6.4.0.jre8.jar /usr/lib/jvm/java-8-openjdk-amd64/jre/bin

Elastic Search

wget -qO - https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add -
sudo apt-get install apt-transport-https
echo "deb https://artifacts.elastic.co/packages/6.x/apt stable main" | sudo tee -a /etc/apt/sources.list.d/elastic-6.x.list
sudo apt-get update && sudo apt-get install elasticsearch
cd /usr/share/elasticsearch/bin
bin/elasticsearch-plugin install analysis-kuromoji
systemctl enable elasticsearch
systemctl start elasticsearch

Logstash

# wget -qO - https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add -
# sudo apt-get install apt-transport-https
# echo "deb https://artifacts.elastic.co/packages/6.x/apt stable main" | sudo tee -a /etc/apt/sources.list.d/elastic-6.x.list
sudo apt-get update && sudo apt-get install logstash
systemctl enable logstash

Kibana

# wget -qO - https://artifacts.elastic.co/GPG-KEY-elasticsearch | sudo apt-key add -
# sudo apt-get install apt-transport-https
# echo "deb https://artifacts.elastic.co/packages/6.x/apt stable main" | sudo tee -a /etc/apt/sources.list.d/elastic-6.x.list
sudo apt-get update && sudo apt-get install kibana
systemctl enable kibana
systemctl start kibana

kibana については、デフォルトでは、127.0.0.1 のローカルアクセスのポートで起動しているようですので、リモートからアクセスできるようにするため、「cat /etc/kibana/kibana.yml」に次の記載を行っています。

server.host: 0.0.0.0

パッケージからインストールすると systemd の設定も行われるため、Logstash 以外は有効にし、サービスを起動した状態としています。
Logstash については、conf ファイルを配置してから起動するため、Enable だけ実行して自動起動設定の実施のみでとどめています。
Logstash の情報取得ですが、今回は次の 2 種類の情報を取得するための conf を「/etc/logstash/conf.d」に .conf として配置しています。
このディレクトリ内に配置した .conf ファイルについては、logstash の systemd を起動したタイミングで自動的に読み込まれるので、各ファイルを一つずつ logstash で起動しなくても問題ないようですので。

待ち事象の情報 (00-wait.conf)

input {
  jdbc {
    jdbc_driver_library => "/usr/lib/jvm/java-8-openjdk-amd64/jre/bin/mssql-jdbc-6.4.0.jre8.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://<サーバー名>;databaseName=master"
    jdbc_user => "ログイン"
    jdbc_password => "パスワード"
    schedule => "*/5 * * * *"
    statement => "SELECT GETDATE() AS collect_date, * FROM sys.dm_os_Wait_stats"
    tags => "00-wait"
  }
}
filter{
  if "00-wait" in [tags] {
    mutate {
      convert => ["collect_date" , "string"]
    }
    date {
      match => ["collect_date", "yyyy-MM-dd HH:mm:ss.SSS", "ISO8601"]
    }
  }
}
output{
  if "00-wait" in [tags] {
    elasticsearch {
        hosts => "localhost"
        index => "00-wait-%{+YYYYMMdd}"
        manage_template => false
      }
  }
#   stdout{}
}

クエリの情報 (00-query.conf)

input {
  jdbc {
    jdbc_driver_library => "/usr/lib/jvm/java-8-openjdk-amd64/jre/bin/mssql-jdbc-6.4.0.jre8.jar"
    jdbc_driver_class => "com.microsoft.sqlserver.jdbc.SQLServerDriver"
    jdbc_connection_string => "jdbc:sqlserver://<サーバー名>;databaseName=master"
    jdbc_user => "ログイン"
    jdbc_password => "パスワード"
    schedule => "45 09 * * *"
    statement => "exec usp_Get_Cache"
#    columns_charset => {"query_hash" => "UTF-8"}
#    columns_charset => {"query_plan_hash" => "UTF-8"}
    tags => "00-query"
  }
}
filter{
   if "00-query" in [tags] {
     mutate {
       convert => ["collect_date", "string"]
     }
     date {
       match => ["collect_date", "yyyy-MM-dd HH:mm:ss.SSS", "ISO8601"]
     }
   }
}
output{
  if  "00-query" in [tags] {
    elasticsearch {
        hosts => "localhost"
        index => "00-query-%{+YYYYMMdd}"
        manage_template => false
#        template_name => "00-query"
      }
  }
#   stdout{}
}

Elasticsearch 5.x 系は、type を使用してアウトプットの制御を実施する例が多いのですが、6.x 系でそれをやると、複数タイプのエラーになってうまく動作しなかったため、tag を使用して分岐させるようにしています。

クエリ用の Index Template としては次のようなテンプレートを適用させています。

待ち事象用テンプレート

{
  "00-wait": {
    "order": 0,
    "index_patterns": [
      "00-wait-*"
    ],
    "settings": {
      "index": {
        "number_of_shards": "1",
        "number_of_replicas": "0"
      }
    },
    "mappings": {
      "doc": {
        "properties": {
          "@timestamp": {
            "type": "date"
          },
          "@version": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "max_wait_time_ms": {
            "type": "long"
          },
          "signal_wait_time_ms": {
            "type": "long"
          },
          "tags": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "wait_time_ms": {
            "type": "long"
          },
          "wait_type": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 100
              }
            }
          },
          "waiting_tasks_count": {
            "type": "long"
          }
        }
      }
    },
    "aliases": {}
  }
}

クエリ情報用テンプレート

POST _template/00-query
{
    "index_patterns": "00-query-*",
    "settings": {
        "number_of_shards": "1",
        "number_of_replicas": "0"
    },
    "mappings": {
      "doc": {
        "properties": {
          "@timestamp": {
            "type": "date"
          },
          "@version": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "average elapsed time (ms)": {
            "type": "float"
          },
          "average logical read count": {
            "type": "long"
          },
          "average logical write": {
            "type": "long"
          },
          "average physical read count": {
            "type": "long"
          },
          "average worker time (ms)": {
            "type": "float"
          },
          "collect_date": {
            "type": "date"
          },
          "creation_time": {
            "type": "date"
          },
          "db_name": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "execution_count": {
            "type": "long"
          },
          "last_dop": {
            "type": "long"
          },
          "last_execution_time": {
            "type": "date"
          },
          "last_grant_kb": {
            "type": "long"
          },
          "last_ideal_grant_kb": {
            "type": "long"
          },
          "last_reserved_threads": {
            "type": "long"
          },
          "last_rows": {
            "type": "long"
          },
          "last_used_grant_kb": {
            "type": "long"
          },
          "max_dop": {
            "type": "long"
          },
          "max_grant_kb": {
            "type": "long"
          },
          "max_ideal_grant_kb": {
            "type": "long"
          },
          "max_reserved_threads": {
            "type": "long"
          },
          "max_rows": {
            "type": "long"
          },
          "max_used_grant_kb": {
            "type": "long"
          },
          "max_used_threads": {
            "type": "long"
          },
          "min_dop": {
            "type": "long"
          },
          "min_grant_kb": {
            "type": "long"
          },
          "min_ideal_grant_kb": {
            "type": "long"
          },
          "min_reserved_threads": {
            "type": "long"
          },
          "min_rows": {
            "type": "long"
          },
          "min_used_grant_kb": {
            "type": "long"
          },
          "min_used_threads": {
            "type": "long"
          },
          "mode": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "plan_generation_num": {
            "type": "long"
          },
          "query_hash": {
            "type": "long"
          },
          "query_plan_hash": {
            "type": "long"
          },
          "stmt_text": {
            "type": "text",
            "analyzer": "kuromoji",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 30000
              }
            }
          },
          "tags": {
            "type": "text",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 256
              }
            }
          },
          "text": {
            "type": "text",
            "analyzer": "kuromoji",
            "fields": {
              "keyword": {
                "type": "keyword",
                "ignore_above": 30000
              }
            }
          },
          "total_dop": {
            "type": "long"
          },
          "total_elapsed_time (ms)": {
            "type": "float"
          },
          "total_grant_kb": {
            "type": "long"
          },
          "total_ideal_grant_kb": {
            "type": "long"
          },
          "total_logical_reads (page)": {
            "type": "long"
          },
          "total_logical_writes (page)": {
            "type": "long"
          },
          "total_physical_reads (page)": {
            "type": "long"
          },
          "total_reserved_threads": {
            "type": "long"
          },
          "total_rows": {
            "type": "long"
          },
          "total_used_grant_kb": {
            "type": "long"
          },
          "total_used_threads": {
            "type": "long"
          },
          "total_worker_time (ms)": {
            "type": "float"
          }
        }
      }
    }
  }
}

あとは、情報取得対象の SQL Server の master データベースに次のストアドプロシージャを登録すれば準備完了です。
このストアドにより、実行したタイミングの直近 24 時間以内に実行されたクエリの中で CPU 使用時間の多いクエリの TOP 300 がElasticsearch に格納されます。

/****** Object:  StoredProcedure [dbo].[usp_Get_Cache]    Script Date: 2018/08/02 23:01:54 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_Get_Cache]
/*********************************************/
-- mode
-- 1 : 実行回数の高いクエリ
-- 2 : 平均 CPU 使用率の高いクエリ
-- 3 : 実行時間の高いクエリ
-- 4 : 平均読み取り回数の高いクエリ
-- 5 : 平均書き込み回数の高いクエリ
/*********************************************/
	@mode int =2
AS
BEGIN
SELECT TOP 300
    GETDATE() AS collect_date,
	CASE @mode
		WHEN 1 THEN 'HighExecution'
		WHEN 2 THEN 'HighAvgCPU'
		WHEN 3 THEN 'HighAvgElapsedTime'
		WHEN 4 THEN 'HighAvgRead'
		WHEN 5 THEN 'HighAvgWrite'
		ELSE 'HighExecution'
	END AS mode,
	[total_elapsed_time] / [execution_count] / 1000.0 AS [Average Elapsed Time (ms)],
	[total_worker_time]  / [execution_count] / 1000.0 AS [Average Worker Time (ms)],
	[total_physical_reads] / [execution_count] AS [Average Physical Read Count],
	[total_logical_reads] / [execution_count] AS [Average Logical Read Count],
	[total_logical_writes]  / [execution_count] AS [Average Logical Write],
	[total_elapsed_time] / 1000.0 AS [total_elapsed_time (ms)],
	[total_worker_time] / 1000.0  AS [total_worker_time (ms)],
	[total_physical_reads] AS [total_physical_reads (page)],
	[total_logical_reads] AS [total_logical_reads (page)],
	[total_logical_writes] AS [total_logical_writes (page)],
	[execution_count],
	[total_rows],
	[last_rows],
	[min_rows],
	[max_rows],
	[total_dop],
	[last_dop],
	[min_dop],
	[max_dop],
	[total_grant_kb],
	[last_grant_kb],
	[min_grant_kb],
	[max_grant_kb],
	[total_used_grant_kb],
	[last_used_grant_kb],
	[min_used_grant_kb],
	[max_used_grant_kb],
	[total_ideal_grant_kb],
	[last_ideal_grant_kb],
	[min_ideal_grant_kb],
	[max_ideal_grant_kb],
	[total_reserved_threads],
	[last_reserved_threads],
	[min_reserved_threads],
	[max_reserved_threads],
	[total_used_threads],
	[last_used_threads]
	[min_used_threads],
	[max_used_threads],
	[plan_generation_num],
	[creation_time],
	[last_execution_time],
	CAST([query_hash] AS int) AS query_hash_int,
	CAST([query_plan_hash] AS int) AS query_plan_hash_int,
	DB_NAME(st.dbid) AS db_name,
	REPLACE(REPLACE(REPLACE(SUBSTRING(text,
	([statement_start_offset] / 2) + 1,
	((CASE [statement_end_offset]
	WHEN -1 THEN DATALENGTH(text)
	ELSE [statement_end_offset]
	END - [statement_start_offset]) / 2) + 1),CHAR(13), ' '), CHAR(10), ' '), CHAR(9), ' ') AS [stmt_text],
	REPLACE(REPLACE(REPLACE([text],CHAR(13), ''), CHAR(10), ' '), CHAR(9), ' ') AS [text]
	-- ,query_plan
FROM
	[sys].[dm_exec_query_stats]
	CROSS APPLY
	[sys].[dm_exec_sql_text]([sql_handle]) AS st
	CROSS APPLY
	[sys].[dm_exec_query_plan]([plan_handle])
WHERE
	last_execution_time >= (DATEADD(day, -1, GETDATE()))
ORDER BY
	CASE @mode
		WHEN 1 THEN [execution_count]
		WHEN 2 THEN [total_worker_time]  / [execution_count] / 1000.0
		WHEN 3 THEN [total_elapsed_time] / [execution_count] / 1000.0
		WHEN 4 THEN ([total_physical_reads] / [execution_count]) + ([total_logical_reads] / [execution_count])
		WHEN 5 THEN [total_logical_writes]  / [execution_count]
		ELSE [execution_count]
	END DESC
OPTION (RECOMPILE)
END

ここまで設定できたら「systemctl start logstash」を実行して、情報の取得を開始します。
クエリの情報については conf の中で 9:45 に取得するようにしていますので、スケジュールを変更しないとすぐに取得は行われませんが、待ち事象については、5 分間隔で実行していますので、正常に設定できていれば 5 分後に情報が取得され、kibana で次のような形式のインデックスが作成されているかと。

クエリの情報については、30,000 文字に収まっているクエリであれば、インデクシングされ、全文検索ができるようになっているかと。

SE の雑記

Elastic Stack を使用した SQL Server の情報取得

Leave a Reply

検索

アーカイブ

最近の投稿

カテゴリー

2018年8月
月	火	水	木	金	土	日
		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	28	29	30	31