On Wednesday, 13 May 2020 8:42:16 PM AEST Arjen Lentz wrote:
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.
Thanks.
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 |
According to "show table status;" the vocabulary and vocabulary_node_types tables each have 16K of data.
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
It's running MySQL 5.6 which apparently has 256K for the default sort_buffer_size and the configuration doesn't seem to specify anything different. So I guess we shouldn't have problems in this regard. Will I gain anything from changing to MariaDB? For reasons that I never worked out the LUV server didn't get changed to MariaDB on the Debian upgrade process while other Debian servers I run did.
Btw if you use SHOW FULL PROCESSLIST you'll get the full rather than a
Thanks, I'll try that.
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 further, long_query_time=0.1
OK, I guess that's one reason for changing to MariaDB.
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.
The VM has 4G of RAM which is a reasonable amount for what is being done. There is 16G of RAM allocated to VMs and 48G in the system. I can easily allocate more RAM to that VM. The total of all databases on the LUV server is 3G of storage. I could allocate it another 3G of RAM to make sure it all stays in cache. Using /dev/shm for tmp seems like a good idea.
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.
Thanks, I'll check that out too. -- My Main Blog http://etbe.coker.com.au/ My Documents Blog http://doc.coker.com.au/