This simple page (displaying an image in a gallery and a menu)
http://www.aguntherphotography.com/oregon/covered_bridges/photos/photo_1...
creates 143 database queries. Yes I have cache off at the moment, because if I turn it on I get "Duplicate entry" warnings and I get Error 12 from the mysql database (maybe the request from the cache exceeds some limit imposed by my provider).
Anyways, I am trying to find out what the requests look like to solve this somehow.
I am stunned to learn, that this simple page requires 143 queries. This is just insane.
Here is the output of the devel.module (all queries)
http://www.aguntherphotography.com/files/queries.txt
sorry for the weird formatting. I wanted to avoid a link showing up.
How can there be those many requests on a simple page like this? Did I mess up somewhere?
Comments
Whoa. I am very interested
Whoa. I am very interested to find out about this too. I am having my own issues with Drupal devouring system resources.
-zach
------------------------
harkey design
: z
The path module performs a
The path module performs a lot of queries. They're very fast queries, fortunately.
-- Merlin
[Point the finger: Assign Blame!]
[Read my writing: ehalseymiles.com]
-- Merlin
[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]
Devel
If you copy the url of the txt file from my original post you can see the output of the devel module.
Even worse, now I have enabled the cache in my local test environment and I am still getting 125 queries:
Would be really nice to hear why those many queries are necessary. Seems like the path module does only one, but it takes the longest to resolve! So it is actually the slowest!!!!
Even more interesting:
Why 143 queries for such a simple page?
Why still 125 with cache turned on?
Odd, really odd!
There is no caching for
There is no caching for registered users. Look at the number of queries for a cached page when an anoymous user accesses the page. The number should be ca. 3.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
No 125
The figure was taken in the local test environment. Turned caching on with Firefox, browsed the page with IE as anonymous. I am aware of the fact that you stated!
I've been curious about the non-caching
[edited: after reading further in the thread I noticed that Dries explained this...]
Killes, I've noticed what you mentioned, that caching happens only for anonymous users, and I was curious about it. Do you happen to know why this is?
On my church sites, most of the visitors are our church members who are registered users. We're getting about 200 queries (!) on our main page, so naturally I'm interested in improving this too.
Thanks!
Ok, the Navigation menu
Ok, the Navigation menu you're using uses a large number of queries because of the nature of hierarchical menus. That's something I've been working on lately; clearly acidfree has an inefficient method of retrieving that menu.
The recent blog posts block is loading the full node for each entry there (that seems odd when it just needs titles) and each node load is taking 7 queries!! That's pretty serious stuff, since 10 times that's 70 queries, half of your total in just that one block.
-- Merlin
[Point the finger: Assign Blame!]
[Read my writing: ehalseymiles.com]
-- Merlin
[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]
Ah. Hm
I used the Recent blog posts block that was built into drupal.
Why doesn't this stuff get cached?
Dunno. You can use a PHP
Dunno. You can use a PHP snippet to do a more efficient recent blog posts, though.
Another big use of queries looks like the breadcrumb trail. Not sure what you can do about that.
-- Merlin
[Point the finger: Assign Blame!]
[Read my writing: ehalseymiles.com]
-- Merlin
[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]
I switched it off
After disabling the whole block it went from 125 queries to 121 (witch cache). That didn't even make a dent.
What's weird is that the
What's weird is that the code for recent blog posts in the blog module doesn't load the full nodes. So maybe I'm wrong about what's doing all those node_loads.
-- Merlin
[Point the finger: Assign Blame!]
[Read my writing: ehalseymiles.com]
-- Merlin
[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]
I switched almost everything off
I switched almost everything off. I am almost convinced now, that this is a problem with acidfree.
Even so, other nodes (like tutorials, which is a book page) also easily get 90 queries (without acidfree, without a lot of features)
Yea, I'm guessing acidfree
Yea, I'm guessing acidfree is doing a lot of queries.
Book pages are going to for similar reasons. There's a lot of information to pull from the database and hierarchies tend to require a lot of queries to get that info.
I don't know anything about Drupal's caching, either.
-- Merlin
[Point the finger: Assign Blame!]
[Read my writing: ehalseymiles.com]
-- Merlin
[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]
Someone claimed
I remember someone here in the forums claiming, that witch cache enabled you get only 2 queries per page (session and cache retrieval). I am shocked to learn I get over 120.
Even phpBB is a lot more gracefull.
Definitely takes more than 2 queries
If you take a look at the cache table in Drupal you will realize that the caching 'unit' is not the entire page. Rather it is generally done at the module level and/or block level. Therefore even if every thing were getting cached nicely you would still require a lot more than 2 queries to retrieve the individual bits and pieces and put them together.
AFAIK In Drupal it is the responsibilty of the module developer to use caching. So if you have a module that does a lot of database querying and does not use caching then enabling caching is not going to make any difference for that mdoule. However I am not 100% sure about this.
A module that uses the cache table effectively reduces the many queries it fires to very few (maybe 1 to 3).
Can you give us 2 listings of the queries executed, 1 with caching disabled and 1 with caching enabled?
My Drupal-powered Blog: ThoughtfulChaos
Page cache
As Varun points out, there are different levels of caching. However, Drupal does have 'page caching' where the caching unit is an entire page. If you enable page caching, it only takes 2 queries to serve a cached page. Page caching only works for anonymous users. We can't serve cached pages to registered users, as these pages are typically personalized. In that case, we try to take advantage of block caching, menu caching, etc.
How do you enable page caching?
How do I enable page caching? Is this the 'loose caching' feature that will be available in Drupal 4.7.0? I don't think 4.6.x has this.
Was I right about the Module developer and cache thing? Does a module developer have to explicitly do something to make sure his module uses cache?
Btw I can see a Drupal 4.7.x link in the downloads. Does this mean the 4.7.0 is going to released soon? :-)
My Drupal-powered Blog: ThoughtfulChaos
More on caching
You can enable page caching from the settings page at
?q=admin/settings. It's available in Drupal 4.6, but it has been extended in Drupal 4.7. Drupal 4.7 has 'loose caching' which allows you to cache more aggressively. Loose caching is especially interesting for large or active Drupal sites whose page cache is otherwise flushed too frequently.You're right about the module thing; Drupal core can't figure out what is cacheable, and what is not cacheable. It is the module developer's responsibility to take advantage of the available cache infrastructure. The cache APIs are documented in the developer documentation.
Is there a separate setting for page caching?
Correct me if I am wrong but I cannot see any separate setting for page caching in Drupal 4.6.3. Maybe what you mean to say is that it is inherently enabled when you enable caching i.e. there is no separate setting for 'page caching' and it is automatically enabled when caching for the site is enabled (as it should be).
It was very stupid of me to assume that Drupal core would understand what is cacheable and what is not. The module developer obviously needs to be explicit in this regard.
Maybe a feature request needs to be filed for acidfree asking the developer to use cache wherever possible.
Thanks.
My Drupal-powered Blog: ThoughtfulChaos
Oehm
Yeah, well I enabled that and I still get 125 quereis.
Either way, even without caching I feel 143 is too much for such a simple page!
Optimize
If 143 queries is too much, you should either (i) turn off more functionality, (ii) help us optimize Drupal, or (iii) use software that can do the same with less queries. I hope you go with option (ii).
Hm
I was wondering when that would come up. Of course I am inclined to go with (ii) as there is nothing comparable to Drupal. I am not saying Drupal does a bad job and I will defenitely try to contribute as much as I can. As of now I have no PHP knowledge, but I am a fast learner (once I find some time).
You maybe should investigate
You maybe should investigate more before making false claims. For an anonymous user the entire generated html is stored and later retrieved.
--
Drupal services
My Drupal services
--
Drupal services
My Drupal services
Is caching enabled for Drupal.org?
My bad. I really didn't have the time to investigate so just tried to help the best I could. Will try to investgate more in the future.
Wanted to ask 2 more question regarding caching.
1st question:
A Drupal site typically has some blocks that cannot be cached. For e.g. the 'Recent Comments' block. This block also shows how long ago the comment was posted. If for anonymous users the entire page is cached then times shown in the 'Recent Comments' block will be outdated. How is this issue dealt with? Or am I missing something here?
2nd question:
Is caching enabled for Drupal.org?
My Drupal-powered Blog: ThoughtfulChaos
Caching on drupal.org
Caching is enabled on drupal.org. You're right; anonymous users will not see the latest changes in between 'cache flushes'. The drupal.org cache is flushed every 3 minutes though so it's not big of a problem.
The stats with 125 queries was retrieved by anonymous
I am not sure why you say that. The cached stats were retrieved by anonymous. For that I enabled access to devel.module for anonymous (I did that in my local test environment) and then browsed the page with another browser.
There are 4 cache queries if you looke closely.
Devel.module output gets cached
I believe that the output of the devel.module gets cached too. It looks like it executed 125 queries, but it didn't. It is somewhat confusing, but might be documented.
Ohhhhhhh
Ohhhhhhh, how interesting!!!!
So how can I figure out the the queries performed?
If that would be true, and I can get the cache working, then all would be fine. Hail Drupal. I am not sure this is true though, as I have put devel.module on a second site where cache was always enabled (even before turning devel.module on).
After I enabled it, I could see 130queries.
Think about it, I enabled the module with the cache on on that site! There is no way the cache could contain output of devel.module with the cache off. And even so, when the cache is off, the output of devel.module does not get cached, or does it?
Not to get off topic but I'm
Not to get off topic but I'm trying to test my own setup to compare with yours so I installed the devel.module and enabled it.
I just can't figure out how to get it to report anything like you did. There isn't any documentation with the module. Do I need to put a line in my page template or something?
-zach
------------------------
harkey design
: z
admin » settings » devel and access permissions
Just check whether you have enabled the module and whether you have configured the access permissions for devel correctly. Devel also has its own settings page (admin » settings » devel) that has a few settings that need to be configured.
I did all of this many weeks back so I might be missing something here. But you definitely do not need to add anything to your templates.
My Drupal-powered Blog: ThoughtfulChaos
Go to
/admin/settings/devel and turn everything on (except the last drop down menu)
-------------------------------------------------
http://www.opentravelinfo.com
http://www.aguntherphotography.com
Should we be worrying about this?
I just had a quick look at the queries that were executed and the time they took and it struck me that even the 143 queries were taking just 332 microseconds i.e. just 0.000332 seconds. Page execution time is also only 1.22 ms.
Consider that in the entire process of a particular page being requested by a browser and the page finally rendered there are many more steps that take a lot more time. 2 steps that come to my mind are:
So even if you were to try and optimize the number of queries executed your user experience would still not improve by much. Unless your site get very high amounts of traffic.
However if you want to reduce the number of queries and execution time of the queries so as to stay within the caps stipulated by your hosting provider then its another issue.
I had a quick look at the queries but they would be much more helpful if the module that fired the query was known. I believe you used devel.module to generate the listing. Does devel.module have an option to show the module? If yes please give us a listing that has the module names beside the queries.
My Drupal-powered Blog: ThoughtfulChaos
Yep
Microseconds is a bug in the version of devel I am using. Those are milliseconds so about 0.3s, which is quite slow according to the stats on Alexa.
I recently switched this site so the Alexa.com stats are not accurate, but I run another site on the same host in drupal which is classified as slow while this one was classified as fast.
I am also worried about serverload!
Thats the main driving factor. The limit is 72000 queries per database user per hour with up to 3 database users this gives me about 210k possible queries per hour (with a small mod to database.mysql.inc that I implemented).
Here is an output with everything turned on. It gives some indication as to where it comes from. The switch I turned on was called "Display redirection page:"
Description: "When a module executes drupal_goto(), the query log and other developer information is lost. Enabling this setting presents an intermediate page to developers so that the log can be examined before continuing to the destination page."
Whatever that means. Anyways the resulting log gives a little more info:
Thanks for your answer.
This page:
http://www.aguntherphotography.com/winstuff.htm
Only generates 20 queries. It has all the other features (breadcrumbs, nav-menu, recent blog entries ...)
So I guess it really is an acidfree problem then.
Can't see any extra info
What extra info? I cannot see any extra info.
My Drupal-powered Blog: ThoughtfulChaos
You are right
This is all it puts out. Sorry for the confusion.
Either way, this page took about 0.9s to generate (like i said it is 3 orders of magnitude wrong micro->milli).
I was under the assumption, that 20 queries would be pretty bad but 140 is much more than I ever thought possible.
If you have 10,000 visitors/hour (I don't) this means 1.4 Million queries / hour. I think this is pretty bad, considering sites like theonion.com. How can they afford such a heavy load on the mysql server?
Weird formatting
By the way if you want to avoid links, just use
<code>tags when posting.--
If you have a problem, please search before posting a question.
No
the
tags do not prevent you url from being clickable. And since I know google indexes drupal.org very well, I don't want them to index my queries.txt files.
Show my the post to a short url with code tags only and I am all with you.
robots.txt
robots.txt is your friend here. See http://www.robotstxt.org/wc/robots.html
--
Tips for posting to the forums
Almost
Yes I know robots.txt and I am using it.
Not every spider cares though and overloading my .htaccess is not really going to make my host happy. So why create unnessary links here, just so I have to remove them on my site?
Now can we please stop this and focus on the subject?
acidfree seems to be making too many database intensive queries
I just did some quick analysis on the queries_all.txt file.
5 of the 8 queries that take more than 10 ms are by acidfree.
8 of the 14 that take more than 5 ms but less than 10 ms are by acidfree.
7 of the 19 queries that take more than 1 ms but less than 5 ms are by acidfree.
Seems like turning off acidfree or optimizing it might reduce the overall time required to execute all the queries significantly.
My Drupal-powered Blog: ThoughtfulChaos
Actually got removed from server...
My sites where removed from Lunarpages server for CPU overuse. See my post here http://drupal.org/node/39427.
I have since moved from Lunarpages. No matter what anyone says, we have a problem. Both of my sites were relatively small, one only allowing registered members to view (using securesite) with membership around 100 and no more than 5 members on at any given time. The other site averages 100 unique users per day, all anonymous.
I am
I am with you on that one. Although I am not (yet) running into issues at my host, I want to avoid this as much as possible.
I do get frequent error 12 messages when the cache is turned on, indicating that my requests are too big.
Either way, this is what got me to investigate further with the devel module. What I saw was really scary.
I don't think mysql queries generate a lot of CPU usage though. Maybe your problem is a different one?
One of the reasons I chose drupal over typo3 was, that it was promoted as more scalable and less resource hungry.
I wonder if this is simply, because drupal without any modules is rather slim. The moment you start to extend functionality, your usage will go through the roof.
My site averages about 700 anonymous visitors a day. For a photo site I do not expect this number to grow that fast, but I am doing other sites too and I can see similar numbers of queries there.
I believe I am still well below the hosts limits, but why wait until it is too late?
The quality control on the
The quality control on the contrib modules is much less robust than on Drupal itself.
-- Merlin
[Point the finger: Assign Blame!]
[Read my writing: ehalseymiles.com]
-- Merlin
[Read my writing: ehalseymiles.com]
[Read my Coding blog: Angry Donuts]
Naturally
Which makes sense, no doubt. It is not necessarily a statement of bad quality. I am extremely satisfied with acidfree for instance.
I am wondering though, would it be possible for drupal to handle the caching for all the requests of the underlying modules? It would probably be only 90% as efficient (if possible). I would hate to see me using some other CMS for such a stupid reason.
Do you have any recommendations what I can do right now to reduce the number of requests (as I said, switching off almost everything on the picture page didn't help much).
Andre
Do ya think?
I posted a topic talking about DB queries not so long ago...
http://drupal.org/node/39607
To help ease some of this I'm adding Zend Platform for page caching, which takes away some of the realtime beauty of the system but I have no choice.
Let me know if you know better ways other then Stored Procs.
-p
I am not sure
I am not sure how to do that on a host (I don't have full access to the unix environment). Can you send me a link to the Zend caching?
I was just doing some simple math.
If I get 100visitors/hour that browse on average 15 pictures each this is:
100*15*143=214,500 queries/hour. This is already exceeding my hosts limits. Very bad! 100 visitors / hour doesn't even sound that much.
Sounds like you're out growing you provider...
Zend is not free and most likely not an option for you in a hosted environment but I included the link anyhow:
http://zend.com/store/products/zend-platform/in-depth.php
I run all my own servers so I have more control (equals expense) but I need it.
-p
Outgrowing?
No way. Before I migrated to drupal I was berely using 10% of my resources. Now all of a sudden I am getting close to the limits?
It is always easy to through more hardware at a problem. Microsoft has been doing that for years.
Do you really think that a site with 700 visitors / day requires a dedicated server or an expensive host?
Based on your estimates...
No! Please don't lump me with Microsoft....I'm a linux guy.
Based on your estimates above you get 100 visitors an hour right? Is the unique, not hits? I don't have the insight into your previous traffic so I can provide anything useful in that area.
My solution works for me and that's because I'm not limited to a hosting company that doesn't mean it will work for you. Just providing one example of how I fixed my issue without moving to another system or trying to rewrite some modules.
:-)
I thought that might get someones attention ;-). No offense intended!
Not quite but getting there, yes. It is unique visitors yes (according to awstats, I get about 10x as many hits and about 5x as many page requests).
My previous traffic refers to a static page. I am using about 10% of my avail. bandwidth and mysql was obviously no issue.
Yes I understand, and I am grateful for the idea. It brought me to this:
http://www.phpfreaks.com/tutorials/110/0.php
I am not sure how much of a rewriting of drupal would be required. It seems as if each query would have to be rewritten.
What do you think about this?
Andre
Caching...
Zend is a content caching system and the link you provided is database caching of sorts.
You may only have to change where the database gets initialized and then call a cache function.
// require('adodb.inc.php');
// $ADODB_CACHE_DIR = '/home/eric/adodbcache';
$db = &ADONewConnection('mysql');
$db->Connect('localhost', 'username', 'password', 'database');
$result = $db->CacheGetRow("SELECT * FROM table WHERE id='1'");
print_r($result);
Either can be used and it may not take all to much work to force your drupal system to use it. Hrmmm! Would be interesting if this type of caching was added to drupal along with real page caching for authenticated users.
Update
With some help, I have managed to get it down between 70 to 40 (depending on wether I am using Breadcrumbs or not):
http://drupal.org/node/40360
It seems that Breadcrumbs can be a major query generator if not used carefully!
Will keep this thread updated!
Andre
How about PostgreSQL?
Most of this thread is concerned with queries to MySQL. I was wondering if someone has done any performance comparisions between MySQL and PostgreSQL when running Drupal?
I'm starting up a new site on a virtual dedicated server account I have so I can install either database. While I understand the advantages and disadvanges of each database, I'm unclear to which database would be the better of the two for Drupal.