I’m not sure if anyone has brought this up before, but I haven’t found
anything on the lists.
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.
That’s broken. 120 queries means a minimum of 120 round trips to the
database. Even at 10ms per query, that’s 1.2 seconds.
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?
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.
-alex
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:
- What version of RT are you using?
- What database and version are you using?
- How many custom fields do you have set up?
- 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
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.
What queries were you seeing here?
What happens if you turn on $UseSQLForACLChecks in your
RT_SiteConfig.pm?
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.
What queries were you seeing here?
The repeated query is:
SELECT * FROM Attributes WHERE LOWER(ObjectType) = LOWER(?) AND LOWER(Name) = LOWER(?) AND ObjectId = ?’
With parameters: 35 / ShowTicket / RT::System-1
What happens if you turn on $UseSQLForACLChecks in your
RT_SiteConfig.pm?
Will try.
What queries were you seeing here?
The repeated query is:
SELECT * FROM Attributes WHERE LOWER(ObjectType) = LOWER(?) AND LOWER(Name) = LOWER(?) AND ObjectId = ?’
Attributes? Really? And not ACL?
[snip]
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.
this is interesting patch to look at.
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. 
We did a lot of in that area and keep doing. It’s often better to fire
several fast queries instead of one big in some cases, but anyway
better caching is welcome.
-alex
List info: The rt-devel Archives
Best regards, Ruslan.