Script name: projectdb_bloat_maint_weekly.sh Features: a) Fully Customizable b) Read database from a parameter files. can also be generated dynamically c) Displays time to fix single table bloat, all table bloat of a db and all table bloats for all db in the projectdb. d) creates script output log for future reference Parameter files: a) projectdb: parameter files to read list of user database in the maintenance script (list can be generated dynamically). [gpadmin@sachi myscripts]$ cat projectdb sachi b) bloattablelist.sql: SQL to generate list of bloat tables in projectdb. List is generated dynamically and customizable. c)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/projectdb_bloat_maint_weekly.sh > $SCRIPTS_LOG/projectdb_bloat_maint_weekly_ouput_$(date +\%Y\%m\%d_\%H:\%M).log 2>&1 & Script output Before bloat maintenance dbname | relid | schemaname | tablename | bloat_ratio | expected_size | relation_size | wasted_space | relative_size | concern --------+-------+------------+--------------+-------------+---------------+---------------+--------------+---------------+------------- sachi | 83678 | public | bloattest | 19.6 | 67 MB | 1312 MB | 1245 MB | 0.9967 | 18.52354263 ############################################################################################## Fix Bloat by REORGANIZE and ANALYZE bloat tables in Greenplum user database ############################################################################################## ALTER TABLE public.bloattest SET WITH (REORGANIZE=TRUE); ALTER TABLE ANALYZE public.bloattest; ANALYZE TIME_ELAPSED to finish public.bloattest maintenance of sachi, 00:00:10 TIME_ELAPSED to finish sachi maintenance , 00:00:10 After bloat maintenance dbname | relid | schemaname | tablename | bloat_ratio | expected_size | relation_size | wasted_space | relative_size | concern --------+-------+------------+--------------+-------------+---------------+---------------+--------------+---------------+------------ TIME_ELAPSED to finish projectdb_bloat_maint_weekly maintenance , 00:00:10 [gpadmin@sachi myscripts]$ |
|
|