-
Notifications
You must be signed in to change notification settings - Fork 1
/
pt-kill.py
67 lines (63 loc) · 2.54 KB
/
pt-kill.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
import sys
import getopt
from settings import DATABASES
import psycopg2
import logging
def main(argv):
logging.basicConfig(filename='slowquery.log', filemode='a',
level=logging.DEBUG,
format='%(name)s - %(levelname)s - %(message)s')
database_config = "default"
run_type = "dryrun"
try:
opts, args = getopt.getopt(argv, "hc:r:", ["config=", "runtype"])
except getopt.GetoptError:
print('pt-kill.py -c <config> -r <kill|dryrun>')
sys.exit(2)
for opt, arg in opts:
if opt == '-h':
print('pt-kill.py -c <config> -r <kill|dryrun>')
sys.exit()
elif opt == '-c':
database_config = arg
elif opt == '-r':
run_type = arg
else:
print('pt-kill.py -c <config> -r <kill|dryrun>')
sys.exit()
connection_params = DATABASES.get(database_config)
if not connection_params:
print('Config not found!! Please provide the right config')
sys.exit()
port = connection_params.get("DB_PORT")
if not port:
port = 5432
conn = psycopg2.connect(dbname=connection_params.get("DBNAME"),
user=connection_params.get("USERNAME"),
password=connection_params.get("PASSWORD"),
host=connection_params.get("DB_HOST"),
port=port)
select_running_queries = ('SELECT pid, \n'
'now() - query_start AS duration, \n'
'query, state, client_addr \n'
'FROM pg_stat_activity \n'
'WHERE now() - query_start > interval \n'
'\'%s second\' \n'
'AND state = \'active\';')
threshold = connection_params.get("THRESHOLD_IN_SEC")
if not threshold:
threshold = 10
select_running_queries = (select_running_queries % threshold)
cursor = conn.cursor()
cursor.execute(select_running_queries)
long_running_queries = cursor.fetchall()
for query in long_running_queries:
query_details = "PID: %s, DURATION: %s, IP_ADDRESS: %s, QUERY: %s"
query_details = query_details % (query[0], query[1],
query[4], query[2])
if run_type == "kill":
kill_query = "SELECT pg_cancel_backend(%s)" % query[0]
cursor.execute(kill_query)
logging.info(query_details)
if __name__ == "__main__":
main(sys.argv[1:])