- Pivotal Greenplum Database (GPDB) 4.3 and above
- Pivotal HAWQ Database (HDB) all versions
When encountering either a performance issue or a crash during the planner step for query execution when using the new query optimizer ORCA.
What to Collect
1. Problem Query
2. Explain "analyze" for the query with the following GUCs set:
optimizer = off; gp_enable_explain_allstat=on;
3.Explain "analyze" for the query with the following GUCs set:
optimizer=on gp_enable_explain_allstat=on optimizer_minidump=always
4. Collect the files from $MASTER_DATA_DIRECTORY/minidumps
5. If available, EXPLAIN and/or EXPLAIN ANALYZE what happened before the problem started.
6. Snippets from pg_log and complete logs using gp_log_collector. (Master and affected segments)
7. DDL’s, DML’s and stats using Minirepro or gpsd
8. If possible, provide sample data from the affected tables.
9. strace/pstack/gcore of hung query’s PID using analyze session
10. Output of pg_stat_activity, pg_locks,gp_segment_configuration,pg_settings tables.
11. Core files (if any) using GPMT Packcore
12. The frequency of issue - Reproducible at will OR was it a one-time occurrence.
14. Detail out any undocumented parameters used, include any non-default values for them.
Note- There are instances where a database crash can be observed while executing a problematic query. So, please execute the queries in a quiet window.