Server-side Performance Monitoring Serviceは、重要なクエリの状態を記録し、サンプリング技術を使用して毎分一般的な統計を収集するようにOptimizationされています。その結果、システムのパフォーマンスには影響を与えませんが、実行に顕著なリソースを必要とするクエリを監視するのに十分なデータを提供します。
パフォーマンス監視サービスはデフォルトで有効になっています。オフにする場合は、システム・ジョブのリスト、そのスケジュール、および のENABLE_PERFORMANCE_MONITORING
デフォルト・オプションFALSE
で「パフォーマンス・メトリクス収集タスク」を無効にします:
CALL
"SYSADMIN.setDefaultOptionValue"
(
"opt"
=>
'ENABLE_PERFORMANCE_MONITORING'
,
"val"
=>
'FALSE'
);;
オプションを有効に戻したい場合は、val
をTRUE
に設定するだけです。
CData Virtuality Server は、 SYSLOG.QueryPerformanceLog
SYSLOG.SystemPerformanceLog
の2つの主なパフォーマンステーブルをメトリックスに使用します。
SYSLOG.SystemPerformanceLog
このテーブルを使用して、CData Virtuality Server やJVM から内部的に使用されたヒープメモリ、GC パラメータ、システムパラメータなどの完全な情報を取得することができます。
Parameter | Type | Description |
---|---|---|
| biginteger | Unique identifier |
| timestamp | Data request time |
| long | Amount of virtual memory guaranteed to be available to the running process, or |
| long | Amount of free physical memory |
| long | Amount of free swap memory space |
| float | Amount (in percentage) of physical memory used by the JVM |
| float | Amount of CPU load, as a value between If the value is If the value is negative: recent CPU load is not available |
| long | CPU time used by the process the JVM is running on in nanoseconds. The returned value is of nanoseconds precision but not necessarily of the same accuracy. If the value is |
| float | CPU load of the machine running the JVM in percent of max usage. If the value is If the value is negative: recent CPU load is not available |
| float | System load average for the last minute. If the value is negative: recent system load is not available) |
| integer | Current number of live threads, including both daemon and non-daemon threads |
| integer | Current number of live daemon threads |
| long | Total amount of physical memory |
| long | Total amount of memory available to the JVM |
| long | Amount of memory the JVM is using |
| long | Total amount of swap space memory |
| long | Amount of memory the system is using |
| long | Amount of memory the system is swapping |
| long | Amount of memory committed to the Java heap |
| long | Amount of used memory on the Java heap |
| long | Amount of free memory on the Java heap |
| float | Percentage of used Java heap |
| long | Amount of memory committed to the Java non-heap |
| long | Amount of used memory on the Java non-heap |
| long | Amount of free memory on the Java non-heap |
| float | Percentage of used Java non-heap |
| long | Amount of memory committed to the Java heap Eden Space |
| long | Amount of used memory on the Java heap Eden Space |
| long | Amount of free memory on the Java heap Eden Space |
| float | Percentage of used Java heap Eden Space |
| long | Amount of memory committed to the Java heap Old (Tenured) Generation |
| long | Amount of used memory on the Java heap Old (Tenured) Generation |
| long | Amount of free memory on the Java heap Old (Tenured) Generation |
| float | Percentage of used Java heap Old (Tenured) Generation |
| long | Amount of memory committed to the Java heap Survivor Space |
| long | Amount of used memory on the Java heap Survivor Space |
| long | Amount of free memory on the Java heap Survivor Space |
| float | Percentage of used Java heap Survivor Space |
| long | Amount of memory committed to the Java non-heap Permanent Generation |
| long | Amount of used memory on the Java non-heap Permanent Generation |
| long | Amount of free memory on the Java non-heap Permanent Generation |
| float | Percentage of used Java non-heap Permanent Generation |
| long | Amount of memory committed to the Java non-heap Code Cache |
| long | Amount of used memory on the Java non-heap Code Cache |
| long | Amount of free memory on the Java non-heap Code Cache |
| float | Percentage of used Java non-heap Code Cache |
| long | Accumulated collection time spent by PS Mark Sweep Garbage Collector |
| long | Number of collections performed by PS Mark Sweep Garbage Collector |
| long | Accumulated collection time spent by PS Scavenge Garbage Collector |
| long | Total number of collections performed by PS Scavenge Garbage Collector |
| long | Accumulated collection time spent by G1 Old Generation Garbage Collector |
| long | Total number of collections performed by G1 Old Generation Garbage Collector |
| long | Accumulated collection time spent by G1 Young Generation Garbage Collector |
| long | Total number of collections performed by G1 Young Generation Garbage Collector |
| integer | Number of currently active user connections |
| integer | Number of currently active queries |
| integer | Number of query plans being processed |
| integer | Number of pending query plans |
| integer | Maximum number of query plans pending simultaneously since the last time the server started |
| integer | Current queries being executed that have surpassed the query threshold (query-threshold-in-seconds) |
| long | Disk write count for the buffer manager |
| long | Disk read count for the buffer manager |
| long | Cache write count for the buffer manager |
| long | Cache read count for the buffer manager |
| long | Amount of storage space currently used by buffer files |
| long | Estimate of the current memory usage by the buffer manager in kilobytes |
| long | Estimate of the current memory usage by active plans by the buffer manager in kilobytes |
| integer | Current number of entries in Prepared Plan cache |
| integer | Total number of requests made against Prepared Plan cache |
| float | Percentage of positive Prepared Plan cache hits |
| integer | Current number of entries in ResultSet cache |
| integer | Total number of requests made against ResultSet cache |
| float | Percentage of positive ResultSet cache hits |
SYSLOG.QueryPerformanceLog
このテーブルは、Performance Monitoringで使用されるすべてのクエリ・メトリクスを格納します。
Parameter | Type | Description |
---|---|---|
| string | Name of the user that issued the query or "system" by default |
| long | Query request index (index number within one session ID) |
| timestamp | Query starting time |
| string(128) | Query state |
| string(4096) | Query text |
| timestamp | Query updating time |
| string(128) | Request type |
| string(32) | Session id used for the query |
| long | Total buffer disk space used by the query in the CData Virtuality Server in bytes |
| long | Total buffer memory space used by the query in the CData Virtuality Server in bytes |
| long | Total buffers used by the query in the CData Virtuality Server |
| long | Total CPU time used by all queries' threads in microseconds. A JVM implementation may support measuring the CPU time for the current thread. The CData Virtuality Server queries are working within separated threads, so we can measure the CPU time between starting and finishing a query (please note that an error or a cancelled query will also have some finishing point) that is between two points of a working thread |
| float | Percentage of CPU time used by all queries' threads |
| long | Total heap memory allocated by a thread for queries in bytes |
| biginteger | Unique identifier |
クエリは、sessionId
およびrequestId
フィールド値によって一意に識別できます。1つのセッションは1つのsessionId
を持ち、内部に複数のrequestId
を持つことができます。sessionId
とrequestId
の組み合わせは一意であり、同じsessionId
とrequestId
を持つすべてのエントリは1つのクエリに属します。
SYSLOG.QueryPerformanceLog
テーブルから特定の日付より前のエントリを消去するには、dedicated stored procedure .
States and Types of Queries
States
クエリは以下のいずれかの状態を持つことができます:
State | Description |
---|---|
STARTED | The query has been just started by the CData Virtuality Server |
PROCESSING (SAMPLING) |
The SAMPLING status is being set only by the performance metrics collection task (see below) for a query with the PROCESSING state at that moment |
DONE | All the data is on the CData Virtuality Server, no cursors are open, and the request is closed |
CANCELED | Request has been cancelled at some point |
Types
クエリには以下のタイプがあります:
Type | Description |
---|---|
| Query is running in the CData Virtuality Server |
| The CData Virtuality Server is getting data from the sources. The query is running on the sources now |
Performance Metrics Collection Task
Performance Monitoring は、CData Virtuality Server で実行中のすべてのクエリのステータスがPROCESSING
の場合、特別な Performance metrics collection タスクを使用して、そのクエリからメトリクスを収集します。この場合、実行中のクエリの状態はSAMPLING
に変わります。
SAMPLING
状態のおかげで、パフォーマンス・メトリクス収集タスクは、長時間実行されるクエリと通常のクエリを区別することができます。
CData Virtuality Server は、Performance metrics collection タスクが存在しないかどうかをチェックし、ブートストラップ時に新しいタスクを自動的に作成します。
この作業で注意すべき点がいくつかあります:
- DELETEはできません;
- 並列での実行は不可;
- 1分間に1回実行されるcron式で自動作成されたスケジュールが1つ(ONLY)あります;
- スケジュールは自由に編集できます;
- 新しいスケジュールを追加すると既存のスケジュールが更新されます。
Clean Performance Log Tables Task
- 1時間おきに運行;
- パフォーマンス・ログ・テーブルから24時間以上前のエントリをすべて削除します;
- まだ存在しない場合、CData Virtuality Server が起動すると自動的にジョブが作成されます。