I have a problem with my wordpress page (woocommerce store, woodmart parent theme), where the wp_options table in my database gains almost 50MB in size, in what I thought was approximently every 10 minutes, but it turns out the interval is completely random, yesterday during the day it was like 10 minutes, but in the evening it was every hour or so.
A few days ago when I noticed it, it was at a record 140 GB, and it would have kept climbing. Optimizing the table will fix it for a bit and make it go down to 300 mb, but it slowly gains size after a random amount of time still.
This issue presists on 2 websites I have. 1. a live woocommerce store (This was the one with 140GB database, before optimazation) and 2. a development new version of the same store (This had a table size of 25GB when I discovered it.)
So the next data is about my development webpage:
I optimized the 25GB database, got it down to like 200mb, and in about 6h, it was at 1,3GB.
I have checked WP activity log, and saw nothing there when it increased by like 50-100mb randomly.
I also checked WP cron, and it seems like the issue isn't coming from cron jobs. As it increased when no cron jobs were running, and after executing a few I thought might be responsible, no change occured whatsoever.
In SQL if I do:
SELECT COUNT(*) AS row_count, SUM(LENGTH(option_value)) AS total_data_size, AVG(LENGTH(option_value)) AS avg_row_size FROM wp_options;
returns: row_count: 15749 total_data_size: 5610091 avg_row_size: 356.2189
So according to this, my average row size is 300 bytes, and total size of wp_options should be 5.6MB.
But if I do SHOW TABLE STATUS I get this result:
Attribute Value Analysis Rows 15,565 Matches the approximate row count previously mentioned.
Avg_row_length 74,677 bytes Each row on average is ~74.6 KB..
Data_length 1,162,362,880 This is the total data size (~1.16 GB).
Index_length 3,129,344 The size of indexes is minimal (~3 MB), so indexes aren't a significant contributor to the bloat.
Data_free 6,291,456 Unused space is only ~6 MB, meaning fragmentation is minimal and not the primary cause of the table size.
Row_format Dynamic
So while each row contains only on average 300 bytes of data, each row on average takes up 75KB!
So should be a simple fix, shouldn't it? Let's just find the biggest row and....
SELECT option_name, LENGTH(option_value) AS option_value_length FROM wp_options ORDER BY option_value_length DESC;
This shows me that the largest entrys option_value_length is 481491, but the 25th is already 37280.
Checking the autoload options:
SELECT option_name, LENGTH(option_value) AS option_size, autoload FROM wp_options WHERE autoload = 'yes' ORDER BY option_size DESC LIMIT 50;
Shows me the largest option_size of 33749, 6th is already down to 7572.
It is highly unlikely that the issue lies within transients, because when I optimize my database it will go down to like 100mb without removing any transients.
At this point, I am pretty stuck, gonna try to disable the child theme and use the parent, and see if the issue persists... Although my child theme shouldn't write anything to the wp_options.... Anyone got any good ideas?
