[Rt-devel] Huge number of database queries

[Jesse Vincent]

On Sun, Nov 18, 2007 at 02:10:09PM -0800, Jeff Davis wrote:
> RT seems to be generating a huge amount of database traffic. After
> investigating, we found that most of the queries were because the static
> content (e.g. .css files, image files, .js files) was also generating
> database traffic, about 19 queries each. 

So, RT 3.6.6 should significantly improve the state of the static
content. We've finally deployed CSS::Squish to force only a single CSS
file for your RT instance and we've improved the browser hinting that
should force caching so you only load it once unless you're forcing the
browser's cache to wipe itself on reload.  But do note that even 3.6.5
should be caching those files.


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

> Here is the breakdown for one dynamic page load (displaying a ticket):

How many custom fields and transactions on the ticket?

> 
> 119  queries total
>   067  statements
>     049  select
>       005  ping test
>       044  other
>     016  deallocate
>     002  set
>   052  parse/bind/execute
> 
> Is this expected? Is there a way to bring this to a more reasonable
> number? 

Do you just find it "not-right" or are you seeing poor performance?
Either way, I'd like to see what the queries are, so as to be able to
give you a decent answer there.


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


The 16 additional prepared statements,
> which are then immediately DEALLOCATEd are probably excessive as well.

I suspect that the issue here stems from the fact that until _very_
recently, DBD::mysql and DBD::Pg didn't support server side prepared
statements - everything was emulated on the client. Putting together a
cache at the ORM layer should be doable. But I've never seen those sorts
of queries actually generate measurable overhead.

Best,
Jesse