How many sql queries per page is normal?

Ki's picture

Hi,

I know drupal is heavily database run to pull a single page.

I'd like to know the average number of sql queries run to construct a tipical (front) page.

I ran devel to collect query info on one of my sites I work on, and it shows that 800+ queries ran! Is this normal? I thought it was too many for a single page. If this is more than average, could you suggest ways to reduce the the number of sql queries? Did I miss big things to check as a new comer?

Thanks.

Comments

800 seems high to me, 3-400

catch's picture

800 seems high to me, 3-400 isn't unusual.

If you're using lots of blocks, you could try the block cache module - that might help reduce some queries. Also for anonymous users, 'aggressive page caching' pulls in the whole page with only one or two queries per user - so you can at least restrict some of the database work to authenticated users.

too many factors to say without more details

mike stewart's picture

your results depend on many factors, and you didn't give us very much information to help.

Off the top of my head:

  • what version of Drupal?
  • what modules are installed?
  • what kind of customization do you have?
  • what blocks do you have? if none, modify your theme to disable queries that aren't even showing data
  • IMPORTANT: what kind of caching are you using? (even modest caching can provide substantially different results)

Since you have devel module - how was your response time? slow or fast? Did you have duplicate queries?

--
mike stewart { twitter: @MediaDoneRight | IRC nick: mike stewart }

More info on my site here.

Ki's picture

Here is some more info on the site.

Version: Drupal 5.7

Modules:
--Core: Blog, Blog API, Book, Comment, Forum, Help, Menu, Path, Poll, Profile, Search, Stats, Taxonomy, Throttle, Tracker, Upload
--Contrib: Ad, Audio, Comment approval, Comment Mail, e-Commerce, Expert, Form collect, Form store, Forward, MyChaptcha, IMCE, Javascript Tools, Quotes, Scheduler, Taxonomy import/export via XML, Textimage, TinyMCE, Token, Update Status, Views, Webform
--Plus about 20 house built little modules.

Blocks: 17 blocks on front page. (about same before/after authentication)

Caching mode: normal.

I believe anon user's page would use significantly less number of queries when cache is used; but we are trying to reduce the number of queries even for logged-in users.

Block cache seems like a good idea; I may try this. It's bad that it is not maintained any more. Is block cache built-in function of Drupal 6?

17 blocks is a reasonable

catch's picture

17 blocks is a reasonable amount, so could be a lot of your queries - what else do you have on there?

There's a block caching function in Drupal 6, although it's a bit different to the one in D5 - I've used block cache for over a year now with few issues - the UI is quite clunky, but it's very flexible.

If your custom modules are very tiny, you might get a bit of a performance gain from amalgamating them into fewer, slightly bigger, modules, but it depends on what they're doing.

turn off Devel in prod.

Chris Charlton's picture

In a staging or production site you should have all Devel modules off or uninstalled. They affect performances quite a bit and should only be used in a development environment. The Devel modules could be adding a handful of queries per everything (node, page, block, etc.)

Chris Charlton, Author & Drupal Community Leader, Enterprise Level Consultant

I teach you how to build Drupal Themes http://tinyurl.com/theme-drupal and provide add-on software at http://xtnd.us

An easy way to do this

mikey_p's picture

An easy way to do this is to move all your development modules, such as code, devel, simpletest, (sometimes update status), and drush, into a folder just for development modules such as sites/all/modules/development/. This also works well for contrib and custom modules as well (sites/all/modules/contrib and sites/all/modules/custom). This lets you keep track of any modules that you may have had to *GASP* modify for your side.

Then it's easy to write a script that deploys your site, and in the process, deletes the development folder from the prod server.

-Mike

Queries

design_dog's picture

FWIW

Just out of curiosity... where are you getting that figure "800" from??

Devel spits out a slew of good interesting information and I'm just wondering what that 800 is based on.
There is also the _devel_queries and _devel_times in your sql db.

I'm slowly trying to figure things out here but I can tell you a basic install of 5.7, a Zen theme, a bunch of modules and ecommerce should be about in the 92 ballpark if I am not mistaken.

I have no idea what you're running on that page pull or even on the site, but 800 seems kinda over the top.

I think what everyone stated here is good info/feedback.

[Def Need a bit more info like maybe a url or some of output from devel, remember to turn devel off, use separate folders.]

Also, just want to point out that I think the ecommerce module is awesome!
__ But It is intensive and does require resource and a bunch of memory to run.
just out of curiosity how much memory does your site require?
Something like: Memory usage:
Memory used at devel_init(): 16.29 MB
Memory used at devel_shutdown(): 17.58 MB
Once you add/factor in ecommerce it jumps quite a bit.
I'm not telling you to turn it off or move it, but I would try to take a good look in that area.

May be a bad question but. Does anyone know the best way to clear your devel cache or log or is that even an issue?
May not even be a factor. Not sure how that works.

Note: disregard this post if any of this information is obsolete but I just figured I'd throw in a nickel.

/* NOT Related Information */ -- Now all I need to do is figure out the best way to Install Linux on a USB drive and run a Drupal dev site off xamps /localhost/ off a USB Stick.
The idea would be to have an independent OS plus a full dev / demo site capable of running independently just on USB.
You could take it anywhere, run your own OS, work on it where ever and no connectivity needed.
Not pointing fingers but Mike S would be good at that since he's the Ubuntu guy.
I tried Ubuntu so far with no luck. But I must say just booting Ubuntu from a live cd is just impressive.

WOW .........

JerryH's picture

800 ? ........ that can't be right .........

I'm at 200 now and thinking this is going to cripple a server under any real load, I think I'm going to have to start stripping a LOT of functionality out or figuring out some way of consolidating (or cleaning up) the queries.

Going to have to pray this goes down when devel (et all) are turned off.

I remember in the last app build getting to 10-15 a page and thinking it was way too many, and that it wouldn't scale.

Pretty much the same

PlayfulWolf's picture

Pretty much the same problem, but Drupal6

Memory used at:

devel_init()=1.71 MB,
devel_shutdown()=14.07 MB.

is it not too big difference?

also 200-400 depending on page... :(

---
naslenas.com. Drupal blog experiment.

drupal+me: jeweler portfolio

number of queries

askibinski's picture

The number of queries isn't the most important factor. It's the slow queries that count.
We've got a D6 site with a complex frontpage and over 40 modules:

Executed 607 queries in 75.12 milliseconds.

So even though there are many queries, it's not a performance issue, all queries are fast.

However, total execution time is around 700-900, so in my case I could probably do some optimizing in the PHP part of the LAMP stack.

You should seriously consider

Sylvain_G's picture

You should seriously consider enabling cache for block and pages, enable apc in php, use a cache bin to not use database cache (think memcache)

And put a reverse proxy ahead

400 queries per pages will never work well at scale

Drupal Dojo

Group notifications

This group offers an RSS feed. Or subscribe to these personalized, sitewide feeds:

Hot content this week