DB Activity Capture in Greenplum

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


Comments