GUCs for Improving Short Running Queries

Post date: Oct 31, 2014 1:26:36 PM

These GUCs are useful for queries that finish within less than 5 seconds and recommended to be used for OLTP-like workloads. Specifically helpful for systems that have large number of segments. Need to be tuned carefully in a high concurrency system. A system is high concurrency if resource queues are configured to run 50 or more queries concurrently.

1) gp_connections_per_thread

• 1. A value larger than or equal to the number of primary segments means that each slice in a query plan will get its own thread when dispatching to segments

  2. Lower values will use more threads, which utilizes more resources on the master

  3. Reducing this value improves the performance of queries that run for a couple of seconds

2. gp_enable_direct_dispatch

• 1. Enables or disables the dispatching of targeted query plans for queries that access data on a single segment

  2. This significantly reduces the response time of qualifying queries as there is no interconnect setup involved

  3. Direct dispatch requires more CPU utilization on the master

  4. Improves performance of queries that have a filter on the distribution keys

  5. This needs to be accounted for when deciding on distribution keys for tables

  6. Specially helpful in high concurrency environments

3. gp_cached_segworkers_threshold

• 1. A higher setting may improve performance for power-users that want to issue many complex queries in a row

  2. Helpful in high concurrency environments