Sachi's daily catalog maintenance scripts - Run vacuum, reindex and analyze on user selected database
Post date: Oct 15, 2014 2:47:34 PM
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 (Can be generated dynamically).
[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.