Pivotal Knowledge Base

Follow

Script - Cleanup/Maintenance script for historical gpperfmon data

Goal

Is to provide a simple shell scripts to help administrator schedule a maintenance task to drop historical partition on gpperfom database greater than specific retention period (This script takes in months as retention period).

This script might be needed to control the size of the gpperfmon database or to eliminate the historical data that is not needed anymore.

Disclaimer

  1. This script is for education purpose only, No support on the script is provided if there is any issues.
  2. Please verify and test the script on a test cluster , before running it on any business critical machines.

Execution

  • Copy the two files (provided as attachment in this documents) "environment_parameters.env" & "gpperfmon_maintenance.sh" to any directory of your choice ( both should be on the same directory like)
gpadmin:Fullrack@mdw $ pwd
/data1/gpadmin
gpadmin:Fullrack@mdw $ ls -ltr
total 56
-rw------- 1 gpadmin gpadmin 7485 Aug 21 02:18 gpperfmon_maintenance.sh
-rw------- 1 gpadmin gpadmin  127 Aug 21 02:29 environment_parameters.env
  • open the "environment_parameters.env" and edit the parameters that reflects to your database environment.
  • Run the shell script using
/bin/sh gpperfmon_maintenance.sh

Script

This script is divided into two parts (The complete shell script is also available as attachment at the bottom of the article).

  • The environment variable file
gphome:/usr/local/greenplum-db-4.2.6.3
pgdatabase:gpperfmon
pgport:5432
master_data_directory:/data/master/gpseg-1
retention:3
  • The shell script
#!/bin/bash
#
#  gpperfmon_maintenance.sh
#  pivotal - 2014
#  
#
#

# Function : To extract the history partition name which is less than retention period.

extract_partition_information() {

echo "INFO - Extracting information of partition older than retention period: "$Retention" Months"
echo

psql -d $PGDATABASE -p $PGPORT -c "SELECT
                    			schemaname||'.'||tablename as \"Parent Table\",
                     			partitionschemaname||'.'||partitiontablename as \"Partition Name\",                    				
age(substring(partitionrangestart from 2 for 19)::timestamp) \"Partition Age\", substring(partitionrangestart from 2 for 19)::timestamp as \"Partition Start\", substring(partitionrangeend from 2 for 19)::timestamp as \"Partition End\", partitionrank as \"Parition Rank\", (select pg_size_pretty(pg_total_relation_size(b.partitiontablename)) from pg_partitions b where p.partitiontablename=b.partitiontablename ) as \"Partition Size\" FROM pg_partitions p WHERE partitionrangestart < current_timestamp::timestamp without time zone - interval '${Retention} months' and tablename like '%history' ORDER BY 3 desc;" }
# Function : To extract the sql to drop those older partition, but it ignores if that is the only partition of the table.
generate_sql_to_drop() { echo "INFO - Generating SQL to drop partition older than retention period: "$Retention" Months" echo psql -d $PGDATABASE -p $PGPORT -Atc "SELECT 'ALTER TABLE ' ||schemaname||'.'||tablename || ' DROP PARTITION FOR (RANK(' || partitionrank|| '));' FROM pg_partitions WHERE partitionrangestart < current_timestamp::timestamp without time zone - interval '${Retention} months' and tablename in ( select a.tablename from pg_partitions a where a.tablename like '%history' group by a.tablename having count(*) > 1 ) ORDER BY partitionrank desc; " > $sql_file }
# Function : To drop the partition.
execute_drop_sql() { echo "INFO - Excecuting the sql file generated to drop the partition with retention older than: " $Retention" Months" echo psql -d $PGDATABASE -p $PGPORT -ef $sql_file > $drop_output }

