Server-side Performance Monitoring Serviceは、重要なクエリの状態を記録し、サンプリング技術を使用して毎分一般的な統計を収集するようにOptimizationされています。その結果、システムのパフォーマンスには影響を与えませんが、実行に顕著なリソースを必要とするクエリを監視するのに十分なデータを提供します。

パフォーマンス監視サービスはデフォルトで有効になっています。オフにする場合は、システム・ジョブのリスト、そのスケジュール、および のENABLE_PERFORMANCE_MONITORINGデフォルト・オプションFALSEで「パフォーマンス・メトリクス収集タスク」を無効にします:

CALL "SYSADMIN.setDefaultOptionValue"(
"opt" => 'ENABLE_PERFORMANCE_MONITORING',
"val" => 'FALSE'
);;

オプションを有効に戻したい場合は、valTRUEに設定するだけです。

CData Virtuality Server は、 SYSLOG.QueryPerformanceLog  SYSLOG.SystemPerformanceLogの2つの主なパフォーマンステーブルをメトリックスに使用します。

SYSLOG.SystemPerformanceLog

このテーブルを使用して、CData Virtuality Server やJVM から内部的に使用されたヒープメモリ、GC パラメータ、システムパラメータなどの完全な情報を取得することができます。

Parameter

Type

Description

id

biginteger 

Unique identifier

updateTime

timestamp

Data request time

committedVirtualMemorySize

long

Amount of virtual memory guaranteed to be available to the running process, or -1 if this operation is not supported

freePhysicalMemorySize

long

Amount of free physical memory

freeSwapSpaceSize

long

Amount of free swap memory space

physicalMemoryUsageInPercent

float

Amount (in percentage) of physical memory used by the JVM

processCpuLoadInPercent

float

Amount of CPU load, as a value between 0.0 and 100.0, used by the JVM

If the value is 0.0: JVM does not use the CPU

If the value is negative: recent CPU load is not available

processCpuTime

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 -1: platform does not support this operation

systemCpuLoadInPercent

float

CPU load of the machine running the JVM in percent of max usage.

If the value is 100: machine is running on full load.

If the value is negative: recent CPU load is not available

systemLoadAverageInPercent

float

System load average for the last minute.

If the value is negative: recent system load is not available)

threadCount

integer

Current number of live threads, including both daemon and non-daemon threads

daemonThreadCount

integer

Current number of live daemon threads

totalPhysicalMemorySize

long

Total amount of physical memory

totalJVMMemorySize

long

Total amount of memory available to the JVM

usedJVMMemorySize

long

Amount of memory the JVM is using

totalSwapSpaceSize

long

Total amount of swap space memory

usedPhysicalMemorySize

long

Amount of memory the system is using

usedSwapSpaceSize

long

Amount of memory the system is swapping

committedHeapMemory

long

Amount of memory committed to the Java heap

usedHeapMemory

long

Amount of used memory on the Java heap

freeHeapMemory

long

Amount of free memory on the Java heap

heapMemoryUsageInPercent

float

Percentage of used Java heap

committedNonHeapMemory

long

Amount of memory committed to the Java non-heap

usedNonHeapMemory

long

Amount of used memory on the Java non-heap

freeNonHeapMemory

long

Amount of free memory on the Java non-heap

nonHeapMemoryUsageInPercent

float

Percentage of used Java non-heap

committedHeapEdenSpace

long

Amount of memory committed to the Java heap Eden Space

usedHeapEdenSpace

long

Amount of used memory on the Java heap Eden Space

freeHeapEdenSpace

long

Amount of free memory on the Java heap Eden Space

heapEdenSpaceUsageInPercent

float

Percentage of used Java heap Eden Space

committedHeapOldGen

long

Amount of memory committed to the Java heap Old (Tenured) Generation

usedHeapOldGen

long

Amount of used memory on the Java heap Old (Tenured) Generation

freeHeapOldGen

long

Amount of free memory on the Java heap Old (Tenured) Generation

heapOldGenUsageInPercent

float

Percentage of used Java heap Old (Tenured) Generation

committedHeapSurvivorSpace

long

Amount of memory committed to the Java heap Survivor Space

usedHeapSurvivorSpace

long

Amount of used memory on the Java heap Survivor Space

freeHeapSurvivorSpace

long

Amount of free memory on the Java heap Survivor Space

heapSurvivorSpaceUsageInPercent

float

Percentage of used Java heap Survivor Space

committedNonHeapPermGen

long

Amount of memory committed to the Java non-heap Permanent Generation

usedNonHeapPermGen

long

Amount of used memory on the Java non-heap Permanent Generation

freeNonHeapPermGen

long

Amount of free memory on the Java non-heap Permanent Generation

