Diamond Notes

I have been pointed to a RAM-disk based device called the HyperDrive4 by a recent comment of a reader of the previous post on SSD drives. It is a “relatively” cheap device that looks to be solidly build- battery backup (with optional automatic hard drive copy in case of power failure). I did say relatively cheap. I think the basic 32GB unit runs about $3,500 (plus RAM). It seems to currently top out at 32GB.

Even so, it is would incredible to have one of these in your server to run the most used indexing files and tables. I know 32 gigs isn’t that much these days but if necessary you can RAID these things to build larger disks. The throughput and access times on these is just incredible. Take a look here:

http://www.hyperossystems.co.uk/

Click on the “more information” button underneath the hyperdrive4 unit to see the product line.

So do I get to test one now?

I am thinking about testing two layouts of hard drives in a db server. I have this small “disagreement” with members of the sys-admin team about which is best: using a striped RAID arrangement to spread reads over multiple drives or what I was always taught — separating your data among various drives. For example I would typically do something like this: 1 drive for OS, 1 drive for data, 1 drive for log files. This spreads the reads and writes somewhat.

Before I go and start testing all this…can someone point me to a test of MySQL on these arrangements?

Thanks!!

Hey everyone..I want to get some feedback. I was wondering if anyone had experience with SSD (Solid State Disk) drives.  They are basically flash memory mounted and designed to be used as a replacement for a typical hard drive.   The prices are really falling on SSDs and, despite people who say otherwise, they are now as reliable, or more reliable, than typical hard drives.  MTBF was rated at 2,000,000 hours for one drive and they all seem to have good warranties (I saw five years on one drive).
What makes me drool is not the throughput of a typical drive (which is about the same as a high-end drive) but the .1 ms random-access seek time. Yes, that is POINT one!! As in 1/10th of a millisecond!! I saw one that was advertised to have a seek time of .03 - .1 milli-seconds.
They are still very expensive.   But, what if I replaced the hard drive holding my data files with one of these drives.  Now my seek time for reading tables off disk is essentially zero.  I wonder how that would affect performance.  Ideally I would like three drives, one for the OS, one for the log files, and one for the data.  I bet your I/O bus bandwith/speed would suddenly become your new bottleneck - not your hard drives themselves.

Size of the drive is an issue. Sixty-four GB size drives are now common and 128GB are becoming available.  I have several databases that would not fit on the current drives, but still I think I could manage to split things up if needed.  Once 128GB becomes the norm even this wouldn’t be a problem.
So, if anyone has had any experience with these I would love to hear from you.  Benchmarks would be great!! Now if I could just get a half dozen to throw in my Coraid and test it out..

Just a follow-up on the two previous post I had about the Coraid SAN. Today I formatted a partition as XFS and ran the iozone tests against it. Here are the results:

Coraid w/xfs filesystem, switch and jumbo frames

Comparing this to previous tests are quite enlightening. First, XFS is the clear leader in the write category. It is over 20MB/sec faster than either ext3 or reiserfs. The read rates are slightly slower than ext3 but it is a very small difference (around 2MB/sec). It is slightly faster than reiserfs.

With the clear write advantage we are very seriously considering using it for our Coraid which will be where we store data for our new cluster.

I have one question. I think I remember that there was a problem with XFS some time ago. If I recall correctly it would actually zero out files with some kind of bug. Another words I might have a very large innodb data file and it ends up as completely empty. Not a good situation. Anyone know about this? If I am correct..has this been resolved?

Thanks for the request to perform this test Stewart. Hope everyone finds it interesting.

I did some testing because we are going to convert a very large merge table (composed of five MyIsam tables) into Innodb. I knew that Innodb tables were larger than MyIsam but I didn’t have any real figures. In addition I used the time to test loading data into both MyIsam and Innodb tables to see which loaded faster. As you will see I ran into some interesting results at the end. If anyone can explain why it would be MUCH slower to load the Innodb data from four files instead of two files I would love to hear from you. The files are loaded sequentially.

