SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

OMS + collectd を使用した SQL Server のパフォーマンス情報の収集

leave a comment

Windows 版の SQL Server であれば、SQL Server の各種情報をパフォーマンスモニター経由で取得 / ロギングすることができますが、Linux ではパフォーマンスモニターは使用することができないため、ほかの方法で SQL Server のパフォーマンス情報の取得を検討する必要があります。

方法の一つとして、collectd を使用して取得することができるかと。
今回は、collectd で取得した情報を OMS 連携して、パフォーマンス情報の取得を実施してみます。
詳細については、Log Analytics で Linux エージェント上の CollectD からデータを収集する を参照して下さい。

本投稿は、Ubuntu の環境で試したものとなっています。

また、collectd の SQL Server の設定については、mssql-monitoring の設定を使用しています。

構成としては、ドキュメントの次の構成を構築することになります。

最初に、OMS Agent で collectd のエンドポイントを起動するため、「/etc/opt/microsoft/omsagent/<ワークスペース ID>/conf/omsagent.d」に設定を追加します。

OMS Agent に構成ファイルのテンプレートが含まれているため、次のコマンドを実行して、構成ファイルをコピーし、OMS Agent のサービスを再起動します。

これで、OMS Agent のサービスを起動すると「TCP 26000」で collectd の HTTP のエンドポイントがリスニングされた状態となります。

sudo cp /etc/opt/microsoft/omsagent/sysconf/omsagent.d/collectd.conf /etc/opt/microsoft/omsagent/<workspace id>/conf/omsagent.d/
sudo chown omsagent:omiusers /etc/opt/microsoft/omsagent/<workspace id>/conf/omsagent.d/collectd.conf
/opt/microsoft/omsagent/bin/service_control restart  

 

次に、collectd の連携先として、OMS Agent でリスニングされた HTTP のエンドポイントを設定するため、次のコマンドを実行します。

sudo cp /etc/opt/microsoft/omsagent/sysconf/omsagent.d/oms.conf /etc/collectd/collectd.conf.d/oms.conf

これで、「write_http」プラグインを使用して、collectd の取得情報が OMS Agent の連携用のエンドポイントに出力されるようになります。

SQL Server 向けの情報については、「dbi」プラグインを使用して、「sys.dm_os_performance_counters」の情報を取得して連携することになるかと。

次のような conf ファイルを「/etc/collectd/collectd.conf.d」に配置します。

今回は「sql.conf」として配置しています。

この構成ファイルについては、https://github.com/Microsoft/mssql-monitoring/blob/master/collectd/collectd.conf の構成ファイルを元にしたものを使用しています。

TypesDB     "/usr/share/collectd/types.db.mssql"
<LoadPlugin dbi>
    Interval 15
