New on LowEndTalk? Please Register and read our Community Rules.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
All new Registrations are manually reviewed and approved, so a short delay after registration may occur before your account becomes active.
weird MariaDB slowness and high IOwait on htop output
This discussion has been closed.
Comments
reinstalling , but do you really think it's my case here ? the disk is always newly formatted
I gave this idea because it helped me once. Quick formatting (installing new system via rescue etc) is rather a placebo, real deep format takes hour not minute. Run fstrim and you will get info how much has been trimmed.
okay , thanks for the hint , let me try it out
okay , I guess it's not the case ...
Yeah, not really
I’m out but it’ll be interesting tomorrow to see the results if you tried no raid and both drives.
If it’s a drive error, it would be unlikely that both drives are just as bad.
thanks for trying
for whatever reason , probably due to disk partition , I can not boot up or SSH in when use single disk
If you have the option and the features match your requirements seriously consider Postgresql. Of course this depends on where your application is in its development curve and your resources and ability.
After years running Maria/MySQL as the base for projects I've been moving over to Postgresql (largely since 16 & 17 where replication has gotten alot better).
MariaDB has often ended up having rather inconsistent performance for me. For the applications we still have on MariaDB we do a database server per application these days, and restart them yearly (if they don't get updated). Its quite fast, until sometimes its not.
well , sadly wordpress doesn't support PGSQL ...
IMO the problem clearly is with the disk. But before hastily cancelling, why not simply bring the problem to their attention? Chances are (I guess but have zero experience with Hetzner) that they'll simply replace the problem drive.
It's par for the course with non-enterprise NVMe - having physical power loss protection rather than relying on journalling in firmware to emulate it makes a gigantic (1-2 orders of magnitude) difference to certain write workloads
It would be better to know what drive is causing issues.
Yes, but it seems OP has problems using the drives without Raid.
well , thanks for the hint , I contact support, they replaced server , yet on this one is still same , same database import takes about 15 seconds which really bugs me
I have been trying to googling around , but seems couldn't find anything useful , is there any tuning I can test a bit ?
Is the database dump private? If not, could you share exactly how you setup the environment and how you restore the file as well as the file?
Also: I'm still very interested to see how things change with the two drives without RAID. It should definitely be possible to do.
Then, sorry, chances are that the cause is somewhere in your config(s). And I strongly agree with @emgh that to properly analyze the problem you should, if any possible, un-Raid at least one partition, so as to allow for separate testing of each drive.
Not doing that IMO boils down to continue stumbling about in the dark.
I didn't find much online about this when I had the issue, either. But what @darkimmortal writes was my experience: On otherwise identical hardware, substituting a non-enterprise SSD for an enterprise one was the solution.
I was running MariaDB using mdraid, just like you, and no amount of tuning or other software fixes could help. The SSD simply could not give reasonable performance when doing sustained writes. Any write-heavy operation, whether it be a database dump or running a benchmark, would cause other operations to pause.
Unfortunately, I don't think you'll gain much by doing any further tuning. My bet is on @darkimmortal's suggestion. If you get better performance with different hardware, then save your time and go with that hardware, even if it costs more. You may not save money, but you'll save your sanity.
it's kind of private , but simply a wordpress database with few dozens of posts and among other things
I couldn't make it work with single disk system , but I create a docker container database and mount it to /dev/shm
okay , so I guess it's about the disk or IO
I also tried the RAID 0 (maybe in theory should double the write/read ?) , pretty much same result , 15-20 seconds importing 35MB size DB
I don't know much about the database, I must say for long time I thought Database is more like CPU-intensive task , specially with nowadays big RAM , and all buffer, cache techniques...etc , never thought NVMe has so many difference and cause things like this , new thing learnt
yeah , understood , it was happy lesson to play around a bit to get know more about database and disks
Is it the same machine that does 15 seconds, but db is in docker container?
yes , DB storage in /dev/shm , same machine , as shm is run on RAMdisk , so I guess it's super fast
Ah, oh, I've missed the /shm part, sorry.
Yes, client SSD's (non-enterprise) are terribly slow when you do frequent fsync() to disk, which databases like to do a lot, to keep data safely on the disk.
What you can try is this:
Make sure to read eatmydata manual page and understand what it does and why it's much faster. Don't come crying here if you lose data with improper usage.
One thing that no CPU or RAM can improve is fsync(). That still depends heavily on the disk, and databases use such calls a lot. That's why using only enterprise NVMe disks is a good idea, they come with small RAM or SLC flash buffer, which is protected from power loss. This allows them to take your small fsync() quickly and immediately return success to your application, then slowly flush it to the persistant storage in the background. Client SSD's are cheaper exactly because they don't have anything similar.
You might also experiment with --single-transaction during mysqldump, and then check if it improves the speed of import. Once again, read mysqldump manual and understand the implications. But, it could help...
thanks mav , I have just cancelled it and going to order an AX102 instead ...
And that is of course the ultimately good solution.
What drives do AX52 and AX102 normally come with?
according to hetzner page , AX52 is "normal" disk , AX102 is "datacenter grade" disk
Ah, same with EX series, EX44 says: 2 x 512 GB NVMe SSD (Gen4)
While EX101 says: 2 x 1.92 TB NVMe SSD Datacenter Edition (Gen 4)
Still, can that difference really explain the 1,2 sec > 15-20 sec? I had no idea if that's the case.
on my AX101 , it's "datacenter edition" disk , which does it 1,2 seconds , I guess that was the case, according to CPU benchmark , AMD 7700 is better on single core perf than 5950X