[Rt-devel] Huge number of database queries

[Jesse Vincent]

Jeff Davis wrote:

> Hmm... perhaps Ctrl-R forced it to re-request everything. I'm not quite
> as concerned about this, because there's a workaround, but I'm glad to
> hear it's being improved.

Yeah, it generally does.

> 
>>> RT. However, a single dynamic page (e.g. ticket view) still generates
>>> 119 queries, which still seems like a huge number to me.
>> When I saw your blog post about this, I thought you were talking about
>> _SQL_ queries. 
> 
> What I mean, very specifically, is that when I turn
> "log_min_duration_statement = 0" in postgresql.conf, it generates 119
> lines that contain "duration:" in the log. These are primarily SQL
> queries, although I suppose that depends on your definition.

*nod* FWIW, enabling "log_statement" on 8.1 didn't do that for me.

> 
>>> Here is the breakdown for one dynamic page load (displaying a ticket):
>> How many custom fields and transactions on the ticket?
> 
> No custom fields and 3 transactions, as far as I can tell. We have an
> almost empty database.

> We haven't deployed it to production yet, but we will have a substantial
> number of users though, and most of those users will be using RT
> constantly through the workday. People will avoid using the ticketing
> system if every minor update or action interrupts them with a delay
> (even a small delay), so I'd like the system to be essentially
> instantaneous.

As would we.

> 
> I'm more worried about the stability of performance than anything else.
> With so many network round-trips to the database, I'm worried that any
> minor slowdown would make RT unavailable. We don't notice any serious
> problem right now, because our network ping time is ~0.2 ms, and our
> processor usage on the database server is also low. Also, we have
> essentially an empty database now, so what will the performance be like
> as it grows? Will the number of queries per page load change as the
> dataset changes?


It shouldn't change significantly, but if you want to stress-test, the
results can help us to improve RT.

> It also just makes it harder to administer. If I turn on query logging
> to diagnose a problem, the signal to noise ratio makes it almost
> useless.

I think that a little experience about what to grep out will go a long
way toward that. We've had a fair amount of experience and luck tuning
RT on Postgres with those logs.

>>> It's hard for me to imagine that we need 44 normal SELECT
>>> statements to view a ticket.
>> Can you pull out what they are? 
> 
> I have to scrub the data before putting it in a public place (yeah, I
> know there's nothing sensitive, but I still have to look to be sure), so
> this is just a little more detail to the summary:
> 
> 016  parse
>   001  "SELECT  * FROM Users WHERE LOWER(Gecos) = LOWER($1)"
>   002  "SELECT  * FROM Users WHERE LOWER(Gecos) = LOWER($1)"

LOWER(Gecos)? Really? Can you pull out the values there? I wouldn't
expect to see that in normal operation unless you're playing external
authenntication tricks.

>   001  "SELECT  * FROM Tickets WHERE id = $1"
>   002  "SELECT  * FROM Queues WHERE id = $1"

All thse seem pretty

>   001  "SELECT  * FROM Transactions WHERE id = $1"

This seems a little surprsiing. That should get pulled in from a bigger
prefetch of all txns related to that ticket

>   009  other
> 018  bind
> 018  execute
> 016  deallocate

I talked about those in my last mail. I'd love to see a patch to
SearchBuilder to reuse prepared statements, but I'm not sure it's a
high-priority target.

> 049  select
>   005  ping test
>   011  "SELECT ... from ACL, Groups, Principals, CachedGroupMembers ..."

11 ACL checks seems slightly high but not insanely high, since RT is
checking queue, ticket and custom field ACLs. Though you should pay
attention to Ruslan's recent mail about refactoring the ACL code to be a
bit smarter and cut down on the number of queries.

>   004  "SELECT main.* FROM ( SELECT main.id FROM CustomFields ..."

Your custom fields.

>   004  "SELECT DISTINCT main.* FROM Users main JOIN Principals ..."

Requestors, Ccs, AdminCcs and Owners.

>   006  "SELECT main.* FROM Tickets main  WHERE ..."

Unsure

>   006  "SELECT main.* FROM Links main  WHERE ..."

Six different kinds of links on tickets

>   002  "...Users main CROSS JOIN ACL..."
>   011  other

What falls into "other"?

> So I guess the numbers are: 119 round trips to the DB server, and 67
> queries. It would be nice if we could get both of those numbers down to
> something reasonable.

*nod* Improving query count has generally taken a back-seat to improving
overall query _performance_, since quite often there are one or two
queries in that set that dwarf the run time of all the rest. That said,
I'd love to make fewer queries and get the same results ;)  Would you
maybe be up for helping to improve things? Setting up a global
prepared-query cache seems like it might be a good, well-contained
project that would have a pretty direct impact on what's made your
spidey-sense tingle.

There's also interesting work to be done with caching and invalidating
caches of collection search results, possibly using a tool like MemCache
that I'd be happy to ramble about if somebody is interested in hacking
on it.

Best,

Jesse

> 
> Regards,
> 	Jeff Davis
> 
>