WP_options growing in disk size but not in contents

Okay, so I’ve been looking at this issue for months now and there’s a lot of similar problems related to the WP_Options table growing uncontrolled.

In some cases, it’s mysql binlogs adding up very fast or transients from assorted plugins. The latter case is annoying because the advice in those threads often pushes the blame to some combination of plugins rather than looking further into root causes.

I’ve already disabled my bin logs and I’ve spent the last several months monitoring my plugins, transients, and table data size. At this point, I have about 30 bookmarks of various threads trying to address the issue in different ways and a cheat sheet of CLI and MYSQL commands for me to copy-paste to check on my server.

My transients are fairly minimal, occupying no more than 100kb with only about 5 critical plugins on Autoload. I’ve even gone as far as calculating the length of every cell in the table. Right now I only have about 600 rows on the table, the majority of which are only a few bytes. The option_id and option_name lengths never exceed 10 characters so they don’t take up much space. The notorious one is the option_value column which plugins like to throw a LOT of data into. Looking at the table structure this cell can hold up to 4GB of data if you really wanted to.

When I query length(option_value) for all these rows, the largest one that comes up is only about 300,000 bytes. Taking the total sum of the size of option_value for all rows gives me just shy of 700,000.

So WHY does wp_options show up as 2.1GB in the table schema? I’ve also verified this by running:

SELECT * FROM information_schema.TABLES WHERE TABLE_NAME = "wp_options"

Where it shows DATA_LENGTH is 2296578048 and the average row size is like 2.5MB

I’ve also verified this further by running

ls -l /opt/bitnami/mysql/data/bitnami_wordpress
from command line and observing the disk space of the wp_options.ibd file.

This continues to expand by about 20mb per hour which is absurdly fast for my website which is relatively small and doesn’t experience that much traffic.

The only ‘quick fix’ I’ve managed to find is to run wp_optimize on the table, which from my understanding behaves like a ‘disk cleanup’ or ‘defragmentation’ of a hdd. The only clue as to why this is happening was suggested by a friend, and I don’t know if this is actually true. It’s possible that when transients are stored on the db they occupy disk space but when those transients are deleted, the disk memory is only marked as ‘erased’ but it doesn’t actually delete it. The data is still there and new data cannot be allocated to it.

Can this behaviour be confirmed? If this is actually the case, can it be changed?
It would be absurd if this is how disk storage is managed by the database.

Yes, I could probably execute wp_optimize on a cron job (not sure how to yet) but that would be like trying to put a bandaid on a leak in a dam every week hoping that it doesn’t grow faster than the fix and one day burst. What I’m asking for a permanent fix to the cause of the issue. What causes this growth in the ibd file when the contents don’t actually take up that much space?