PostgreSQL Query is trimming in the QAN dashboard

Percona Monitoring and Management (PMM)
1

Hi Team,

We can only view half of the query, as the query has been truncated in the QAN.
Could you please tell me how I may retrieve the whole query in the QAN?

2

Hi,

As per the postgres documentation 


track_activity_query_size (integer)

Specifies the amount of memory reserved to store the text of the currently executing command for each active session, for the pg_stat_activity.query field. If this value is specified without units, it is taken as bytes. The default value is 1024 bytes. This parameter can only be set at server start.

Hope this helps.

3

Thanks @Robert_Bernier for the quick response.

Is there any performance impact if we increase the ‘track_activity_query_size’ parameter in the PostgreSQL database, however we did not specify anything for Mariadb, therefore I am simply curious on the DB performance.

4

No performance impact.

regards :slight_smile:

5

Thanks to @Robert_Bernier for all the information.

6

Hi @Robert_Bernier

I have checked in the DB, and I can see only 2KB. But the default value is 1024 bytes.

postgres=#
postgres=# show track_activity_query_size ;
track_activity_query_size

2kB
(1 row)

postgres=#
postgres=#

I have already set to 2GB. but still its showing 2 KB.

[postgres@rntpgd201 12]$ cat /db/pgdata/12/postgresql.conf |grep -i ‘track_activity_query_size’
#track_activity_query_size = 1024 # (change requires restart)
track_activity_query_size = 2048 # Increase tracked query string size
[postgres@rntpgd201 12]$

7

There’s a hardcoded limit of 100KB so you can’t go to 2GB. As per your message, you also have to restart the server for the new parameter to take effect.

– server restart is required
alter system set track_activity_query_size=‘100kB’;

Hope this helps.

8

Hi @Robert_Bernier

Unfortunately, the postgresql.log file contains the entire query, whereas QAN just contains the trimmed query (half of the query).

9

They PMM maintainers have asked me “What is the string length of your query”?

10

Following up 


You need to remove and add the server again in order to perform the following

pmm-admin add postgresql server-1 --max-query-length=3072

11

and this bug report 


1 Like
12

Thanks @Robert_Bernier

I’ll try the above option and let you know what happens.

13

@Naresh9999, what do you use inside PostgreSQL pg_stat_statements or pg_stat_monitor ?

14

@Roma_Novikov

We are utilizing both the monitors.

sds=# show shared_preload_libraries;
shared_preload_libraries

pgaudit, pgauditlogtofile, pg_stat_statements, pg_stat_monitor
(1 row)

sds=# SELECT pg_stat_monitor_version();
pg_stat_monitor_version

2.0.1
(1 row)

15

Hi @Robert_Bernier

Still, I can see only the trimmed query.

16

Hi @Roma_Novikov

Any thoughts on this issue? Am I missing something here? This is how I have added the PostgreSQL servers in the PMM.

pmm-admin add postgresql --server-insecure-tls --username=‘pmm_usr’ --password=‘xxxxxxxxxxxx’ --host=‘10.112.xx.xx’ --port=5432 --service-name=C-QA-xxxx --query-source=“pgstatmonitor” --max-query-length=3072 --environment=C-QA

17

@Naresh9999 check the data you have in `pg_stat_monitor` view reference - pg_stat_monitor Documentation
Are there full queries collected?

18

Hi @Roma_Novikov

I can see the below output.

sds=# \d pg_stat_monitor;
View “public.pg_stat_monitor”
Column | Type | Collation | Nullable | Default
---------------------±-------------------------±----------±---------±--------
bucket | bigint | | |
bucket_start_time | timestamp with time zone | | |
userid | oid | | |
username | text | | |
dbid | oid | | |
datname | text | | |
client_ip | inet | | |
pgsm_query_id | bigint | | |
queryid | bigint | | |
top_queryid | bigint | | |
query | text | | |
comments | text | | |
planid | bigint | | |
query_plan | text | | |
top_query | text | | |
application_name | text | | |
relations | text | | |
cmd_type | integer | | |
cmd_type_text | text | | |
elevel | integer | | |
sqlcode | text | | |
message | text | | |
calls | bigint | | |
total_time | double precision | | |
min_time | double precision | | |
max_time | double precision | | |
mean_time | double precision | | |
stddev_time | double precision | | |
rows | bigint | | |
shared_blks_hit | bigint | | |
shared_blks_read | bigint | | |
shared_blks_dirtied | bigint | | |
shared_blks_written | bigint | | |
local_blks_hit | bigint | | |
local_blks_read | bigint | | |
local_blks_dirtied | bigint | | |
local_blks_written | bigint | | |
temp_blks_read | bigint | | |
temp_blks_written | bigint | | |
blk_read_time | double precision | | |
blk_write_time | double precision | | |
resp_calls | text | | |
cpu_user_time | double precision | | |
cpu_sys_time | double precision | | |
bucket_done | boolean | | |

sds=#

image
image878×976 18.4 KB

In the Postgresql.log file, I can see the full query.

19

@Naresh9999, can you select * from this table? do you have data inside it ?

20

Yes @Roma_Novikov

I can see the data inside the below table.

select * from pg_stat_monitor;