Microsoft SQL Serverからデータベース単位のOTelメトリックを取得してみた

Microsoft SQL ServerのメトリックはMackerelのmackerel-plugin-mssql プラグインで取得できるが、総体としての統計情報は見られるものの、複数のデータベースが相乗りしているときに個々の負荷状況はわからない。

SQL Server自体のログなどを追いかけたり、トレースを並べてみたりすることで詳細はわかりそうだが、もう少し手軽に、メトリックで見られないか。

ということで、OpenTelemetryのMicrosoft SQL Server Receiverを眺めてみると、データベース単位のメトリックも出せるようだったので試してみた。

SQL Serverに対して、OpenTelemetry CollectorのMicrosoft SQL Server Receiverが情報をスクレイピングし、それをOpenTelemetry Collectorがラベル付きメトリックとしてMackerelに送るという構成になる。

確かにメトリックを取れているね。

SQL Server側の設定
OpenTelemetry Collectorの設定
負荷テストの実施
Mackerelでの見え方
まとめ

SQL Server側の設定

実験環境はSQL Server Express 2022。設定することとしては以下の3つとなる。

SQL認証の有効化：SSMS（SQL Server Management Studio）でサーバープロパティのセキュリティ項目「SQL ServerおよびWindows認証モード」を有効化する
TCP/IPポートでの待ち受け：SQL Server Configuration ManagerでSQL Server Network Configurationの「Protocols for SQLEXPRESS」の「TCP/IP」をEnabledにし、さらにIP Addressタブの最後のほうにある「IP All」の「TCP Port」に1433を指定する
SQL認証ユーザーの作成：Microsoft SQL Server Receiverのアクセスに使う認証ユーザーを作成し、認可の権限を付与する。今回はSSMSでクエリを発行した

  CREATE LOGIN otel_reader WITH PASSWORD = '《パスワード》';
  USE master;
  CREATE USER otel_reader FOR LOGIN otel_reader;
  GRANT VIEW SERVER STATE TO otel_reader;
  GRANT VIEW ANY DEFINITION TO otel_reader;

いずれも本来は強固になっているセキュリティを弱めることにはなるので、本番環境でどうするかは熟考したほうがよさそうだ。

sqlcmd -S localhost\SQLEXPRESS -U otel_reader -P 《パスワード》で接続できることを確認する。

OpenTelemetry Collectorの設定

opentelemetry-collector-releasesの最新リリースの中から「otelcol-contrib_《バージョン》_windows_amd64.tar.gz」をダウンロードして、SQL Serverと同じホストに展開する。今回は試行錯誤のためにPowerShell上でotelcol-contribを実行してはCtrl-Cで終了を繰り返しやすいようにこのアーカイブを使ったが、普通に動かすにはmsi インストーラで入れるのがよいだろう。

設定ファイルotel-col.yamlを作成した。

receivers:
  sqlserver:
    collection_interval: 60s
    computer_name: 《コンピュータ名》
    instance_name: SQLEXPRESS
    username: otel_reader
    password: 《パスワード》
    server: localhost
    port: 1433

processors:
  batch:
    timeout: 1m
  resource:
    attributes:
    - key: service.name
      value: sqlserver-metrics
      action: insert
    - key: service.namespace
      value: sqlserver-demo
      action: insert

exporters:
  debug:
    verbosity: detailed
  otlp/mackerel:
    endpoint: otlp.mackerelio.com:4317
    headers:
      Mackerel-Api-Key: "《APIキー》"

service:
  telemetry:
    metrics:
      level: none
  pipelines:
    metrics:
      receivers: [sqlserver]
      processors: [resource, batch]
      exporters: [debug, otlp/mackerel]

sqlserverがMicrosoft SQL Server Receiverの設定となる。collection_intervalで60秒ごとにスクレイプさせている。computer_nameは必須だった。はっきりわからないときにはGet-Counter '\MSSQL$SQLEXPRESS:General Statistics\User Connections'などを実行すればコンピュータ名付きで出てくる。ほかのパラメータはそのままの意味なので説明不要だろう
batch Processorでは単純に1分ごとに投稿処理させている（Mackerel用）
sqlserverは名前空間（service.namespace）やサービス名（service.name）を設定しないので、resource Processorでサービス名前空間とサービス名が空だったときのデフォルト値を指定することで代替としている
Exporterではdebug冗長出力とMackerel投稿を設定しているだけ
otelcol-contribにはPrometheus向けのテレメトリメトリックサーバーが入っており、デフォルトではこれが動いてしまうので、telemetryのメトリック設定パラメータで無効化している

