[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:  5.8.1
 Severity:  major         |   Keywords:
  Focuses:                |
--------------------------+-----------------------------
 Hi there, folks. After months of perf issues on a fairly large site (10s
 of millions of monthly PVs), I finally dug in properly and discovered an
 absolutely massive `_transient_dirsize_cache` option which was set to
 autoload=yes... currently sitting at 207221 entries worth 20MB of data.
 That's 20MB every

 ```
 SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
 ```

 loads each time. The query takes 1s+ on 32GB RAM 8-CPU db servers.

 From the looks of it, it was added in 5.6.0
 https://core.trac.wordpress.org/ticket/19879#comment:52.

 For now, I set the transient to autoload=no, which considering its
 potential size, it should be set by default to no.

 If this functionality is only used in multisite and only in some specific
 quota codepaths, it should definitely be set to `autoload=no`.

 I'm also wondering if shortcircuitting it with

 ```
 add_filter( "transient_dirsize_cache", function() {return; } );
 ```

 and deleting the transient to avoid such a huge 20MB db entry would have
 any side effects. Or if it'd be a better idea to disable the cache
 altogether?

 See https://wordpress.slack.com/archives/C02RQBWTW/p1633393189369400.

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