MySQL performance tuning for HP Microserver using software RAID 5

P

Pada

Executive Member
Joined
Feb 18, 2009
Messages
8,191
Reaction score
6
Location
Stellenbosch
Hi guys,

I'm having bit of an issue with my MySQL server being INCREDIBLY slow on my Ubuntu's software RAID 5!
I made the mistake of allocating all my space of my 4x 2TB's to software RAID 5 partitions and the overhead on it is insane!

Like instead of idling, I had a CPU load of 1.2 from like 01:00 - 14:00 when my PC should've idled.
I think it took like 2 minutes to import the Zabbix schema (1.7MB SQL file).

I have 8GB of RAM installed, so for the while being I've modified my startup scripts for MySQL to copy the database onto a RAM drive and then everything is lightening fast. Like this time it imported the Zabbix schema in under 2 seconds, if not less than 1s. This will work since my Zabbix DB is pretty small still, but as the days go by it will start using more and more space.

So I hope to set some kind of cache parameter in MySQL to not try and flush everything straight to the RAID 5 filesystem :(

If there are no decent way to cache MySQL, would there be a way of increasing the software RAID cache instead to reduce the small read/write cycles?

Lastly, would it be possible to install a hardware RAID card without having to start over with the whole RAID setup? I suppose it won't be possible.
 
Last edited:
Doing hardware raid would mean starting over.
Can you pm me your my.cnf file and I can give you tips on where to tweak it.
The overhead on software raid5 for ubuntu isnt high at all, there must be something else running.
maybe try and run a ps -ef to see what is nomming your processor time. I have often found on big high end servers that the software raid is actually faster than their hardware raid controllers.
 
Thanks Rocket-Boy.

`top` doesn't show any process using much CPU (nothing more than 10%).
If I sort it by time, then I get the following:
Code: 
  PID USER      PR  NI  VIRT  RES  SHR S %CPU %MEM    TIME+  COMMAND
  306 root      20   0     0    0    0 S    0  0.0 258:34.01 md2_raid5
 1746 root      20   0  324m  53m  45m S    7  0.7 133:21.42 VBoxHeadless
 1517 chris     20   0  592m 107m 3764 S    0  1.4  10:53.52 SABnzbd.py
 1692 chris     20   0  433m 131m 4556 S    0  1.7  10:20.43 python

That list makes sense because they're the ones doing the most IO when looking at `iotop`.

/dev/md2 is my 8TB (6TB of storage) RAID 5 volume.

Here is my config:
Code: 
[client]
port            = 3306
socket          = /var/run/mysqld/mysqld.sock

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
user            = mysql
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
#datadir                = /var/lib/mysql
datadir         = /tmp/mysqldata
tmpdir          = /tmp
skip-external-locking
bind-address            = 127.0.0.1

key_buffer              = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
myisam-recover         = BACKUP
query_cache_limit       = 8M
query_cache_size        = 64M
log_error                = /var/log/mysql/error.log
expire_logs_days        = 2
max_binlog_size         = 100M
key_buffer_size                 = 256M
table_open_cache                = 512
sort_buffer_size                = 4M
read_buffer_size                = 4M
read_rnd_buffer_size            = 8M
myisam_sort_buffer_size         = 64M
thread_concurrency              = 2

innodb_file_per_table

innodb_flush_log_at_trx_commit = 2
innodb_buffer_pool_size = 512M
#innodb_buffer_pool_instances = 2
innodb_log_file_size = 64M
innodb_log_buffer_size = 8M
innodb_lock_wait_timeout = 120
tmp_table_size = 128M
max_heap_table_size = 128M

[mysqldump]
quick
quote-names
max_allowed_packet      = 16M

[isamchk]
key_buffer              = 16M
I've played around with the innodb_flush_log_at_trx_commit but it did not do anything since I've disabled binary logging.

So far I've had greater success with running MySQL within RAM drives than trying to optimize its cache. This is probably one of the reasons why I had to upgrade my desktop's memory to 32GB :D
 
There's your problem then. Your OS is dependent on your host's disc, so whether it's RAID 5 or (I'd suggest RAID 10) not, it won't speed up anything, in fact, I think the software RAID 5 on the VirtualBox is actually slowing things down for you.
 
Thanks AcidRaZor! Looking back, I should've made my home folder and OS partitions RAID 10 like you said, and not RAID 5.
I think I'll try to change it to RAID 10 during the holidays - when I have some more time.

I don't think VirtualBox is running badly at all though, because it is just using CPU & RAM resources for MikroTik RouterOS. I'm mainly using my HP N36L for downloading and a NAS.
I can stop VirtualBox and see if that had an effect on the MySQL on RAID 5's performance.

@Nod:
Thanks for those links. I'll run those when I have time.
 
The thing I noticed with running VirtualBox (depending on what you run on it of course) is that it, and it's hosts disk, is very dependent on each other. If the host disk is busy, it slows down VirtualBox dramatically, if VirtualBox is busy, same effect (although I've never really ran intensive stuff on VirtualBox with linux, but do have a Windows instance or two)

So your Disk IO becomes very important with a setup like that. What you could do is try and disable the virtualbox you do run on the enviroment and see if mysql speeds up somehow
 
Yes. I thought of disabling VirtualBox and performing a few MySQL benchmarks, but I highly doubt that it will have any effect.

There is a storage option in VirtualBox to "Use host I/O cache", which makes a big (positive) difference when the guest OS is doing lots of HDD actions.

I'm skeptical about VirtualBox having a negative effect on my MySQL at all, because when I'm uploading & downloading files on that machine I don't notice it being slow at all. Writing to the RAID 5 is slow, because the CPU gets maxed out at around 20MB/s.
 
Nod:
I'm using a paid license for MikroTik RouterOS, which is linked to the hard drive serial of my VirtualBox image. Secondly, I'm not running the MySQL server inside the VM.

The performance of my RouterOS is pretty good in the VM, so I'm not too much concerned about running it in another VM environment.

My issue has never been the VM - but instead just the slow performance of the MySQL server running in the host Ubuntu 11.04.
 
Well MySQL holds stuff in memory/caches and depending on the database engine (I assume it's InnoDB) it regularly flushes the data back to disk. This, couple with a slow write-time and a virtual instance doing it's business, can slow down the disk io dramatically slowing select queries etc

Run those two links someone gave you and see, maybe it's a question of tweaking your MySQL instance instead, but MySQL in general needs real fast read/write (SSD for a Xmas pressie to yourself!!?)

What are you running on MySQL? I've only experienced slow downs on a MySQL instance when I get hit by Google at about 150k bots every 30min. And even then I was able to get away with just a few tweaks. Your situation sounds like the disk is just way too busy
 
I had this problem when I reinstalled our MySQL server at the office on a Mirror RAID setup.

If I recall it was something to do with the indexing service of Ubuntu causing massive Disk I/O.

Try running iotop on that box and seeing which processes are eating at your disks.
 
Thanks SauRoNZA. I've already mentioned in my earlier posts that the the md2_raid5 process was the one doing most of the I/O when looking in iotop - while doing a MySQL insert.
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X