# Function : To extract the history partition name after executing the drop. extract_partition_info_after_drop() { echo "INFO - Extracting information of partition after dropping the partition more than the retention period: "$Retention" Months" echo echo "MESG - If any partition left after drop, the partition could be the last partition of the table" echo "MESG - Drop script ignore the last partition , to avoid the below error \"cannot drop partition for rank 1 of relation \"\" -- only one remains\" " echo psql -d $PGDATABASE -p $PGPORT -c "SELECT schemaname||'.'||tablename as \"Parent Table\", partitionschemaname||'.'||partitiontablename as \"Partition Name\", age(substring(partitionrangestart from 2 for 19)::timestamp) \"Partition Age\", substring(partitionrangestart from 2 for 19)::timestamp as \"Partition Start\", substring(partitionrangeend from 2 for 19)::timestamp as \"Partition End\"
FROM pg_partitions p WHERE partitionrangestart < current_timestamp::timestamp without time zone - interval '${Retention} months' and tablename like '%history' ORDER BY 3 desc;" } # Main program starts here # Script and log directories echo "INFO - Generating the directories name / location where the output logs will saved / stored" echo export script=$0 export script_basename=`basename $script` export script_dir=`dirname $script` cd $script_dir export script_dir=`pwd` export install_dir=`dirname $script_dir` export logdir=$script_dir/log export tmpdir=$script_dir/tmp export fixdir=$script_dir/fix # Creating tmp / log directory echo "INFO - Creating the directories which will be used for storing logs / temp files ( if not available ) " echo mkdir -p $script_dir/log mkdir -p $script_dir/tmp mkdir -p $script_dir/fix # Reading the parameter file to set the environment echo "INFO - Reading the parameter file to set the environment" echo export paramfile=$script_dir/environment_parameters.env export GPHOME=`grep -i gphome $paramfile | grep -v grep | cut -d: -f2` source $GPHOME/greenplum_path.sh export PGDATABASE=`grep -i pgdatabase $paramfile | grep -v grep | cut -d: -f2` export PGPORT=`grep -i pgport $paramfile | grep -v grep | cut -d: -f2` export MASTER_DATA_DIRECTORY=`grep -i master_data_directory $paramfile | grep -v grep | cut -d: -f2` export Retention=`grep -i retention $paramfile | grep -v grep | cut -d: -f2` # Script and log filenames echo "INFO - Generating filenames needed for output logs" echo export logfile=${logdir}/${script_basename}.${PGDATABASE}.${PGPORT}.log export oldlog1=${logdir}/${script_basename}.${PGDATABASE}.${PGPORT}.log.1 export oldlog2=${logdir}/${script_basename}.${PGDATABASE}.${PGPORT}.log.2 export junkfile=${tmpdir}/${script_basename}.${PGDATABASE}.${PGPORT}.junk export sql_file=${fixdir}/${script_basename}.${PGDATABASE}.${PGPORT}.dropping_older_partition.sql export drop_output=${tmpdir}/${script_basename}.${PGDATABASE}.${PGPORT}.drop_output.tmp # Save old log files echo "INFO - Checking / archiving the old log files from previous run" echo if (test -f $oldlog1 ) then mv -f $oldlog1 $oldlog2 > $junkfile 2>> $junkfile fi if (test -f $logfile ) then mv -f $logfile $oldlog1 > $junkfile 2>> $junkfile fi # Remove old temporary files. echo "INFO - Removing the old / temporary files from previous run, if any" echo if (test -f $extract_table ) then rm -r $sql_file > $junkfile 2>> $junkfile fi # Direct messages to logfile echo "INFO - All the log / output messages are being moved to logfile: " $logfile echo "INFO - Please use a different session to view the progress / logfile: " $logfile echo "INFO - Do not press ctrl + c or kill the session unless its needed , allow the program to complete" echo exec > $logfile 2>> $logfile # Printing the message on the environment that will be used by this script echo "INFO - Program succesfully started" echo "INFO - Program started at" `date` echo echo "--------------------------------------------------------------------------------------------------------------------------------------------------------------------" echo echo "MESG - GreenPlum Database Cluster Environment: " echo echo " INFO - Software Location:" $GPHOME echo " INFO - Database:" $PGDATABASE echo " INFO - Port:" $PGPORT echo " INFO - Master Data Directory:" $MASTER_DATA_DIRECTORY echo " INFO - Retention:"$Retention" Months" echo echo "MESG - The script logs name / location" echo echo " INFO - Logfile Destination:" $logdir echo " INFO - Logfile Name:" $logfile echo echo "--------------------------------------------------------------------------------------------------------------------------------------------------------------------" echo # Calling the Function to confirm the script execution extract_partition_information generate_sql_to_drop execute_drop_sql extract_partition_info_after_drop # Program ending messages. echo "INFO - Progam succesfully completed" echo "INFO - Program ended at" `date` echo

Output

If the script execute successfully there should be three folders created.

-rw------- 1 gpadmin gpadmin 7485 Aug 21 02:18 gpperfmon_maintenance.sh
-rw------- 1 gpadmin gpadmin  127 Aug 21 02:29 environment_parameters.env
drwx------ 2 gpadmin gpadmin  111 Aug 21 02:18 fix
drwx------ 2 gpadmin gpadmin  153 Aug 21 02:18 tmp
drwx------ 2 gpadmin gpadmin  125 Aug 21 02:18 log

Where directory,

  • fix : Contains the sql to drop the historical partition.
  • tmp : junkfile and also the output from the command executing the drop statement
  • log : logfile of the shell script

The output of the script would look like

Shell execution:

gpadmin:Fullrack@mdw $ /bin/sh gpperfmon_maintenance.sh
INFO - Generating the directories name / location where the output logs will saved / stored

INFO - Creating the directories which will be used for storing logs / temp files ( if not available )

INFO - Reading the parameter file to set the environment

INFO - Generating filenames needed for output logs

INFO - Checking / archiving the old log files from previous run

INFO - Removing the old / temporary files from previous run, if any

