Cleaning up a lot of WordPress _transients

WordPress seems to like hiding a load of ‘transient’ (cacheable) stuff in it’s wp_options table. Unfortunately for one site, it seems it didn’t bother to clean up the transient stuff, leaving behind about 750,000 records… which made a WP version upgrade painful, as MySQL locks the wp_option table which causes all other page loads to get stuck waiting (and the site to stop working).

Fix / hacky solution after the ‘more’…

The troublesome query WordPress was trying to run was :

DELETE a, b FROM wp_options a, wp_options b
        WHERE a.option_name LIKE '_transient_%'
        AND a.option_name NOT LIKE '_transient_timeout_%'
        AND b.option_name = CONCAT( '_transient_timeout_', SUBSTRING( a.option_name, 12 ) ) AND b.option_value < 1430213580   

From “reading the internet” it appears _transient items can be deleted without fear of breaking anything.

Given there were 750,000 of them, trying to do a DELETE FROM wp_options where option_name LIKE ‘_transient%’ would take too long, so I wrote a hacky script to delete them in chunks :

#!/bin/bash

mysql="mysql --defaults-extra-file=/etc/mysql/debian.cnf -A wordpressdatabase "

while [ true ]; do
    echo "delete from wp_options where option_name like '_transient_%' limit 500; " | $mysql
    echo -n " d"
    while [ $(echo "show processlist" | $mysql | wc -l ) -gt 30 ]; do
        sleep 5
        echo -n 's'
    done
    sleep 2
done

No doubt something more elegant is possible, consider options from The Digital Swarm … and my use of while [ true ] and ‘wc’ is pretty nasty … but that’s good enough for now.