powa-team/pg_stat_kcache

Gather statistics about physical disk access and CPU consumption done by backends.

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

From PGDG repositories

If you installed PostgreSQL from the PGDG repositories (either APT on Debian/Ubuntu <https://apt.postgresql.org> or YUM on RHEL/Rocky <https://yum.postgresql.org), the recommended way to install pg_stat_kcache is to get it from the same repositories.

For Debian/Ubuntu:

apt install postgresql-XY-pg-stat-kcache

and RHEL/Rocky:

yum install pg_stat_kcacheXY

or for PostgreSQL 11 and above:

yum install pg_stat_kcache_XY

(where XY is your major PostgreSQL version)

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;

Configuration

The following GUCs can be configured, in postgresql.conf:

• pg_stat_kcache.linux_hz (int, default -1): informs pg_stat_kcache of the linux CONFIG_HZ config option. This is used by pg_stat_kcache to compensate for sampling errors. The default value is -1, tries to guess it at startup.
• pg_stat_kcache.track (enum, default top): controls which statements are tracked by pg_stat_kcache. Specify top to track top-level statements (those issued directly by clients), all to also track nested statements (such as statements invoked within functions), or none to disable statement statistics collection.
• pg_stat_kcache.track_planning (bool, default off): controls whether planning operations and duration are tracked by pg_stat_kcache (requires PostgreSQL 13 or above).

Usage

pg_stat_kcache create several objects.

pg_stat_kcache view

Name	Type	Description
datname	name	Name of the database
plan_user_time	double precision	User CPU time used planning statements in this database, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_system_time	double precision	System CPU time used planning statements in this database, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_minflts	bigint	Number of page reclaims (soft page faults) planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_majflts	bigint	Number of page faults (hard page faults) planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nswaps	bigint	Number of swaps planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_reads	bigint	Number of bytes read by the filesystem layer planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_reads_blks	bigint	Number of 8K blocks read by the filesystem layer planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_writes	bigint	Number of bytes written by the filesystem layer planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_writes_blks	bigint	Number of 8K blocks written by the filesystem layer planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_msgsnds	bigint	Number of IPC messages sent planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_msgrcvs	bigint	Number of IPC messages received planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nsignals	bigint	Number of signals received planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nvcsws	bigint	Number of voluntary context switches planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nivcsws	bigint	Number of involuntary context switches planning statements in this database (if pg_stat_kcache.track_planning is enabled, otherwise zero)
exec_user_time	double precision	User CPU time used executing statements in this database, in seconds and milliseconds
exec_system_time	double precision	System CPU time used executing statements in this database, in seconds and milliseconds
exec_minflts	bigint	Number of page reclaims (soft page faults) executing statements in this database
exec_majflts	bigint	Number of page faults (hard page faults) executing statements in this database
exec_nswaps	bigint	Number of swaps executing statements in this database
exec_reads	bigint	Number of bytes read by the filesystem layer executing statements in this database
exec_reads_blks	bigint	Number of 8K blocks read by the filesystem layer executing statements in this database
exec_writes	bigint	Number of bytes written by the filesystem layer executing statements in this database
exec_writes_blks	bigint	Number of 8K blocks written by the filesystem layer executing statements in this database
exec_msgsnds	bigint	Number of IPC messages sent executing statements in this database
exec_msgrcvs	bigint	Number of IPC messages received executing statements in this database
exec_nsignals	bigint	Number of signals received executing statements in this database
exec_nvcsws	bigint	Number of voluntary context switches executing statements in this database
exec_nivcsws	bigint	Number of involuntary context switches executing statements in this database

pg_stat_kcache_detail view

Name	Type	Description
query	text	Query text
top	bool	True if the statement is top-level
datname	name	Database name
rolname	name	Role name
plan_user_time	double precision	User CPU time used planning the statement, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_system_time	double precision	System CPU time used planning the statement, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_minflts	bigint	Number of page reclaims (soft page faults) planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_majflts	bigint	Number of page faults (hard page faults) planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nswaps	bigint	Number of swaps planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_reads	bigint	Number of bytes read by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_reads_blks	bigint	Number of 8K blocks read by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_writes	bigint	Number of bytes written by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_writes_blks	bigint	Number of 8K blocks written by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_msgsnds	bigint	Number of IPC messages sent planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_msgrcvs	bigint	Number of IPC messages received planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nsignals	bigint	Number of signals received planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nvcsws	bigint	Number of voluntary context switches planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nivcsws	bigint	Number of involuntary context switches planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
exec_user_time	double precision	User CPU time used executing the statement, in seconds and milliseconds
exec_system_time	double precision	System CPU time used executing the statement, in seconds and milliseconds
exec_minflts	bigint	Number of page reclaims (soft page faults) executing the statements
exec_majflts	bigint	Number of page faults (hard page faults) executing the statements
exec_nswaps	bigint	Number of swaps executing the statements
exec_reads	bigint	Number of bytes read by the filesystem layer executing the statements
exec_reads_blks	bigint	Number of 8K blocks read by the filesystem layer executing the statements
exec_writes	bigint	Number of bytes written by the filesystem layer executing the statements
exec_writes_blks	bigint	Number of 8K blocks written by the filesystem layer executing the statements
exec_msgsnds	bigint	Number of IPC messages sent executing the statements
exec_msgrcvs	bigint	Number of IPC messages received executing the statements
exec_nsignals	bigint	Number of signals received executing the statements
exec_nvcsws	bigint	Number of voluntary context switches executing the statements
exec_nivcsws	bigint	Number of involuntary context switches executing the statements

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
top	bool	True if the statement is top-level
userid	oid	Database OID
dbid	oid	Database OID
plan_user_time	double precision	User CPU time used planning the statement, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_system_time	double precision	System CPU time used planning the statement, in seconds and milliseconds (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_minflts	bigint	Number of page reclaims (soft page faults) planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_majflts	bigint	Number of page faults (hard page faults) planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nswaps	bigint	Number of swaps planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_reads	bigint	Number of bytes read by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_reads_blks	bigint	Number of 8K blocks read by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_writes	bigint	Number of bytes written by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_writes_blks	bigint	Number of 8K blocks written by the filesystem layer planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_msgsnds	bigint	Number of IPC messages sent planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_msgrcvs	bigint	Number of IPC messages received planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nsignals	bigint	Number of signals received planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nvcsws	bigint	Number of voluntary context switches planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
plan_nivcsws	bigint	Number of involuntary context switches planning the statement (if pg_stat_kcache.track_planning is enabled, otherwise zero)
exec_user_time	double precision	User CPU time used executing the statement, in seconds and milliseconds
exec_system_time	double precision	System CPU time used executing the statement, in seconds and milliseconds
exec_minflts	bigint	Number of page reclaims (soft page faults) executing the statements
exec_majflts	bigint	Number of page faults (hard page faults) executing the statements
exec_nswaps	bigint	Number of swaps executing the statements
exec_reads	bigint	Number of bytes read by the filesystem layer executing the statements
exec_reads_blks	bigint	Number of 8K blocks read by the filesystem layer executing the statements
exec_writes	bigint	Number of bytes written by the filesystem layer executing the statements
exec_writes_blks	bigint	Number of 8K blocks written by the filesystem layer executing the statements
exec_msgsnds	bigint	Number of IPC messages sent executing the statements
exec_msgrcvs	bigint	Number of IPC messages received executing the statements
exec_nsignals	bigint	Number of signals received executing the statements
exec_nvcsws	bigint	Number of voluntary context switches executing the statements
exec_nivcsws	bigint	Number of involuntary context switches executing the statements

Updating the extension

Note that a PostgreSQL restart is required for changes other than than SQL objects. Most of the new code will be enabled as soon as the restart is done, whether or not the extension is updated, as the extension only takes care of exposing the internal data structure in SQL.

Please also note that when the set-returning function fields are changes, a PostgreSQL restart is required to load the new version of the extension. Until the restart is done, updating the extension will fail with messages similar to:

could not find function "pg_stat_kcache_2_2" in file .../pg_stat_kcache.so

Bugs and limitations

No known bugs.

Tracking planner resources usage requires PostgreSQL 13 or above.

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.

If pg_stat_kcache.track is all, pg_stat_kcache tracks nested statements. The max number of nesting level that will be tracked is is limited to 64, in order to keep implementation simple, but this should be enough for reasonable use cases.

Even if pg_stat_kcache.track is all, pg_stat_kcache view considers only statistics of top-level statements. So, there is the case which even though user cpu time used planning a nested statement is high, plan_user_time of pg_stat_kcache view is small. In such a case, user cpu time used planning a nested statement is counted in exec_user_time.

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.

Copyright (c) 2014-2017, Dalibo Copyright (c) 2018-2022, The PoWA-team

pg_stat_kcache's Issues

Overhead from the pg_stat_kcache extension

Please take a look at the following results of the synthetic (read-only) pgbench test
Which we run on servers: c3-standard-176 (176 vCPU Intel, 704 GB Memory) and c3d-standard-360 (360 vCPU AMD, 1440 GB Memory).

We observe the degradation with over 100 clients:

[c1|workload_pgbench] 2024-01-05 04:03:30 tps = 12177
[c50|workload_pgbench] 2024-01-05 04:09:01 tps = 565902
[c100|workload_pgbench] 2024-01-05 04:14:34 tps = 696601
[c150|workload_pgbench] 2024-01-05 04:20:07 tps = 402113
[c200|workload_pgbench] 2024-01-05 04:25:40 tps = 314192
[c250|workload_pgbench] 2024-01-05 04:31:14 tps = 289959
[c300|workload_pgbench] 2024-01-05 04:36:47 tps = 281305
[c350|workload_pgbench] 2024-01-05 04:42:20 tps = 285510
[c400|workload_pgbench] 2024-01-05 04:47:53 tps = 277419

Analyzing the expectation event profile (based on pg_wait_sampling), we see how the number of LWLock:pg_stat_kcache wait events increases with an increase in the number of clients, until eventually pg_stat_kcache becomes the TOP-1 event in the profile.

1 client:

• No 'pg_stat_kcache' wait event

50 clients:

• 'pg_stat_kcache' wait event is present

100 clients:

• 'pg_stat_kcache' wait event in TOP-5

150 clients:

• 'pg_stat_kcache' wait event is TOP-1

In the attachment you will find artifacts including settings, postgres stats, logs, and more:

• ARTIFACTS.zip

Rusage of parallel query workers seem not be counted

Thanks for developing this useful extension!
I was searching a way to track CPU usage on PostgreSQL, and this seems what I want.

I'm a newbie for this extension, so I may misunderstand something, but when I run parallel query, pg_stat_kcache_detail view seems only count for the leader process and ignore workers.

Here is an example.

Query wih 5 workers:

=# SELECT pg_stat_kcache_reset();
=# set max_parallel_workers_per_gather to 5;

=# SELECT query, user_time FROM pg_stat_kcache_detail WHERE query LIKE '%MAX%';
-[ RECORD 1 ]--------------------------------------
query       | SELECT MAX(bid) FROM pgbench_accounts
user_time   | 1.1822049999999997

Query wihout workers:

=# SELECT pg_stat_kcache_reset();
=# set max_parallel_workers_per_gather to 0;

=# SELECT query, user_time FROM pg_stat_kcache_detail WHERE query LIKE '%MAX%';
[ RECORD 1 ]--------------------------------------
query       | SELECT MAX(bid) FROM pgbench_accounts
user_time   | 5.66161

Even more, when I turn parallel_leader_participation to off, 'user_time' was nealy zero.

Is this behavior intentional or not?
If not, is there any plan to summing up worker rusages?

As far as I investigated, queryId of parallel workers are assigned to 0, so their rusage are not summed up to the original query.

I once thought calculating queryId in executor_hook on workers, but the pg_stat_statements does not expose queryId calculation functions(JumbleQuery()).

Considering that parallel aware query information like BufferUsage and WalUsage are summed up in accumulation functions in PostgreSQL-core (InstrAccumParallelQuery()), I suspect it might hard to do it in extensions.

Thanks.

unknown type name «LWLock»

OS: Centos 7
DB: PostgreSQL 12

Hi! I am getting error while trying to compile extension. Installed pg_config from postgresql-devel.
When I do "make", C errors occur. Compiler can't find type LWLock, and then it tells that function arguments does not match.
Command line:

[admin@localhost pg_stat_kcache-REL2_2_1]$ make
gcc -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -DLINUX_OOM_SCORE_ADJ=0 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -fPIC -I. -I. -I/usr/include/pgsql/server -I/usr/include/pgsql/internal -D_GNU_SOURCE -I/usr/include/libxml2   -c -o pg_stat_kcache.o pg_stat_kcache.c
pg_stat_kcache.c:130:2: ошибка: unknown type name «LWLock»
  LWLock    *lock;     /* protects hashtable search/modification */
  ^
...
...
pg_stat_kcache.c:1297:2: ошибка: несовместимый тип аргумента 1 функции «LWLockRelease»
  LWLockRelease(pgsk->lock);

  ^
In file included from /usr/include/pgsql/server/storage/lock.h:18:0,
                 from /usr/include/pgsql/server/access/genam.h:20,
                 from /usr/include/pgsql/server/access/hash.h:20,
                 from pg_stat_kcache.c:29:
/usr/include/pgsql/server/storage/lwlock.h:112:13: замечание: expected «LWLockId» but argument is of type «int *»
 extern void LWLockRelease(LWLockId lockid);
             ^
make: *** [pg_stat_kcache.o] Ошибка 1

Attached two files, full text of make errors and pg_config.

make_error.txt
pg_config.txt

unable to run make pg_stat_kcache in Postgres @Mac

root@Amits-MacBook-Pro extension # git clone https://github.com/powa-team/pg_stat_kcache.git
Cloning into 'pg_stat_kcache'...
remote: Enumerating objects: 577, done.
remote: Counting objects: 100% (104/104), done.
remote: Compressing objects: 100% (54/54), done.
remote: Total 577 (delta 64), reused 75 (delta 48), pack-reused 473
Receiving objects: 100% (577/577), 157.33 KiB | 1.03 MiB/s, done.
Resolving deltas: 100% (341/341), done.

root@Amits-MacBook-Pro extension # cd pg_stat_kcache
root@Amits-MacBook-Pro pg_stat_kcache # make
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Werror=unguarded-availability-new -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -g -isysroot /Library/Developer/CommandLineTools/SDKs/MacOSX11.sdk -mmacosx-version-min=10.15 -arch x86_64 -arch arm64 -O2 -I. -I./ -I/Library/PostgreSQL/15/include/postgresql/server -I/Library/PostgreSQL/15/include/postgresql/internal -I/opt/local/Current_v15/include -isysroot /Library/Developer/CommandLineTools/SDKs/MacOSX11.3.sdk -I/opt/local/20220418/include/libxml2 -I/opt/local/Current_v15/include -I/opt/local/Current_v15/include -I/opt/local/Current_v15/include/libxml2 -I/opt/local/Current_v15/include -I/opt/local/Current_v15/include/openssl/ -c -o pg_stat_kcache.o pg_stat_kcache.c
clang: warning: no such sysroot directory: '/Library/Developer/CommandLineTools/SDKs/MacOSX11.3.sdk' [-Wmissing-sysroot]
clang: warning: no such sysroot directory: '/Library/Developer/CommandLineTools/SDKs/MacOSX11.3.sdk' [-Wmissing-sysroot]
In file included from pg_stat_kcache.c:16:
In file included from /Library/PostgreSQL/15/include/postgresql/server/postgres.h:46:
/Library/PostgreSQL/15/include/postgresql/server/c.h:59:10: fatal error: 'stdio.h' file not found
#include <stdio.h>
^~~~~~~~~
1 error generated.
make: *** [pg_stat_kcache.o] Error 1

unable to use it on pg11 beta3

When will it be usable on PG11? I get errors trying to use it on pg 11 beta 3, but it installs fine on pg10.
pgxn install pg_stat_kcache --pg_config /usr/pgsql-11/bin/pg_config

INFO: best version: pg_stat_kcache 2.1.1
INFO: saving /tmp/tmpdx5LMc/pg_stat_kcache-2.1.1.zip
INFO: unpacking: /tmp/tmpdx5LMc/pg_stat_kcache-2.1.1.zip
INFO: building extension
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include  -c -o pg_stat_kcache.o pg_stat_kcache.c
gcc -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -shared -o pg_stat_kcache.so pg_stat_kcache.o -L/usr/pgsql-11/lib  -Wl,--as-needed -L/usr/lib64/llvm5.0/lib  -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-11/lib',--enable-new-dtags
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2  -I. -I./ -I/usr/pgsql-11/include/server -I/usr/pgsql-11/include/internal  -D_GNU_SOURCE -I/usr/include/libxml2  -I/usr/include -flto=thin -emit-llvm -c -o pg_stat_kcache.bc pg_stat_kcache.c
gmake: /opt/rh/llvm-toolset-7/root/usr/bin/clang: Command not found
gmake: *** [pg_stat_kcache.bc] Error 127
ERROR: command returned 2: gmake PG_CONFIG=/usr/pgsql-11/bin/pg_config all

pg_stat_statements must be loaded via shared_preload_libraries

Hi,
I'm trying to use the extension pg_stat_kcache but i have an error in powa-web ==>Test_remote_NAMESRV-Training: Error while calling public.powa_statements_src: ERREUR: pg_stat_statements must be loaded via shared_preload_libraries CONTEXT: fonction PL/pgsql powa_statements_src(integer), ligne 4 à RETURN QUERY
My postgresql.conf is configured==>

shared_preload_libraries='pg_stat_statements,pg_qualstats,pg_stat_kcache,hypopg,pg_track_settings,powa,'
pg_stat_statements.max = 10000
pg_stat_statements.track = all

When I try to add the extension, I have this error =>

CREATE EXTENSION pg_stat_kcache;

ERROR:  ERREUR:  This module can only be loaded via shared_preload_libraries
État SQL :XX000

I use last git pg_stat_kcache with postgresql 9.4 on ubuntu 16

Thanks for help

pg 15 compatibility

shared_preload_libraries = '...,pg_stat_kcache,...'

pg_ctl -w start
waiting for server to start....2022-07-01 06:45:51.050 UTC [190] LOG:  Auto detecting pg_stat_kcache.linux_hz parameter...
2022-07-01 06:45:51.050 UTC [190] LOG:  pg_stat_kcache.linux_hz is set to 1000000
2022-07-01 06:45:51.051 UTC [190] FATAL:  cannot request additional shared memory outside shmem_request_hook
2022-07-01 06:45:51.051 UTC [190] LOG:  database system is shut down
pg_ctl: could not start server

Change query ID from 32 bits to 64 bits

query ID is now on 64 bits (to avoid collisions). From commit cff440d368 :

This takes advantage of the infrastructure introduced by commit
81c5e46c490e2426db243eada186995da5bb0ba7 to greatly reduce the
likelihood that two different queries will end up with the same query
ID. It's still possible, of course, but whereas before it the chances
of a collision reached 25% around 50,000 queries, it will now take
more than 3 billion queries.

Backward incompatibility: Because the type exposed at the SQL level is
int8, users may now see negative query IDs in the pg_stat_statements
view (and also, query IDs more than 4 billion, which was the old
limit).

Segmentation fault

On one of our systems, stat_kache triggers a segfault:

PG: 11.8, kcache: 2.1.2

(gdb) bt
#0  0x000000000085c3d7 in get_hash_entry (freelist_idx=0, hashp=0xd74d50) at dynahash.c:1322
#1  hash_search_with_hash_value (hashp=0xd74d50, keyPtr=keyPtr@entry=0x7fffffffd360, hashvalue=492298745, action=action@entry=HASH_ENTER, foundPtr=foundPtr@entry=0x7fffffffd270) at dynahash.c:1055
#2  0x000000000085c46d in hash_search (hashp=<optimized out>, keyPtr=keyPtr@entry=0x7fffffffd360, action=action@entry=HASH_ENTER, foundPtr=foundPtr@entry=0x7fffffffd270) at dynahash.c:911
#3  0x00007fffe8834c85 in pgsk_entry_alloc (key=key@entry=0x7fffffffd360) at pg_stat_kcache.c:634
#4  0x00007fffe8835640 in pgsk_entry_store (queryId=<optimized out>, counters=...) at pg_stat_kcache.c:587
#5  pgsk_ExecutorEnd (queryDesc=0xddfd40) at pg_stat_kcache.c:806
#6  0x00000000005bdb24 in PortalCleanup (portal=<optimized out>) at portalcmds.c:301
#7  0x00000000008797aa in PortalDrop (portal=0xe19970, isTopCommit=<optimized out>) at portalmem.c:499
#8  0x0000000000746008 in exec_simple_query (query_string=0xd783e0 "SELECT datname FROM pg_database WHERE datistemplate = false;") at postgres.c:1155
#9  0x00000000007472ca in PostgresMain (argc=<optimized out>, argv=argv@entry=0xdc5a90, dbname=0xdc59b8 "postgres", username=<optimized out>) at postgres.c:4193
#10 0x00000000004807f6 in BackendRun (port=0xdc3420) at postmaster.c:4364
#11 BackendStartup (port=0xdc3420) at postmaster.c:4036
#12 ServerLoop () at postmaster.c:1707
#13 0x00000000006d9fd9 in PostmasterMain (argc=argc@entry=5, argv=argv@entry=0xd72d20) at postmaster.c:1380
#14 0x000000000048167f in main (argc=5, argv=0xd72d20) at main.c:228

This happens on a hot-standby system

Write the statistics file at the right place

It should be in whichever directory stats_temp_directory points to while PostgreSQL is running.

When PostgreSQL is stopped, the file should be in global if the PostgreSQL release is less than 9.3, and in pg_stat if the release is 9.3 or bigger.

Calling `getrusage()` inside `pgsk_assign_linux_hz_check_hook()` corrupts calling stack

PostgreSQL 16devel cannot run due to following error:

LOG:  Auto detecting pg_stat_kcache.linux_hz parameter...
LOG:  pg_stat_kcache.linux_hz is set to 333333
*** stack smashing detected ***: terminated
Aborted (core dumped)

Closer looking into issue revealed the problem in calling getrusage() function inside pgsk_assign_linux_hz_check_hook(). Before the first call getrusage() we had clear stack but after - corrupted one:

(gdb) f
#0  pgsk_assign_linux_hz_check_hook (newval=0x7ffccafa41e0, extra=0x7ffccafa41e8, source=PGC_S_DEFAULT) at pg_stat_kcache.c:358
358			getrusage(RUSAGE_SELF, &myrusage);
(gdb) bt 5
#0  pgsk_assign_linux_hz_check_hook (newval=0x7ffccafa41e0, extra=0x7ffccafa41e8, source=PGC_S_DEFAULT) at pg_stat_kcache.c:358
#1  0x0000564cffeb133e in call_int_check_hook (conf=0x564d01b481d0, newval=0x7ffccafa41e0, extra=0x7ffccafa41e8, source=PGC_S_DEFAULT, elevel=15) at guc.c:6294
#2  0x0000564cffea871a in InitializeOneGUCOption (gconf=0x564d01b481d0) at guc.c:1399
#3  0x0000564cffeae3eb in define_custom_variable (variable=0x564d01b481d0) at guc.c:4469
#4  0x0000564cffeae919 in DefineCustomIntVariable (name=0x7f4410f04153 "pg_stat_kcache.linux_hz", short_desc=0x7f4410f04118 "Inform pg_stat_kcache of the linux CONFIG_HZ config option", 
    long_desc=0x7f4410f04068 "This is used by pg_stat_kcache to compensate for sampling errors in getrusage due to the kernel adhering to its ticks. The default value, -1, tries to guess it at startup. ", valueAddr=0x7f4410f082a0 <pgsk_linux_hz>, bootValue=-1, 
    minValue=-1, maxValue=2147483647, context=PGC_USERSET, flags=0, check_hook=0x7f4410f01b23 <pgsk_assign_linux_hz_check_hook>, assign_hook=0x0, show_hook=0x0) at guc.c:4669
(More stack frames follow...)
(gdb) n
359			previous_value = myrusage.ru_utime;
(gdb) bt 5
#0  pgsk_assign_linux_hz_check_hook (newval=0x7ffccafa41e0, extra=0x7ffccafa41e8, source=PGC_S_DEFAULT) at pg_stat_kcache.c:359
#1  0x0000000000000000 in ?? ()

I have not idea what might be reason of this behavior.

My environment:

• Linux 5.15.0-46-generic Ubuntu 20.04.5 LTS
• gcc (Ubuntu 9.4.0-1ubuntu1~20.04.1) 9.4.0
• pg_stat_kcache at commit 08a2f0f
• postgresql at commit 7beda8

Return inexact result if nested query is executed

Thanks for developing the useful extension.

I tried to track the nested statement's usages but I can't get them correctly.
I want to know the following behavior is expected or not and if there is any workaround.

Describe the behavior
The counter doesn't show the expected value If executing the nested statements like PL/pgSQL.

The nested statement's example is the following.

CREATE OR REPLACE FUNCTION plpgsql_cpu()
  RETURNS void AS $$
BEGIN
  -- consume too much cpu resources
  PERFORM i::text as str from generate_series(1, 10000000) as i;
  PERFORM md5(i::text) as str from generate_series(1, 10000000) as i;
END
$$ LANGUAGE plpgsql;

SELECT plpgsql_cpu();

The views of pg_stat_kcache show the following.

I expected that the top-level function's counter is accumulated, but it's not be accumulated in pg_stat_kcache_detail.
Since pg_stat_kcache is just the sum of the pg_stat_kcache_detail, the pg_stat_kcache counters might be not correct, isn't it?

postgres=# SELECT query,user_time,system_time FROM pg_stat_kcache_detail ;
-[ RECORD 2 ]-------------------------------------------------------------
query       | SELECT md5(i::text) as str from generate_series($1, $2) as i
user_time   | 10.684798
system_time | 0.395946
-[ RECORD 3 ]-------------------------------------------------------------
query       | SELECT plpgsql_cpu()
user_time   | 11.123643999999999     // If accumulated this must be bigger than 16.1(10.6 + 5.5)
system_time | 0.463917
-[ RECORD 5 ]-------------------------------------------------------------
query       | SELECT i::text as str from generate_series($1, $2) as i
user_time   | 5.515042
system_time | 0.253732

postgres=# SELECT user_time,system_time FROM pg_stat_kcache ;
-[ RECORD 1 ]----------
user_time   | 27.328341     // top-level statements and nested query might be counted in double
system_time | 1.114047

Since pg_stat_statements supports the nested query, the counter of the top-level query is accumulated.

postgres=# SELECT query,total_exec_time FROM pg_stat_statements ;
-[ RECORD 2 ]---+------------------------------------------------------------------
query           | SELECT plpgsql_cpu()
total_exec_time | 17912.331461
-[ RECORD 5 ]---+------------------------------------------------------------------
query           | SELECT i::text as str from generate_series(1, 10000000) as i
total_exec_time | 5847.201838
-[ RECORD 1 ]---+------------------------------------------------------------------
query           | SELECT md5(i::text) as str from generate_series(1, 10000000) as i
total_exec_time | 11219.154150999999

If my understanding is correct, this extension doesn't support the nested query now.

Since this extension didn't track the nested level, if a nested query is executed,
the counters of top-level statements are tracked from the start point of the last nested query in them to the endpoint of the statements.

If there is any workaround or do you have any plan to support it, please let me know.

Tested environments

• PostgreSQL: v14 devel 97b61448262eae5e1b4a631aeac63b11d902a474
• pg_stat_kcache: 1ab528f
• Ubuntu: 20.04.1 LTS (Focal Fossa)

Best regards.

Assert failed with postgres 16devel

Hi,

When I try to use pg_stat_kcache under pg16devel, here is an error.

2023-03-24 11:14:27.793 CST [258384] LOG:  Auto detecting pg_stat_kcache.linux_hz parameter...
2023-03-24 11:14:27.793 CST [258384] LOG:  pg_stat_kcache.linux_hz is set to 1000000
2023-03-24 11:14:27.793 CST [258384] LOG:  GUC (PGC_ENUM) pg_stat_kcache.track, boot_val=1, C-var=0
TRAP: failed Assert("check_GUC_init(variable)"), File: "/home/japin/Codes/postgresql/Debug/../src/backend/utils/misc/guc.c", Line: 4764, PID: 258384
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(ExceptionalCondition+0xb5)[0x555555d771eb]
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(+0x84aade)[0x555555d9eade]
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(DefineCustomEnumVariable+0xc9)[0x555555d9f3c4]
/home/japin/Codes/postgresql/Debug/pg/lib/pg_stat_kcache.so(_PG_init+0xda)[0x7ffff53dea3c]
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(+0x82bad7)[0x555555d7fad7]
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(load_file+0x43)[0x555555d7f560]
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(+0x83b199)[0x555555d8f199]
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(process_shared_preload_libraries+0x2a)[0x555555d8f2a2]
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(PostmasterMain+0xa60)[0x555555ac3be4]
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(+0x430ada)[0x555555984ada]
/lib/x86_64-linux-gnu/libc.so.6(__libc_start_main+0xf3)[0x7ffff73ec083]
/home/japin/Codes/postgresql/Debug/pg/bin/postgres(_start+0x2e)[0x55555562546e]

This is because pg16devel introduces a new function check_GUC_init in commit a73952b7.

commit a73952b795632b2cf5acada8476e7cf75857e9be
Author: Michael Paquier <[email protected]>
Date:   Mon Oct 31 13:54:23 2022 +0900

    Add check on initial and boot values when loading GUCs

    This commit adds a function to perform a cross-check between the initial
    value of the C declaration associated to a GUC and its actual boot
    value in assert-enabled builds.  The purpose of this is to prevent
    anybody reading these C declarations from being fooled by mismatched
    values before they are loaded at program startup.

    The following rules apply depending on the GUC type:
    * bool - can be false, or same as boot_val.
    * int - can be 0, or same as the boot_val.
    * real - can be 0.0, or same as the boot_val.
    * string - can be NULL, or strcmp'd equal to the boot_val.
    * enum - equal to the boot_val.

    This is done for the system as well custom GUCs loaded by external
    modules, which may require extension developers to adapt the C
    declaration of the variables used by these GUCs (testing this change
    with some of my own modules has allowed me to catch some stupid typos,
    FWIW).  This may finish by being a bad experiment depending on the
    feedbcak received, but let's see how it goes.

    Author: Peter Smith
    Reviewed-by: Nathan Bossart, Tom Lane, Michael Paquier, Justin Pryzby
    Discussion: https://postgr.es/m/CAHut+PtHE0XSfjjRQ6D4v7+dqzCw=[e-mail]

Here is a patch to fix it.

diff --git a/pg_stat_kcache.c b/pg_stat_kcache.c
index e0552ae..c40dd73 100644
--- a/pg_stat_kcache.c
+++ b/pg_stat_kcache.c
@@ -185,7 +185,7 @@ static const struct config_enum_entry pgs_track_options[] =
        {NULL, 0, false}
 };

