I happened to spot that you tagged me - mind that I don't do much DB stuff these days,
but happy to help if I can.
The iowait while not correlated with this issue was
higher than I expected, I
ran "iotop -o -d5 -b -P" which indicated that writes from mysqld was the main
disk access. I ran "fatrace -f W" which indicated that mysqld was writing to
deleted files in /tmp.
| 68991 | luv_drupal | localhost | luv_drupal | Query | 0 | Creating sort
index | SELECT v.vid, v.*, n.type FROM vocabulary v LEFT JOIN
vocabulary_node_types n ON v.vid = n.vid WHERE |
The only time I caught an access with the "show processlist;" SQL command was
the above, might "Creating sort index" mean writing to deleted files in /tmp?
If sort_buffer_size is too small (generally 2M or 4M is good), or the SELECT columns
contain TEXT or BLOB fields (using * in the SELECT list is not a good habit), the sort
operation will have to use disk-files to do its thing. Something else might also be
"wrong" with this query, perhaps the LEFT JOIN wasn't supposed to be a LEFT
JOIN and is returning way too many rows, borky WHERE clause, or other factors.
Drupal is pretty decent these days (say Drupal 8), but it depends on the version, and
there are borky plugins of course.
Btw if you use SHOW FULL PROCESSLIST you'll get the full rather than a possibly
truncated query string.
If you are using MariaDB, you can set up the slow query log and enable extra options so
that sorts that go to disk get logged. But if you just set up slow query log with 1
second, you can already see what shows. If it takes that long it'll show up. Or go
I think the resultset of this query is way bigger than it should be, otherwise it possibly
wouldn't go disk, and wouldn't produce enough disk I/O to blip.
Tuning the server a bit might help. Did you change any settings from the defaults?
If you have enough RAM you could take a sneaky shortcut and define tmp=/dev/shm, but if
the tmp files that MySQL does need during normal operations are big enough, you'll get
memory troubles instead.
And as you'll know, swapping is never a good thing on a DB server.
But tuning the server just a bit is always a good idea, the defaults on most distros are
to minimise RAM usage and minimal logging.
Also set stuff like innodb_flush_method=O_DIRECT to optimise the I/O further.