Performance question

RT Users
21

forgot patchOn 12/6/06, Ruslan Zakirov ruslan.zakirov@gmail.com wrote:

On 11/30/06, Filip Rembialkowski filip.rembialkowski@eo.pl wrote:

[snip]

That sounds surprisingly high. But you’re also testing with
an…older RT. And I know we’ve made some big performance
improvements since then. I can’t guarantee that we’ve fixed whatever
you’re running into, but coming up to 3.4.6 or 3.6 is strongly
recommended.

actually i discovered this while evaluating RT 3.6.1 :slight_smile:
I was talking about 3.4.1, because the results are very similar.

to be precise, I made this test again, just for a single home page view,
and have posted SQL log for single home page view here:
http://depesz.com/various/rt-3.6.1-homepage-view-sql-log.html
if you look at this, you will know what I mean
Thanks for great report and good representation. Could you spend some
time and write instructions about how to create such report? :slight_smile:

so the question is still valid.
is it needed to ask all these queries?
can we optimize it somehow?
yep, at least get rid of the second line in your log. try attached
patch. don’t forget to purge mason cache.

greets,
Filip

–
Best regards, Ruslan.

Best regards, Ruslan.

3.6-queue_summary_performance.patch (719 Bytes)

22

Yep, this could help, however in this case we have to make step back
from generalized component /Elements/QueuesSummary we implemented in
3.6 branch.On 12/1/06, Roy El-Hames rfh@pipex.net wrote:

Quick Search is the biggest culprit , typically 4 queries per queue …
so in your case (4 x 135 = 540 queries) just for the little column on
the right …
in 3.6.x you have the ability to only display the queues you are
interested in
It will be nice if the Quick search queries changed to use Group by …
Roy

Filip Rembialkowski wrote:

On Thu, November 30, 2006 18:12, Jesse Vincent wrote:

I was talking about 3.4.1, because the results are very similar.

to be precise, I made this test again, just for a single home page view,
and have posted SQL log for single home page view here:
http://depesz.com/various/rt-3.6.1-homepage-view-sql-log.html
if you look at this, you will know what I mean

so the question is still valid.
is it needed to ask all these queries?

That sure looks to me like 21 real queries and 156 “DEALLOCATE” commands
that are (I presume) the result of reading whatever the queries return.
I don’t think things are as bad as you thought they were.

i’d be happy if it was so good :slight_smile:

first column is the query with placeholders, second column - number of
times it was executed

SELECT count(main.id) FROM Tickets main WHERE ((main.EffectiveId =
main.id)) AND ((main.Status != ?)) AND ((main.Type = ?)) AND ((main.Queue
= ?) AND (main.Status = ?)) – 405 times

EXECUTE [PREPARE: SELECT * FROM Queues WHERE LOWER(Name) =
LOWER($1)] – 135 times

and so on…

i’m just curious why does RT have to count tickets 405 times if there are
only 135 queues?
and why does it run “SELECT * FROM Queues” 135 times just do display the
homepage?

regards
F.

Best regards, Ruslan.

23

Ruslan Zakirov napisał(a):

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

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

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

  2. click “Home” in browser window - this will display the home page and fill
    your SQL log

  3. copy out this logfile to avoid polluting it with other queries

  4. transform the logfile to following format:

    it can be tricky if queries have newlines or tabs

  5. anonymize queries by replacing some literals by placeholders

  6. 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@eo.pl
jabber:filip@jabber.eo.pl