nonHeapPermGenUsageInPercent

float

Percentage of used Java non-heap Permanent Generation

committedNonHeapCodeCache

long

Amount of memory committed to the Java non-heap Code Cache

usedNonHeapCodeCache

long

Amount of used memory on the Java non-heap Code Cache

freeNonHeapCodeCache

long

Amount of free memory on the Java non-heap Code Cache

nonHeapCodeCacheUsageInPercent

float

Percentage of used Java non-heap Code Cache

collectionTimePSMarkSweepGC

long

Accumulated collection time spent by PS Mark Sweep Garbage Collector

collectionCountPSMarkSweepGC

long

Number of collections performed by PS Mark Sweep Garbage Collector

collectionTimePSScavengeGC

long

Accumulated collection time spent by PS Scavenge Garbage Collector

collectionCountPSScavengeGC

long

Total number of collections performed by PS Scavenge Garbage Collector

collectionTimeG1OldGenGC

long

Accumulated collection time spent by G1 Old Generation Garbage Collector

collectionCountG1OldGenGC

long

Total number of collections performed by G1 Old Generation Garbage Collector

collectionTimeG1YoungGenGC

long

Accumulated collection time spent by G1 Young Generation Garbage Collector

collectionCountG1YoungGenGC

long

Total number of collections performed by G1 Young Generation Garbage Collector

sessionCount

integer

Number of currently active user connections

queryCount

integer

Number of currently active queries

activeQueryPlanCount

integer

Number of query plans being processed

waitingQueryPlanCount

integer

Number of pending query plans

maxWaitingQueryPlanWatermark

integer

Maximum number of query plans pending simultaneously since the last time the server started

longRunningQueries

integer

Current queries being executed that have surpassed the query threshold (query-threshold-in-seconds)

bufferDiskWriteCount

long

Disk write count for the buffer manager

bufferDiskReadCount

long

Disk read count for the buffer manager

bufferCacheWriteCount

long

Cache write count for the buffer manager

bufferCacheReadCount

long

Cache read count for the buffer manager

bufferDiskSpaceUsedInMb

long

Amount of storage space currently used by buffer files

totalBufferMemoryUsageInKb

long

Estimate of the current memory usage by the buffer manager in kilobytes

totalBufferMemoryUsageByActivePlansInKb

long

Estimate of the current memory usage by active plans by the buffer manager in kilobytes

preparedPlanCacheTotalEntries

integer

Current number of entries in Prepared Plan cache

preparedPlanCacheRequestCount

integer

Total number of requests made against Prepared Plan cache

preparedPlanCacheHitRatioInPercent

float

Percentage of positive Prepared Plan cache hits

resultSetCacheTotalEntries

integer

Current number of entries in ResultSet cache

resultSetCacheRequestCount

integer

Total number of requests made against ResultSet cache

resultSetCacheHitRatioInPercent

float

Percentage of positive ResultSet cache hits

SYSLOG.QueryPerformanceLog

このテーブルは、Performance Monitoringで使用されるすべてのクエリ・メトリクスを格納します。

Parameter

Type

Description

userName

string

Name of the user that issued the query or "system" by default

requestId

long

Query request index (index number within one session ID)

startTime

timestamp

Query starting time

state

string(128)

Query state

query

string(4096)

Query text

updateTime

timestamp

Query updating time

type

string(128)

Request type

sessionId

string(32)

Session id used for the query

bufferDiskUsage

long

Total buffer disk space used by the query in the CData Virtuality Server in bytes

bufferMemoryUsage

long

Total buffer memory space used by the query in the CData Virtuality Server in bytes

totalBuffers

long

Total buffers used by the query in the CData Virtuality Server

cpuTimeInMicros

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

cpuUsageInPercent

float

Percentage of CPU time used by all queries' threads

queryHeapAllocated

long

Total heap memory allocated by a thread for queries in bytes

id

biginteger

Unique identifier

クエリは、sessionIdおよびrequestIdフィールド値によって一意に識別できます。1つのセッションは1つのsessionIdを持ち、内部に複数のrequestIdを持つことができます。sessionIdrequestIdの組み合わせは一意であり、同じsessionIdrequestIdを持つすべてのエントリは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)

  1. The query is running in the CData Virtuality Server, and the query plan is generated.

  2. The CData Virtuality Server is getting data from the sources. The query is running on the sources now.

  3. All the data is on the CData Virtuality Server, but there are still open cursors (e.g., an SQL Editor tab in the CData Virtuality Studio is open and shows the result of the query), so the request is, technically, still open.

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

REQUEST

Query is running in the CData Virtuality Server

CONNECTOR_REQUEST

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 が起動すると自動的にジョブが作成されます。