otelcol-contribアーカイブを展開していたフォルダで.\otelcol-contrib --config otel-col.yamlを実行する。

これで早速スクレイピングが始まり、メトリックがわしわしと生成されていく。

負荷テストの実施

負荷テストはChatGPTに相談してみた。大量にデータを入れたテーブルを作って、そこにクエリなどをかけてはどうか、と提案された。

データベースを作成。

CREATE DATABASE mydb;
GO
USE mydb;
GO

テーブルを作成。

CREATE TABLE bigtable (
    id INT IDENTITY(1,1) PRIMARY KEY,
    name NVARCHAR(100),
    description NVARCHAR(500),
    created_at DATETIME DEFAULT GETDATE()
);
GO

100万行のデータを投入。

SET NOCOUNT ON;

INSERT INTO bigtable (name, description)
SELECT TOP (1000000)
    'item_' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR(10)),
    REPLICATE('x', 400)
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c;
GO

クエリをかけてみる。

SELECT COUNT(*)
FROM bigtable
WHERE description LIKE '%x%';

わりとすぐ返ってくるなぁ。%1x%のように存在しないものにしたら全行スキャンでちょっと重めになった。

コンテンツの全スキャン。これはさほど負荷にならなかった。

SELECT SUM(LEN(description)) FROM bigtable;

ランダムアクセス。tempdbが膨らむ。

USE mydb;
GO

-- 一時テーブルを作ってランダムにアクセス
DECLARE @i INT = 0;
WHILE @i < 50000
BEGIN
    SELECT TOP 100 *
    FROM bigtable
    WHERE id BETWEEN FLOOR(RAND() * 900000) + 1 AND FLOOR(RAND() * 900000) + 1000;
    SET @i = @i + 1;
END;
GO

一時テーブルとJOIN。これはtempdbが激しい（終わったら「DROP TABLE #tmp」してね、とのこと）。

USE mydb;
GO

SELECT a.id, b.name
INTO #tmp
FROM bigtable a
CROSS JOIN bigtable b;
GO

Mackerelでの見え方

今回の環境では6つのデータベースが存在するが、全部で61本のメトリックグラフが作られた。13＋(8×データベース数)かな。

ほとんどが即値のgauge型なので、Mackerelでのグラフも変換の必要なく利用できる（sqlserver.transaction_log.growth.countとsqlserver.transaction_log.shrunk.countだけは積み上げのsum型）。

データベース固有のグラフは以下のとおりだった。sqlserver.database.name属性にデータベース名が格納されている。

sqlserver.transaction_log.flush.data.rate
sqlserver.transaction_log.flush.rate
sqlserver.transaction_log.flush.wait.rate
sqlserver.transaction_log.growth.count
sqlserver.transaction_log.shrink.count
sqlserver.transaction_log.usage
sqlserver.transaction.rate
sqlserver.transaction.write.rate

なお、従来のmackerel-plugin-mssql プラグインだと以下のようになる（可視性はこちらのほうが良いが…）。冒頭に書いたとおり、総体としてのサーバー状況のメトリックだけになっている。ここで出ているメトリックは、OpenTelemetryメトリックでもすべてカバーされている。

まとめ

従来のプラグインでは不明だったデータベース個々の状況だが、Microsoft SQL Server Receiverを使うと、OpenTelemetryメトリックでラベルで区別して可視化できるようになった。

実際どのようなクエリでこうなったのかについてはトレースなどが必要になるものの、相乗り環境で負荷原因のデータベースを初手で見つけるのに役立つのではないだろうか。

今回は有効にしなかったが、Optional Metricsを見ると、ほかにも多数、取得可能な値があるようだ。

kmuto’s blog

はてな社でMackerel CREをやっています。料理と旅行といろんなIT技術

Microsoft SQL Serverからデータベース単位のOTelメトリックを取得してみた

SQL Server側の設定

OpenTelemetry Collectorの設定

負荷テストの実施

Mackerelでの見え方

まとめ