</LoadPlugin>
<Plugin dbi>
    <Query "perfstats">
	Statement "select replace(rtrim(counter_name),' ','_') as counter_name, replace(rtrim(instance_name),' ','_') as instance_name,cntr_value from sys.dm_os_performance_counters where (counter_name in ('SQL Compilations/sec','SQL Re-Compilations/sec','User Connections','Batch Requests/sec','Logouts/sec','Logins/sec','Processes blocked','Latch Waits/sec','Full Scans/sec','Index Searches/sec','Page Splits/sec','Page Lookups/sec','Page Reads/sec','Page Writes/sec','Readahead Pages/sec','Lazy Writes/sec','Checkpoint Pages/sec','Database Cache Memory (KB)','Log Pool Memory (KB)','Optimizer Memory (KB)','SQL Cache Memory (KB)','Connection Memory (KB)','Lock Memory (KB)', 'Memory broker clerk size','Page life expectancy')) or (instance_name in ('_Total','Column store object pool') and counter_name in ('Transactions/sec','Write Transactions/sec','Log Flushes/sec','Log Flush Wait Time','Lock Timeouts/sec','Number of Deadlocks/sec','Lock Waits/sec','Latch Waits/sec','Memory broker clerk size','Log Bytes Flushed/sec','Bytes Sent to Replica/sec','Log Send Queue','Bytes Sent to Transport/sec','Sends to Replica/sec','Bytes Sent to Transport/sec','Sends to Transport/sec','Bytes Received from Replica/sec','Receives from Replica/sec','Flow Control Time (ms/sec)','Flow Control/sec','Resent Messages/sec','Redone Bytes/sec') or (object_name = 'SQLServer:Database Replica' and counter_name in ('Log Bytes Received/sec','Log Apply Pending Queue','Redone Bytes/sec','Recovery Queue','Log Apply Ready Queue') and instance_name = '_Total')) or (object_name = 'SQLServer:Database Replica' and counter_name in ('Transaction Delay'))"
	<Result>
            Type "sql_server_perf_stats"
            InstancesFrom "counter_name" "instance_name"
            ValuesFrom "cntr_value"
        </Result>
    </Query>
    <Query "waitstats">
        Statement "WITH WaitCategoryStats ( wait_category, wait_type, wait_time_ms, waiting_tasks_count, max_wait_time_ms) AS ( SELECT CASE WHEN wait_type LIKE 'LCK%' THEN 'LOCKS' WHEN wait_type LIKE 'PAGEIO%' THEN 'PAGE I/O LATCH' WHEN wait_type LIKE 'PAGELATCH%' THEN 'PAGE LATCH (non-I/O)' WHEN wait_type LIKE 'LATCH%' THEN 'LATCH (non-buffer)' WHEN wait_type LIKE 'LATCH%' THEN 'LATCH (non-buffer)' ELSE wait_type END AS wait_category, wait_type, wait_time_ms, waiting_tasks_count, max_wait_time_ms FROM sys.dm_os_wait_stats WHERE wait_type NOT IN ('LAZYWRITER_SLEEP', 'CLR_AUTO_EVENT' , 'CLR_MANUAL_EVENT', 'REQUEST_FOR_DEADLOCK_SEARCH', 'BACKUPTHREAD', 'CHECKPOINT_QUEUE' , 'EXECSYNC', 'FFT_RECOVERY', 'SNI_CRITICAL_SECTION', 'SOS_PHYS_PAGE_CACHE' , 'CXROWSET_SYNC', 'DAC_INIT', 'DIRTY_PAGE_POLL', 'PWAIT_ALL_COMPONENTS_INITIALIZED' , 'MSQL_XP', 'WAIT_FOR','DBMIRRORING_CMD','DBMIRROR_DBM_EVENT','DBMIRROR_EVENTS_QUEUE','DBMIRROR_WORKER_QUEUE', 'XE_TIMER_EVENT','XE_DISPATCHER_WAIT','WAITFOR_TASKSHUTDOWN','WAIT_FOR_RESULTS' ,'SQLTRACE_INCREMENTAL_FLUSH_SLEEP','WAITFOR' ,'QDS_CLEANUP_STALE_QUERIES_TASK_MAIN_LOOP_SLEEP','QDS_PERSIST_TASK_MAIN_LOOP_SLEEP','HADR_FILESTREAM_IOMGR_IOCOMPLETION','LOGMGR_QUEUE','FSAGENT' ) AND wait_type NOT LIKE 'PREEMPTIVE%' AND wait_type NOT LIKE 'SQLTRACE%' AND wait_type NOT LIKE 'SLEEP%' AND wait_type NOT LIKE 'FT_%' AND wait_type NOT LIKE 'XE%' AND wait_type NOT LIKE 'BROKER%' AND wait_type NOT LIKE 'DISPATCHER%' AND wait_type NOT LIKE 'PWAIT%' AND wait_type NOT LIKE 'SP_SERVER%') select  wait_category, sum(wait_time_ms) as wait_time_ms, sum(waiting_tasks_count) as waiting_tasks_count, max(max_wait_time_ms) as max_wait_time_ms from WaitCategoryStats where wait_time_ms >100 group by wait_category"
        <Result>
            Type "sql_server_wait_stats"
            InstancesFrom "wait_category"
            ValuesFrom "wait_time_ms" "waiting_tasks_count" "max_wait_time_ms"
        </Result>
    </Query>

    <Database "master">
        Host "localhost"
        Driver "freetds"
        DriverOption "host" "127.0.0.1"
        DriverOption "username" "<SQL Server Login>"
        DriverOption "password" "<Login Password>"
        Query "perfstats"
        Query "waitstats"
    </Database>
