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

[Michelle Sullivan]

Michelle Sullivan wrote:
> 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).
>   

FYI it appears every new connection to the DB is causing
DBI->column_info to be called.  On the 'in test' system this happens a
lot .. page loads when it opens a new connection is +4 minutes...
regardless of the page.

It's quite usable with and without pgpool (it's faster without) - except
when a new connection is opened.


>> 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 might be a misnomer, as it's connection opening that causes it...
and a call to customfields should be using the already open handle... in
theory..

Michelle

>>   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/