[rt-users] Slow ticket loading....

[Alex Vandiver]

On 05/13/2014 05:39 AM, Michelle Sullivan via RT wrote:
> [snip]

This message was rejected from rt-users because it was over 1M of
logfiles.  Please don't send large messages to the list; you're welcome
to provide a link to complete logs for those interested in them, of course.

> Here's the result of what RT4 does to the DB (note: RT3.8 does *NOT*
> suffer the same problems - as am running that and just trying to
> upgrade..  Also note:  RT 4.0.19 on Apache 2.2 with ModPerl2 running
> against some very fast PostgreSQL 8.4.10 DBs (cluster of 4 servers) -
> round trip to them is around 25-50ms)... the query log for a *SINGLE*
> ticket follows...

This only occurs on the first request; DBIx::SearchBuilder calls
DBI->column_info to determine the set of columns for each RT table, to
be able to use them in the GROUP BY.  It then caches the information;
none of the queries to pg_constraints should happen after the first request.

There are two fixes to this; one is to find a way to not call
DBIx::SearchBuilder->Fields, which is what calls DBI->column_info, which
causes the slow queries.  RT is currently using the set of columns so
obtained to pass to GROUP BY to perform both a DISTINCT and ORDER BY; Pg
9.1 is smart enough to know that grouping by the primary key is enough
to imply the remaining columns as well[1].  As such, the attached patch
(which has only been briefly tested) may suffice.

This is only slightly papering around the problem, however, as any later
call to ->Fields will incur the same cost.  RT does not currently
contain any such calls, so I _expect_ it to be sufficient.  A more
robust solution would be to call ->Fields at server initialization time,
before the first request.  This would require also modifying
DBIx::SearchBuilder::Handle to cache the information by DSN, as it
currently only lasts until the dbh gets replaced -- which happens at
server startup.

> This has to be a bug of RT4 or of the DBIx modules it uses so cross
> posting to rt-bugs@ as well...

Please don't cross-post to rt-bugs@; it leads to duplicate tickets.
Once discussion has achieved consensus on the bug is the right time to
create a bug ticket.
 - Alex

[1] http://www.postgresql.org/docs/9.1/static/release-9-1.html#AEN109008

-------------- next part --------------
A non-text attachment was scrubbed...
Name: 0001-We-can-omit-the-heavy-on-Pg-Fields-call-on-9.1-and-a.patch
Type: text/x-patch
Size: 1447 bytes
Desc: not available
URL: <http://lists.bestpractical.com/pipermail/rt-users/attachments/20140513/7d3dff9a/attachment.bin>