Server-side Service

The server-side performance monitoring service records important query states and is optimized to collect general statistics every minute using the sampling technique. As a result, it has no impact on system performance but still provides enough data to monitor queries that require noticeable resources to perform.

The performance monitoring service is enabled by default. If you want to turn it off, disable the "Performance metrics collection task" in the list of system jobs, its schedule, and set the ENABLE_PERFORMANCE_MONITORING default option to FALSE:

SQL

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

If you want to enable the option back, just set val to TRUE.

The CData Virtuality Server uses two main performance tables for metrics: SYSLOG.QueryPerformanceLog and SYSLOG.SystemPerformanceLog.

SYSLOG.SystemPerformanceLog

You can use this table to get complete information about used heap memory, GC parameters, system parameters, and so on internally from the CData Virtuality Server and JVM.

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

This table stores all query metrics used by 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

A query can be uniquely identified by its sessionId and requestId field values. One session has one sessionId and can have multiple requestId inside; the combination of sessionId and requestId is unique, and all entries that have the same sessionId and requestId belong to one query.

To clear the SYSLOG.QueryPerformanceLog table from entries earlier than a specific date, you can use a dedicated stored procedure.

States and Types of Queries

States

A query can have one of the following states:

State	Description
STARTED	The query has been just started by the CData Virtuality Server
PROCESSING (SAMPLING)	The query is running in the CData Virtuality Server, and the query plan is generated. The CData Virtuality Server is getting data from the sources. The query is running on the sources now. 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

A query can have one of the following 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 uses a special Performance metrics collection task to collect metrics from all running queries in the CData Virtuality Server if their status is PROCESSING. In this case, the state of the running queries changes to SAMPLING.

Thanks to the SAMPLING state, the Performance metrics collection task can distinguish long-running queries from the usual ones.

The CData Virtuality Server will check if there is no Performance metrics collection task and will create a new one automatically when it is bootstrapping.

Some important points to keep in mind about this task:

It is not deletable;
Parallel runs are not allowed;
It has one (and only one) auto-created schedule with cron expression to be run once a minute;
The schedule can be edited freely;
Adding a new schedule will update the existing one.

Clean Performance Log Tables Task

Runs every hour;
Removes all entries older than 24 hours from the performance log tables;
If not yet present, the job is automatically created when the CData Virtuality Server is started.