-1

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
ewwhite
  • 194,921
  • 91
  • 434
  • 799
AngularAddict
  • 111
  • 1
  • 6
  • Pretty much what @TomTom said. 7.2k rpm is nowhere near fast enough if you have large databases. 10K rpm isn't good enough, either, unless your database is relatively small. 15K rpm at least in RAID 10. – Katherine Villyard Jan 19 '14 at 17:20
  • 1
    And here we go - basic school math. SAS discs: 4819.5 TPS. SATA = 129 TPS. Factor of 37 right here - and i doubt the IOSTAT was even set up properly (as in: the numbers likely get worse with really large data). That is quit paethetic, though - 43 to 54 IO's per second is terrifically bad. Must be either crappy drives or really bad hardware setup. – TomTom Jan 19 '14 at 19:43
  • "really bad hardware setup" This exactly what I am talking about. If it is this, then it could be increased. What TPS should I get with these (http://h30094.www3.hp.com/product.aspx?sku=10389321&pagemode=ca) drives? – AngularAddict Jan 19 '14 at 19:48

2 Answers2

4

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.

TomTom
  • 50,857
  • 7
  • 52
  • 134
  • Ok. Thanks for reply. Any advise for doing IOPS test in windows and linux? I dont have so much experience in these things – AngularAddict Jan 19 '14 at 12:38
  • 1
    Here the advice then is: Hire someone who has. This site does not deal in beginner questions. On windows I would use SQLIO - which is a tool replicating SQL Server access patterns. Not that MySQL CAN be that different. YOu can likely see a hugh IO backlog - io operations taking too long - on your server. – TomTom Jan 19 '14 at 12:42
  • I had possibility to run same test on HP ML350p Gen8 E5-2609 2.4Ghz server with 4 HP 146GB 6G SAS 15K 2.5in SC ENT HDD disks in raid 10 mode. Test took about 10 seconds. Is these SAS disks that much better? What I am wondering about, is this SAS based server supposed to be faster that SSD drive on my desktop? According to this (http://en.wikipedia.org/wiki/IOPS) hhey should not be. I was hoping that I had something wrong in my server setup that is making queries so slow, especially INSERTS AND UPDATES. – AngularAddict Jan 19 '14 at 14:56
  • 1
    Thise discs possibly have 2.5 times the IOPS - minimum. According to your own link (that you should be able to read - it is right there) as htey likely have 15k RPM. What is wrong most basically is someone not knowing what he does buying a server and being surprised he bought the wrong one. – TomTom Jan 19 '14 at 15:44
  • I really don't understand people who are extremely rude to beginners. How the hell should we ever learn everything if it questions always results whining. Should I just stop being interested about these stuff and go hang myself? No I wont. I want learn, that is why I am asking. Not because I dont want to learn. As what comes to real topic here, I really cant understand why these SAS disks perform faster than SSD. Did you mean they have 2.5*60.000 IOPS or 2.5 * 300 IOPS? If second one, I dont understand how 2.5*300 IOPS can be faster than 60.000 IOPS if it is all about disk speed like you said. – AngularAddict Jan 19 '14 at 19:30
  • Would it be likely to have increased performance if I setup HW raid10 setup? I could consider getting license for SUSE or REDHAT as they have raid drivers available for my controller. – AngularAddict Jan 19 '14 at 19:41
  • 4
    Get a book? Do not walk into a place where beginner questions are not welcome and then complain that people tell you that. THIS is rude - slap in face rude if you ever try that for example in a star ragted kitchen to the chef. Books are there for a reason. We all learned, and we did not by going to places where beginenr questions are explicitly not welcome and insist on asking them. – TomTom Jan 19 '14 at 19:41
  • This had response did not have anything to do with problem which was solved with help of ewwhite's answer. – AngularAddict Mar 16 '14 at 14:58
1

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.

enter image description here

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

Community
  • 1
ewwhite
  • 194,921
  • 91
  • 434
  • 799