INFO - All the log / output messages are being moved to logfile:  /data1/gpadmin/log/gpperfmon_maintenance.sh.gpperfmon.5432.log
INFO - Please use a different session to view the progress / logfile:  /data1/gpadmin/log/gpperfmon_maintenance.sh.gpperfmon.5432.log
INFO - Do not press ctrl + c or kill the session unless its needed , allow the program to complete

Message from the logfile:

gpadmin:Fullrack@mdw $ cat gpperfmon_maintenance.sh.gpperfmon.5432.log

INFO - Program succesfully started
INFO - Program started at Thu Aug 21 02:07:50 PDT 2014

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

MESG - GreenPlum Database Cluster Environment:

       INFO - Software Location:  /usr/local/greenplum-db-4.2.6.3
       INFO - Database:  gpperfmon
       INFO - Port:  5432
       INFO - Master Data Directory:  /data/master/gpseg-1
       INFO - Retention: 3 Months

MESG - The script logs name / location

       INFO - Logfile Destination:  /data1/gpadmin/log
       INFO - Logfile Name:  /data1/gpadmin/log/gpperfmon_maintenance.sh.gpperfmon.5432.log

--------------------------------------------------------------------------------------------------------------------------------------------------------------------

INFO - Extracting information of partition older than retention period: 3 Months

           Parent Table           |              Partition Name              |          Partition Age          |   Partition Start   |    Partition End    | Parition Rank | Partition Size
----------------------------------+------------------------------------------+---------------------------------+---------------------+---------------------+---------------+----------------
 public.log_alert_history         | public.log_alert_history_1_prt_1         | 4 years 7 mons 20 days 03:00:00 | 2009-12-31 21:00:00 | 2010-01-31 21:00:00 |             1 | 288 kB
 public.iterators_history         | public.iterators_history_1_prt_1         | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 288 kB
 public.database_history          | public.database_history_1_prt_1          | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 0 bytes
 public.segment_history           | public.segment_history_1_prt_1           | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 0 bytes
 public.emcconnect_history        | public.emcconnect_history_1_prt_1        | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 288 kB
 public.health_history            | public.health_history_1_prt_1            | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 288 kB
 public.filerep_history           | public.filerep_history_1_prt_1           | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 0 bytes
 public.diskspace_history         | public.diskspace_history_1_prt_1         | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 288 kB
 public.network_interface_history | public.network_interface_history_1_prt_1 | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 0 bytes
 public.socket_history            | public.socket_history_1_prt_1            | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 0 bytes
 public.udp_history               | public.udp_history_1_prt_1               | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 0 bytes
 public.tcp_history               | public.tcp_history_1_prt_1               | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 0 bytes
 public.tcp_extended_history      | public.tcp_extended_history_1_prt_1      | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 0 bytes
 public.queries_history           | public.queries_history_1_prt_1           | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00 |             1 | 288 kB
(14 rows)

INFO - Generating SQL to drop partiton older than retention period: 3 Months

INFO - Excecuting the sql file generated to drop the partition with retention older than:  3 Months

INFO - Extracting information of partition after dropping the partition more than the retention period: 3 Months

MESG - If any partition left after drop, the partition could be the last partition of the table
MESG - Drop script ignore the last partition , to avoid the below error "cannot drop partition for rank 1 of relation "" -- only one remains"

           Parent Table           |              Partition Name              |          Partition Age          |   Partition Start   |    Partition End
----------------------------------+------------------------------------------+---------------------------------+---------------------+---------------------
 public.log_alert_history         | public.log_alert_history_1_prt_1         | 4 years 7 mons 20 days 03:00:00 | 2009-12-31 21:00:00 | 2010-01-31 21:00:00
 public.socket_history            | public.socket_history_1_prt_1            | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00
 public.udp_history               | public.udp_history_1_prt_1               | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00
 public.tcp_history               | public.tcp_history_1_prt_1               | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00
 public.tcp_extended_history      | public.tcp_extended_history_1_prt_1      | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00
 public.network_interface_history | public.network_interface_history_1_prt_1 | 4 years 7 mons 20 days          | 2010-01-01 00:00:00 | 2010-02-01 00:00:00
(6 rows)

INFO - Progam succesfully completed
INFO - Program ended at Thu Aug 21 02:07:51 PDT 2014

Comments

  • Avatar
    Kushal Choubay

    In addition to this - VACUUM,VACUUM FULL and REINDEX will also help in reducing the size of gpperfmon (any) db.

    gpperfmon=# REINDEX DATABASE gpperfmon;
    gpperfmon=# VACUUM [FULL] [TABLENAME] ; ------ It is recommended that you create a script or set of commands that execute vacuum on tables(users and catalogs) individually.

    VACUUM is an expensive operation and will take time to complete.It is recommended to execute it during quiet time.
    Please do not kill the process and engage support in case you suspect that it is hung.

Powered by Zendesk