Sachi's bloat table maintenance script in Greenplum database

posted Oct 16, 2014, 4:37 PM by Sachchida Ojha   [ updated Jul 23, 2018, 12:03 PM ]
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]$ 
Email us at gpdba@greenplumdba.com to get this script or buy our premium email support

Comments