-static int     pgsk_track;                     /* tracking level */
+static int     pgsk_track = 1;                 /* tracking level */
 #if PG_VERSION_NUM >= 130000
 static bool pgsk_track_planning;       /* whether to track planning duration */
 #endif

Missing Application ID string column for pg_stat_kcache_detail view

Can we have application id string column for pg_stat_kcache_detail view?

P.S. we need the details view bound to a logical block of code on the application side. That is the reason.

about the difference between value of auto-detected linux_hz and CONFIG_HZ

Hello,

I have little knowledge on kernel time and I may have basic misunderstanding, but I noticed that auto-detected linux_hz is different from the CONFIG_HZ.

$ cat /etc/redhat-release 
CentOS Linux release 8.1.1911 (Core) 

# grep CONFIG_HZ /boot/config-4.18.0-147.el8.x86_64 
...
CONFIG_HZ_1000=y
CONFIG_HZ=1000

$ pg_ctl start
...
2020-07-14 02:17:26.001 EDT [13688] LOG:  Auto detecting pg_stat_kcache.linux_hz parameter...
2020-07-14 02:17:26.001 EDT [13688] LOG:  pg_stat_kcache.linux_hz is set to 500000
...

I first tried this on a virtual machine, and doubted VM might be the cause.
So I also tried the same thing on a physical server, but the guessed linux_hz is far more than CONFIG_HZ.

