timing queries

On Tue, 29 Jan 2002, Christopher Kings-Lynne wrote:

Hi hackers,

Is there any way of logging all queries and their duration in postgres? I
know you can log queries - but I can't see any way of logging durations.

I discussed auditing in PostgreSQL 7.3. This would probably be a useful
bit of data to include in the audit trail.

Gavin

Gavin Sherry wrote:

On Tue, 29 Jan 2002, Christopher Kings-Lynne wrote:

Hi hackers,

Is there any way of logging all queries and their duration in postgres? I
know you can log queries - but I can't see any way of logging durations.

I discussed auditing in PostgreSQL 7.3. This would probably be a useful
bit of data to include in the audit trail.

I use the following code snippet to extract slow queries from postgresql
log

It could be improved trivially to include also the query text -
currently it
just shows the start time and backend number.

#!/usr/bin/python

""
020118.10:05:11.879 [27337] ProcessQuery
020118.10:05:11.879 [27337] CommitTransactionCommand
020118.10:05:11.882 [27337] StartTransactionCommand
020118.10:05:11.882 [27337] query: SET DateStyle TO 'ISO';
"""

import sys,string,time

def ts2time(ts):
yr = int(ts[ 0: 2])
mo = int(ts[ 2: 4])
dy = int(ts[ 4: 6])
hr = int(ts[ 7: 9])
mi = int(ts[10:12])
se = int(ts[13:15])
hu = float(ts[15:])
return time.mktime((yr,mo,dy,hr,mi,se,0,0,0)) + hu

file = sys.stdin
lnr = 0
trx_dict = {}

SLOW = 1 # longer than this seconds is slow

while 1:
pos = file.tell()
line = file.readline()
if not line: break
lnr = lnr + 1
try:
ts, pid, inf = string.split(line)[:3]
# print '-', ts, pid, inf
ti = ts2time(ts)
except:
continue
try:
lastlnr, lastpos, lasttime, lastts, lastinf = trx_dict[pid]
# print '*',lastlnr, lastpos, lasttime, lastts, lastinf
# if lastinf == 'ProcessQuery\n':
# print ti-lasttime, lastlnr, lastpos, lastts, ts
if((ti-lasttime) > SLOW) and (lastinf == 'ProcessQuery'):
print ti-lasttime, lastlnr, lastpos, lastts, ts
except:
pass
if inf=='exit(0)\n':
del trx_dict[pid]
else:
trx_dict[pid] = (lnr,pos,ti,ts,inf)

for key in trx_dict.keys():
print trx_dict[key]