Skip to:

  1. Skip to Jira Navigation
  2. Skip to Side Navigation
  3. Skip to Main Content

Wrong command type for queries

Description

Hi,

In `pg_stat_monitor` the cmd_type is not right for some queries, basically any write query has the wrong type:

postgres=# select cmd_type_text,cmd_type,left(query,20) from pg_stat_monitor where query like 'UPDATE%' limit 100; cmd_type_text | cmd_type | left ---------------+----------+---------------------- | 0 | UPDATE sbtest2 SET k | 0 | UPDATE sbtest1 SET k | 0 | UPDATE sbtest2 SET c | 0 | UPDATE sbtest1 SET c | 0 | UPDATE sbtest2 SET k | 0 | UPDATE sbtest1 SET c | 0 | UPDATE sbtest2 SET c | 0 | UPDATE sbtest1 SET k | 0 | UPDATE sbtest2 SET c

 

postgres=# select cmd_type_text,cmd_type,left(query,20) from pg_stat_monitor where query like 'DELETE%' limit 100; cmd_type_text | cmd_type | left ---------------+----------+---------------------- | 0 | DELETE FROM sbtest1 | 0 | DELETE FROM sbtest2 | 0 | DELETE FROM sbtest1 | 0 | DELETE FROM sbtest2 | 0 | DELETE FROM sbtest1 | 0 | DELETE FROM sbtest2 | 0 | DELETE FROM sbtest2 | 0 | DELETE FROM sbtest1

 

I tested this on Everest with the latest postgres operator. I simple ran the following sysbench command:

sysbench --db-driver=pgsql --table_size=100000 --tables=2 --threads=10 --pgsql-host=127.0.0.1 --pgsql-port=5432 --pgsql-user=postgres --pgsql-password="XXXXX" --pgsql-db=sbtest --report-interval=1 --time=0 /opt/homebrew/opt/sysbench/share/sysbench/oltp_read_write.lua run

Because cmd_type is not reported correctly, PMM is also collecting wrong metrics.

Environment

None

Attachments

2
  • 11 Mar 2025, 01:43 PM
  • 11 Mar 2025, 01:43 PM

Activity

Show:

Tibor Korocz (Percona) 5 days ago
Edited

I might found something, if I disable prepared statements is sysbench--db-ps-mode=disable I can see the right cmd_type in the pg_stat_monitor

sbtest=# select cmd_type_text,cmd_type,left(query,20) from pg_stat_monitor where LOWER(query) like LOWER('delete%') or LOWER(query) like LOWER('insert%') or LOWER(query) like LOWER('insert%') limit 100; cmd_type_text | cmd_type | left ---------------+----------+---------------------- | 0 | INSERT INTO sbtest1 DELETE | 4 | DELETE FROM sbtest2 INSERT | 3 | INSERT INTO sbtest2 | 0 | INSERT INTO sbtest1 DELETE | 4 | DELETE FROM sbtest1 DELETE | 4 | DELETE FROM sbtest1 | 0 | INSERT INTO sbtest2 INSERT | 3 | INSERT INTO sbtest1

I try to manually reproduce it and I created a prepared statement but when I execute that in the pg_stat_monitorI see the prepared statement like:

cmd_type_text | cmd_type | left ---------------+----------+------------------------------------------------------------------------------------------------------------------ | 0 | PREPARE simple_stmt (int) AS + | | SELECT $1 * 2;

So I think if sysbench use prepared statements I should also see the prepare there..

Yusaf Awan 5 days ago


I just verified on my end and observed the following:

1. When I manually ran Insert, update, delete, I got the following:
mydb=# Select cmd_type_text,cmd_type,left(query,20) from pg_stat_monitor where LOWER(query) like LOWER('delete%') or LOWER(query) like LOWER('insert%') or LOWER(query) like LOWER('insert%') limit 100;
cmd_type_text |cmd_type |left
DELETE |4 |DELETE FROM my_table
INSERT |3 |INSERT INTO my_table

mydb=# Select cmd_type_text,cmd_type,left(query,20) from pg_stat_monitor where query like 'UPDATE%' limit 100;
cmd_type_text |cmd_type |left
UPDATE |2 |UPDATE my_table

2. Upon executing the same sysbench:

image-20250311-134148.png
image-20250311-134221.png

naeem.akhter 5 days ago

I have verified it using 2.1.0 packages and build from sources on all supported 14 platforms. It is not reproducible on my end. However we use VMs primarily in our Release and Testing workflows, and maybe this is some behavior specific to K8/dockers.

Details

Assignee

Reporter

Needs QA

Yes

Components

Affects versions

Priority

Smart Checklist

Created last week
Updated 5 days ago
Configure

Flag notifications