Sachi's Greenplum Catalog Maintenance Scripts

Script name: gpdb_catalog_maintenance_daily_sachi.sh

Parameter files: 

a) catalogmaindb: parameter files to read list of user database to in the maintenance script.

[gpadmin@sachi myscripts]$ cat catalogmaindb

sachi

gpadmin

b)dir_param: Scripts and log file locations

[gpadmin@sachi myscripts]$ cat dir_param

#########################################################################################################

## Script name: dir_param

## Decription: Script to define directory structure used in Greenplum database maintenance scripts

#######################################################################################################

export SCRIPTS_DIR=/home/gpadmin/maintenance/myscripts

export SCRIPTS_LOG=/home/gpadmin/maintenance/myscripts_log

########################################################################################################

########################################################################################################

c) Running the script

nohup $SCRIPTS_DIR/gpdb_catalog_maintenance_daily_sachi.sh > $SCRIPTS_LOG/gpdb_catalog_maintenance_daily_sachi_ouput_$(date +\%Y\%m\%d_\%H:\%M).log 2>&1 &

Script output

##########################################################################################################################

This script will run VACUUM, REINDEX and ANALYZE on the Greenplum catalog tables. Shared catalog indexes will be skipped.

##########################################################################################################################

"sachi" Database System Catalog Size Before Catalog Maintenance (VACUUM/REINDEX/ANALYZE) = 48 MB

---------------------------------------------------------------------------------------------

2014-10-15 18:33:10 EDT BEGIN VACUUM ON SYSTEM CATALOG in sachi DB

2014-10-15 18:33:25 EDT END VACUUM ON SYSTEM CATALOG in sachi DB

TIME_ELAPSED, 00:00:15,sachi,VACUUM System Catalog

2014-10-15 18:33:25 EDT BEGIN REINDEX SYSTEM CATALOG in sachi DB

2014-10-15 18:34:27 EDT END REINDEX SYSTEM CATALOG in sachi DB

TIME_ELAPSED, 00:01:02,sachi,REINDEX System Catalog

2014-10-15 18:34:27 EDT BEGIN ANALYZE ON SYSTEM CATALOG in sachi DB

2014-10-15 18:34:38 EDT END ANALYZE ON SYSTEM CATALOG in sachi DB

TIME_ELAPSED, 00:00:11,sachi,ANALYZE System Catalog

sachi Database System Catalog Size After VACUUM/REINDEX/ANALYZE = 48 MB

------------------------------------------------------------------------

TIME_ELAPSED to finish sachi catalog maintenance , 00:01:28

"gpadmin" Database System Catalog Size Before Catalog Maintenance (VACUUM/REINDEX/ANALYZE) = 35 MB

---------------------------------------------------------------------------------------------

2014-10-15 18:34:38 EDT BEGIN VACUUM ON SYSTEM CATALOG in gpadmin DB

2014-10-15 18:35:11 EDT END VACUUM ON SYSTEM CATALOG in gpadmin DB

TIME_ELAPSED, 00:00:33,gpadmin,VACUUM System Catalog

2014-10-15 18:35:11 EDT BEGIN REINDEX SYSTEM CATALOG in gpadmin DB

2014-10-15 18:36:30 EDT END REINDEX SYSTEM CATALOG in gpadmin DB

TIME_ELAPSED, 00:01:19,gpadmin,REINDEX System Catalog

2014-10-15 18:36:30 EDT BEGIN ANALYZE ON SYSTEM CATALOG in gpadmin DB

2014-10-15 18:36:41 EDT END ANALYZE ON SYSTEM CATALOG in gpadmin DB

TIME_ELAPSED, 00:00:11,gpadmin,ANALYZE System Catalog

gpadmin Database System Catalog Size After VACUUM/REINDEX/ANALYZE = 35 MB

------------------------------------------------------------------------

TIME_ELAPSED to finish gpadmin catalog maintenance , 00:02:03

TOTAL_TIME_ELAPSED, 00:03:31: to VACUUM/REINDEX/ANALYZE ALL listed database 

[gpadmin@sachi myscripts]$ 

Email us gpdba@greenplumdba.com to get this script.