[wp-trac] [WordPress Trac] #54221: _transient_dirsize_cache is set to autoload=yes and kills db performance if it grows (20MB in our case)

[WordPress Trac]

#54221: _transient_dirsize_cache is set to autoload=yes and kills db performance if
it grows (20MB in our case)
--------------------------+-------------------------------------
 Reporter:  archon810     |       Owner:  (none)
     Type:  defect (bug)  |      Status:  new
 Priority:  normal        |   Milestone:  Awaiting Review
Component:  Database      |     Version:  2.8
 Severity:  major         |  Resolution:
 Keywords:                |     Focuses:  multisite, performance
--------------------------+-------------------------------------

Comment (by iandunn):

 > if it'll flip back to autoload=yes next time it decides to update. When
 does that happen, by the way? How frequently or in what scenarios?

 I think it will if the transient is deleted and recreated, but it's
 possible that'll never happen in practice.

 Since it doesn't have an expiration, it shouldn't get cleared when
 upgrading WP. Since there's no object cache, it won't get cleared when
 rebooting the server. Since it's not Multisite, `clean_dirsize_cache()`
 won't be called.

 To be safe, you could do something like this until a fix is available:

 1. Add a `pre_set_transient_dirsize_cache` callback that sets the value to
 an empty array
 1. Delete the transient

 That would probably result Site Health, the Dashboard, and file upload
 pages showing wrong data, but IIRC some of that is only for Multisite.
 ----

 > what is the plan to fix this permanently? Should we add autoload=no
 support to set_transient?

 I'm guessing this only affects sites w/ a very large number of subfolders,
 so it may be a bit of an edge case, and therefore a lower priority. I'm
 curious to hear what others think, though.

 I think there are two problems:

 1. `set_transient()` makes sketchy assumptions about whether or not a
 transient should be autoloaded. Adding a new `$autoload` param to the
 function could allow us to specify one in cases like this. I'm guessing
 there are other transients that are only used on a few pages despite being
 autoloaded. More research into the original intent, benchmarks to verify
 assumptions, etc might be a good next step.
 1. `dirsize_cache` can become unwieldy on sites with tons of folders.
 Given its limited usage, one option would be to just disable it on those
 sites (ala `wp_is_large_network`). I'm assuming that the admins on those
 types of sites will usually capable of doing something custom if they
 actually need the functionality. Alternatively, the value could be sharded
 across multiple options, moved onto disk, etc. There could be better ideas
 too.



 ----
 > have probably over 2x that amount of folders ... its size is still the
 same 207221 elements [even though] there are hundreds such directories
 created daily
 > the option is probably so big that it actually breaks mysqldump

 Huh, maybe those are related? Maybe there's an error when trying to add
 more entries to the value too? I'd assume MySQL could handle that just
 fine, but maybe there's a config option that limits it?

-- 
Ticket URL: <https://core.trac.wordpress.org/ticket/54221#comment:5>
WordPress Trac <https://core.trac.wordpress.org/>
WordPress publishing platform