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
