Huge WP_OPTIONS table size!
-
I love Spectra! But there is a big problem such as 2.0.14 DB error | www.remarpro.com, Spectra gone crazy on wp_actionscheduler_actions | www.remarpro.com and Cant enable plugin / spectra_total_blocks_count_action | www.remarpro.com, it is still not resolved yet.
The row “spectra_blocks_count_status” will lead to huge WP_OPTIONS table, even up to 10GB in my case. But there is no data in WP_OPTIONS table and table keep big size. And then it is hard to backup.
The status of “spectra_blocks_count_status” is autoload, then creating many ‘wp_collect_spectra_blocks_count_batch_[hexdec]’. If I enable Redis Cache, it will creating automatically.
Now I set the autoload status of “spectra_blocks_count_status” as NO and turn off Redis Cache, finally it stop to lead to huge WP_OPTIONS table.
Actually I don’t understand why “spectra_blocks_count_status” should exist and autoload. Hope it will disappear in future.
-
YES, thank you for that! I kept blowing past my available memory and knew it was in the options table, but couldn’t the cause. Setting to ‘no’ brought my admin area back online.
Edit:
Full fix for me, which dropped my wp_options table from 1.8GB to 9MB. This might be a little overkill, but it seems to be based on data collection, so you definitely need to stop the data from generating or it comes right back.
- Go to wp-admin -> settings -> general. On the very bottom of that page, uncheck the box that says to allow Brainstorm Force to collect data.
- Run the following database commands:
DELETE FROM wp_options WHERE option_name LIKE 'wp_collect_spectra%';UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_count_status';UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_pages_counted';
Thanks for great and complete solution. It did work and help people facing the same problem.
Actually, I think it is not overkill and safe until Brainstorm Force?can really solve the problem.
Seems that it is not helping to open a ticket on https://wpspectra.com/support/ until now.
Here is another option which also doesn’t solve the root problem, but may help in certain cases, like litespeed cache.
Create a mysql event to delete the posts hourly. This does require turning on the event scheduler, which may not be available, particularly on shared hosting. If you’re managing your own database, you can do this (change items <marked> as applicable to your own database:
SET DELIMITER ;; CREATE EVENTwp_options_cleanupON SCHEDULE EVERY 1 HOUR STARTS '2023-01-01 00:15:00' ON COMPLETION NOT PRESERVE ENABLE COMMENT 'Cleanup of Spectra events' DO BEGIN -- CLEAN UP HUGE wp_options FROM SPECTRA DELETE FROM<schema>.wp_optionsWHEREoption_nameLIKE 'wp_collect_spectra%'; END;; SET DELIMITER ;Then go to /etc/my.cnf (or applicable file for your system) and under the [mysqld] section, add a line like this — a reboot may be required, but I think you can just restart the sql service:
[mysqld]
event_scheduler=ONThis will delete the entries every hour and might buy some time.
Wish I had a better answer… but like you, it doesn’t matter how good the plugin is, it can’t bring so many systems to a grind and while it’s going to be a PITA, I may need to start looking at other options as well.
Random question: I have more than 8000 entries in my wp_posts table on my site. Is this happening to anyone with only a few posts for a static-ish site?
I try to share this issue on SPECTRA official FB group: https://www.facebook.com/groups/wpspectra.
Then the post was rejected and deleted. What a pity!
Manners Maketh Plugin!
I am not a programmer, what I can do is try and error. Based on my tries.
When you run commands below:
DELETE FROM wp_options WHERE option_name LIKE 'wp_collect_spectra%';UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_count_status';UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_pages_counted';
There are 8 rows of SPECTRA in wp-options. When running some cache or database cleaning plugins, and cleaning _transient data in wp-options. The row of “spectra_settings_data” disappeared.
Now autoload status of “spectra_blocks_count_status” became YES again, and 3 or more rows created with nightmare.
And try to run commands again, and clean 3 or more new rows created. There will be 7 rows left.
DELETE FROM wp_options WHERE option_name LIKE 'wp_collect_spectra%';UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_count_status';UPDATE wp_options SET autoload = 'no' WHERE option_name = 'spectra_blocks_pages_counted';
Finally, go to wp-admin and refresh setting page of SPECTRA. The disappeared row of “spectra_settings_data” comeback again.
If the _transient of SPECTRA is cleaned, do it again and again, again, again, again, again, again, again, again, again…………………….
- The topic ‘Huge WP_OPTIONS table size!’ is closed to new replies.