Is this an intentional behavior?
And as far as I read the explanation of linux_hz and man time(7), it seems better to set the value of CONFIG_HZ to linux_hz, is this a right way to go?

    The software clock, HZ, and jiffies
       The  accuracy  of  various system calls that set timeouts, (e.g., select(2), sigtimedwait(2)) and measure CPU time (e.g.,
       getrusage(2)) is limited by the resolution of the software clock, a clock maintained by the kernel which measures time in
       jiffies.  The size of a jiffy is determined by the value of the kernel constant HZ.

Have an error when I include the pg_stat_kcache extension

I'm receiving the following error after I log into the Web interface:

powa_take_snapshot(0): function "powa_kcache_snapshot" failed: function pg_stat_kcache() does not exist

I've installed both the pg_stat_statements and pg_stat_kcache extension into all databases I'm monitoring. Other than this error, everything else appears to be working and the graphs are updating normally.

I'm running Postgresql 14.5, powa 4.1.4 and powa-web 4.1.4. Let me know if you need further information.

make: "/postgres_extensions/pg_stat_kcache/Makefile" line 26: Could not find

Hi,

Trying to install this on FreeBSD.

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

getting the following error:

make: "/postgres_extensions/pg_stat_kcache/Makefile" line 26: Could not find
make: Fatal errors encountered -- cannot continue
make: stopped in /postgres_extensions/pg_stat_kcache

