create schema dbadmin; create table dbadmin.dba_pg_stat_activity ( ctime timestamp , datid integer , datname varchar , procpid integer , sess_id integer , usesysid integer , usename varchar , current_query text , waiting boolean , query_start timestamp with time zone , backend_start timestamp with time zone , client_addr inet , client_port integer , application_name text , xact_start timestamp with time zone , waiting_reason text ) distributed randomly; create table dbadmin.dba_pg_locks ( ctime timestamp, locktype text , database integer , relation integer , db_name varchar(100), relation_name varchar(100), page integer , tuple smallint , transactionid integer , classid integer , objid integer , objsubid smallint , transaction integer , pid integer , mode text , granted boolean , mppsessionid integer , mppiswriter boolean , gpsegmentid integer ) distributed randomly; #!/bin/bash source /usr/local/greenplum-db/greenplum_path.sh bindir=$(cd $(dirname $0); pwd) source ${bindir}/env.sh logfl=${logdir}/capture_$(date +%Y%m%d).log fname1=/tmp/dba_pg_stat_activity_$$ table1="dbadmin.dba_pg_stat_activity" fname2=/tmp/dba_pg_locks_$$ table2="dbadmin.dba_pg_locks" max_segment_id=0 timeout=30000 exec 1>>${logfl} 2>&1 tm=$(psql -At -c "select current_timestamp::timestamp") echo "${tm}: Run extract ${table1}" # Select all active sessions except the current session echo "set statement_timeout = ${timeout}; \\copy (select '$tm', a.* from pg_stat_activity a where procpid != pg_backend_pid()) to ${fname1}" | psql -At echo "${tm}: Capture from Extract ${table1}" # Disable auto stat collection as this will conflict with an exclusive lock on pg_class and hang echo "set statement_timeout = ${timeout}; set gp_autostats_mode = none; \\copy ${table1} from ${fname1}" | psql -At rm -f ${fname1} echo "${tm}: Run extract ${table2}" # Select all active sessions except the current session # Decipher database name and table name with schema name # Get locks at segment level only upto a pre-defined max segment id to limit size of data collected echo "set statement_timeout = ${timeout}; \\copy ( select '$tm', locktype, database, relation, pg_database.datname, '"'"'"' || pg_namespace.nspname || '"'"'"' || '.' || '"'"'"' || pg_class.relname || '"'"'"', page, tuple, transactionid, classid, objid, objsubid, transaction, pid, mode, granted, mppsessionid, mppiswriter, l.gp_segment_id from pg_locks l left outer join pg_database on pg_database.oid = l.database left outer join pg_class on l.locktype = 'relation' and pg_class.oid = l.relation left outer join pg_namespace on pg_namespace.oid = pg_class.relnamespace where l.pid != pg_backend_pid() and l.gp_segment_id <= ${max_segment_id}) to ${fname2}" | \ awk '{printf("%s ", $0);}' | psql -At echo "${tm}: Capture from Extract ${table2}" # Disable auto stat collection as this will conflict with an exclusive lock on pg_class and hang echo "set statement_timeout = ${timeout}; set gp_autostats_mode = none; \\copy ${table2} from ${fname2}" | psql -At rm -f ${fname2} #!/bin/bash function del_data() { echo "Cleaning up ${1} at $(date)..." echo "set statement_timeout = ${timeout}; delete from ${1} where ctime < date_trunc('day', current_date) - interval '${time_to_keep}'; vacuum ${1}; analyze ${1};" | \ psql -e -At } cleanup_capture.sh =================================== source /usr/local/greenplum-db/greenplum_path.sh bindir=$(cd $(dirname $0); pwd) source ${bindir}/env.sh logfl=${logdir}/cleanup_capture_$(date +%Y%m%d).log fname1=/tmp/dba_pg_stat_activity_$$ table1="dbadmin.dba_pg_stat_activity" table2="dbadmin.dba_pg_locks" timeout=60000 time_to_keep="1 month" exec 1>>${logfl} 2>&1 echo "Started at $(date)..." del_data ${table1} del_data ${table2} echo "Completed at $(date)..." echo "" ENV #!/bin/bash export PGDATABASE=??? export PGHOST=mdw export PGPORT=1587 export logdir=/home/gpadmin/sachi/logs #!/usr/bin/env python
import sys
import re
def sub(infile, var, val):
f = open(infile, 'r')
pat = re.compile("^export\s+{0}=(.*)$".format(var))
lines = f.readlines()
nlines = []
f.close()
i = 0
for line in lines:
ln = line[:-1]
m = pat.match(ln)
if (m):
ln = "{0}{1}".format(ln[0:m.start(1)], val)
nlines.append(ln + "\n")
else:
nlines.append(line)
i += 1
f = open(infile, 'w')
for line in nlines:
f.write(line)
f.close()
if __name__ == '__main__':
from sys import argv
if (len(argv) != 4):
print("Usage: setup.py <Script Name> <Parameter> <Value>")
sys.exit(1)
sub(argv[1], argv[2], argv[3])#!/bin/bash function docmd() { cmd="${1+$@}" echo $cmd $cmd } if [ "$1" = "" ] then echo "Usage: setup_capture.1 <Database Name>" exit 1 fi db=$1 cd $(dirname $0) ./setp.py env.txt PGDATABASE $db for file in $(ls -1 *.txt 2>/dev/null | grep -iv "readme") do filestart=${file%.*} newfile=${filestart}.sh docmd mv ${file} ${newfile} docmd chmod +x ${newfile} done 1. Copy the zip file to /home/gpadmin/sachi/scripts on Greenplum master as gpadmin 2. Unzip the file. 3. psql as gpadmin and run ddl.sql. The expected output is like below: sachi=# \i ./ddl.sql psql:./ddl.sql:1: ERROR: schema "dbadmin" already exists CREATE TABLE CREATE TABLE sachi=# \q 4. Run the below command: chmod u+x setup_capture.1 setp.py 5. Run the below command (substitute <Database Name> ./setup_capture.1 <Database Name> 6. Test the capture script ./capture.sh Check the log file /home/gpadmin/sachi/logs/capture_*.log 7. Test the cleanup_capture script ./cleanup_capture.sh Check the log file /home/gpadmin/sachi/logs/cleanup_capture_*.log 8. Set up the cron jobs. The crontab lines should look like the following: # capture pg_locks and pg_stat_activity every 15 minutes 0,15,30,45 * * * * /home/gpadmin/sachi/scripts/capture.sh # cleanup dbadmin.dba_pg_locks and dbadmin.dba_pg_stat_activity monthly at # 5:10 PM on first of every month 10 17 1 * * /home/gpadmin/sachi/scripts/cleanup_capture.sh |
|
|