PostgreSQL query samples are truncated in DPM

6/29/2020 6:31 AM

9/24/2020 8:17 PM

Query samples represent specific individual executions of a query on a monitored database. DPM captures samples in order to provide additional details about the work the database is doing. These query samples include the literal text of the query, data about the execution such as latency, and (if possible) an execution plan. Query samples are different from query digests because these samples include literal query text. Like query digests however they may be truncated, either by DPM or by PostgreSQL, depending on the monitoring method you are using.

Database Performance Monitor

Sample text can be truncated in two ways:

• For all installation types, DPM truncates query text after 4096 characters by default, in order to conserve resources on the server where the agent is running.
• If monitoring a PostgreSQL database using pg_stat_statements, such as Amazon RDS or a self-managed database using encrypted connections, sample text is truncated by PostgreSQL at a default length of 1024 characters.

Depending on the length of the truncated sample and monitoring method, you will need to configure either DPM, PostgreSQL, or both to capture additional query text.

• If monitoring via packet sniffing, samples are truncated by DPM only. You can increase the amount of captured text by adding the option "max-sample-length":"4096" to your global.conf DPM configuration file. This can be set from 4096 up to 32000. Higher numbers will consume more CPU resources by the agent. By default this file is /etc/vividcortex/global.conf.
• If monitoring via pg_stat_statements, either DPM or PostgreSQL may truncate the sample text. By default, pg_stat_activity (the source for query samples) will truncate text at 1024 characters. You will need to change the server variable track_activity_query_size. Refer to the PostgreSQL documentation for more information about this variable. (© 2020 The PostgreSQL Global Development Group, available at https://www.postgresql.org/, obtained on June 4, 2020). Amazon Aurora PostgreSQL does not currently allow this setting to be changed. See the previous bullet for details on configuring DPM to capture additional query text.