0

I have started using pg_stat_monitor extension from : https://www.percona.com/doc/percona-monitoring-and-management/2.x/manage/conf-postgres.html with my postgresql database.I have a problem here. I though of using this so that I can see my query with actual parameters instead of placeholders. However, still I see my query with placeholder. Even though , I have set pg_stat_monitor.pgsm_normalized_query =0 and by default it's 0 only. Which means it has to show me query with actual parameters.

You can refer attached screenshot: enter image description here

2 Answers 2

1

While the docs do say that the default is 0, the default is clearly not 0. Looks like a bug. After you set it to really be 0, did you execute the reset so you wouldn't be looking at old data?

But the entire pgsm_normalized_query=0 feature seems misbegotten. With it, the query is still normalized, it just that the text reported is the parameterized text of an arbitrary (the first?) execution of that normalized query in that bucket, but all the stats seem to have been aggregated across the normalized query even though the text reported is not normalized.

Also, it seems pretty flaky even within those constraints:

select bucket, calls, query  from pg_stat_monitor where query like 'UPDATE pgbench_accounts%' order by total_time desc;
 bucket | calls  |                                   query                                    
--------+--------+----------------------------------------------------------------------------
      3 | 95,109 | UPDATE pgbench_accounts SET abalance = abalance + 3008 WHERE aid = 
      4 | 92,216 | UPDATE pgbench_accounts SET abalance = abalance + 3186 WHERE aid = 421795
      8 | 93,381 | UPDATE pgbench_accounts SET abalance = abalance + 1998 WHERE aid = 
      5 | 90,735 | UPDATE pgbench_accounts SET abalance = abalance + -2438 WHERE aid = 574540
      9 | 81,484 | UPDATE pgbench_accounts SET abalance = abalance + 3594 WHERE aid = 
      6 | 86,158 | UPDATE pgbench_accounts SET abalance = abalance + -41 WHERE aid = 1
      0 | 90,503 | UPDATE pgbench_accounts SET abalance = abalance + -2664 WHERE aid =
      7 | 93,446 | UPDATE pgbench_accounts SET abalance = abalance + -3997 WHERE aid =
      1 | 55,960 | UPDATE pgbench_accounts SET abalance = abalance + 4804 WHERE aid = 

Why is the final parameter sometimes there and sometimes not?

Overall, I would take the caution on that page "pg_stat_monitor is beta software and currently unsupported." to heart.

1
  • I have tried with reloading conf several times. It's not changing the behaviour
    – Shiwangini
    Commented Oct 19, 2020 at 13:50
0

Update On This:: I have checked further with Percona Forums - and I got to know:

It's not a bug. It's working as expected. Queries where user provide the values directly, in that case pg_stat_statment (A PostgreSQL tool) is replacing these values with placeholders, but in pg_stat_monitor (Percona Tool) they provide a way to select whether you want to see actual values or placeholders. if user use bind variable and bind the parameters, then system will not able to detect actual values.

For more information you can refer the discussion: https://forums.percona.com/discussion/comment/57277#Comment_57277

1
  • This explanation doesn't really make sense. Even when you choose the setting to see the actual value, you are only seeing an arbitrary example of one of the actual values used for that normalized query in that bucket (which is not evident from there example as they only test with one execution). If the query has different performance based on different parameterizations, you are not going to see that reliably, unless your parameterizations change slowly over time.
    – jjanes
    Commented Oct 29, 2020 at 18:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.