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.