Data Collector
Sets
A data collector set organizes multiple counters into a single component that can be used to review or log performance. Pervasive PSQL provides the following data collector sets.
Pervasive PSQL MicroKernel Btrieve Operations
Pervasive PSQL MicroKernel Cache
Pervasive PSQL MicroKernel I/O
Pervasive PSQL MicroKernel Locks and Waits
Pervasive PSQL MicroKernel Transactions
Pervasive PSQL MicroKernel Btrieve Operations
These counters are useful for characterizing the behavior of client applications in terms of the transactional (Btrieve) interface. The counters report the types of operations being processed by the database engine at a given point in time.
Pervasive PSQL MicroKernel Cache
The database engine uses a two-level memory cache system to Table 15 Counters for MicroKernel Btrieve Operations
Counter Description Typical Use
Btrieve Close Operations per Second
The number of Btrieve Close operations per Second
To provide insight into the behavior of client applications. As a first step in
troubleshooting issues, you may find it helpful to analyze your application behavior in terms of Btrieve operations.
Btrieve Get/Step Operations per Second
The number of Btrieve Get and Step operations per second. See Btrieve API Operations in Btrieve API Guide for the various Get and Step operations. Btrieve Open Operations
per Second
The number of Btrieve Open operations per Second Btrieve Records Deleted
per Second
The number of Btrieve records deleted per second
Btrieve Records Inserted per Second
The number of Btrieve records inserted per second
Btrieve Records Updated per Second
The number of Btrieve records updated per second
Change Operations per Second
The number of Btrieve operations that modify the data files per second
Operations per Second The number of Btrieve operations executed per second Page Server Requests
Per Second
The number of page server requests received per second
Analyzing Performance
hold all of the data pages, so the database engine also uses a second level cache. Pages in the L2 Cache are stored in a compressed format, allowing for more pages to fit in memory. Consequently, it is lower performing that the L1 Cache, but higher performing than the database engine reading the pages from disk.
See also To calculate the ideal size of the database memory cache. Table 16 Counters for MicroKernel Cache
Counter Description Typical Use
Level 1 Cache Dirty Percentage
The percentage of the Level 1 Cache in use that contains dirty pages
To help determine if heavily accessed pages are continuously being forced out of the cache, which may adversely affect performance.
Dirty pages, ones with changes that have not been written to disk, may only reside in the L1 Cache. Under heavy write loads, the L1 Cache may predominately contain dirty pages. This forces pages out of the L1 Cache and into the L2 Cache, if configured, or out of the L1 Cache entirely. The database engine writes dirty pages to disk at scheduled intervals or when the L1 Cache gets close to full. Frequently writing pages to disk may also adversely affect performance.
It may benefit performance to adjust the L1 Cache size so that the percentage of dirty pages is not always high. See also Cache Allocation Size. Level 1 Cache Hits per
Second
The number of Level 1 Cache hits per second
To help determine how successfully the database engine finds requested pages in L1 Cache. A higher rate of hits-to-misses indicates that the engine is finding pages in L1 Cache rather than needing to Level 1 Cache Hit The percentage of cache hits
Level 1 Cache Usage The percentage of Level 1 Cache currently in use
To aid in adjusting the size of the L1 Cache to fit your application(s). For example, applications that use small or predominately read-only data files may not fill up the L1 Cache as configured by default. The unused memory is not available to the operating system or to other applications.
You can change the L1 Cache size to release the memory back to the operating system. Conversely, if you want to have an entire database in memory, you can monitor this value to know when the setting is as desired.
Level 2 Cache Hits per Second
The number of Level 2 Cache hits per second
To help determine how successfully the database engine finds requested pages in L2 Cache. A higher rate of hits-to-misses indicates that the engine is finding pages in L2 Cache rather than needing to access physical storage.
Level 2 Cache Hit Ratio
The percentage of cache hits to total cache access for the L2 cache
The percentage displayed applies to the life of the database engine since the MicroKernel was last started. Level 2 Cache Misses
per Second
The number of Level 2 Cache misses per second
Level 2 Cache Raw Size
The current size of the Level 2 Cache in bytes
To help determine the size of the optional L2 Cache. The L2 Cache is one component of the setting for
Max MicroKernel Memory Usage. That setting specifies the maximum proportion of total physical memory that the database engine is allowed to consume, which includes L1 Cache, L2 Cache, and all miscellaneous memory usage by the database engine.
Level 2 Cache Raw Usage
The amount of the Level 2 Cache currently in use in bytes
Table 16 Counters for MicroKernel Cache continued
Analyzing Performance
Pervasive PSQL MicroKernel I/O
The counters in this set are useful for understanding the interactions of the database engine and data read and written to physical storage. The pages reported by the counters are data file pages. These counters do not report data for pages in files used for archival logging or for transaction logging.
See also Pages in Pervasive PSQL Programmer's Guide.
Pervasive PSQL MicroKernel Locks and Waits
Client requests may be delayed by waiting for a resource to become available. These counters give insight into the types of database resources on which a client request may have to wait until the resource is available. As such, these counters may provide insight into the behavior of the database engine when multiple clients access it. A value close to or equal to the number of clients may indicate collisions for the same resources. Any corrective actions that can be done to alleviate these collisions may improve responsiveness. Table 17 Counters for MicroKernel Input/Output
Counter Description Typical Use
Pages Read per Second The number of pages read from disk per second
To determine the interaction of the database engine and data read and written to physical storage.
Pages Written per Second The number of pages written to disk per second
Table 18 Counters for MicroKernel Locks and Waits
Counter Description Typical Use
Client Record Locks The number of records explicitly locked by clients
To provide insight into the work load of client applications.
Waits on Active Reader Lock
The number of clients waiting on the Active Reader Lock. Multiple clients may hold the Active Reader Lock at the same time; however, the Active Reader Lock and the Active Writer Lock are exclusive. Consequently, a single client that holds the Active Reader Lock prevents any client from obtaining the Active Writer Lock. A single client that holds the Active Writer Lock prevents multiple clients from obtaining the Active Reader Lock. Each file has its own reader (and writer) lock.
See also Waits on Active Writer Lock counter.
Waits on Active Writer Lock The number of clients waiting on the Active Writer Lock. Only one client may hold the Active Writer Lock for a file at a time. Each file has its own writer (and reader) lock.
See also Waits on Active Reader Lock counter.
Waits on File Locks The number of clients currently waiting on a file lock
Analyzing Performance
Waits on Page Buffers The number of clients waiting on a page buffer to become available. If a page is not available to service a request, the request blocks until the MicroKernel is able to make a page available.
To indicate whether or not the database engine has a page buffer available in the cache. Use this value along with the memory cache counters to decide if the caches are sized appropriately for the work load. Increasing the cache size will increase the total number of available pages, which can reduce the waits on page buffers.
Three things may cause this value to spike when pages are not in cache:
• a data file was recently opened • first time or infrequent access of a data
page
• the caches may be too small to contain all of the pages frequently accessed and modified.
The spike for the first two items cannot be avoided because of accessing a file for the first time. The third item can be avoided by using a larger cache. If the caches are full and the cache misses are high, it is possible that the caches may be too small to contain all the pages frequently accessed and modified.
See also Counters for MicroKernel Cache. Waits on Page Locks The number of clients currently
waiting on page locks
To provide insight into the work load of client applications.
Table 18 Counters for MicroKernel Locks and Waits continued
transactions that involve many changes cause a different behavior than many short-lived transactions.
See also Begin Transaction (19 or 1019), End Transaction (20), and
Abort Transaction (21) in Btrieve API Guide, and Transactional Interface Fundamentals in Pervasive PSQL Programmer's Guide.