[Rt-devel] SQL/DBI caching

[Alex Pilosov]

On Mon, 6 Jul 2009, Jesse Vincent wrote:

> On Sun, Jul 05, 2009 at 04:36:09PM -0400, Alex Pilosov wrote:
> > I'm not sure if anyone has brought this up before, but I haven't found 
> > anything on the lists.
> 
> Database optimization, both in how we use the database and how the
> database is set up is a pretty common topic.
> 
> > I'm seeing that RT makes very very large number of SQL queries even to
> > process a minimal page. To display a single ticket, I'm seeing *120*
> > queries to the database in a very basic RT install. Most of them are very
> > much repetitive - ACL, Atrtibutes, Users, Groups, Principals.
> 
> Can you tell me:
> 
> 1) What version of RT are you using?
3.8.4

> 2) What database and version are you using?
pgsql 8.3

> 3) How many custom fields do you have set up?
0

> 4) How long is this ticket's history?
1 (just one entry in the ticket).

> 
> > I see that there's some support in DBIx::SearchBuilder for cacheable 
> > records - but nevertheless, I see certain things repeatedly fetched.
> > 
> > Is this a known problem? 
> 
> It's an area that can always see improvement.
Well, so far, I've found that SearchBuilder will not do negative result
caching (as in, if there's no entry for a certain query, it will keep
re-querying repeatedly). Fixing that went from 120 queries to 80.

> > I've hacked up enough things for DBD::Gofer caching support - and my
> > display times went from 1s to .3s. Of course, this is not the right
> > solution - DBIx::SearchBuilder should be more efficient in caching.
> 
> I'm not sure I agree with you, but I haven't seen the logs you're
> working with.  The danger of caching too agressively at the
> SearchBuilder layer is that your cache isn't shared between multiple
> processes and can quickly become stale/inaccurate.
> 
> I look forward to more details of your analysis. I'd be thrilled to see
> improvements in this area.
I was kind of hoping someone would have done this already. :)

-alex