pg_stat_kcache: A C repository from mikecaat

pg_stat_kcache

Features

Gathers statistics about real reads and writes done by the filesystem layer. It is provided in the form of an extension for PostgreSQL >= 9.4., and requires pg_stat_statements extension to be installed. PostgreSQL 9.4 or more is required as previous version of provided pg_stat_statements didn't expose the queryid field.

Installation

Compiling

The module can be built using the standard PGXS infrastructure. For this to work, the pg_config program must be available in your $PATH. Instruction to install follows:

git clone https://github.com/powa-team/pg_stat_kcache.git
cd pg_stat_kcache
make
make install

PostgreSQL setup

The extension is now available. But, as it requires some shared memory to hold its counters, the module must be loaded at PostgreSQL startup. Thus, you must add the module to shared_preload_libraries in your postgresql.conf. You need a server restart to take the change into account. As this extension depends on pg_stat_statements, it also need to be added to shared_preload_libraries.

Add the following parameters into you postgresql.conf:

# postgresql.conf
shared_preload_libraries = 'pg_stat_statements,pg_stat_kcache'

Once your PostgreSQL cluster is restarted, you can install the extension in every database where you need to access the statistics:

mydb=# CREATE EXTENSION pg_stat_kcache;

Usage

pg_stat_kcache create several objects.

pg_stat_kcache view

Name	Type	Description
datname	name	Name of the database
user_time	double precision	User CPU time used
system_time	double precision	System CPU time used
minflts	bigint	Number of page reclaims (soft page faults)
majflts	bigint	Number of page faults (hard page faults)
nswaps	bigint	Number of swaps (unmaintained on GNU/Linux)
reads	bigint	Number of bytes read by the filesystem layer
reads_blks	bigint	Number of 8K blocks read by the filesystem layer
writes	bigint	Number of bytes written by the filesystem layer
writes_blks	bigint	Number of 8K blocks written by the filesystem layer
msgsnds	bigint	Number of IPC messages sent (unmaintained on GNU/Linux)
msgrcvs	bigint	Number of IPC messages received (unmaintained on GNU/Linux)
nsignals	bigint	Number of signals received (unmaintained on GNU/Linux)
nvcsws	bigint	Number of voluntary context switches
nivcsws	bigint	Number of involuntary context switches

pg_stat_kcache_detail view

Name	Type	Description
query	text	Query text
datname	name	Database name
rolname	name	Role name
user_time	double precision	User CPU time used
system_time	double precision	System CPU time used
minflts	bigint	Number of page reclaims (soft page faults)
majflts	bigint	Number of page faults (hard page faults)
nswaps	bigint	Number of swaps
reads	bigint	Number of bytes read by the filesystem layer
reads_blks	bigint	Number of 8K blocks read by the filesystem layer
writes	bigint	Number of bytes written by the filesystem layer
writes_blks	bigint	Number of 8K blocks written by the filesystem layer
msgsnds	bigint	Number of IPC messages sent
msgrcvs	bigint	Number of IPC messages received
nsignals	bigint	Number of signals received
nvcsws	bigint	Number of voluntary context switches
nivcsws	bigint	Number of involuntary context switches

pg_stat_kcache_reset function

Resets the statistics gathered by pg_stat_kcache. Can be called by superusers:

pg_stat_kcache_reset()

pg_stat_kcache function

This function is a set-returning functions that dumps the containt of the counters of the shared memory structure. This function is used by the pg_stat_kcache view. The function can be called by any user:

SELECT * FROM pg_stat_kcache();

It provides the following columns:

Name	Type	Description
queryid	bigint	pg_stat_statements' query identifier
userid	oid	Database OID
dbid	oid	Database OID
user_time	double precision	User CPU time used
system_time	double precision	System CPU time use
minflts	bigint	Number of page reclaims (soft page faults)
majflts	bigint	Number of page faults (hard page faults)
nswaps	bigint	Number of swaps
reads	bigint	Number of bytes read by the filesystem layer
writes	bigint	Number of bytes written by the filesystem layer
msgsnds	bigint	Number of IPC messages sent
msgrcvs	bigint	Number of IPC messages received
nsignals	bigint	Number of signals received
nvcsws	bigint	Number of voluntary context switches
nivcsws	bigint	Number of involuntary context switches

Bugs and limitations

No known bugs.

We assume that a kernel block is 512 bytes. This is true for Linux, but may not be the case for another Unix implementation.

See: http://lkml.indiana.edu/hypermail/linux/kernel/0703.2/0937.html

On platforms without a native getrusage(2), all fields except user_time and system_time will be NULL.

On platforms with a native getrusage(2), some of the fields may not be maintained. This is a platform dependent behavior, please refer to your platform getrusage(2) manual page for more details.

Authors

pg_stat_kcache is an original development from Thomas Reiss, with large portions of code inspired from pg_stat_plans. Julien Rouhaud also contributed some parts of the extension.

Thanks goes to Peter Geoghegan for providing much inspiration with pg_stat_plans so we could write this extension quite straightforward.

License

pg_stat_kcache is free software distributed under the PostgreSQL license.

mikecaat/pg_stat_kcache