Mailing List Archive: Request Tracker: Devel

SQL/DBI caching

Index | Next | Previous | View Threaded

alex at pilosoft

Jul 5, 2009, 1:36 PM

Post #1 of 7 (1466 views)
Permalink

SQL/DBI caching

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

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

jesse at bestpractical

Jul 6, 2009, 7:02 AM

Post #2 of 7 (1385 views)
Permalink

Re: SQL/DBI caching [In reply to]

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
>

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

alex at pilosoft

Jul 6, 2009, 7:11 AM

Post #3 of 7 (1383 views)
Permalink

Re: SQL/DBI caching [In reply to]

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

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

jesse at bestpractical

Jul 6, 2009, 7:14 AM

Post #4 of 7 (1390 views)
Permalink

Re: SQL/DBI caching [In reply to]

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

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

alex at pilosoft

Jul 6, 2009, 7:19 AM

Post #5 of 7 (1388 views)
Permalink

Re: SQL/DBI caching [In reply to]

On Mon, 6 Jul 2009, Jesse Vincent wrote:

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

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

jesse at bestpractical

Jul 6, 2009, 7:21 AM

Post #6 of 7 (1385 views)
Permalink

Re: SQL/DBI caching [In reply to]

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

>
> With parameters: 35 / ShowTicket / RT::System-1
>
> > What happens if you turn on $UseSQLForACLChecks in your
> > RT_SiteConfig.pm?
> Will try.
>

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

ruslan.zakirov at gmail

Jul 6, 2009, 2:36 PM

Post #7 of 7 (1382 views)
Permalink

Re: SQL/DBI caching [In reply to]

On Mon, Jul 6, 2009 at 6:11 PM, Alex Pilosov<alex [at] pilosoft> wrote:
> On Mon, 6 Jul 2009, Jesse Vincent wrote:

[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: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel
>

--
Best regards, Ruslan.
_______________________________________________
List info: http://lists.bestpractical.com/cgi-bin/mailman/listinfo/rt-devel

Index | Next | Previous | View Threaded

Interested in having your list archived? Contact Gossamer Threads