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?

Viewing 5 replies - 1 through 5 (of 5 total)

  • Do you happen to have Core Control plugin installed ?
    List your plugins, clearly something is logged into your table, or you have a runaway process / memory leak from theme, plugin, server set up.

    If all of this is a No, I would look at my bin logs from bitnami.
    Do you have full service access to bitnami, or is this coming from your host?

    Thread Starter gonazar

    (@gonazar)

    I have full access.

    Are these different logs than the mysql bin logs?

    Here are the plugins, it’s not very many, half of them are just the theme:

    betterdocs/betterdocs.php
    js_composer_salient/js_composer.php
    redirection/redirection.php
    salient-core/plugin.php
    salient-demo-importer/plugin.php
    salient-home-slider/plugin.php
    salient-nectar-slider/plugin.php
    salient-portfolio/plugin.php
    salient-shortcodes/plugin.php
    salient-social/plugin.php
    salient-widgets/plugin.php
    simple-local-avatars/simple-local-avatars.php
    wordpress-importer/wordpress-importer.php
    wpforms-lite/wpforms.php

    How would I check for a runaway process caused by a plugin? Is there a way to look at a log or history of the changes on the table?

    Ask your host where you can read your php error logs.

    Based on the description of your issue, I would be suspicious of this one :
    redirection/redirection.php

    Thread Starter gonazar

    (@gonazar)

    So I’m not sure if I’ve tapped into my php logs just yet as i’m a bit confused whether it’s logging at all, logging in a file I can’t find or just dumping it into my apache logs.

    In my apache logs I’ve found a lot of this:
    [Mon Jan 11 06:34:54.288344 2021] [proxy_fcgi:error] [pid 9096:tid 140056198936320] [client 162.158.203.13:44448] AH01071: Got error 'PHP message: PHP Warning: Use of undefined constant \x91COOKIE_DOMAIN\x92 - assumed '\x91COOKIE_DOMAIN\x92' (this will throw an Error in a future version of PHP) in /opt/bitnami/apps/wordpress/htdocs/wp-config.php on line 83PHP message: PHP Warning: Use of undefined constant \x91HTTP_HOST\x92 - assumed '\x91HTTP_HOST\x92' (this will throw an Error in a future version of PHP) in /opt/bitnami/apps/wordpress/htdocs/wp-config.php on line 83PHP message: PHP Warning: Use of undefined constant TEST_COOKIE - assumed 'TEST_COOKIE' (this will throw an Error in a future version of PHP) in /opt/bitnami/apps/wordpress/htdocs/wp-includes/functions.php on line 7601PHP message: PHP Warning: Use of undefined constant COOKIE_DOMAIN - assumed 'COOKIE_DOMAIN' (this will throw an Error in a future version of PHP) in /opt/bitnami/apps/wordpress/htdocs/wp-includes/functions.php on line 7601'

    This error accounted for 122,500 of the 125,000 lines in the log over the course of just one week. @__@
    It appears to be some leftover debugging crap related to cookies. I adjusted the line in wp_config and removed the extra code in functions.php.

    Despite that, I’m wondering whether or not this would dump data into the wp_options table. Other than just filling my error logs, is this actually the source of the issue with my database? I’ll have to leave it running for a bit to see if the problem persists.

    Other than that line of code, the thee other most common errors in my log are a lot of:
    RateController: drop deferred fetch of "XYZ" on shutdown
    and
    PageSpeed Serf fetch failure rate extremely high; only 0 of 1173 recent fetches fully successful; is fetching working?
    and
    [Sat Jan 16 23:14:48.762017 2021] [pagespeed:error] [pid 27045:tid 140056215721728] [mod_pagespeed 1.13.35.2-0 @27045] Slow ReadFile operation on file /opt/bitnami/apache2/var/cache/mod_pagespeed/!clean!time!: 78.661ms; configure SlowFileLatencyUs to change threshold\n

    Thread Starter gonazar

    (@gonazar)

    Really wish wordpress devs could enlighten us as to how wp_options table handles caching and memory allocation. I still believe it’s something like memory defragmentation and not being able to allocate new data into sectors that were marked for deletion.

    I haven’t found anything else that would insert 20mb/hr of data into the table otherwise.

    For the time being i’m running a nightly cron to run the wp-optimize command. I did it by setting up the event scheduler in mysql. The annoying thing is that the command isn’t able to store the result anywhere so the only thing I could do was do an insert command into a log table I made so I knew when it ran. That still doesn’t tell me if the execution of the wp-optimize was successful or not though so I set up some monitoring and alerts to keep an eye on disk utilization.

    If it goes above 85% I’ll get an alert so I can fix the issue before it runs out of space again.

Viewing 5 replies - 1 through 5 (of 5 total)
  • The topic ‘WP_options growing in disk size but not in contents’ is closed to new replies.