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