Enable PQO (Pivotal Query Optimizer) at the database level

posted Jan 14, 2016, 5:53 PM by Sachchida Ojha   [ updated May 20, 2016, 5:52 PM ]
Step 1: Check the status of the optimizer

gpadmin=# show optimizer;
 optimizer 
-----------
 off
(1 row)

gpadmin=# show optimizer_control;
 optimizer_control 
-------------------
 on
(1 row)

Step 1: Check state of the database (optional)

[gpadmin@gpdb-sandbox ~]$ gpstate
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-Starting gpstate with args: 
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-local Greenplum Version: 'postgres (Greenplum Database) 4.3.6.1 build 2'
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-master Greenplum Version: 'PostgreSQL 8.2.15 (Greenplum Database 4.3.6.1 build 2) on x86_64-unknown-linux-gnu, compiled by GCC gcc (GCC) 4.4.2 compiled on Oct  1 2015 15:14:22'
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-Obtaining Segment details from master...
20160114:12:38:42:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-Gathering data from segments...
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-Greenplum instance status summary
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Master instance                                = Active
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Master standby                                 = No master standby configured
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total segment instance count from metadata     = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Primary Segment Status
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total primary segments                         = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total primary segment valid (at master)        = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total primary segment failures (at master)     = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total number of postmaster.pid files missing   = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total number of postmaster.pid files found     = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total number of postmaster.pid PIDs missing    = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total number of postmaster.pid PIDs found      = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total number of /tmp lock files missing        = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total number of /tmp lock files found          = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total number postmaster processes missing      = 0
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Total number postmaster processes found        = 2
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Mirror Segment Status
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-   Mirrors not configured on this array
20160114:12:38:43:169214 gpstate:gpdb-sandbox:gpadmin-[INFO]:-----------------------------------------------------

Step 3: Turn on optimizer_analyze_root_partition 
[gpadmin@gpdb-sandbox ~]$ gpconfig -c optimizer_analyze_root_partition -v on --masteronly
20160114:12:39:18:169310 gpconfig:gpdb-sandbox:gpadmin-[INFO]:-completed successfully

Step 4: Turn on the optimizer
[gpadmin@gpdb-sandbox ~]$ gpconfig -c optimizer -v on --masteronly
20160114:12:39:59:169532 gpconfig:gpdb-sandbox:gpadmin-[INFO]:-completed successfully

Step 5: Apply the changes
[gpadmin@gpdb-sandbox ~]$ gpstop -u
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Starting gpstop with args: -u
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Gathering information and validating the environment...
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Obtaining Greenplum Master catalog information
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Obtaining Segment details from master...
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Greenplum Version: 'postgres (Greenplum Database) 4.3.6.1 build 2'
20160114:12:40:23:169600 gpstop:gpdb-sandbox:gpadmin-[INFO]:-Signalling all postmaster processes to reload
Step 6: Check the status
[gpadmin@gpdb-sandbox ~]$ psql
psql (8.2.15)
Type "help" for help.

gpadmin=# show optimizer;
 optimizer 
-----------
 on
(1 row)

gpadmin=# 

Comments