[Rt-devel] SQL/DBI caching

[Jesse Vincent]

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?
2) What database and version are you using?
3) How many custom fields do you have set up?
4) How long is this ticket's history?

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

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

Best,
Jesse


> -alex
> 
> _______________________________________________
> List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
> 

--