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/ _______________________________________________ luv-main mailing list [email protected] https://lists.luv.asn.au/cgi-bin/mailman/listinfo/luv-main
