It appears there is a very large number of DB queries associated with Acidfree Albums.
Please see this node for a further description:
http://drupal.org/node/40355

CommentFileSizeAuthor
#10 queries_search.txt34.84 KBandre75
#9 queries_image_all_off.txt7.68 KBandre75
#8 queries_gallery_all_off.txt14.81 KBandre75
#4 acidfree_pager_performance.patch628 bytesvhmauery

Comments

andre75’s picture

andre75 commented

I am wondering if it would be possible to make more efficient use of the cache?

robb’s picture

robb commented

Based on the queries dump provided it looks very strange. If I am reading it right Acidfree is doing a lot more queries than it should. Caching would not help, except for anonymous users. Besides I dislike caching as a way to work around poor performance. I need to set up a test bed for this and do not have time for the next couple of days. Until then if anyone can provide additional details (query logs) for this, especially with a stock site with ONLY AcidFree running I would love to see them!

andre75’s picture

andre75 commented

Hello Rob,

thanks for the answer. I have done some testing with everything disabled except acidfree and some drupal essentials (like breadcrumbs and the primary links). The request did not drop much. It also looks as if gsitemap is doing a bunch of requests (I wouldn't know why). After disabling it, I got about 10 queries less.
I will post the output tonight, after I get home.
It almost looks as if Acidfree is doing all queries per default and then selects which ones to display.
In the log for instance, I am looking at a picture (no child below it). I can still see a whole bunch of requests which query the child and a whole bunch of requests which fetch the other pictures (in the acidfree navigation menu: 1 2 3 4 5 6 7 8 next page last page). It seems to query the full nodes for those, even though they are not displayed.
I would certainly be very happy if you could look into this, even if you get to it a few weeks from now.

Andre

vhmauery’s picture

vhmauery commented
Status: Active » Needs review
StatusFileSize
new acidfree_pager_performance.patch628 bytes

The function _acidfree_pager_rewrite loads each of the nodes. This is needed to check if there are multiple parent albums. I suppose for efficiency sake, we could simply do a query, but that really does not go well with the goal of encapsulation.

But here is a patch that does just that. Let me know what you think.

andre75’s picture

andre75 commented

I followed your earlier suggestion and modified the pager from 9 to 1 item. This already saved me 40 queries !!!
Impressive.
I am defenitely going to give your performance patch a shot. It sounds very interesting.
Thanks a lot !!!!!!!

andre75’s picture

andre75 commented

Seems to work nicely ;-)
I noticed another tremendous performance boost when I disabled the breadcrumbs in acidfree. Generally I like breadcrumbs, so I was wondering if a similar hack can be found for the breadcrumbs.
With breadcrumbs disabled, less items in the pager and your patch I went from 143 to 40 !!!!!!!!!!
When I enable breadcrumbs I am at around 71 (after I got rid of one level of hierarchy).
Seems like we are getting somewhere really fast now!

merlinofchaos’s picture

merlinofchaos commented

FYI, all of those gsitemap loads were part of node_load(), I believe. gsitemap has a piece of data that gets loaded into a node every time it loads.

andre75’s picture

andre75 commented
StatusFileSize
new queries_gallery_all_off.txt14.81 KB

Logfile 1 for robb:
This is a Album which has a bunch of child albums (7 total).
Queries 158, with every block turned off and 4 items in breadcrumbs (incl. home)
See Attachment

andre75’s picture

andre75 commented
StatusFileSize
new queries_image_all_off.txt7.68 KB

Logfile 2 for Robb,

this is a picture in a gallery. Everything is turned off again except comments (they were off in the gallery). No menu no sidebar at all, 7 items in the breadcrumb:
still 80 queries (this is all with the above patch already in place and the number of items in the pager set to 3 - everything below 3 does not reduce the number of queries)

With the sidebar menu, recent blogs, search and ad banner I only get 3 more queries.

andre75’s picture

andre75 commented
StatusFileSize
new queries_search.txt34.84 KB

Here is another list of queries. This time it is from a search i performed. The search extracted a whole bunch of pictures with pagers. It comes down to about 300 queries (this time I didn't bother switching the menu and latest blogs off)

andre75’s picture

andre75 commented

Would it be possible to have only 2 or 3 levels in the breadcrumb trail?
.../subcat5/subcat6/subcat7
Instead of the full breadcrumb trail? A lot of the queries seem to be related to fetching the parents across these.
I would also like to change the pager to : "previous" and "next" and nothing else, but I don't want to do it globally (pager.inc) unless there is no other way.

Let me know what you think.

vhmauery’s picture

vhmauery commented

I am not sure about the breadcrumb trail. I really don't think it is acidfree's problem to do the trimming. But by not doing the trimming this early in the page, it means more SQL queries. But I think that is really something that should be up to the theme.

As for the pager, Acidfree just calls the standard drupal pager, so you would have to change it for your entire site. However, it would be possible to write a pager theme that drew the pager different depending on the drupal path or something, so you could have next and prev for Acidfree pages and the whole gamut for other pages. And I think this one actually would save some SQL queries because the nodes get loaded as it is trying to figure out what is next and stuff.

vhmauery’s picture

vhmauery commented
Status: Needs review » Fixed

I am afraid for now, all I can offer is the performance patch from post #4. I am really not sure what else to tell you. This code will be in the latest release.

vhmauery’s picture

vhmauery commented
Status: Fixed » Closed (fixed)
andre75’s picture

andre75 commented

I put a html cache on top of my drupal install. Now my DB is queried only sporadically.
Thanks for the update.