wp_options _transient_dirsize_cache autoload=yes
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.
3MB位になっていた
mysql> SELECT 'autoloaded data' as name, format_bytes(SUM(LENGTH(option_value))) as value FROM wp_options WHERE autoload='yes' UNION SELECT 'autoloaded data count', count(*) FROM wp_options WHERE autoload='yes' UNION (SELECT option_name, format_bytes(length(option_value)) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10); +--------------------------------+-----------------------------------------+ | name | format_bytes(SUM(LENGTH(option_value))) | +--------------------------------+-----------------------------------------+ | autoloaded data in KiB | 3.26 MiB | | autoloaded data count | 654 | | _transient_dirsize_cache | 2.95 MiB | | rewrite_rules | 57.98 KiB | | fs_accounts | 39.88 KiB | | disable_rest_api_options | 28.71 KiB | | aioseo_options_v3 | 13.62 KiB | | aioseop_options | 13.62 KiB | | aioseo_options | 11.18 KiB | | wp_user_roles | 9.84 KiB | | aioseo_options_dynamic_network | 8.90 KiB | | aioseo_options_network | 8.81 KiB | +--------------------------------+-----------------------------------------+ 12 rows in set (0.02 sec)
autoload=noにする
mysql> UPDATE wp_options SET autoload='no' where option_name='_transient_dirsize_cache'; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT option_name, autoload from wp_options where option_name='_transient_dirsize_cache'; +--------------------------+----------+ | option_name | autoload | +--------------------------+----------+ | _transient_dirsize_cache | no | +--------------------------+----------+ 1 row in set (0.00 sec)
SQLは↓を参考にした
wp_optionsテーブルと自動読み込みデータの整理のやり方
wp_options wc_remote_inbox_notifications_specs autoload=yes
アンインストールしたwoocommerceのautoloadが残ってたのでautoload=noにする
mysql> SELECT 'autoloaded data' as name, format_bytes(SUM(LENGTH(option_value))) as value FROM wp_options WHERE autoload='yes' UNION SELECT 'autoloaded data count', count(*) FROM wp_options WHERE autoload='yes' UNION (SELECT option_name, format_bytes(length(option_value)) FROM wp_options WHERE autoload='yes' ORDER BY length(option_value) DESC LIMIT 10); +-------------------------------------+------------+ | name | value | +-------------------------------------+------------+ | autoloaded data | 284.64 KiB | | autoloaded data count | 471 | | wc_remote_inbox_notifications_specs | 90.08 KiB | | rewrite_rules | 27.26 KiB | | revslider-addons | 19.52 KiB | | otgs-installer-log | 9.52 KiB | | wp_user_roles | 9.36 KiB | | aioseo_options | 9.24 KiB | | wpmtst_fields | 9.10 KiB | | aioseo_options_network | 8.69 KiB | | wpmtst_base_forms | 8.37 KiB | | layerslider_update_info | 7.33 KiB | +-------------------------------------+------------+ 12 rows in set (0.01 sec) UPDATE wp_options SET autoload='no' where option_name='wc_remote_inbox_notifications_specs';
wp_options fbrfg_favicon_non_interactive_api_request autoload=yes
Favicon by RealFaviconGenerator
https://wordpress.org/plugins/favicon-by-realfavicongenerator/
https://wordpress.org/support/topic/huge-entry-in-wp_options-2/
| fbrfg_favicon_non_interactive_api_request | 550.81 KiB |
