Tuesday, July 04, 2006

MySQL sysbench benchmarking - iSCSI

Our company, RightMedia is in the process of evaluating a storage subsystem for our ever growing MySQL databases. It seems that our data is growing at an alarming rate (this is a good thing) and our internal SCSI disks aren't handling the load of massive inserts, plus large queries. So the time is now to evaluate a storage subsystem that will grow with us and scale for our performance needs.

I was really reluctant when I first heard iSCSI proposed as a potential solution. I am familiar with large storage subsystems all hooked in through fibre channel. It's an expensive solution, but performance is great. There are a few iSCSI vendors, and we had lots of problems with kernel version & driver version. But, we perservered, and have systems where we can benchmark the performance of iSCSI.

Now before I get to the actual numbers, let me give you the environment for the testing. I have tried to use as much identical hardware as possible for testing. All of the tests were done with Gentoo Linux, kernel version 2.6.16. The machines have at least 8GB memory (one has 16GB of memory), and all have 2 dual core Opteron CPUS. MySQL version 5.0.21 was used for testing, and InnoDB used as the storage engine. The following types of drives were used for benchmarking: 1) Single 7200 RPM 200GB SATA drive 2) 3 15,000 RPM 73GB SCSI drives in a RAID-0 configuration (128k stripe size) 3) 4 disk RAID-10 configuration on an iSCSI volume (actually using 8 disks) 4) 8 disk RAID-10 configuration on an iSCSI volume (16 disks,, 1M stripe size)

Without further ado, here is a chart of the results:



Shocking isn't it? The iSCSI disk outperforms even a local SCSI array? I found this hard to believe myself, but it gives me more hope that this might be a viable solution. I am still conducting lots of tests on teh device to make sure that the machine scales, and I'll be posting results soon.

8 comments:

Anonymous said...

Hi

I've commented on this in my blog
http://www.mysqlperformanceblog.com/2006/07/06/sysbench-evaluation-of-iscsi-performance/

If you need any help with benchmarks let me know. I would be very interesting to have something like this published.

Anonymous said...

Hi,
what a really interested question in accordance with your result is: how many CPU cycles were spended on one IO? (because iSCSI should has hiest CPU utilization). It could be huge problem for CPU-intensive InnoDB engine.
-
regards
Vitaly

Partha Dutta said...

Vitaly,

With the dual-core opterons, iSCSI CPU utilization is fairly negligible. If the innodb_thread_concurrency variable is set to 4, we can avoid a lot of thread thrashing, and fairly spread load accross the CPUs. In fact, during the 16 thread benchmark, I observed the User CPU utilization reach 30%, but on only one of the cores. The first core was still idle and had a utilization of only 20%. Most of the work is offloaded to the network card, and the actual iSCSI controllers themselves.

Partha

Partha Dutta said...

Jeremy,

Take a look at Benchmark details. It has all of the details of the sysbench test.

Partha

Anonymous said...

Hello , Partha I want to make system in which , I have to handle 300Gb database using mysql 5.0 , can u tell me what should be best hardware configration, we are going to use Kernal Virsion:- 2.4.20-30.9
OS. :- Red Hat Linux release 9 i686 please can u help us to make this system. your ideas and suggestion will be most useful for us thanks in advance , Please give me suggestion at eluminous007@yahoo.co.in

Thank You
sandeep patil

Anonymous said...

Hello, Partha.
Thanks for comments, it was very impressive (about innodb_thread_concurrency), but more clear and deterministic result may be achieved with step-by-step and head-to-head comparison. For example:
1) Local RAID-0(16-disks) vs ISCSI RAID-0(16-disks) for different IO patterns (scattered,sequential,direct,async...) simulated by iozone or dt.
2) Combine best solution from step 1 with InnoDB and tune it.
Otherwise community will be concerned about comparison of apples and oranges again ;).
I'm afraid it couldn't be said better than:
"http://www.tpc.org/tpcc/faq.asp", look at "Q: How can I tell if one of the TPC-C benchmarks is relevant to my application and environment?
"
regards
Vitaly

Anonymous said...

It is a very nice game silkroad gold, I like sro gold. You can play it silkroad online gold, you can buy the cheap silk road gold. You smart and buy cheap silkroad gold.
I want give they happy time, twelve sky Gold, I work here with 12sky gold. Though, I can not make much money, twelvesky Gold, But I want to Exercise myself, 12Sky Silver Coins, I get up early 12 sky gold

Anonymous said...

Remembered the first time I played the game and bought wonderland Gold , it is about two years. Since the first day I bought wonderland online Gold , we should know that the day will come sooner or later. And I will not leave my wonderland money . My friends forever, even though I will leave you one day, I will remember you forever. At that time, I was a naive; I must used money to buy wonderland Gold , as that I would not depend on myself. But I was not regret to buy cheap wonderland online Gold . It is just a way bring me to the success.

To buy shaiya gold , it is not the aim of the shaiya. It is the game which brings a lot of happiness to me. We buy the shaiya money together. The beautiful story, we can not be forgotten, buy shaiya gold . You will not regret to have cheap shaiya gold . So I will continue to have shaiya online gold , it is a beautiful fairy tale, it looks like my life.