Wrong command type for queries
Description
Environment
Attachments
- 11 Mar 2025, 01:43 PM
- 11 Mar 2025, 01:43 PM
Activity
Tibor Korocz (Percona) 5 days agoEdited
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_monitor
I 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
@Tibor Korocz (Percona) 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:
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
Details
Assignee
Reporter
Needs QA
Components
Affects versions
Priority
Smart Checklist
Open Smart Checklist
Smart Checklist

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.