Almost 1.9 million temp user folder records in craft_volumefolders

[chadcrowell]

Description

We've seen a major, major slowdown over the last month or so on a large site using current version of Craft. The slowdown is very evident within the Control Panel when loading and saving certain entries. (The front end uses Blitz so it rarely displays performance issues). I was able to narrow it down I think, to the volumefolders table in the db. When loading a "parts" section entry for editing, is when we'd see a very bad delay, and then saving the same thing, even worse delay. I was able to find this query taking over 16 seconds:
SELECT id, parentId, volumeId, name, path, uidFROMcraft_volumefolders WHERE (volumeId='12') AND (path='products/WM-0023-32/') ORDER BY name LIMIT 1

I copied and ran the query in my MySQL GUI and got the same timing, more or less. Then I noticed there are almost 1.9 million rows in the volumefolders table. I hunted through here a bit and noticed all but about 2300 of them look like this
https://d.pr/i/VXGeRc

I did some searching through the craft CMS vendor folder and found these records are created based on session IDs. That makes sense, but I am not sure the situations when they would be created and not removed.

Can you let me know if it is safe to purge them, and if they are supposed to be removed at some point by Craft? Also, typical scenarios when they would be created so I can eval my system to figure out if something outside of Craft itself is causing this?

Additional info

• Craft version: Craft CMS 3.7.36
• PHP version: 7.4.27
• Database driver & version: MySQL 5.7.37
• Plugins & versions: Many

[andris-sevcenko]

Currently, they are not expected to be removed by Craft. They are, however, safe to delete.

Maybe a good idea for us to delete any guest user temp upload folders without any assets in them, when garbage collection runs, thought!

[brandonkelly]

Craft 4.0.0-beta.4 is out, which now removes unused temp subfolders during garbage collection.

[simonleadbetter]

We’ve run into this same issue on a client’s website, which has amassed 1.5 million records in the craft_volumefolders table.

Unfortunately, we are currently versioned locked to Craft 3 (we are running 3.7.50) and are unable to upgrade to Craft 4 to benefit from the update to the garbage collection. Also, this website is hosted on a platform where we don’t have direct access to the database so we can not delete the records manually.

The fact the craft_volumefolders is swelling and can not be purged means we are seeing a slow CP, the Ad Wizard plugin is timing out and the database size is now 1GB and growing.

Is it possible for this fix to be back-ported to Craft 3?

It would also be good to know what may be triggering these rows to be created. Is there anything we can do to stop it?

[simonleadbetter]

Would it be safe to run the follow query on the database?

DELETE FROM craft_volumefolders where name like 'user\_%'

The rows data looks like this…

We need to find a quick solution as this issue is impeding our client’s adding content updates to their website.

[brandonkelly]

@simonleadbetter Just backported that functionality to Craft 3 for the next release.

To get it early, change your craftcms/cms requirement in composer.json to "v3.x-dev as 3.7.52" and run composer update.

[brandonkelly]

Craft 3.7.53 is out with that change.

[simonleadbetter]

Hi @brandonkelly

Thanks for backporting the fix and deploying it in a recent update. We upgraded Craft to this latest version and attempted to run gc locally, however, we are still hitting the same memory issue. I have ensured memory_limit is set to -1 in the php.ini file, so I am still stuck with the 1.5 million records.

Can I check that the following query will be okay to run on this table and that there are no foreign key constraints:

DELETE FROM craft_volumefolders where name like 'user\_%'

We have run it locally and everything seems okay, but I wanted to double-check with you.

[chadcrowell]

I've been using this one, which leaves the most recent user records incase they do get turned into real folders (if, for example, a user registers after uploading something)

delete from craft_volumefolders where 
parentId = 1 and 
volumeId is null and 
name like 'user_%' and 
length(name) > 20 and
dateCreated < (NOW() + INTERVAL -12 HOUR)

Curiously, after requesting this initial fix, and having it then only implemented for Craft 4, I continued running this query once per day back in the spring and at some point, user_xxx folders stopped getting added to my table (in Craft 3) regularly. I stopped running the query months ago because it didn't seem necessary any longer. Even to this day, user_xxx folder records just don't seem to get added to that table anymore, even though the fix was just recently added for Craft 3. Not sure what ever happened, I assumed maybe there was a code change in the core so they aren't added anymore (vs. this fix that removes them during GC). Just kind of weird.

[brandonkelly]

@simonleadbetter That query could remove folders that actually contain temp files. You’d probably want to left-join the assets table to ensure they’re empty first.

DELETE `vf`
FROM `craft_volumefolders` `vf`
LEFT JOIN `craft_assets` `a` ON `a`.`folderId` = `vf`.`id`
WHERE `a`.`id` IS NULL
AND `vf`.`name` LIKE 'user\_%'

Note that this will only remove the folder records; the folders themselves will still exists in the actual storage location.

@chadcrowell Probably due to this change in 3.7.45: b75103d