[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:  spacedmonkey
     Type:  defect (bug)      |      Status:  closed
 Priority:  normal            |   Milestone:  6.4
Component:  Database          |     Version:  2.8
 Severity:  major             |  Resolution:  fixed
 Keywords:  has-patch commit  |     Focuses:  administration, performance
------------------------------+------------------------------------------

Comment (by kc3302):

 This issue doesn't seem to have been resolved yet??? Using v6.7.1 I got
 the autoloaded options critical issue with the options being over 1MB.

 Complicating the matter was that all the info I found online told me to
 check the autoloaded options with autoload='yes'. V6.6 completely changed
 the values of the autoload field from 'yes'/'no' to 'on', 'auto-on',
 'auto', 'false' (from what I can see).

 I found that _transient_dirsize_cache was over 1MB, so I changed it to
 false and the site seems to work fine so far and the critical issue
 warning is no longer appearing.

 N.B. Anyone finding this page to resolve either issue, the SQL query in
 the top post needs to change from:
 SELECT option_name, option_value FROM wp_options WHERE autoload = 'yes'
 to:
 SELECT option_name, option_value FROM wp_options WHERE autoload IN (
 'yes', 'on', 'auto-on', 'auto')

 You can also use the following which will list the size of each autoloaded
 option:
 SELECT option_id, option_name, autoload,
 ROUND(LENGTH(option_value)/1024,2) AS Size_in_KB
 FROM wp_options
 WHERE autoload IN ('yes', 'on', 'auto-on', 'auto')
 ORDER BY Size_in_KB DESC;

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