Testing MyIsam and Innodb

One of the problems that we have on an older database is that we have one table arranged as a merged-MyIsam table of five MyIsam tables. Whenever a long-running select query is issued on the table it blocks writes from happening. Even though we used insert delays on this table, after a while we simply run out of connections because there is a limit of 400.Moving to the new version of MySQL gives us a good opportunity to do some upgrading. It has been proposed that we replace the Merge table with an Innodb table. One consideration is how much space that this is going to consume.

Setup

I am importing the table from the last backup we ran on on the database. On satest2 I will set up the same table structure and data with two different names and with an engine type of Innodb for one and MyIsam for another. Then we can compare the disk space used by the two tables to see which is better (in regards to space). In addition I will be testing the import times of innodb and myisam.

Tests

The first set of results will show the time taken to import the data. The dataset was the same with just the table type changed (one was the original myisam and one was innodb).Innodb

MyIsam

The second set of results will show the size of the table after import.

Innodb

5930745856 (5.6G) opens_5_innodb.ibd

5.6G

MyIsam

1240851825 (1.2G) opens_5_myisam.MYD

920984576 (879M) opens_5_myisam.MYI

2.1G

The MyIsam tables are roughly 62% smaller.

Trying to speed up the Innodb import I split the import file into two parts. I fed both parts - one after the other - into the Innodb table to see if this changed the load time significantly.

Innodb (data split into two files)

Seeing that this improved things quite a bit (44:27 vs 1:38:14) I then split the data into four files and once again fed them into the table one by one:

This did not do so well. It was only a few minutes faster than loading the data from one file. I don’t know why it was so much slower loading from four files than two files.

Conclusions

This is fascinating. The difference in load time between MyIsam and Innodb tables is so dramatic that I went back and did a count of the rows on each table to make sure that everything was actually loaded during the MyIsam import. The only major difference is that MyIsam is a non-transactional engine and Innodb is a transactional engine. I do not understand why the splitting of the input file for the Innodb table into two files decreased load time dramatically (compared to a single input file) but when I split the same input file into four files it made almost no affect.

First, thanks for all the input everyone. It has helped immensely. I ran more test with the suggested ideas. Some of them helped a great deal. Some, not so much, but that could be the hardware I am running the test on. The server is running 32-bit Linux, but has 4 gigs of RAM. All of the RAM is not visible to the OS. I think that is why raising the buffer on Innodb actually slowed it down.

One other note. I didn’t take the time to average the test results of each group run.
All of the following tests were run with the Innodb buffer set to one gig.

