Poor SQL performance on HP ProLiant ML310e Gen8 v2 with raid10

I recently bought HP ProLiant ML310e Gen8 v2 server with 8G's RAM and 4 HP 1TB 6G 7.2k rpm SATA (3.5in) Non-Hot Plug MDL HDD disks.

It was surprise to me that HP didn't provide debian drivers for raid controller and I had to go with software raid. I installed Debian with it's own software raid and raid level 10.

After installation I am experiencing huge performance loss and it looks like I managed to track it to MySQL operations.

Server is used mainly as Apache2 server with MySQL database.

Here is simple script that I ran to measure SQL operation times with server and my desktop PC.

$sql = "SELECT id FROM c_event";
$events = $db->getall($sql);
foreach($events as $item)
    $db->query("INSERT table1 set id = {$item['id']}");
foreach($events as $item)
    $db->query("DELETE FROM table1 WHERE id = {$item['id']}");

I compared results with my desktop PC with Intel Q6600, 4GB of ram and 120G SSD disk.

• desktop completed in 45 secs
• server in 2 hours, 46 mins and 18 secs.

Difference is totally insane. Any advise?

Server raid performance seems ok to me, as disks ain't top quality ones.

hdparm -t /dev/md1
/dev/md1:
 Timing buffered disk reads: 1024 MB in  3.00 seconds = 341.04 MB/sec

EDIT:

IOSTAT: on problem server during test:

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda              43.00         0.00       256.00          0        512
sdb              43.00         0.00       256.00          0        512
sdc              54.00         0.00       262.00          0        524
sdd              54.50         0.00       266.00          0        532
md0               0.00         0.00         0.00          0          0
md1             129.00         0.00       520.00          0       1040

And on server with 15k SAS disks

Device:            tps    kB_read/s    kB_wrtn/s    kB_read    kB_wrtn
sda            4819.50         0.00     24240.00          0      48480

You have no write caching on your RAID controller because you've bypassed the hardware RAID functionality. That's your main problem...

People are buying these HP Gen8 "e" versions instead of the "p" versions. The "e" servers have limited operating system support and feature HP Dynamic Smart Array RAID controllers, which only ship with binary drivers... If you fall outside of the supported OS with these controllers, your only recourse is software RAID or purchasing a proper PCIe RAID controller.

Your performance will be limited by the slower 7,200 RPM disks, but the hardware RAID situation will make a bigger impact at your scale than changing the disks to enterprise SAS.

Also see rant at: Install Oracle Linux 6.4 on HP ProLiant DL380e Gen8 server

Difference is totally insane.

No, it is quite ok if you have large databases. see...

120G SSD disk.

That is about 60.000 IOPS

4 HP 1TB 6G 7.2k rpm SATA

That is about - hm - 300 IOPS. If it is all read only and the controller is really good maybe a little more.

Basically the SSD based laptop is a much better database server than the crappy disc layout that is build for size not speed. And database LOVE disc speed.

You should never have bought that machine to use it like that as a database server. Simple like that.

Now:

Server raid performance seems ok to me, as disks ain't top quality ones.

Hahaha. Yeah. Measuring linear speed is soooo useless. What about you do a proper IOPS measurement and compare that. Yes, discs are ok for single threaded linead data access. BAD NEWS: that is not what happens on a server and REALLY not what happens on a database system.