pg_wait_sampling
pg_wait_sampling copied to clipboard
postgrespro
can not show qeuryid
postgres=# select version(); version
PostgreSQL 12.9 on x86_64-pc-linux-gnu, compiled by gcc (GCC) 4.8.5 20150623 (Red Hat 4.8.5-44), 64-bit (1 row)
postgres=# select name,setting from pg_settings where name like 'pg_wait_sampling%'; name | setting ----------------------------------+--------- pg_wait_sampling.history_period | 10 pg_wait_sampling.history_size | 5000 pg_wait_sampling.profile_period | 10 pg_wait_sampling.profile_pid | on pg_wait_sampling.profile_queries | on (5 rows)
then did a pgbench and monitor pg_wait_sampling_current, the queryid always show zero.
Check the readme.md, when pg_wait_sampling.profile_queries=false, the queryid is zero, but in my env, the pg_wait_sampling.profile_queries=on.
postgres=# select * from pg_wait_sampling_current;
Thu 03 Nov 2022 12:18:57 AM CST (every 2s)
pid | event_type | event | queryid -------+------------+---------------------+--------- 13358 | LWLock | lock_manager | 0 13387 | Lock | transactionid | 0 13351 | Lock | transactionid | 0 13392 | LWLock | lock_manager | 0 13391 | IPC | SyncRep | 0 13399 | LWLock | lock_manager | 0 13395 | Lock | transactionid | 0 13360 | Lock | transactionid | 0 13389 | Lock | transactionid | 0 13394 | Lock | transactionid | 0 13400 | LWLock | lock_manager | 0 13355 | LWLock | lock_manager | 0
May I know how to solve this issue. Waiting for your replay. Thanks.
Hi @rudonx! Have you installed pg_stat_statements extension that is responsible for computing queryid value?
@maksm90 Thank you for your reply. I have installed pg_stat_statement. But seems it doesn't work well
Hmm, could you tell whether non-zero queryid is exposed in pg_wait_sampling_profile call?
The same problem. select * from pg_wait_sampling_history where queryid != 0 is empty
You seem to have query ids turned 'off'.
In PostgreSQL 12 and 13 query ids are turned 'on' by installing pg_stat_statements extension and adding in to shared_preload_libraries in postgresql.conf
Since PostgreSQL 14 you have [at least] two choices for turning query ids 'on'
First one is like before - install pg_stat_statements and add it to shared_preload_libraries in postgresql.conf
Second one is to set GUC parameter compute_query_id to 'on' using alter system:
ALTER SYSTEM SET compute_query_id=on;
This parameter is set to 'auto' by default, and this means that query ids will be calculated only if an extension that turns them on is installed (and added to shared_preload_libraries if needed).
You can read about it more here:
https://www.postgresql.org/docs/16/runtime-config-statistics.html#GUC-COMPUTE-QUERY-ID
Here's some demonstrations for more clarity:
postgres=# select version();
version
--------------------------------------------------------------------------------------------------------
PostgreSQL 12.18 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)
postgres=# alter system set pg_wait_sampling.history_period=60000; --needed only for demo
ALTER SYSTEM
-- reload server for changes to take effect.
postgres=# create extension pg_wait_sampling;
CREATE EXTENSION
create table test (a int, b text);
insert into test values (1, 'a'), (2, 'b'), (3, 'c');
begin;
update test set a = 500 where b = 'c';
-- commands with >> prefix should be performed in another session
>>begin;
>>update test set a = 600 where b = 'c';
>>update test set a = 400 where b = 'a';
-- wait pg_wait_sampling.history_period milliseconds until statisics are renewed
select * from pg_wait_sampling_history;
pid | ts | event_type | event | queryid
--------+-------------------------------+------------+---------------------+---------
204428 | 2024-04-18 14:32:20.309643+07 | Lock | transactionid | 0 -- this is the target row
204399 | 2024-04-18 14:32:20.309643+07 | Client | ClientRead | 0
204388 | 2024-04-18 14:32:20.309643+07 | Activity | AutoVacuumMain | 0
204391 | 2024-04-18 14:32:20.309643+07 | Activity | LogicalLauncherMain | 0
204385 | 2024-04-18 14:32:20.309643+07 | Activity | CheckpointerMain | 0
204386 | 2024-04-18 14:32:20.309643+07 | Activity | BgWriterMain | 0
204387 | 2024-04-18 14:32:20.309643+07 | Activity | WalWriterMain | 0
204428 | 2024-04-18 14:33:20.310753+07 | Lock | transactionid | 0
commit;
>>commit;
-- here we add pg_stat_statements to shared_preload_libraries offscreen
postgres=# create extension pg_stat_statements;
CREATE EXTENSION
begin;
update test set a = 500 where b = 'c';
-- commands with >> prefix should be performed in another session
>>begin;
>>update test set a = 600 where b = 'c';
>>update test set a = 400 where b = 'a';
-- wait pg_wait_sampling.history_period milliseconds until statisics are renewed
select * from pg_wait_sampling_history;
pid | ts | event_type | event | queryid
--------+-------------------------------+------------+---------------------+----------------------
207260 | 2024-04-18 14:44:05.847029+07 | Lock | transactionid | -3111441474331741455
207244 | 2024-04-18 14:44:05.847029+07 | Client | ClientRead | 0
207217 | 2024-04-18 14:44:05.847029+07 | Activity | AutoVacuumMain | 0
207220 | 2024-04-18 14:44:05.847029+07 | Activity | LogicalLauncherMain | 0
207214 | 2024-04-18 14:44:05.847029+07 | Activity | CheckpointerMain | 0
207215 | 2024-04-18 14:44:05.847029+07 | Activity | BgWriterMain | 0
207216 | 2024-04-18 14:44:05.847029+07 | Activity | WalWriterMain | 0
commit;
>>commit;
-- use compute_query_id in PostgreSQL >= 14
postgres=# select version();
version
-------------------------------------------------------------------------------------------------------
PostgreSQL 16.2 on x86_64-pc-linux-gnu, compiled by gcc (Ubuntu 11.4.0-1ubuntu1~22.04) 11.4.0, 64-bit
(1 row)
postgres=# alter system set compute_query_id=on;
ALTER SYSTEM
-- reload server for changes to take effect.
begin;
update test set a = 500 where b = 'c';
-- commands with >> prefix should be performed in another session
>>begin;
>>update test set a = 600 where b = 'c';
>>update test set a = 400 where b = 'a';
-- wait pg_wait_sampling.history_period milliseconds until statisics are renewed
select * from pg_wait_sampling_history;
pid | ts | event_type | event | queryid
--------+-------------------------------+------------+---------------------+---------------------
224298 | 2024-04-18 16:40:56.519881+07 | Lock | transactionid | 8630262565054199049
224296 | 2024-04-18 16:40:56.519881+07 | Client | ClientRead | 0
224292 | 2024-04-18 16:40:56.519881+07 | Activity | AutoVacuumMain | 0
224294 | 2024-04-18 16:40:56.519881+07 | Activity | LogicalLauncherMain | 0
224289 | 2024-04-18 16:40:56.519881+07 | Activity | BgWriterMain | 0
224291 | 2024-04-18 16:40:56.519881+07 | Activity | WalWriterMain | 0
224288 | 2024-04-18 16:40:56.519881+07 | Activity | CheckpointerMain | 0
commit;
>>commit;
It turns out that queryId is always zero for prepared statements (extended protocol). This can be reproduced with pgbench -Mprepared. Maybe that was the real reason of complaints. We'll try to fix it.
