OVH Community, your new community space.

Abysmal mysql performance on SP BestOf server


chmac
21-04-2012, 14:59
I installed mysql from the Ubuntu repositories or from a PPA. I can tune mysql and performance will improve, but sysbench is benchmarking the hard drives directly, without mysql in the middle. So I'm comparing like with like performance.

I'm guessing the Rackspace cloud hosts use a hardware raid controller with a write-back cache. From my reading, it seems that performance can improve radically with a write back cache, but it's only safe to do so with a battery backup unit. I'd guess our Rackspace server and the Rackspace cloud machines use BBUs and write-back, hence their stellar disk performance on random writes.

IainK
21-04-2012, 11:10
How did you install the cloud VPS? Did it come with MySQL pre-installed?

Have you tried tuning /etc/my.cnf?

Thelen
19-04-2012, 13:15
RAID with read/write caching (well not much of the later really) definitely helps enormously.

chmac
18-04-2012, 19:50
I've looked at the smartctl output from both drives and I'm told (by OVH) that everything is fine on that front.

We will dump the disk machine and switch it to an SSD box, the price is the same and we don't need the extra 1.9T of space.

From further reading I'm guessing that our Rackspace machine had a hardware raid card with a backup battery that was using Write Back caching. Apparently it can improve random read / write performance by over 30x. I'm currently benchmarking 36 different hard disk setups on our Hetzner box to see how best to setup the disks. Hopefully when that completes I'll have a better idea of how to set up the disks for optimal mysql performance.

3r1c
14-04-2012, 01:38
I don't have a SP BestOf but I do have mysql running on some HDD and SSD servers and the SSD servers performs significantly better.
But this is with websites running queries against the databases, I don't use mysqldump/import. I would have guess it would be slower on SSD because of sequential writes.

I do notice that a single SSD disk and a single HDD disk the SSD is slower in sequential write test, but most servers have mostly random writes, and SSD is much better at that.

If you can afford SSD then get SSD always.

It could be that one of your HDD is dying, 0.5MB/s is very slow no matter what you are doing it should never be that bad.
I have hundreds of servers and I have seen a 2 or 3 times a HDD shows no errors in testing, but is just very slow for some unknown reason.

chmac
14-04-2012, 00:00
@Myatu Please do share... :-)

@3r1c: My SSD machine completes the import in ~5 seconds, versus ~30 minutes for my disk based machine, so in this case, the SSDs are 300x faster. Could a hardware raid controller explain the difference between 0.5Mb/s and 60Mb/s on a single threaded read/write test?

chmac
14-04-2012, 00:00
@Myatu Please do share... :-)

@3r1c: My SSD machine completes the import in ~5 seconds, versus ~30 minutes for my disk based machine, so in this case, the SSDs are 300x faster. Could a hardware raid controller explain the difference between 0.5Mb/s and 60Mb/s on a single threaded read/write test?

3r1c
13-04-2012, 22:51
Individual writes to SSD are slower then HDD.
Mysql dumps/restores are going to be slower on a SSD.

To get good speeds from a SSD you have to do many simultaneous read/writes.
A dump/restore is a sequential operation.

If you had a hardware RAID card, the buffering would overcome the sequential write problem, thats likely how rackspace is doing it.

I have an OVH server with a few SSD and a hardware raid and I get over 1GB/s

Myatu
13-04-2012, 22:05
Quote Originally Posted by chmac
Or is there something blazingly obvious that I'm missing?
Yes

chmac
13-04-2012, 20:09
To put this in perspective, a Rackspace cloud server 512M RAM, 10G disk, £15 a month, when benchmarked with sysbench[1] averaged 50MB/s over 108 tests run. The OVH SP BestOf machine averaged 2.2MB/s over 54 tests. Performance on the Rackspace box is consistent with 1 or 64 threads, on the OVH box performance improves as the thread count increases. Performance with 1 thread is 0.3MB/s up to 4.4MB/s with 64 threads.

To put that into perspective, even with the inconsistent IO of a virtualised server, the Rackspace machine at 1/4 the price outperforms the OVH database server 25 times over.

This machine, in its current configuration, is unusable to us as a mysql node. I can't believe that other customers are accepting this kind of performance. I'm assuming something has to be wrong with my configuration. I tested the same configuration on a new Hetzner box and depending on software raid, lvm, and / or filesystem, performance is anywhere from the same 0.3MB/s (1 thread) to 25MB/s (64 threads).

Can this really be simply how MySQL performs on 7.2k disks? Is this what every other OVH and Hetzner customer is experiencing? Or is there something blazingly obvious that I'm missing?

[1]
Code:
sysbench --num-threads=$threads --test=fileio --file-test-mode=rndrw prepare && sysbench --num-threads=$threads --test=fileio --file-test-mode=rndrw run

marks
13-04-2012, 12:02
yes, for performance, the default Soft RAID1 can be broken up, specially if you're after SQL performance. You can do that reinstalling the server from the manager, and choose individual drives.

Remember to keep backups of your data then, though.

In any case, it would be good to run individual tests on each drive, to make sure that there is no hardware problem. You can do that through rescue mode, so there is interference of the OS. But even if no hardware problem is found, the issue could be down to optimization of the drives, there is quite a lot of work to do about it.

Myatu
12-04-2012, 20:02
The Kimsufi is a single-disk system, with no soft-raid in between. However, the SP BestOf does have soft-raid in between, which is a big bottleneck in high IO routines (the SSD's compensate for this due to their extremely high IO processing capabilities). This can be made worse (in the magnitude of 5x or worse) if LVM has snapshots enabled. You're better off running a separate, non-raid partition on the SP BestOf.

chmac
10-04-2012, 13:39
Holamigos,

We have two SP SSD servers and one SP BestOf (twin 2T SATA disks). MySQL performs abysmally on the BestOf server.

Our complete setup is:
  • Ubuntu 11.10, ovh kernel
  • LVM on top of software RAID /dev/md2, ext4 filesystem
  • mysql-server-5.5 from the nathan-renniewaldock ppa


To illustrate just how atrocious our performance is, I installed zabbix and imported two MySQL files, timing them both.
Code:
time mysql -p zabbix_temp < /usr/share/zabbix-server/mysql.sql
time mysql -p zabbix_temp < /usr/share/zabbix-server/data.sql
These two files took 1m25s, 28m29s respectively. The second run was better, 1m21s and 13m42s with the MySQL slave stopped.

Comparatively, on a kimsufi server (2G, dual core 1.8GHz cpu, single disk) the files take 36s and 8m28s, while on our SP SSD server it takes 4s and 5s. So on two nearly identical servers, identical setup, only SSD versus physical disks, the difference in importing the data.sql file is 5s versus 28m, or more than 300x slower. I can't believe that difference is down to only disk performance.

Is anyone running a similar setup? Does MySQL perform well on the SP BestOf server? I'm debating whether to replace this server with another one the same (performance is not the key priority on this box), switch to SSD, or simply abandon OVH and move our backup externally. If anyone can provide insights into SP BestOf performance that would greatly inform my decision.

Cheers - Callum.