My pg_config executes and displays the following:

BINDIR = /usr/local/bin
DOCDIR = /usr/local/share/doc/postgresql
HTMLDIR = /usr/local/share/doc/postgresql
INCLUDEDIR = /usr/local/include
PKGINCLUDEDIR = /usr/local/include/postgresql
INCLUDEDIR-SERVER = /usr/local/include/postgresql/server
LIBDIR = /usr/local/lib
PKGLIBDIR = /usr/local/lib/postgresql
LOCALEDIR = /usr/local/share/locale
MANDIR = /usr/local/man
SHAREDIR = /usr/local/share/postgresql
SYSCONFDIR = /usr/local/etc/postgresql
PGXS = /usr/local/lib/postgresql/pgxs/src/makefiles/pgxs.mk
CONFIGURE =  '--with-libraries=/usr/local/lib' '--with-includes=/usr/local/include' '--enable-thread-safety' '--disable-debug' '--without-gssapi' '--enable-nls' '--without-pam' '--with-openssl' '--prefix=/usr/local' '--localstatedir=/var' '--mandir=/usr/local/man' '--infodir=/usr/local/share/info/' '--build=amd64-portbld-freebsd12.2' 'build_alias=amd64-portbld-freebsd12.2' 'CC=cc' 'CFLAGS=-O2 -pipe  -fstack-protector-strong -fno-strict-aliasing ' 'LDFLAGS= -L/usr/local/lib -lpthread -L/usr/local/lib  -fstack-protector-strong ' 'LIBS=' 'CPPFLAGS=-I/usr/local/include' 'CXX=c++' 'CXXFLAGS=-O2 -pipe -fstack-protector-strong -fno-strict-aliasing  ' 'CPP=cpp' 'PKG_CONFIG=pkgconf' 'LDFLAGS_SL='
CC = cc
CPPFLAGS = -I/usr/local/include -I/usr/local/include -I/usr/local/include
CFLAGS = -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Werror=unguarded-availability-new -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -Wno-unused-command-line-argument -O2 -pipe  -fstack-protector-strong -fno-strict-aliasing
CFLAGS_SL = -fPIC -DPIC
LDFLAGS = -L/usr/local/lib -lpthread -L/usr/local/lib -fstack-protector-strong -L/usr/local/lib -Wl,--as-needed -Wl,-R'/usr/local/lib'
LDFLAGS_EX =
LDFLAGS_SL =
LIBS = -lpgcommon -lpgport -lintl -lssl -lcrypto -lz -lreadline -lexecinfo -lm
VERSION = PostgreSQL 14.1

