In storage_class (class.admin.inc) which is executed when you visit the storage container page /admin/structure/storage/class/% a query is executed to determine the storage container sizes for managed assets.

For our queries this takes ~ 15 minutes, and multiple refreshes of the page spin off new queries. With enough, we can bring down the site altogether by tying up mysql.

The explain shows that the query is using temp tables and filesort, we need to either optimize this query, or allow the size indication to be optional.
id select_type table type possible_keys key key_len ref rows Extra
1 SIMPLE storage_instance index NULL file_id 9 NULL 134312 Using index; Using temporary; Using filesort
1 SIMPLE storage ALL NULL NULL NULL NULL 114129 Using where; Using join buffer
1 SIMPLE storage_file eq_ref PRIMARY PRIMARY 4 domesticweathertest.storage.file_id 1 Using where
1 SIMPLE storage_selector eq_ref PRIMARY PRIMARY 261 domesticweathertest.storage.selector_id,const 1 Using index

CommentFileSizeAuthor
#3 storage_api_class_queries-2402849-3.patch4.69 KBmrjmd
Support from Acquia helps fund testing for Drupal Acquia logo

Comments

cancerian7’s picture

cancerian7 CreditAttribution: cancerian7 commented

I am also facing this issue with over 40K image files, any patch for this?

I can go directly to the edit page without waiting /admin/structure/storage/class/%/edit

But If I make some changes and save the settings then I have to wait for this query to finish.

Perignon’s picture

Perignon CreditAttribution: Perignon commented

I am going to get a DBA friend of mine to take a look at this.

mrjmd’s picture

mrjmd CreditAttribution: mrjmd commented
Version: 7.x-1.6 » 7.x-1.x-dev
Status: Active » Needs review
FileSize
storage_api_class_queries-2402849-3.patch4.69 KB

Here's my attempt at a solution to this issue. I've completely removed the subquery in storage class and moved the calculations to be done per class/container on demand via ajax links. Initial testing seems to run smoothly.

Perignon’s picture

Perignon CreditAttribution: Perignon commented

Thank you for the contribution. I will get a look at is as soon as possible. I'm trying to put out some burning fires tonight we broken websites from upgrades!

Perignon’s picture

Perignon CreditAttribution: Perignon commented

I think this is an acceptable approach. Can we get someone that has a lot of files in Storage API to check this out?

mrjmd’s picture

mrjmd CreditAttribution: mrjmd commented

Hi Perignon. For my part, I have tested this patch on classes with over 100,000 files / 50+GB. From what I've seen the main class page load time is down from several minutes to under two seconds, and the calculations run in about one second each. We have already merged this patch onto our production environment and have seen no problems so far.

I am very curious to hear if others are getting similar results.

cancerian7’s picture

cancerian7 CreditAttribution: cancerian7 commented

Thanks for the patch. This solved my problem.

Perignon’s picture

Perignon CreditAttribution: Perignon commented

I will get this committed to dev sometime today!

Perignon’s picture

Perignon CreditAttribution: Perignon commented

This completely slipped my mind! Got a lot of things going on with my day job.

  • Perignon committed 1e85bff on 7.x-1.x authored by mrjmd 
    Issue #2402849 by mrjmd: Storage container size calculation query brings...
Perignon’s picture

Perignon CreditAttribution: Perignon commented
Status: Needs review » Fixed

Committed to dev! Thank you for the patch!

Perignon’s picture

Perignon CreditAttribution: Perignon commented
Issue tags: +Storage API 7.x-1.7

Tagging for release documentation.

Status: Fixed » Closed (fixed)

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