performance issue with extreme long waiting times

[clemens]

The "Changelog" Plugin seem to be very slow with >100 changesets.

This ticket was created for the corresponding ​mailing list thread

We recently installed the TracTicketChangelogPlugin on our system (TRAC 1.4.2, Linux, SQLite). It is very welcome because it gives a nice overview of all changesets related to a ticket.

However, my users complain about slow loading of tickets. Some tickets with 50-150 changesets have loading times >30 seconds. If I disable the TracTicketChangelogPlugin, then loading time is only 2 seconds. Even with endabled TracTicketChangelogPlugin, some tickets are quite fast (typically those with few changesets).

Setup

Some specs about my setup:

• TRAC 1.4.2
• TracTicketChangelogPlugin 1.2 (r17251 by rjollos on 2018-07-30)
• Linux with TRAC running via apache Mod-WSQI
• SQLite database
• 500 tickets
• SVN with 5000 commits
• I have a powerful server with plenty of RAM, SSD and otherwise fast TRAC

Config

I tried to shorten the message length, but this did not change the loading times:

[ticketlog]
log_message_maxlength = 50

Genshi

As far as I know, the plugin is still based on Genshi (see #13283). However, experiments indicate that this may not be the cause of the problem.

SQL

For me it looks like the performance bottleneck is the SQL query not the Genshi template processing.

I made an experiment to limit the number of change-log to 10 (instead 150). As expected I got only 10 change-logs in the output. Unfortunately this did not change the extreme long wait time (still >30 seconds).

The SQL query is made in function _get_ticket_revisions in https://trac-hacks.org/browser/tracticketchangelogplugin/1.2/ticketlog/web_ui.py#L187

    SELECT p.value, r.rev, r.author, r.time, r.message
    FROM ticket_revision AS tr
     LEFT JOIN revision AS r
      ON r.repos=tr.repos AND r.rev=tr.rev
     LEFT JOIN repository AS p
      ON p.id=tr.repos AND p.name='name'
    WHERE tr.ticket=%s
    LIMIT 10

Note the LIMIT 10 in the last SQL line which I inserted in my experiment. The total wait time is not influenced by this "LIMIT 10". I am neither a database nor TRAC expert. But this SQL query looks rather simple. I cannot imagine that this takes 30 seconds. The ticket_revision table is short and has only 1000 rows on my repo.

Please help, my TracTicketChangelogPlugin is still very slow. It takes over 30 seconds for some tickets. If I cannot find a solution I must disable this wonderful plugin.