| #!/usr/bin/env python |
| # coding: UTF-8 |
| |
| # pt-snap-statements |
| # |
| # Copyright(c) 2015 Uptime Technologies, LLC. |
| |
| import sys, os |
| libpath = os.path.abspath(os.path.dirname(sys.argv[0]) + "/../lib") |
| sys.path.append(libpath) |
| |
| import getopt |
| import PsqlWrapper |
| import log |
| |
| class SnapStatements: |
| def build_where_clause(self, where_clause, cond): |
| if where_clause is None: |
| where_clause = " where " |
| else: |
| where_clause = where_clause + " and " |
| where_clause = where_clause + " " + cond |
| return where_clause |
| |
| def __init__(self, psql, interval, n_top, debug=False): |
| self.debug = debug |
| |
| self.psql = psql |
| |
| # Max queries to be listed. |
| if n_top is None: |
| n_top = 10000 |
| |
| log.debug("version: " + str(self.psql.get_version())) |
| |
| if self.psql.get_version() < 9.4: |
| queryid1 = '' |
| queryid2 = '' |
| else: |
| queryid1 = 'queryid, ' |
| queryid2 = 'to_hex(s1.queryid) AS "QUERYID", ' |
| |
| if self.psql.get_version() < 9.2: |
| dirtied1 = '' |
| dirtied2 = '' |
| blkreadtime1 = '' |
| blkreadtime2 = '' |
| blkwritetime1 = '' |
| blkwritetime2 = '' |
| else: |
| dirtied1 = 'sum(shared_blks_dirtied) + sum(local_blks_dirtied) AS blks_dirtied,' |
| dirtied2 = '( s1.blks_dirtied - coalesce(s2.blks_dirtied,0) ) AS "B_DIRT", ' |
| blkreadtime1 = ', sum(blk_read_time) AS blk_read_time' |
| blkreadtime2 = ', round( (s1.blk_read_time - coalesce(s2.blk_read_time,0))::numeric, 1) AS "R_TIME"' |
| blkwritetime1 = ', sum(blk_write_time) AS blk_write_time' |
| blkwritetime2 = ', round( (s1.blk_write_time - coalesce(s2.blk_write_time,0))::numeric, 1) AS "W_TIME" ' |
| |
| self.query = ' \ |
| /*SNAP*/ CREATE TEMP TABLE snap_pg_stat_statements \ |
| AS SELECT userid, \ |
| dbid, \ |
| %s \ |
| query, \ |
| sum(calls) AS calls, \ |
| sum(total_time) AS total_time, \ |
| sum(rows) AS rows, \ |
| sum(shared_blks_hit) + sum(local_blks_hit) AS blks_hit, \ |
| sum(shared_blks_read) + sum(local_blks_read) + sum(temp_blks_read) AS blks_read, \ |
| %s /* blks_dirtied */ \ |
| sum(shared_blks_written) + sum(local_blks_written) + sum(temp_blks_written) AS blks_written \ |
| %s /* blk_read_time */ \ |
| %s /* blk_write_time */ \ |
| FROM pg_stat_statements \ |
| GROUP BY userid,dbid, %s query; \ |
| \ |
| /*SNAP*/ SELECT pg_sleep(%d); \ |
| \ |
| /*SNAP*/ CREATE TEMP TABLE snap_pg_stat_statements2 \ |
| AS SELECT userid, \ |
| dbid, \ |
| %s /* queryid */ \ |
| query, \ |
| sum(calls) AS calls, \ |
| sum(total_time) AS total_time, \ |
| sum(rows) AS rows, \ |
| sum(shared_blks_hit) + sum(local_blks_hit) AS blks_hit, \ |
| sum(shared_blks_read) + sum(local_blks_read) + sum(temp_blks_read) AS blks_read, \ |
| %s /* blks_dirtied */ \ |
| sum(shared_blks_written) + sum(local_blks_written) + sum(temp_blks_written) AS blks_written \ |
| %s /* blk_read_time */ \ |
| %s /* blk_write_time */ \ |
| FROM pg_stat_statements \ |
| GROUP BY userid,dbid, %s query; \ |
| \ |
| SELECT u.usename AS "USER", \ |
| d.datname AS "DBNAME", \ |
| %s \ |
| substring(s1.query, 1, 30) AS "QUERY", \ |
| ( s1.calls - coalesce(s2.calls,0) ) AS "CALLS", \ |
| ( s1.total_time - coalesce(s2.total_time,0) )::integer AS "T_TIME", \ |
| ( s1.rows - coalesce(s2.rows,0) ) AS "ROWS", \ |
| ( s1.blks_hit - coalesce(s2.blks_hit,0) ) AS "B_HIT", \ |
| ( s1.blks_read - coalesce(s2.blks_read,0) ) AS "B_READ", \ |
| %s /* blks_dirtied */ \ |
| ( s1.blks_written - coalesce(s2.blks_written,0) ) AS "B_WRTN" \ |
| %s /* blk_read_time */ \ |
| %s /* blk_write_time */ \ |
| FROM snap_pg_stat_statements2 AS s1 \ |
| LEFT OUTER JOIN snap_pg_stat_statements s2 ON s1.userid = s2.userid \ |
| AND s1.dbid = s2.dbid \ |
| AND s1.query = s2.query \ |
| LEFT OUTER JOIN pg_database d ON s1.dbid = d.oid \ |
| LEFT OUTER JOIN pg_user u ON s1.userid = u.usesysid \ |
| WHERE ( s1.calls - coalesce(s2.calls,0) ) > 0 \ |
| AND s1.query NOT LIKE \'--%%\' \ |
| AND s1.query NOT LIKE \'/*SNAP*/ %%\' \ |
| ORDER BY 6 DESC \ |
| LIMIT %d; \ |
| ' % (queryid1, dirtied1, blkreadtime1, blkwritetime1, queryid1, |
| interval, |
| queryid1, dirtied1, blkreadtime1, blkwritetime1, queryid1, |
| queryid2, dirtied2, blkreadtime2, blkwritetime2, n_top) |
| |
| def check(self): |
| query = ' \ |
| select count(*) as "pg_stat_statements" \ |
| from pg_class c left outer join pg_namespace n \ |
| on c.relnamespace = n.oid \ |
| where n.nspname=\'public\' \ |
| and c.relname=\'pg_stat_statements\'' |
| |
| rs = self.psql.execute_query(query) |
| log.debug("check: " + str(rs)) |
| |
| if int(rs[1][0]) != 1: |
| log.error("pg_stat_statements view not found.") |
| return False |
| |
| query = ' \ |
| select count(*) as "track_io_timing"\ |
| from pg_settings \ |
| where name = \'track_io_timing\' \ |
| and setting = \'on\'' |
| |
| rs = self.psql.execute_query(query) |
| log.debug("check: " + str(rs)) |
| |
| if self.psql.get_version() >= 9.2 and int(rs[1][0]) != 1: |
| log.warning("track_io_timing is diabled.") |
| |
| return True |
| |
| def reset(self): |
| query = 'SELECT pg_stat_statements_reset();' |
| |
| rs = self.psql.execute_query(query) |
| log.debug("reset: " + str(rs)) |
| |
| if len(rs) == 0 or rs[0][0] != 'pg_stat_statements_reset': |
| log.error("Cannot reset.") |
| log.error("Check your privilege and database.") |
| |
| return True |
| |
| def get(self): |
| if self.check() is False: |
| return False |
| |
| log.debug("get: " + self.query) |
| |
| rs = self.psql.execute_query(self.query) |
| |
| avail = False |
| rs2 = [] |
| for r in rs: |
| if r[0] == 'USER': |
| avail = True |
| if avail is True: |
| rs2.append(r) |
| |
| log.debug("get: " + str(rs2)) |
| |
| self.psql.print_result(rs2) |
| |
| return True |
| |
| def usage(): |
| print "" |
| print "Usage: " + os.path.basename(sys.argv[0]) + " [option...] [interval]" |
| print "" |
| print "Options:" |
| print " -h, --host=HOSTNAME Host name of the postgres server" |
| print " -p, --port=PORT Port number of the postgres server" |
| print " -U, --username=USERNAME User name to connect" |
| print " -d, --dbname=DBNAME Database name to connect" |
| print "" |
| print " -t, --top=NUMBER Number of queries to be listed" |
| print " -R, --reset Reset statistics" |
| print "" |
| print " --help Print this help." |
| print "" |
| |
| if __name__ == "__main__": |
| try: |
| opts, args = getopt.getopt(sys.argv[1:], "h:p:U:d:Rt:", |
| ["help", "debug", "host=", "port=", "username=", "dbname=", |
| "reset", "top="]) |
| except getopt.GetoptError, err: |
| log.error(str(err)) |
| usage() |
| sys.exit(2) |
| |
| host = None |
| port = None |
| username = None |
| dbname = None |
| n_top = None |
| |
| do_reset = False |
| |
| debug = None |
| |
| for o, a in opts: |
| if o in ("-h", "--host"): |
| host = a |
| elif o in ("-p", "--port"): |
| port = int(a) |
| elif o in ("-U", "--username"): |
| username = a |
| elif o in ("-d", "--dbname"): |
| dbname = a |
| elif o in ("-R", "--reset"): |
| do_reset = True |
| elif o in ("-t", "--top"): |
| n_top = int(a) |
| elif o in ("--debug"): |
| log.setLevel(log.DEBUG) |
| debug = True |
| elif o in ("--help"): |
| usage() |
| sys.exit(0) |
| else: |
| log.error("unknown option: " + o + "," + a) |
| sys.exit(1) |
| |
| p = PsqlWrapper.PsqlWrapper(host=host, port=port, username=username, dbname=dbname, on_error_stop=True, debug=debug) |
| |
| if do_reset is True: |
| log.info("Resetting statistics.") |
| snap = SnapStatements(p, 0, 0, debug=debug) |
| snap.reset() |
| sys.exit(0) |
| |
| try: |
| if (len(args) == 0): |
| log.info("Interval is 10 seconds.") |
| interval = 10 |
| else: |
| interval = int(args[0]) |
| except ValueError, err: |
| log.error(str(err)) |
| usage() |
| sys.exit(2) |
| |
| snap = SnapStatements(p, interval, n_top, debug=debug) |
| try: |
| if snap.get() is False: |
| sys.exit(1) |
| except KeyboardInterrupt, err: |
| log.info("Terminated.") |
| sys.exit(1) |
| |
| sys.exit(0) |