A few years ago, I expressed on the midrange-l mailing list some frustration I was having regarding database performance on an AS/400. I recall saying something like …
MySQL vastly outperforms the AS/400.
For the tests I was doing, it appeared that MySQL was indeed faster. My tests weren’t very rigorous, however. My posts caught the attention of Joe Pluta who challenged me to write a more rigorous test comparing MySQL and the AS/400. He claimed that the AS/400 would beat MySQL every time.
Joe, you were right.
At that time I began to write some comparison tests, but other projects prevented me from ever finishing them and collecting sufficient data turned out to be troublesome at the time. Now I am in the middle of a rather large project that involves both MySQL and the IBM i. Since I had large, identical data sets for both databases I decided to return to my earlier assertions and do some performance comparisons.
First, a quick note about the configuration of each test is in order. I use two testing environments: a linux server with two AMD dual core Opterons (total of four cores), 2GB of RAM, connected to a somewhat aging iSeries model 820 via a 10/100Mb switch and the JTOpen toolkit. The MySQL environment is my development laptop which is also a dual core AMD CPU and similar RAM. In both cases I’m using Apache Tomcat as the application server and database access is done via JDBC. The critical difference is that MySQL is run on the same machine as Tomcat, whereas access to the iSeries is done over the LAN.
I loaded a large data set on the iSeries to do some testing of my application. I discovered that it was taking longer to run than was acceptable. I blamed this on the remote database, thinking that because the database access was running over the LAN it was causing a slowdown. So I was quite keen to load the exact same data into MySQL on my laptop and do the same test. A few CPYTOIMPF commands later and I was ready to go.
To my utter astonishment my application ran slower in MySQL! Despite not having the delays inherent to a remote database MySQL could not keep up. My application does a very large number of queries and the iSeries was beating MySQL, even though it had to do so over the LAN! Now of course this test isn’t perfect. The two environments run on different hardware. But I fully expected MySQL running on the same machine as the application server to be faster than the iSeries which was running over a LAN connection. I thought that simply avoiding the network would more than tilt the performance in favor of MySQL.
To quote Mr. Burns, “Joe, I owe you a Coke.”