WordPress.org

Ready to get started?Download WordPress

Forums

Transient Cleaner
Good, but not fast! (28 posts)

  1. David Anderson
    Member
    Posted 1 year ago #

    It might be worth noting on the plugin page that the method used removes about 5 transients a second - that's my average speed so far on an InnoDB table with 250,000 entries.

    I've got all day so I don't mind. But other users may want to hack the code for a quicker method.

    David

    http://wordpress.org/extend/plugins/artiss-transient-cleaner/

  2. dartiss
    Member
    Posted 1 year ago #

    How did you time it?

    David.

  3. David Anderson
    Member
    Posted 1 year ago #

    # while true; do echo "SELECT COUNT(*) FROM wp_options" | mysql examplecom |tail -1; sleep 300; done
    226168
    224330
    222517
    221012
  4. David Anderson
    Member
    Posted 1 year ago #

    Current server 15-minute load is 2.66 - server has 4 CPUs, i.e. only running at 66% of capacity.

  5. dartiss
    Member
    Posted 1 year ago #

    It will probably be better to find and remove the transient records in a single SQL statement. However, my SQL is extremely rusty - it may take me some time to work it out!

    David.

  6. dartiss
    Member
    Posted 1 year ago #

    Probably the easiest solution is to get it to only housekeep so many per night - just as long as this figure if more than the number you generate per day it will slowly clear it down.

    Open up artiss-transient-cleaner.php and find the line...

    $sql = "SELECT option_name FROM $wpdb->options WHERE option_name LIKE '_transient_timeout%'";

    Change it to...

    $sql = "SELECT option_name FROM $wpdb->options WHERE option_name LIKE '_transient_timeout%' LIMIT 10000";

    You'd change 10000 to whatever limit you want.

    David.

  7. dartiss
    Member
    Posted 1 year ago #

    David,

    I've thought of a more efficient way of performing the housekeeping. Would you be willing to beta test the result for me?

    David.

  8. David Anderson
    Member
    Posted 1 year ago #

    Yes, no problem... I did a mysqldump before this current purge, so I can do it again... presently down to the last 100,000 rows - we reached 6 rows/second earlier!

  9. David Anderson
    Member
    Posted 1 year ago #

    I just noticed that WooCommerce has code/SQL for doing this fast - it's in admin/woocommerce-admin-status.php

  10. dartiss
    Member
    Posted 1 year ago #

    Thanks - I'll take a look at it.

    David.

  11. dartiss
    Member
    Posted 1 year ago #

    Ok, looked at it sooner than I intended ;)

    A-ha, found the code - it's actually taken from a blog that explains how to do it in withint SQL, rather than busting out to PHP to do some of the processing. This is what I was after - the solution I was talking about before still relied on some PHP processing. Having said that I think my solution wouldn't have been too much slower.

    None-the-less, I'll test this new solution and if I'm 100% happy it works I'll add it to my plugin.

    David.

  12. David Anderson
    Member
    Posted 1 year ago #

    Thank you...

    Also, the OPTIMIZE TABLE turned out to be equivalent to performing a DOS upon myself... I wondered if after 1/3 of the transients had gone, if running that would speed the future deletions (i.e. the remaining 160,000). It didn't... but it did take the table out of action for 11 minutes whilst it optimized it!

    David

  13. dartiss
    Member
    Posted 1 year ago #

    Hmmm, well it obviously needed it ;)

    Ok, the next release is going to introduce an options screen - I'll allow users to turn off overnight optimisation, maybe just having it when a database upgrade occurs.

    David.

  14. David Anderson
    Member
    Posted 1 year ago #

    Perhaps an optimum solution would be to count the number of deleted rows. If it passes a certain threshold, email the user to advise them that an optimize would perhaps help.

    The problem is that the sites that need an optimize are likely to be those that are really busy and hence that least want one to happen without them scheduling it.

  15. dartiss
    Member
    Posted 1 year ago #

    Not sure how easy that would be as WordPress is modifying that table all the time.

    I think the switch off option along with the ability to specify a maximum number of deletions per run should help.

    David.

  16. David Anderson
    Member
    Posted 1 year ago #

    I'm not convinced about a 'maximum number of deletions' option. Seems likely to be an option that will also be in a catch-22 situation: those who are not in danger of having cripplingly-large options tables won't need it, and those who are in danger will find that such an option prevents the table from getting cripplingly large! Isn't one of the benefits of the "single SQL statement" approach that it obviates the need to limit the number of deletions?

  17. David Anderson
    Member
    Posted 1 year ago #

    P.S. I thought that when you issue the SQL DELETE statement it advises you afterwards of how many rows are deleted. So that can be used to advise the user on whether an OPTIMIZE might be a good idea.

  18. dartiss
    Member
    Posted 1 year ago #

    SQL DELETE does but you're assuming my plugin is the only one deleting from this table - it's not. An optimization may be required before my plugin even touches it, hence the recommendation would be a bit pointless. Having looked up on optimisations it looks like the current optimisation-per-night is overkill. I think moving it to only occur upon the database upgrade will be the best move.

    As for the limit, possibly - let's see how quick I can get it and if you think it would quite happily work with a large number of deletions in a single go I won't bother with the limit options.

    David.

  19. David Anderson
    Member
    Posted 1 year ago #

    Yes, all makes sense... Personally I think that WordPress should never do an automatic OPTIMIZE TABLE. Nobody wants their site to go down for 10 minutes without being informed up front and given the option to prevent it. I'd come back to the point that the people that an OPTIMIZE will give a significant boost to are largely the people who should be doing it manually or will want to schedule it in their own time.

  20. dartiss
    Member
    Posted 1 year ago #

    Just to keep you up-to-date, I've made most of the changes required (including a lovely new admin screen which gives you a bit more control, including the ability to run the housekeeping ad-hoc). Hopefully tomorrow I should have a beta version ready for anyone who wishes to try it out.

    David.

  21. dartiss
    Member
    Posted 1 year ago #

    Ok, here's a beta release of the next version...

    https://dl.dropboxusercontent.com/u/61522/artiss-transient-cleaner.zip

    The admin screen can be found in Tools -> Transients. From here you can perform ad-hoc runs.

    All functionality has been added - it's just a case of testing it now.

    To save you digging through the README here are the changes...

    Maintenance: Split files because of additional code size
    Maintenance: Removed run upon activation
    Enhancement: Improved transient cleaning code efficiency
    Enhancement: Added administration screen (Tools->Transients) to allow ad-hoc runs and specify run options
    Enhancement: Added internationalisation

    One thing to specifically check is the datestamp in the option screen - by default PHP returns the server time which didn't necessarily correspond to the WP user's time. I've therefore timestamped it with, what I believe is, the local time.

    Let me know if you come across any issues. I'll be testing it over the next few days.

    David.

  22. dartiss
    Member
    Posted 1 year ago #

    Ok, uploading version 1.2 now - this should resolve the problems. Please let me know if you continue to have performance issues.

    David.

  23. David Anderson
    Member
    Posted 1 year ago #

    Hi,

    Sorry for being slow to communicate.

    Looks good. The options page throws some warnings about use of uninitialized variables if you have WP_DEBUG turned on and you save settings:

    Notice: Undefined index: clean_optimize in wp-content/plugins/artiss-transient-cleaner/includes/options-general.php on line 23
    Notice: Undefined index: upgrade_enable in wp-content/plugins/artiss-transient-cleaner/includes/options-general.php on line 24
    Notice: Undefined index: upgrade_optimize in wp-content/plugins/artiss-transient-cleaner/includes/options-general.php on line 25

    David

  24. dartiss
    Member
    Posted 1 year ago #

    Ah, you're right - apologies. I checked for these a couple of days ago but forgot to after the latest changes. I'll put out a minor update in the next few days to resolve these.

    David.

  25. dartiss
    Member
    Posted 1 year ago #

    I've had a look at this but can't re-create it. Those variables should all be set-up upon entering the options screen.

    Did you try the beta version, by any chance? I'm wondering if that was the cause.

    David.

  26. dartiss
    Member
    Posted 1 year ago #

    I'm about to (today or tomorrow) push out a minor release.

    I, incorrectly, thought that transients that only consisted of one record (rather than the usual two) had been orphaned. This isn't the case - if you create a transient with no timeout then it is created as just one record. I've therefore updated the plugin to not report on "orphaned" transients.

    Also, if you're interested, I've written an article on how transients work...

    http://www.artiss.co.uk/2013/05/using-transients

    David.

  27. eldenroot
    Member
    Posted 9 months ago #

    any news?

  28. Dark Designs
    Member
    Plugin Author

    Posted 3 months ago #

    The latest release has been pushed out. Have all of these issues been resolved to satisfaction?

    DD.

Topic Closed

This topic has been closed to new replies.

About this Plugin

About this Topic

Tags

No tags yet.