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/ -- RT Training - Dallas May 20-21 http://bestpractical.com/training
