Greenplum Database Maintenance and Performance Monitoring Report Scripts

1. Regular database maintenance scripts
2. Performance monitoring using gpperfmon database tables and views
3. Activity monitoring using gp_toolkit schema tables and views
4. Script to generate daily health check status report 

Subscribe to posts

Running smart vacuum and analyze in pre-specified maintenance window

posted Nov 10, 2014, 4:16 PM by Sachchida Ojha   [ updated Jul 23, 2018, 12:01 PM ]

There is a  compelling requirement for all Greenplum DBA to run the maintenance tasks in a pre-specified window. Depending on the data volume, No of columns in tables and other factors, your daily maintenance job may run longer than maintenance window. One dirty way is to kill the job that is running beyond the maintenance window but this may leads your database in a inconsistent state and may raise other issues.


Here is the script that will do vacuum and analyze on tables in a prespecified time limit. The script will automatically exit after Runtime Exceeded Project maintenance Window.


[gpadmin@sachi scripts]$ ./projectdb_maint_daily.sh
Script is running.Please wait.It may take few minutes...
Runtime Exceeded Project maintenance Window. Now exiting!!!!!!!!

Script completed. Script log is available at /home/gpadmin/maintenance/logs
Filename:/home/gpadmin/maintenance/logs/projectdb_maint_daily_log_2014-11-10_19:03.log
[gpadmin@sachi scripts]$ 



Script will run next day from the table where it left on previous day.


Sachi's greenplum catalog bloat maintenance script

posted Oct 16, 2014, 5:08 PM by Sachchida Ojha   [ updated Jul 23, 2018, 12:02 PM ]

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. 

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. 

Sachi's greenplum database weekly/daily index maintenance scripts

posted Oct 16, 2014, 7:18 AM by Sachchida Ojha   [ updated Oct 16, 2014, 7:19 AM ]

Script name: projectdb_reindex_weekly.sh

Features: a) Fully Customizable b) Read database from a parameter files. can also be generated dynamically c) Displays time to complete single index, all indexes of a db and all indexes 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) indexlist.sql: SQL to generate list of indexes for the 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/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

[gpadmin@sachi myscripts]$ ./projectdb_reindex_weekly.sh

##############################################################################################
VACUUM and ANALYZE Greenplum user database tables. 
##############################################################################################
TIME_ELAPSED to finish pg_toast.pg_toast_2606_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10785_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10790_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10795_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10800_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_10805_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_10775_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_10780_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_3231_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_9903_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_3220_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_2619_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_1255_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_2604_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_25234_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_52289_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_52336_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_52361_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_52386_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_52411_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_60478_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_2618_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_2609_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_78039_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74505_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74530_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74555_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74580_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74606_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74632_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74658_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74683_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74709_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74736_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74763_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74790_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74816_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_74842_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74869_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74896_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74923_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74949_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_74975_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75002_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75029_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75056_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75082_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75108_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75135_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75162_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75189_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75215_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75241_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75268_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75295_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75322_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75348_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75374_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_75401_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75428_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75455_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_75481_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_75507_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_75534_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_75561_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75588_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75614_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75640_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75667_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75694_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75721_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75747_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75773_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75800_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75827_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75854_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75880_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75906_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75933_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75960_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_75987_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76013_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76039_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76066_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_76093_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76120_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76146_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_76172_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76199_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76226_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76253_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76279_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76305_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76331_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76358_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76385_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76412_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76438_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76465_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76493_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76521_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76549_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76576_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76603_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76631_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76659_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76687_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76714_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76741_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76769_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76797_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76825_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76852_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_76879_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_76907_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76935_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76963_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_76990_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77017_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77045_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77073_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_78067_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77101_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77929_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77128_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77155_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77183_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77211_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77239_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77266_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77293_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77321_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_77349_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77377_index maintenance of sachi database, 00:00:00

TIME_ELAPSED to finish pg_toast.pg_toast_77404_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_77431_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77459_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77487_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77515_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77542_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77569_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish pg_toast.pg_toast_77597_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77625_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77653_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77680_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77707_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77735_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77763_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77791_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77818_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77845_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77873_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77901_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77956_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_77983_index maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish pg_toast.pg_toast_78011_index maintenance of sachi database, 00:00:02

TIME_ELAPSED to finish public.abc_id maintenance of sachi database, 00:00:01

TIME_ELAPSED to finish sachi index maintenance , 00:02:26


TIME_ELAPSED to finish projectdb_reindex_weekly maintenance , 00:02:26
[gpadmin@sachi myscripts]$ 

Email us at gpdba@greenplumdba.com to get this script.

Sachi's Greenplum user database maintenance script

posted Oct 16, 2014, 5:23 AM by Sachchida Ojha   [ updated Jul 23, 2018, 12:03 PM ]

Script name: projectdb_maintenance_daily_sachi.sh
Parameter files: 
a) projectdb: parameter files to read list of user database in the maintenance script. (Can be generated dynamically)
[gpadmin@sachi myscripts]$ cat projectdb
sachi