Add write blocks statistic

It would be great to have the number of pages/blocks written by each database.

Cannot update either create extension on PG12.5 on CentOS "could not find function "pg_stat_kcache_2_2"

Hello
Where: Postgres 12.5 on CentOS7
What: Getting error when running ALTER EXTENSION UPDATE either CREATE EXTENTION
could not find function "pg_stat_kcache_2_2" in file "/usr/pgsql-12/lib/pg_stat_kcache.so"
How I installed it:
as root
git clone https://github.com/powa-team/pg_stat_kcache.git
cd pg_stat_kcache
make
_```
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -c -o pg_stat_kcache.o pg_stat_kcache.c
gcc -std=gnu99 -Wall -Wmissing-prototypes -Wpointer-arith -Wdeclaration-after-statement -Werror=vla -Wendif-labels -Wmissing-format-attribute -Wformat-security -fno-strict-aliasing -fwrapv -fexcess-precision=standard -O2 -g -pipe -Wall -Wp,-D_FORTIFY_SOURCE=2 -fexceptions -fstack-protector-strong --param=ssp-buffer-size=4 -grecord-gcc-switches -m64 -mtune=generic -fPIC -shared -o pg_stat_kcache.so pg_stat_kcache.o -L/usr/pgsql-12/lib -Wl,--as-needed -L/usr/lib64/llvm5.0/lib -L/usr/lib64 -Wl,--as-needed -Wl,-rpath,'/usr/pgsql-12/lib',--enable-new-dtags
/opt/rh/llvm-toolset-7/root/usr/bin/clang -Wno-ignored-attributes -fno-strict-aliasing -fwrapv -O2 -I. -I./ -I/usr/pgsql-12/include/server -I/usr/pgsql-12/include/internal -D_GNU_SOURCE -I/usr/include/libxml2 -I/usr/include -flto=thin -emit-llvm -c -o pg_stat_kcache.bc pg_stat_kcache.c

**make install**
`_/bin/mkdir -p '/usr/pgsql-12/lib'
/bin/mkdir -p '/usr/pgsql-12/share/extension'
/bin/mkdir -p '/usr/pgsql-12/share/extension'
/bin/install -c -m 755  pg_stat_kcache.so '/usr/pgsql-12/lib/pg_stat_kcache.so'
/bin/install -c -m 644 .//pg_stat_kcache.control '/usr/pgsql-12/share/extension/'
/bin/install -c -m 644 .//pg_stat_kcache--2.1.1.sql .//pg_stat_kcache--2.2.0.sql .//pg_stat_kcache--2.1.2--2.1.3.sql .//pg_stat_kcache--2.1.0.sql .//pg_stat_kcache--2.1.1--2.1.2.sql .//pg_stat_kcache--2.1.0--2.1.1.sql .//pg_stat_kcache--2.1.3--2.2.0.sql .//pg_stat_kcache--2.1.2.sql .//pg_stat_kcache--2.1.3.sql  '/usr/pgsql-12/share/extension/'
/bin/mkdir -p '/usr/pgsql-12/lib/bitcode/pg_stat_kcache'
/bin/mkdir -p '/usr/pgsql-12/lib/bitcode'/pg_stat_kcache/
/bin/install -c -m 644 pg_stat_kcache.bc '/usr/pgsql-12/lib/bitcode'/pg_stat_kcache/./
cd '/usr/pgsql-12/lib/bitcode' && /usr/lib64/llvm5.0/bin/llvm-lto -thinlto -thinlto-action=thinlink -o pg_stat_kcache.index.bc pg_stat_kcache/pg_stat_kcache.bc_`

su - postgres -c "psql -dpowa -c 'create extension pg_stat_kcache'"
**ERROR:  could not find function "pg_stat_kcache_2_2" in file "/usr/pgsql-12/lib/pg_stat_kcache.so"**

Same error when I was trying to **alter extension pg_stat_kcache update;**
`ERROR:  could not find function "pg_stat_kcache_2_2" in file "/usr/pgsql-12/lib/pg_stat_kcache.so"
powa=# select * from pg_available_extensions where name ='pg_stat_kcache';

      name      | default_version | installed_version |           comment
