Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Maybe I'll try my luck here. Server Fault site was quite hostile for non-professional server user.

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.

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

I was wondering if there was something misconfigured in my server or is it totally just lack of disk performance.

For comparsion, I had possibility to run same test on 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.

share|improve this question

migrated from stackoverflow.com Jan 19 '14 at 20:09

This question came from our site for professional and enthusiast programmers.

    
Just to be clear - is your desktop running the same OS and does it have RAID? – bhttoan Jan 19 '14 at 16:57
    
Desktop is on windows and single disk. – AngularAddict Jan 19 '14 at 18:24
2  
How many rows are we talking about here? As I see your queries they're mostly doing ad-hoc (random) small reads and writes. Don't use the sequential copy perf value as a baseline here, it's useless. Better check the IOPS and random read/write performance of your RAID, that looks far more useful for your workload. Now, why are you doing reads and writes based on Id? Can't you use a set based approach (get 1000 events at once -> insert 1000 items + delete 1000 and then rinse, repeat)? – Marian Jan 21 '14 at 9:44

Check your configuration. Do SHOW VARIABLES; on each machine, then diff the output. It smells like one of these is different: innodb_buffer_pool_size sync_binlog innodb_flush_binlog_on_trx_commit or maybe something else.

Now to be hostile... RAID-10 (or -5) without a battery-backed-write-cache is hardly worth having. Sure, you gain recovery from a disk crash (a rare event), but meanwhile, you don't get the write speedup of the write cache.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.