b)tablelist.sql: List of tables for projectdb to be used (vacuum and analyzed) in maintenance script ( generated dynamically)

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_maintenance_daily_sachi.sh > $SCRIPTS_LOG/projectdb_maintenance_daily_sachi_ouput_$(date +\%Y\%m\%d_\%H:\%M).log 2>&1 &

Script output
[gpadmin@sachi myscripts]$ ./projectdb_maint_daily.sh 
##############################################################################################
VACUUM and ANALYZE Greenplum user database tables. 
##############################################################################################
TIME_ELAPSED to finish public.abc maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.foo maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.foo1 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.usha maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.usha1 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.usha3 maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sachi3 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sachi maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.test maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_months_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_months_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_months_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_mont_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_2_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_2_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_2_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_2_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_3_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_3_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_3_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_3_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_4_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_4_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_4_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_4_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_5_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_5_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_5_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_5_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_6_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_6_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_6_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_6_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_7_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_7_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_7_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_7_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_8_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_8_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_8_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_8_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_9_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_9_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_9_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_9_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_10_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_10_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_10_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_10_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_13_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_13_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_13_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_13_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_2_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_2_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_2_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_2_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_3_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_3_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_3_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_3_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_6_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_6_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_6_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_6_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_10_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_10_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_10_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_10_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_11_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_11_3_prt_europe maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_11_3_prt_asia maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_11_3_prt_other_regions maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_12_3_prt_usa maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_12_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_12_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_12_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_13_3_prt_usa maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_13_3_prt_europe maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_13_3_prt_asia maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_13_3_prt_other_regions maintenance of sachi, 00:00:00

TIME_ELAPSED to finish public.sales maintenance of sachi, 00:00:39

TIME_ELAPSED to finish public.sales_1_prt_outlying_years maintenance of sachi, 00:00:20

TIME_ELAPSED to finish public.sales_1_prt_2 maintenance of sachi, 00:00:19

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_8 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_9 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_10 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_13 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_2 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_3 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_months maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_6 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_10 maintenance of sachi, 00:00:01
TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_11 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_12 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_13 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_other_months maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_2 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_3 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_4 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_other_months maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_5 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_6 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_7 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_8 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_9 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_10 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_11 maintenance of sachi, 00:00:03

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_12 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_2_2_prt_13 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_2 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_3 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_4 maintenance of sachi, 00:00:01

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_5 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_6 maintenance of sachi, 00:00:02

TIME_ELAPSED to finish public.sales_1_prt_outlying_years_2_prt_7 maintenance of sachi, 00:00:03

TIME_ELAPSED to finish sachi maintenance , 00:03:03


TIME_ELAPSED to finish projectdb_maintenance_daily_sachi maintenance script , 00:03:03
[gpadmin@sachi myscripts]$ 

Email us gpdba@greenplumdba.com to get this script.

Sachi's daily catalog maintenance scripts - Run vacuum, reindex and analyze on user selected database

posted Oct 15, 2014, 7:47 AM by Sachchida Ojha   [ updated Oct 16, 2014, 5:45 AM ]

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.

Finding distribution keys of all tables in the greenplum database

posted Jul 25, 2014, 4:59 AM by Sachchida Ojha   [ updated Jul 23, 2018, 12:01 PM ]

SELECT pgn.nspname as schemaname,
pgc.relname as tablename,
pga.attname  as distributionkey
FROM (SELECT gdp.localoid,
CASE
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN
Unnest(gdp.attrnums)
ELSE NULL
END AS attnum
FROM gp_distribution_policy gdp
ORDER BY gdp.localoid) AS distrokey
INNER JOIN pg_class AS pgc
ON distrokey.localoid = pgc.oid
INNER JOIN pg_namespace pgn
ON pgc.relnamespace = pgn.oid
LEFT OUTER JOIN pg_attribute pga
ON distrokey.attnum = pga.attnum
AND distrokey.localoid = pga.attrelid
ORDER BY pgn.nspname,
pgc.relname;
Preserve the order in which distribution key is defines. View shows n row for table having composite (n columns) distribution key.

create view dbadmin.v_distribution_key as 
SELECT pgn.nspname as schemaname,                                                                          
pgc.relname as tablename,                                                                                                                                    
pga.attname  as distributionkey,distrokey.attnum                                                                                                             
FROM (SELECT gdp.localoid,                                                                                                                                   
CASE                                                                                                                                                         
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN                                                                                                               
Unnest(gdp.attrnums)                                                                                                                                         
ELSE NULL                                                                                                                                                    
END AS attnum                                                                                                                                                
FROM gp_distribution_policy gdp                                                                                                                              
ORDER BY gdp.localoid) AS distrokey                                                                                                                          
INNER JOIN pg_class AS pgc                                                                                                                                   
ON distrokey.localoid = pgc.oid                                                                                                                              
INNER JOIN pg_namespace pgn                                                                                                                                  
ON pgc.relnamespace = pgn.oid                                                                                                                                
LEFT OUTER JOIN pg_attribute pga                                                                                                                             
ON distrokey.attnum = pga.attnum                                                                                                                             
AND distrokey.localoid = pga.attrelid 
ORDER by pgn.nspname,pgc.relname,distrokey.attnum ;
Preserve the order in which distribution key is defineds. View shows 1 row for table having composite (n columns) distribution key.

