Linux – Insanely high thesqld write I/O on SELECT queries

linuxMySQL

I am struggling to find a reason for my mysql have this much of disk write, it just doesn't make any sense. It it doing mostly of the time SELECT queries, so the only reason I find to it write so much data is of JOIN queries that must be cached on disk. I have a medium WordPress stack with 6 thousand posts, 15k terms and about 80k metas. The database dump itself is only 150mb.

The general_log generated by these monstruous I/O can be seen here: http://pastebin.com/Pu5YrFz9

iotop output
Linode longview report

Best Answer

I would guess there are lots of disk-based temporary tables created for sorting and other stuff. Keep your eye on created_disk_tmp_tables MySQL variable.