wp options table transient row continuous and bulk writing in WooCommerce

  • Resolved T0M75

    (@t0m75)


    4 months, 1 week ago

    I manage a WooCommerce store. The Hosting service provider reports that it has noticed excessively intensive disk usage on the website, which results from continuous and large-scale database writing, which loads the server disks. The problem seems to be in the options table, where a large number of “transient” entries are added. Looking at the options database table myself, I only notice data records for wooCommerce. The database size is 0.05 GB and the “overhead” is 11.16 GB. I find this strange, as the normal operation of wooCommerce should not interfere with the hosting provider. The website has about 800 products and 5 default attribute filters with about 20-30 choices each to improve the user experience..

    I see no way that any other plugin on this website could be causing this behavior. I have removed all plugins that may be related to the problem and the few that remain cannot be turned off on a live website. Options table id has increased from 470418 to 471681 in 12 hours – should this be normal? WooCommerce has been working on the package provided by the service provider for about 10 years without any problems. The WooCommerce version is of course the most recent. Unfortunately, I can’t point out a specific Woocommerce version. Is there any additional information I should ask the website hosting provider about the server or the events mentioned?

    Is this topic still being worked on as it seems quite similar? Have there been any solutions since then? I have istalled ?Transient Cleaner, but don’t understand how this would help reduce the number of writes to the database? I also read on Slack that woo has a WooCommerce > Status > Tools >Transient cleaner, how are they different?

    I’m familiar with docker, but have mainly used it to resolve errors that I can log. Also I have the ability to read a little code, but I don’t know if it would be a solution (the mentioned topic says not to turn off the transient). So where to start.

    Why doesn’t WooCommerce create a separate db table for its temporary data since there is so much? This would make it much easier to detect such problems. Is it necessary to write this information constantly, the need for _transient_wc_related seems especially suspicious, this should only happen if the admin has changed something on the product page and then the data should be updated, not created.

Viewing 3 replies - 1 through 3 (of 3 total)
  • Plugin Support Reynier (a11n)

    (@reynierc)

    Hi @t0m75 ,

    Thanks for reaching out and taking the time to clearly explain what you’re experiencing.

    I can understand how unexpected disk usage can be concerning. Transients in WooCommerce are used for storing temporary data to speed up your site but shouldn’t be causing such high overhead. The increase in the options table IDs suggests that transients aren’t being cleared as they should.

    The Transient Cleaner plugin and the WooCommerce > Status > Tools > Clear transients option from the default system tools both aim to help with this by cleaning up expired transients from your database. The difference is that WooCommerce’s built-in tool is specifically for its transients, while the plugin can handle all transients on your site. You can read more about the WooCommerce System Tools here.

    WooCommerce uses the options table to ensure compatibility across different WordPress setups. Creating a separate table could help, but the system is designed to work within the WordPress framework. It’s unusual that _transient_wc_related entries are being written so frequently. This behavior typically occurs when product information is updated, not continuously.

    To investigate further, check if any recent changes have been made on your site that might affect transients. Also, ask your hosting provider for logs that might indicate what’s triggering these writes. This information could be crucial in identifying the cause.

    Regarding the constant writing of _transient_wc_related data, this shouldn’t be happening unless there are regular updates to product data. It might be worthwhile reviewing if any background processes or cron jobs could be triggering this activity.

    For now, regularly clearing transients and monitoring the options table growth should help manage the situation. Keep an eye on the options table after clearing transients to see if the growth rate decreases, which would confirm that transients are the root cause.

    Let me know how this goes and if you need further assistance.

    I have the same problem with transients.
    Within three days the wp_options table had grown to over 12GB. The original size of the entire database was about 650MB. We have over 8700 products on the site. Including variants, it is about 18,500 products.
    I did not make any updates to WP, WC, theme or plugins. The size of the wp_options table started to grow for practically no reason. I restored the website from a backup from a time when the database was a good size and its size started to grow again. New regulars in wp_options are still being added and the size of the database is growing.
    I copied the entire website including the database to another domain. The database size is the same and new rows are not added. It only happens on the main domain.
    I have disabled all plugins except woocommerce and rank math and activated the base theme. The number of rows in the wp_options table keeps increasing.
    I am attaching a link to a screenshot from the DB: https://ibb.co/8gQxqWc
    I have set the default row display in the database table to 25 rows per page.
    On the test domain, the wp_options table has 1570 pages, and from time to time about 6 rows are added, which gradually disappear.
    On the main domain, the wp_options table has 2065 pages, and their number is constantly increasing.
    I can even go to page 2267, but all of them up to page 2065 are empty. But it is gradually filling up, and by the time I finished writing this post, there are already new rows on page 2067 and they are still increasing…
    I tried deleting transients through wp and also through a plugin, their number did not decrease.
    According to the hosting where my site is located, the following are the numbers of accesses to the site by Google bot and Byte Speeder:
    Google bot
    Yesterday 15,000 times
    Sunday 5,600 times
    Saturday 6,000 times
    Friday 5,900 times
    Thursday 4,200 times
    Byte Speeder
    Today 1,700 times
    Monday 4,600 times
    Sunday 4,700 times
    Saturday 6,200 times
    Friday 3,800 times
    Could this be caused by bots? Or is the site’s security broken? It really only happens on the main domain, and a website with a database copied without any changes to the backup domain does not have this error.

    WP: 6.7.1
    WC: 9.6.0
    PHP: 8.3.14
    DB: Maria DB 11.4.3
    Theme: Woodmart

    Thread Starter T0M75

    (@t0m75)

    My problem was that the option_value was too long and the constant deletions increased the database size in MB. So first I had to figure out what the database size consists of.
    I don’t know your background, so I can’t recommend anything other than asking someone who knows how to code to take a look at it.

    I do not agree with @reynierc next statements: “This behavior typically occurs when product information is updated, not continuously.” (It depends on how the website is designed.) and “The increase in the options table IDs suggests that transients aren’t being cleared as they should.” (I don’t know of a way for the code to delete transitions in a way that would reduce the number of ids.). But his answer was of some use to me.

    But I agree “It might be worthwhile reviewing if any background processes or cron jobs could be triggering this activity.”. It is also important to understand why a particular transient is being created.

Viewing 3 replies - 1 through 3 (of 3 total)
  • You must be logged in to reply to this topic.