create view dbadmin.v_distribution_key3
as
select 
a.schemaname,
a. tablename, 
max(pgc.relpages) as relpages, 
max(pgc.reltuples) as reltuples
, (select array_to_string(ARRAY(select distributionkey from 
(
SELECT pgn.nspname as schemaname,                                                                          
pgc.relname as tablename,                                                                                                                                    
pga.attname  as distributionkey,distrokey.attnum                                                                                                             
FROM (SELECT gdp.localoid,                                                                                                                                   
CASE                                                                                                                                                         
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN                                                                                                               
Unnest(gdp.attrnums)                                                                                                                                         
ELSE NULL                                                                                                                                                    
END AS attnum                                                                                                                                                
FROM gp_distribution_policy gdp                                                                                                                              
ORDER BY gdp.localoid) AS distrokey                                                                                                                          
INNER JOIN pg_class AS pgc                                                                                                                                   
ON distrokey.localoid = pgc.oid                                                                                                                              
INNER JOIN pg_namespace pgn                                                                                                                                  
ON pgc.relnamespace = pgn.oid                                                                                                                                
LEFT OUTER JOIN pg_attribute pga                                                                                                                             
ON distrokey.attnum = pga.attnum                                                                                                                             
AND distrokey.localoid = pga.attrelid 
ORDER by pgn.nspname,pgc.relname,distrokey.attnum
) as dkey where schemaname=a.schemaname 
and tablename=a.tablename),', ') )as distkey
from dbadmin.v_distribution_key a , pg_class pgc, pg_namespace pgn
where a. tablename=pgc.relname
and pgc.relnamespace = pgn.oid 
and a.schemaname=pgn.nspname
group by  a.schemaname,a.tablename;

create view dbadmin.v_distribution_key2
as
select 
a.schemaname,
a. tablename, 
max(pgc.relpages) as relpages, 
max(pgc.reltuples) as reltuples
, (select array_to_string(ARRAY(select distributionkey from dbadmin.v_distribution_key where schemaname=a.schemaname 
 and tablename=a.tablename),', ') )as distkey
from dbadmin.v_distribution_key a , pg_class pgc, pg_namespace pgn
where a. tablename=pgc.relname
and pgc.relnamespace = pgn.oid 
and a.schemaname=pgn.nspname
group by  a.schemaname,a.tablename;



create view dbadmin.v_distribution_key2
as
select 
a.schemaname,
a. tablename, 
max(pgc.relpages) as relpages, 
max(pgc.reltuples) as reltuples
, (select ARRAY(select distributionkey from dbadmin.v_distribution_key where schemaname=a.schemaname 
 and tablename=a.tablename))as distkey
from dbadmin.v_distribution_key a , pg_class pgc, pg_namespace pgn
where a. tablename=pgc.relname
and pgc.relnamespace = pgn.oid 
and a.schemaname=pgn.nspname
group by  a.schemaname,a.tablename;

select distinct 
schemaname,
tablename,
threshold,
relpages,
reltuples,
count(distributionkey) over (partition by schemaname,tablename) as noofcolindistkey, 
array_to_string(array_agg(distributionkey) over (partition by schemaname,tablename),',') as distkey 
from 
(
SELECT pgn.nspname as schemaname,                                                                          
pgc.relname as tablename,                                                                                                                                    
pga.attname  as distributionkey,distrokey.attnum,
 '72 hours' as threshold, 
 pgc.relpages, 
 pgc.reltuples                                                                                                              
FROM (SELECT gdp.localoid,                                                                                                                                   
CASE                                                                                                                                                         
WHEN ( Array_upper(gdp.attrnums, 1) > 0 ) THEN                                                                                                               
Unnest(gdp.attrnums)                                                                                                                                         
ELSE NULL                                                                                                                                                    
END AS attnum                                                                                                                                                
FROM gp_distribution_policy gdp                                                                                                                              
ORDER BY gdp.localoid) AS distrokey                                                                                                                          
INNER JOIN pg_class AS pgc                                                                                                                                   
ON distrokey.localoid = pgc.oid                                                                                                                              
INNER JOIN pg_namespace pgn                                                                                                                                  
ON pgc.relnamespace = pgn.oid                                                                                                                                
LEFT OUTER JOIN pg_attribute pga                                                                                                                             
ON distrokey.attnum = pga.attnum                                                                                                                             
AND distrokey.localoid = pga.attrelid 
ORDER by pgn.nspname,pgc.relname,distrokey.attnum)
 as dkey;















1-7 of 7