Slow inserts/updates with MariaDB on VM

[kennelm]

Hello,

 

I have terrible MariaDB insert/update performance on an unRaid VM running Ubuntu 20.04 LTS. 

 

The virtual image is stored at /mnt/user/domains/ which I've tried both on cache disk (btrfs) and within the array (xfs). 

 

Is there something obvious I should be looking for?  

 

As a test, I installed the MariaDB docker image and the data directory is on spinning disks (/mnt/usr/appdata).  Tests reveal this database is much faster than the same database on the VM.  In both cases, the same unRaid server (i7-8700K, 32GB RAM) is the host machine. 

 

Thoughts appreciated.

Larry

 

 

tower-diagnostics-20220618-1155.zip

Edited June 18, 2022 by kennelm
attachment

[Squid]

You domains share exists on disks 2 & 3.  Unless you physically moved the files to the cache pool, then they would remain on the disks regardless of the use cache settings.  Your appdata share only exists on the cache drive, contrary to what you've said.

 

This is probably your issue as by definition, writes to the array (parity protected) are guaranteed to be 4x slower than the base speed of the drive unless Reconstruct Write in Settings - Disk Settings is enabled.

[kennelm]

1 hour ago, Squid said:

You domains share exists on disks 2 & 3.  Unless you physically moved the files to the cache pool, then they would remain on the disks regardless of the use cache settings.  Your appdata share only exists on the cache drive, contrary to what you've said.

 

This is probably your issue as by definition, writes to the array (parity protected) are guaranteed to be 4x slower than the base speed of the drive unless Reconstruct Write in Settings - Disk Settings is enabled.

 

Yes, I should have been more clear that I've been experimenting, moving shares back and forth from cache to the array (and forcing the mover to run after making changes).   Am I wrong to believe the mover will handle the shift from one place to the other based on the cache settings for the share?  I thought I observed that behavior when I changed the settings and forced the mover to run. 

 

Sounds like you are pretty certain that the slow performance is due to NOT being on the cache drive?  I wondered if there are other settings I need to look into. 

 

Larry

Edited June 18, 2022 by kennelm

[Squid]

Setting a share that exists on the array to be cache ONLY will result in no move being done.  You need to set it to prefer.  IIRC, your domains share was set to Only, but existed on the array, so nothing will change.

[kennelm]

6 hours ago, Squid said:

Setting a share that exists on the array to be cache ONLY will result in no move being done.  You need to set it to prefer.  IIRC, your domains share was set to Only, but existed on the array, so nothing will change.

I'm an idiot.

Somehow, my vdisk migrated from the cache drive to the array. I moved it back and that did the trick.

 

Squid, to your point about the protected array being 4x slower, I was seeing 2 orders of magnitude of slower progress with inserts/updates to the database.



Sent from my SM-G975U using Tapatalk
 

Edited June 19, 2022 by kennelm

[JonathanM]

17 hours ago, kennelm said:

protected array being 4x slower, I was seeing 2 orders of magnitude of slower progress with inserts/updates to the database.

4X is for bulk data transfer rate, random write access will be MUCH worse than that, depending on exact I/O pattern. Layering the array I/O with the VM I/O, and your results are unsurprising.