----------------+-----------------+-------------------+-----------------------------
 pg_stat_kcache | 2.2.0           | 2.1.3             | Kernel statistics gathering
(1 row)`

Please advice if I can provide anything else.

Thank you in advance
Andriy

Using schema for placing pg_stat_statements and pg_stat_kcache

Hello!
If I install extension pg_stat_statement into schema pgss, then I have error:

CREATE EXTENSION pg_stat_statements SCHEMA pgss;
CREATE EXTENSION pg_stat_kcache SCHEMA pgsk;
ERROR:  relation "pg_stat_statements" does not exist

But as you can see

show search_path;
                     search_path
-----------------------------------------------------
 "$user", public, pg_profile, pgsk, pgws, pgss, pgps

Is it possible to resolve it?

Collect statistics by groups of queries

Hi!
I made a patched version of pg_stat_kcache (on behalf of Yandex.Cloud). We think that added functionality can be useful for other users. Therefore, it would be great if it appeared in this repository as well. How do you look at it?

The patch allows you to collect statistics on groups of requests, which are determined by the user. Groups are formed as sets of unique key-value pairs that the user specifies in the comment line in the request.

We use this functionality to search for users who create the anomalous load on a certain resources.

Example:
The database receives requests of the form

/* sql: {name_of_sql_query} request_id: {request_id} bucket: {name_of_bucket} */ {some_sql_query};

In this case, we are only interested in statistics on sql and bucket, so we add the request_id key to the exceptions in advance.

So we can find out what type of requests was called most often over the entire interval of collecting statistics (in this configuration - one day)

> select * from pgsk_get_stats() order by query_count desc limit 1;
NOTICE:  00000: pgsk: Show stats from '2021-04-08 13:35:22.532476+03' to '2021-04-09 13:42:53.853272+03'
LOCATION:  pgsk_internal_get_stats_time_interval, pgsk_aggregated_stats.c:735
-[ RECORD 1 ]+----------------------------------------------
comment_keys | {"sql": "drop_object", "bucket": "f-web"}
query_count  | 43264
userid       | 16394
dbid         | 16607
reads        | 0
writes       | 0
user_time    | 2.081037999999943
system_time  | 0.1518120000000021
minflts      | 1101
majflts      | 0
nswaps       | 0
msgsnds      | 0
msgrcvs      | 0
nsignals     | 0
nvcsws       | 24
nivcsws      | 13
in_network   | 13791440
out_network  | 5883904

Time: 4.134 ms

Or, for example, the top by execution time in the last two hours

> select comment_keys, system_time from pgsk_get_stats_time_interval(current_timestamp - '2h'::interval, current_timestamp) order by system_time desc limit 3;
NOTICE:  00000: pgsk: Show stats from '2021-04-09 11:40:10.532476+03' to '2021-04-09 13:44:33.7397+03'
LOCATION:  pgsk_internal_get_stats_time_interval, pgsk_aggregated_stats.c:735
                            comment_keys                            |      system_time      
--------------------------------------------------------------------+-----------------------
 {"sql": "list_objects_experiment", "bucket": "mct"}            |              0.040639
 {"sql": "list_objects", "bucket": "s-compile"}                 | 0.0044680000000000275
 {"sql": "add_chunk", "bucket": "s3-test-acceptance-j8l8lx05d2-31"} | 0.0011609999999999954
(3 rows)

Time: 2.481 ms

In the postgres config file you can configure the amount of memory allocated for this statistics, the duration of interval for collecting statistics and add exceptions for keys.

Attempt to create **pg_stat_kcache** extension forcibly switch **check_function_bodies** parameter to **false**.

Attempt to create pg_stat_kcache extension forcibly switch check_function_bodies parameter to false.

    CREATE EXTENSION IF NOT EXISTS pg_stat_statements WITH SCHEMA public;
    COMMENT ON EXTENSION pg_stat_statements IS 'track execution statistics of all SQL statements executed';
    SET check_function_bodies = false;
    show check_function_bodies; *--false

    CREATE EXTENSION IF NOT EXISTS pg_stat_kcache WITH SCHEMA public;
    COMMENT ON EXTENSION pg_stat_kcache IS 'Kernel cache statistics gathering';
    show check_function_bodies; *--true

doesn't seem to work with postgres10

I tried installing this on a fresh postgresql10 install.

When I add "pg_stat_kcache" to my shared_preload_libraries, the server crashes. Has anyone been able to get it working on Postgres 10?

please reopen closed issue 15 until it is resolved.

thanks in advance

/usr/pgsql-9.6/lib/pg_stat_kcache.so: undefined symbol: RequestAddinLWLocks

I was unable to start a 9.6 cluster with this extension, I must remove it compltely.

ERROR: could not load library "/usr/pgsql-9.6/lib/pg_stat_kcache.so": /usr/pgsql-9.6/lib/pg_stat_kcache.so: undefined symbol: RequestAddinLWLocks