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.