• Resolved DR Yuill-Kirkwood

    (@dr-yuill-kirkwood)


    Since updating to wordpress Version 5.0.1
    WPSSO Core [Main Plugin] Version 4.18.0
    WPSSO Ridiculously Responsive Social Sharing Buttons Version 1.13.0
    WPSSO Schema JSON-LD Markup Version 1.38.0
    The wp_options table has skyrocketed to over 100Mb and keeps rising. The entire database for the entire site was around 40mb for the past couple of years but now weighs in at 155mb.
    When downloading the wp_options table it there is over 10000 lines starting with _transient_wpsso_ the plugin ‘Optimize Database after Deleting Revisions’ is not clearing your data. My database is gaining 20mb a day now in _transient_wpsso_. How can I clear the data and can you please fix this issue before my site (and likely others that don’t check and back up the database every week) are suspended by the web host for exceeding database size limits.

    The page I need help with: [log in to see the link]

Viewing 14 replies - 1 through 14 (of 14 total)
  • Plugin Author JS Morisset

    (@jsmoriss)

    WPSSO uses the WP transient cache to store its meta tags and Schema markup – there are generally 1-2 transients per post and term, so there can be many transients, but they should be fairly small. You can view the number of transients used on the SSO > Dashboard page, and clear the WPSSO cache on the SSO > Tools page. You can hook the ‘wpsso_cache_expire_head_array’ filter and return 0 to disable the head meta tag cache, and ‘wpsso_cache_expire_schema_json_data’ for the Schema JSON cache. Pro version users can also fine-tune cache settings from the SSO > Advanced settings page.

    js.

    DR Yuill-Kirkwood

    (@dr-yuill-kirkwood)

    The cache status reads as
    WPSSO Core Database Transients
    Count Expiration
    Article Topics: 1 2592000
    Buttons HTML: 1797 604800
    Head Markup: 5462 604800
    Image URL Info: 18 86400
    Schema Data: 1841 1209600
    Shortened URLs: 0 7776000
    Schema Types: 39 2592000
    All Transients: 9210
    I have run the Clear all caches twice in the past hour and it’s all still there. I have used your plugin for years with no issue and I am certainly not paying for the privilege of not having my database inflated. This has never been an issue before so something in your update is broken unless in an Apple-style you are using this to force people to go Pro. Can you please explain in precise detail how to clear up and fix your mess, as I have no idea what to do with your wpsso_cache_expire_head_array response.

    Plugin Author JS Morisset

    (@jsmoriss)

    Was the tone of my reply aggressive? I’m surprised at the tone of your follow-up.

    So when you click the “Clear All Cache” button, the counters are not returned to 0 (or near 0)?

    Here is some information on how to hook filters, but if the “Clear All Cache” button is not working on your site, I’m guessing that would be the issue to focus on.

    https://www.google.com/search?q=how+to+hook+a+wordpress+filter

    WPSSO runs a “select” command from the database to get a list of its transients and delete them – I’m not sure why your database is not returning that list (or an incomplete list) – it’s a pretty straightforward function that hasn’t changed in a long time. You could give the https://wordpress.org/plugins/artiss-transient-cleaner/ plugin a try and see if that helps.

    js.

    DR Yuill-Kirkwood

    (@dr-yuill-kirkwood)

    Since I started this query the wp_oprions have grown a further 15mb leaving me no choice for now but to remove the plugin and it’s extras until a later version can correct the issue.

    I run the plugin ‘Optimize Database after Deleting Revisions’ after this returning the the entire database back to 40Mb. It stayed stable at 40Mb for 30 minutes so I reinstalled the plugins, it’s about an hour since then and the database has grown to 50Mb the can’t be cleaned automatically without the re removal of the pkugin indicating that there be something new interacting with the previously noted untouched elements of the plugin in a way unintended and needs correction.

    Plugin Author JS Morisset

    (@jsmoriss)

    I appreciate your feedback, and I would definitely be interested in finding the source of this issue on your site, but it would help if you could work with me – it sounds like you may have ignored my earlier suggestions.

    I don’t know what the “Optimize Database after Deleting Revisions” plugin does, but considering that WPSSO does not create transients for revisions, and that WPSSO refreshes 1-2 specific transients when a post is updated (specific to that post ID), which should not create any fragmentation in the database, I’m not sure what there is to optimize (optimizing in database terms usually refers to defragmenting), unless that plugin decides to clear all transients, which could cause significant performance issues (transients a created to improve page load times by minimizing redundant code execution).

    1-2 transients are created for every post ID, term ID, user ID, and archive page URL (one for meta tags, and one for Schema JSON markup). Generally, depending on the number of posts, terms, users, and archive page URLs, this would be a few thousand (depending on the number of posts / page on a site). Each transient should only be a few KB since they’re only meta tags and Schema markup.

    There is one condition that could create more transients, which is for incorrectly registered custom post types, where the post ID is always 0. In that case, WPSSO must use the sharing URL to create a unique transient index ID. If the URL changes a lot, with many different URL query arguments, this can create many more transients. Their size still wouldn’t change – still a few KB, but there may be many more since WPSSO cannot use a valid post object ID.

    When using the “Clear All Cache” button from the SSO > Tools page, WPSSO executes a background task to clear all its transients (as the notice would have indicated). That task would create another notice when it ends, unless there’s a significant performance issue with the database, and PHP terminated the task prematurely.

    The transient cleaner plugin I mentioned earlier would clear transients in a foreground task, so you would get more immediate feedback about possible database performance issues and timeouts.

    I’m happy to help figure out the issue on your site, and if you could work with me, and provide feedback on my suggestions, we could narrow down the source of this problem.

    js.

    Plugin Author JS Morisset

    (@jsmoriss)

    FYI – WPSSO uses the $wp_query->query values to create a unique key for the content being stored in the transient cache. I am testing various scenarios to see if this may create too many unique keys.

    js.

    Plugin Author JS Morisset

    (@jsmoriss)

    It’s hard to predict all the “creative” ways some sites generate webpages, which is why the $wp_query->query values were includes in the transient array index keys, but I believe this may not be necessary for 99.99% of sites, and may create more index keys than necessary (leading to larger meta tag and Schema json transient arrays), so I’ve removed those values in the latest WPSSO Core v4.18.1-rc.1 release. You can download the latest development / trunk ZIP to give it a spin, or see the SSO > Update Manager settings page to select the development version filters (if you’re using WPSSO Core Pro).

    js.

    DR Yuill-Kirkwood

    (@dr-yuill-kirkwood)

    The “Optimize Database after Deleting Revisions” is just another plugin that clears out all or expired transient enters and schedules clearing revisions, however, I have installed the alternative suggested above for testing.

    I was tempted to change the “plugin_head_cache_exp\”;i:604800; and other expiry timers to shorten them for an experiment but have not had the time today to analyse WPSSO to see if there are a restore default settings if they have been modified directly.

    I have reinstalled/activated WPSSO and will let it accumulate enteritis overnight and see where it lands, wp_Options is 5Mb at this time 01:00, with the entire database at 44Mb.

    Plugin Author JS Morisset

    (@jsmoriss)

    I assume you’re running WPSSO Core v4.18.1-rc.1?

    js.

    Plugin Author JS Morisset

    (@jsmoriss)

    You should not clear all transients after editing a post. WPSSO, for example, clears specific transients related to that post and its terms. Clearing all transients would have an effect on general site performance (a lot of plugins and themes use transients, not just WPSSO).

    WordPress already removes expired transients since v4.9, so that would not be a useful feature.

    Unless you set the expiration time to 0 (to disable that transient feature), smaller expiration times would not be beneficial – the issue is not with the age of the transient, but it’s size (taking an educated guess here since we don’t have specifics on transient content or size).

    js.

    Plugin Author JS Morisset

    (@jsmoriss)

    BTW, I see you’re using the WPSSO RRSSB add-on as well – you should install the current development version of that add-on, which has the same $wp_query->query fix as WPSSO Core.

    js.

    Plugin Author JS Morisset

    (@jsmoriss)

    Any update on this?

    js.

    It has been left to its self for the past week while I have been away and when I have had signal to check it the wp_options table has averaged around 75Mb, and given the information you gave above in that 1-2 transients for every post, page etc and my measurement of individual _transient_wpsso_ lines come in on average of 7Kb per line so worst case scenario is _transient_wpsso_ would be approximately 62Mb. The wp_options where we reset and cleared transient to start again last week was 1.5Mb and now once rebuild has averaged around 75Mb, so wpsso plus any others plus the 1.5mb could be 75Mb but that still leaves the question of now that wp_options are stable at 75mb why was the entire database stable at 40-45mb for the past several years.

    Plugin Author JS Morisset

    (@jsmoriss)

    The requirements for meta tags and Schema markup are always changing. If you’re using the WPSSO JSON add-on, you can uncheck the SSO > Schema Markup > Add CreativeWork Text Property option to include less information about the webpage in the Schema markup (thus minimizing the size of each transient with CreativeWork sub-types).

    js.

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