[rt-users] performance question

[Filip Rembiałkowski]

Ruslan Zakirov napisał(a):

>  Could you spend some
> time and write instructions about how to create such report? :)

sure.

1) login to rt (probably ou want to login as root, to see all queues)

2) enable logging of all SQL queries.
- for postgresql you can do it by putting these lines in your postgresql.conf
log_destination = 'stderr'
redirect_stderr = on
log_line_prefix = '[%t] [%p] <%u@%d> '
log_min_duration_statement = 0
- for mysql you could use 'log' option

3) restart your database (or do whatever is needed to create fresh logfile)

4) click "Home" in browser window - this will display the home page and fill
your SQL log

5) copy out this logfile to avoid polluting it with other queries

6) transform the logfile to following format:
<duration><tab><query>
it can be tricky if queries have newlines or tabs

7) anonymize queries by replacing some literals by placeholders

8) calculate some statistics in tabular format


tools I have used:

step 6) http://filip.rembialkowski.net/tool/pglogstat/pglogtimes
step 7) http://filip.rembialkowski.net/tool/pglogstat/sqa
step 8) http://filip.rembialkowski.net/tool/pglogstat/pgtimestat


one-liner:
pglogtimes < sample.log | sqa | pgtimestat > sample.csv


Filip




-- 
Filip Rembiałkowski
eo Networks sp. z o.o.
tel:+48-604-065651
mailto:filip at eo.pl
jabber:filip at jabber.eo.pl