DPA cannot collect complete query text from a PostgreSQL instance

4/3/2020 4:47 AM

8/21/2020 3:41 PM

You are reviewing the DPA Query Details page for a query that ran against a PostgreSQL database instance, and the Statistics section displays the following message:

Statistics for this query are unavailable because DPA was not able to collect complete query text from the PostgreSQL instance.

Database Performance Analyzer

Statistics for this query are unavailable because DPA was not able to collect the complete query text from the pg_stat_statements system view in the PostgreSQL database instance. Information from this view is essential for collecting statistics. Statistics can be unavailable for the following reasons:

• The track_activity_query_size parameter value is too small.

  The track_activity_query_size parameter specifies the amount of memory reserved to store the text of the currently executing command. If the query is too large to be stored in that amount of memory, the SQL text in the pg_catalog.pg_stat_activity view is truncated. When that happens, DPA can't match the complete query text from pg_stat_statements. 

• The pg_stat_statements extension is not enabled.

  If this extension is not configured, DPA cannot get complete SQL texts and query execution statistics.

• The pg_stat_statements.track value is none. 

  If the value is none, no SQL statements are tracked.

Resolution 1

If you have not already done so, complete the steps under "Configure each database instance" in the topic "Register a PostgreSQL database instance and prepare for monitoring" to enable the pg_stat_statements extension and set the pg_stat_statements.track value to top or all.

Resolution 2

If the database has already been configured, complete the following steps to increase the value of the track_activity_query_size parameter:

1. Open the postgresql.conf file in a text editor. (The postgresql.conf file is normally stored in the data directory.)
2. Change the value of the track_activity_query_size entry. For example:
   track_activity_query_size=4096
   Note: For more information about this parameter, see Run-time Statistics (© 1996-2020 The PostgreSQL Global Development Group, available at https://www.postgresql.org/, obtained on February 20, 2020) in the PostgreSQL documentation.
3. Restart the PostgreSQL server.

Disclaimer: Please note, any content posted herein is provided as a suggestion or recommendation to you for your internal use. This is not part of the SolarWinds software or documentation that you purchased from SolarWinds, and the information set forth herein may come from third parties. Your organization should internally review and assess to what extent, if any, such custom scripts or recommendations will be incorporated into your environment.  You elect to use third party content at your own risk, and you will be solely responsible for the incorporation of the same, if any.