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.

Comments