I'm seeing this query near the top of slow query logs on one project:

LPAD(COUNT(*), 8, '00000000') AS count,
bundle_md5,
filename
FROM advagg_bundles AS ab
INNER JOIN advagg_files AS af USING ( filename_md5 )
WHERE ab.root = 1
AND timestamp > 1314892636
GROUP BY ( af.filename )

Part of the reason this is so slow is because there are over 177k rows in advagg_bundles, the oldest timestamp from a bundle is from August so it seems like this could increase still further.

I've not looked into why so many rows are being created, but wanted to check whether this number of rows is unexpected or not (advagg_files has around 315 rows which seems more likely).

CommentFileSizeAuthor
#6 faster_bundler_analysis-1283522-6-D6.patch1.85 KBpdrake

Comments

killes@www.drop.org’s picture

killes@www.drop.org commented

On another install I've found liek 30k rows in the advagg_bundles table. However, these bundles do not exist on-disk. Would it be safe to remove them? Is this a bug in advagg which should delete the rows when it removes certain bundles?

mikeytown2’s picture

mikeytown2 commented

advagg_cron should prune old database entries. I have 12k entries in my DB for the oldest site running advagg; 40k on another one. Query takes 300ms for me to run.

Adding a single index for root & timestamp does speed it up, but not a lot. Doing a simple SELECT * on the advagg_bundles table takes around 120ms to run for me.

By default the file is gone after 2 weeks of doing nothing & the database entry is gone after 6 weeks. Once the database entry is gone I can no longer re-create that exact combo, that's why I have it stick around a little bit longer.

This will tell you if CSS or JS is causing most of the bundles.

SELECT 
  COUNT( * ) AS count, 
  temp.filetype
FROM (
  SELECT 
    COUNT( * ) AS count, 
    af.filetype
  FROM advagg_bundles AS ab
  INNER JOIN advagg_files AS af
  USING ( filename_md5 )
  GROUP BY (
    ab.filename_md5
  )
) AS temp
GROUP BY (
  temp.filetype
)

On one setup its about 1/5 CSS, 4/5 JS. Another is 50/50. Yet another is 3/4 CSS, 1/4 JS.


The solution I see is running this on cron and having the cache last longer. Any other ideas?

catch’s picture

catch
he/him
Primary language English
commented

Having the cache last longer sounds good. This particular site is in development so it is hard to tell exactly how it would pan out in practice.

rjbrown99’s picture

rjbrown99 commented

+1, same query showing up on my site from time to time. At the moment I have about 35k rows in that table. I'm on a slightly older release before advagg_cron, so at least in my case upgrading may fix me.

mrfelton’s picture

mrfelton commented
Priority: Normal » Major

Ours is showing 63157 rows in that table, with devel showing that the advagg_bundler_analysis function is taiking 2665.68ms!! wow, that is slow. No wonder the ste has been performing like a dog. We have some 7 or 8 themes, and the bundler is set to do 3 bundles per page... so I'm guessing that the number of themes has massively magnified the number of bundles.

I'm upgrading this issue to major status, as this is a serious performance hit - for a module that's supposed to improve performance!

pdrake’s picture

pdrake commented
StatusFileSize
new faster_bundler_analysis-1283522-6-D6.patch1.85 KB

The attached patch provides significant performance improvements in the advagg_bundler_analysis function.

pdrake’s picture

pdrake commented
Status: Active » Needs review

Guess I should have set this as needs review. The patch reduced query time from 1.26s to 0.07s in my tests.

mikeytown2’s picture

mikeytown2 commented
Status: Needs review » Fixed

Tested the patch and it has been committed. Thanks for rewriting the query!
http://drupalcode.org/project/advagg.git/commitdiff/c799f66fa9e1d419dee3...

paulgemini’s picture

paulgemini commented

Any special steps I should take after updating to the latest dev with this patch? Should I rebuild my cache? Flush it?

mikeytown2’s picture

mikeytown2 commented

No special steps need to be taken. Run update.php or in drush run updb and you should be good to go. Take note that the db update gives a minor speed improvement; the main speed improvement came from rewriting the query from a join into a sub query. For more information see this explanation on the subject: http://stackoverflow.com/questions/141278/subqueries-vs-joins

Status: Fixed » Closed (fixed)

Automatically closed -- issue fixed for 2 weeks with no activity.

rjbrown99’s picture

rjbrown99 commented

For what it's worth, updating to 6.x-1.6 was completely seamless. Checking NewRelic, there are no calls from advagg at either the php or database layer in the top 20. Nice enhancements, especially with killing the COUNT queries.