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. |