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

[Michelle Sullivan]

Alex Vandiver wrote:
> 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.
>   
I actually didn't know the size (though I knew it might be bigger than
acceptable by the list software - figured some intelligent person would
choose to deal or allow it ;-) )

>   
>> 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.
>   
Yeah - i'm seeing this every time a ticket is loaded - so RT/DBIx is not
caching it... and that's probably because the IDs are different - so
even pgpool/memcached setup can't cache it...

> 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.
>   
I'm on 8.4 will that work as well, or should I not bother? (I can't
upgrade to 9.1 yet, as much as I want to - the software on the system is
in the hands of the Operations team and controlled by puppet so 8.x ->
9.x is impossible without corrupting everything or Operations input).
> 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.
That sounds sane (calling at init) .. as for the calls to ->Fields I
think you do call it in the custom fields code... because that's where I
first saw this issue 6 months ago (and it was dismissed.)
>   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.
>   
Ok my apologies - it's been a while since I've been here - maybe a cross
post to rt-devel would have been more appropriate - anyhow, my apologies.

 


-- 
Michelle Sullivan
http://www.mhix.org/