importing icengine3_2 with mysqlimport -u root -p –use-threads=2 database3 /data2/database3/*.txt

importing icengine3_2 with mysqlimport -u root -p –use-threads=4 database3 /data2/database3/*.txt

importing icengine3_2 with mysqlimport -u root -p database2 /data2/database3/*.txt

The following tests were run with innodb_flush_log_at_trx_commit = 2 in the my.cnf file.

importing icengine3_2 with mysqlimport -u root -p –use-threads=4 database3 /data2/database3/*.txt

The following tests were run with innodb_flush_log_at_trx_commit = 2 in the my.cnf file.

Conclusions

Obviously the mysqlimport command (which really maps to �load data infile�) is the fastest. In its basic form it speeds up an import by around 29% average over the mysql -u root -p database < database.sql command. The innodb buffer variable change of 1 gigabyte to 2 gigabytes actually hurt the performance. I think this was because the 2 gig buffer size was simply to large for a machine with 3.75 gigs of RAM. Also, this was a 32-bit server (the server really has 4 gigs but doesn�t map it properly).

The �use-threads option in mysqlimport helped quit a bit. In fact, going from one (the default) to two threads decreased load time almost 25%. Increasing it from two threads to four threads doesn�t make much of a difference - about 30 seconds over a 1.3 gig import. One caveat, this option is only available on the mysqlimport binaries from the 5.1.x server series. I downloaded the 32-bit version and just grabbed the single binary for mysqlimport. I copied the old binary to mysqlimport.old in case there was any problems and copied over the new version. It worked as advertised.

Using the innodb_flush_log_at_trx_commit = 2 in the my.cnf options didnt’ really make any significant difference in my setup. Make sure that this is set to zero after done with the importing to make certain your data is safe.

One interesting note. Moving from 4.1 to 5.0 shrinks the size of the database. I expected this somewhat, but was supprised at how much. In the testing case it went from 1468M of data to 1315M. This is a 10.4% reduction in size just from the move. Please note that the 4.1 data which was replicated from had been imported into the server from production data. Because of the import before replication the database was at optimum size.

For our uses the optimal command would be: mysqimport -u root -p –use-threads=4 database3 /data2/database3/*.txt

A few days ago I came across an article about a test comparing Oracle to PostgreSQL. First of all, where was MySQL? The only figure I saw in the article was that PostgreSQL was 12% slower than Oracle.

Then the article talks about how the hardware was “similar” but not the same.

HMMMMMMMMMMMMMMMMMMMMMMMM

Now I am not a testing expert. I didn’t stay in a Holiday Inn Express last night. However, this I know. This test is worthless. It doesn’t prove anything. If you can’t run the test on the same hardware than you can’t rely on the results. I won’t even get it into if one database was tuned and the other wasn’t. I don’t know.
Anyways, just my little rant for the day.

So, drumroll…here are the results of testing:

In the quantitative testing I tested against an internal hard drive (formatted with ext3 and then reiserfs), a partition on the Coraid formatted with ext3 and a partition on the Coraid formatted with reiserfs. I also tested with a failed hard drive in the Coraid (while it was rebuilding).

/dev/sda reiserfs (mirrored)

/dev/sdb1 ext3

/dev/sdb1 reiserfs

/dev/sdb1 reiserfs (noatime,notail)

Coraid w/ext3 filesystem, switch and no jumbo frames

Coraid w/reiserfs filesystem, switch and no jumbo frames

Coraid w/reiserfs filesystem, no switch and jumbo frames

Coraid w/reiserfs filesystem, switch and jumbo frames

Coraid w/ext3 filesystem, switch and jumbo frames

Coraid w/ext3 filesystem, switch, jumbo frames and degraded raid

Coraid w/reiserfs filesystem, switch, jumbo frames and degraded raid

Coraid w/ocfs2 filesystem, switch and jumbo frames

Qualitative Testing Results

For the qualitative testing I loaded up a stock binary install of MySQL 5.0. The database (icengine3_2) was loaded from a dump of a production server. I used mybench to execute a query against the database pulling random records from one of the tables. The number of connections utilized are included below with the summary data for each test run. To establish a baseline I ran the test against the MySQL server pulling data off the interal drives (mirrored reiserfs) and a single drive dedicated only to MySQL data. Then I ran the tests against data loaded onto ext3 and reiserfs Coraid partitions. Nothing else was happening to the the Coraid. This would be similar to a single server attached to the Coraid. Then I ran the test against both the ext3 and reiserfs partitions on the Coraid with a throughput test being performed on the partition not being used to hold the mysql data. As an example: if the ext3 partition was holding the data for the test than the reiserfs partition of the Coraid had a throughput (both read and write) test being performed at the same time. This would more closely simulate what it would be like with multiple servers attached to different partitions of the Coraid. Finally, these tests were repeated while one drive of the Coraid was being rebuilt.

/dev/sda drive w/reiserfs (mirrored)

/dev/sdb1 drive w/reiserfs (noatime, notail)

/dev/sdb1 drive w/ext3

Coraid w/reiserfs filesystem, switch, jumbo frames

Coraid w/ext3 filesystem, switch, jumbo frames

Coraid w/ext3 filesystem, switch, jumbo frames

These tests were run while iozone was running against the reiserfs partition on the Coraid.

Coraid w/ext3 filesystem, switch, jumbo frames

These tests were run while iozone was running against the reiserfs partition on the Coraid. In addition the Coraid had a degraded array.

Conclusions

I think this conclusively proves that the Coraid is capable of handling multiple servers while maintaining reasonable throughput. Even with a degraded drive the actual (read) performance of the MySQL server does not suffer. Heavy write performance to a database would suffer as the write throughput during a rebuild of an array is roughly half of the normal throughput. As for the question about using reiserfs or ext3 as a filesystem the performance numbers are close enough that it would be wise to consider that reiserfs has better functionality under the LVM system that we use on our hard drives. Currently there are six drives (five active) in the Coraid. Increasing the drive count in the coraid will also improve the throughput. According to the numbers released by Coraid it should be a fairly dramatic increase (on the order of 100% faster write performance with a full complement of 14 drives vs the current complement of six drives). Of course increasing the drive count will increase the number of platters and spindles so it would be expected. I also tried bonding two Ethernet ports. This did not increase throughput.

With Ian’s help I did test the Oracle clustering filesystem. At this point it is really to fragile to consider using. In addition, the performance throughput testing that I did perform indicated that it was going to be significantly slower than both ext3 and the reiserfs. While we have to partition off the Coraid and dedicate each specific parition to a server - I think that this is certainly justified for more a more reliable filesystem that gives better performance.

Jumbo frames make a difference. I proved early in the testing that just configuring the switch and the ethernet card for jumbo frames increases raw throughput by around 20 Mbytes a second. NOT ALL ETHERNET CARDS SUPPORT MTUS ABOVE 1500!!! Check with the vendor before purchasing to see if this is supported.

Thanks to both Ian and Justin for their help with LVM, the ocfs and general system crap

I just wrapped up a test to determine if it would be possible to implement a SAN as a backend for our data storage. For those who do not know - SAN stands for Storage Area Network. It is a close relative to a NAS (Network Area Storage) unit. Basically it a fancy RAID unit that you plug into your network instead of directly into the server itself.

I tested a storage unit by Coraid (http://www.coraid.com). The model was an SR1521. It will take up to 14 drives. I actually did the testing with six drives installed. Before I get into some details about the testing itself let me mention a couple of things about Coraid itself. I think Coraid has built a great product. I am not completely certain, but if I recall Coraid is the inventor of the AoE (ATA over Ethernet) protocol. It is a protocol that allows very fast communication between the server and the SAN unit. The AoE protocol and driver are open and the source code for the driver is available for download from the Coraid website. I emailed the company a couple of times during testing with various questions. They responded quickly and were helpful with my problems. From a company perspective I have absolutely no qualms recommending them. When doing any type of benchmarking it is important that you change as little as possible between test. If I was trying to determine what various changes to a my.cnf file might make I would not make four, three, or even two changes to the my.cnf file and run my benchmarking program. I would make one change and run my benchmarking program. Otherwise, how will I know what is happening? Adding 512MB to the Innodb buffer pool might improve performance by 20%. But, adding the general query log option might drop performance 10%. I have a net gain of 10% changing both variables but I would not realize what was really going on under the hood.

I used two primary tools when doing my benchmarking. For basic throughput (both read and write) testing I used the iozone (http://www.iozone.org)program. I used this to run basic write and read tests against an individual hard drive, a software mirrored hard drive, and then partitions formatted with ext3, reiserfs and the oracle clustering file system (yes, I know oracle..laugh) mounted on the Coraid unit. I also used the mybench program (http://jeremy.zawodny.com/mysql/mybench/) of Jeremy Zawodny. This tool will let you take a query and run it against a database many times and with multiple clients. I used it to run test of 10 simultaneous connections through 400 simultaneous connections.

Next time I will talk about the actual test results.