Configuration options and troubleshooting for PostgreSQL and MySQL table and index advisors

See the following sections to configure DPA to generate table and index advisors. for PostgreSQL and MySQL database instances.

Specify which PostgreSQL or MySQL databases DPA collects plans from

By default, DPA collects plans from all databases. If you do not want to use plans from all databases, you can specify a list of databases to include or to exclude.

1. Depending on the type of database instance, set one of the following advanced options to specify the list of databases you want to include or exclude:

   • POSTGRESQL_PLAN_COLLECTION_DB_NAMES
   • MYSQL_PLANS_LIST_DATABASES

2. Set one of the following advanced options to specify whether you want to include or exclude the list of databases:

   • POSTGRESQL_PLAN_COLLECTION_DB_NAMES_FILTER
   • MYSQL_PLANS_COLLECTION_FILTER_TYPE

These options can be set at both the global level and the monitored instance level. If they are set at the instance level, DPA considers both the global and instance-level values. If the values do not conflict, DPA applies all values. For example:

Global settings	Monitored instance settings	Result
Not Set	Include A, B	For monitored instances with the instance-level settings, DPA collects plans from A and B. For all other monitored instances, DPA collects plans from all databases.
Include A, B, C	Include D	For monitored instances with the instance-level settings, DPA collects plans from A, B, C, and D. For all other monitored instances, DPA collects plans from A, B, and C.
Exclude A, B, C	Exclude D, E, F	For monitored instances with the instance-level settings, DPA collects plans from all databases except A, B, C, D, E, and F. For all other monitored instances, DPA collects plans from all databases except A, B, and C.

When the global setting and the instance-level setting have different specifications for a specific database, the instance-level setting overrides the global setting for the specified instance. Otherwise, the global settings apply. For example:

Global setting	Monitored instance settings	Result
Include A, B, C	Exclude C	For monitored instances with the instance-level settings, DPA collects plans from A and B. For all other monitored instances, DPA collects plans from A, B, and C.
Exclude A, B, C	Include C	For monitored instances with the instance-level settings, DPA collects plans from all databases except A and B. For all other monitored instances, DPA collects plans from all databases except A, B, and C.

Troubleshooting PostgreSQL and MySQL table and index advisors

Execution plans are missing for some PostgreSQL or MySQL SQL statements

To provide table and index advisors for a monitored PostgreSQL or MySQL instance, DPA needs the execution plans that PostgreSQL or MySQL generates. To determine if DPA is able to get the plan for a SQL statement, search for the SQL statement and get the SQL hash value. Then run the following query:

PostgreSQL:	select * from CONMPT_ where sqlhash = 'SQLHash'
MySQL:	select * from CONPPT_ where sqlhash = 'SQLHash'

If no values are returned, check for the following issues:

• The DPA monitoring user does not have the required privileges on tables where the SQL statement runs.

  Check the idc.log file and look for one of the following messages: 

  PostgreSQL:	Failed to collect query plans due to: ERROR: permission denied for table TableName.
  MySQL:	Failed to collect query plans due to either query is truncated or there is a sql syntax error: SELECT command denied to user 'dpa_user' for table 'table_name'.

  Resolution: To grant the required privileges, see the instructions for creating the monitoring user.

• (PostgreSQL only) The SQL statement is truncated in the pg_stat_activity.query field.

  PostgreSQL stores currently running SQL statements in the pg_stat_activity.query field. If there is not enough memory allocated to store the entire SQL statement, it truncates the query. DPA cannot collect the execution plan for a truncated SQL statement. By default, PostgreSQL reserves 1024 bytes of memory.

  Resolution: To avoid truncated queries, DPA recommends increasing the amount of reserved memory to 4096 bytes. To increase the reserved memory, modify the track_activity_query_size value as described in the instructions for configuring each database instance.

For a PostgreSQL instance, the Index Bloat Metrics value is Unknown

On the Table Tuning Advisor page, the Index Bloat Metrics value is Unknown if the pgstattuple extension is not enabled on the PostgreSQL database.

To enable the pgstattuple extension, connect to the database and run the following command:

CREATE EXTENSION pgstattuple;