Sachi's greenplum catalog bloat maintenance script

Post date: Oct 17, 2014 12:8:24 AM

Script name: catalog_bloat_maint_weekly.sh

Features: a) Fully Customizable b) Read database from a parameter files. can 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) bloatcatalogtablelist.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/catalog_bloat_maint_weekly.sh > $SCRIPTS_LOG/catalog_bloat_maint_weekly_ouput_$(date +\%Y\%m\%d_\%H:\%M).log 2>&1 &

Script output

Before

dbname | relid | schemaname |  tablename   | bloat_ratio | expected_size | relation_size | wasted_space | relative_size |  concern   

--------+-------+------------+--------------+-------------+---------------+---------------+--------------+---------------+------------

 sachi  |  1249 | pg_catalog | pg_attribute |         2.6 | 1696 kB       | 4416 kB       | 2720 kB      |        1.0000 | 1.60377358

After

dbname | relid | schemaname | tablename | bloat_ratio | expected_size | relation_size | wasted_space | relative_size | concern 

--------+-------+------------+-----------+-------------+---------------+---------------+--------------+---------------+---------

(0 rows)

##############################################################################################

Fix Bloat by REORGANIZE and ANALYZE bloat tables in Greenplum catalog  

##############################################################################################

ALTER TABLE pg_catalog.pg_attribute SET WITH (REORGANIZE=TRUE);

ERROR:  permission denied: "pg_attribute" is a system catalog

ANALYZE pg_catalog.pg_attribute;

ANALYZE

TIME_ELAPSED to finish pg_catalog.pg_attribute maintenance of sachi, 00:00:01

TIME_ELAPSED to finish sachi maintenance , 00:00:01

TIME_ELAPSED to finish projectdb_bloat_maint_weekly  maintenance , 00:00:01

[gpadmin@sachi myscripts]$ 

Email us at gpdba@greenplumdba.com to get this script or buy our premium email support