</Plugin>

元にした構成ファイルでは、「network」プラグインを使用して、Influx DB に対して連携をしていますが、今回は OMS Agent に連携されます。

最後に、types ファイルを登録するため、次の内容のファイルを「/usr/share/collectd/types.db.mssql」として、次の内容を記載したファイルを作成します。

sql_server_perf_stats	perf_stats_value:GAUGE:0:U
sql_server_wait_stats	wait_time_ms:DERIVE:0:U, waiting_tasks_count:DERIVE:0:U, max_wait_time_ms:GAUGE:0:U

 

ここまでで設定が終わったので、次のコマンドで collectd を再起動します。

systemctl restart collectd 

問題なく設定ができていれば 15 秒間隔で SQL Server にクエリが実行され、OMS にデータが連携されます。

OMS からの情報ですが、今回は、ubuntu というコンピューターから取得した Batch Request / sec の情報を次のような OMS のクエリで取得します。

Perf 
| where Computer == "ubuntu"
| where ObjectName  == "sql_server_perf_stats"
| where CounterName contains "Batch" 
| sort by TimeGenerated asc nulls last 
| extend PrevCounterValue = prev(CounterValue, 1, 0)  
| extend PrevTimeGenerated = prev(TimeGenerated, 1, 0)
| extend TimeInterval = case(
PrevCounterValue == 0, toreal(0),
round(datetime_diff("Second", TimeGenerated, PrevTimeGenerated))
)
| extend  BRPS = case(
 TimeInterval == 0 , toreal(0),
 PrevCounterValue == 0 , toreal(0),
 CounterValue - PrevCounterValue <= 0 , toreal(0), 
 (CounterValue - PrevCounterValue)
 ) 
| extend BRPS_Interval = case(
 TimeInterval == 0 , toreal(0),
 PrevCounterValue == 0 , toreal(0),
 CounterValue - PrevCounterValue <= 0 , toreal(0), 
 (CounterValue - PrevCounterValue) / TimeInterval 
 ) 
| where TimeInterval  >= 15
| project TimeGenerated_JPN = datetime_add("Hour", 9, TimeGenerated) , BRPS_Interval, 
 Computer , TimeInterval, BRPS
| render timechart

実行すると、次のようなチャートで情報を取得することができます。

image

少し手の込んだ情報の取得方法を行っていますが、これは「Batch Requests/sec」の「cntr_type」が「272696576」(PERF_COUNTER_BULK_COUNT) となっているためです。

このタイプの場合、カウンターの値が累積値となり、秒間の値に変換する場合には、取得間隔の秒数で割る必要があります。

そのため、前回取得タイミングとの秒数の差分を算出して、計算するような式で実行しています。

今回は、SQL Tiger Team の collectd の設定ファイルをそのまま使用していますが、これについては、「types.db.mssql」の設定値を「GAUGE」ではなく「DERIVE」にすることでも対応することができます。

sql_server_perf_stats  cntr_value:DERIVE:0:U    

「cntr_type=272696576」の値については、OMS のクエリ側で差分を出さなくても、collectd の構成ファイルで計算を吸収することもできるかと思います。

collectd の dbi の取得先は、Windows の環境にすることもできますので、最初は Windows の SQL Server で情報を取得して、パフォーマンスモニターの内容と比較をしながら、情報の妥当性を確認してみるとよいかもしれないですね。

Written by masayuki.ozawa

1月 20th, 2018 at 11:55 pm

Posted in SQL Server

Tagged with , ,

Leave a Reply

*