def slow_query_stats(sort='date'):
"""Compile slow queries
sort: one of "duration", "total", "date" (default: date)
"""
pattern = re.compile(
r'(?P<date>\d{4}-\d{2}-\d{2} \d{2}:\d{2}:\d{2} (?:CES?T|UTC)) '
r'\[\d+-\d+\] '
r'tilery@tilery LOG: duration: (?P<duration>\d+\.\d+) ms execute '
r'<unnamed>: (?P<query>SELECT ST_AsBinary\("geometry"\) AS '
r'geom,[^\[]*AS data)', re.DOTALL)
clean_bbox = re.compile(r'BOX3D\([^)]+\)') # Allow to join same queries.
queries = {}
data = BytesIO()
get(f'/var/log/postgresql/postgresql-{config.psql_version}-main.log', data)
matches = pattern.findall(data.read().decode())
for date, duration, query in matches:
id_ = clean_bbox.sub('BBOX', query)
if id_ not in queries:
queries[id_] = {'date': date, 'duration': float(duration),
'total': 1, 'example': query}
else:
stats = queries[id_]
if date > stats['date']:
stats['date'] = date
stats['duration'] += float(duration)
stats['total'] += 1
# Compute duration so we can sort by.
for query in queries.values():
query['duration'] = query['duration'] / query['total']
queries = sorted(queries.values(), key=lambda d: d[sort])
for query in queries:
print('-'*80)
print(query['example'])
print('Total:', query['total'],
'● Average duration:', int(query['duration']),
'● Last seen', query['date'])
print('—'*80)
print('Total requests:', len(queries), f'(sorted by {sort})')
def test_get_path(remotefile):
get(remotefile, 'testget')
with Path('testget').open() as f:
assert f.read() == 'foobarééœ'
Path('testget').unlink()
def test_get_with_cd(remotefile):
with cd('/tmp'):
data = BytesIO()
get('usinetestget', data)
assert data.read().decode() == 'foobarééœ'
def test_get_bytesio(remotefile):
data = BytesIO()
get(remotefile, data)
assert data.read().decode() == 'foobarééœ'