Sponsored By Aspose - File Format APIs for .NET

Aspose are the market leader of .NET APIs for file business formats – natively work with DOCX, XLSX, PPT, PDF, MSG, MPP, images formats and many more!

Sidetracked with MySQL & SQL Server Performance

Here's the short version:

  1. This is only for inserting 500 rows into a 3 column table
  2. InnoDB  really seems screwed on Windows platforms running 5.x – Certainly don't have anything conclusive, but I'd watch out 
  3. For my very simple test, MySQL on Linux blew SQL Server 2005 on windows out of the water.
  4. On windows, ASP.NET's connection to MySQL seems as fast as PHP's

Here's the long version
For a large upcoming project, we've pretty much decided on a .NET application and MySQL database. We'll be using a database cluster for our primary work and smaller database servers to handle secondary tasks (i.e., set up logging as a simpler replication solution).

Before jumping head-first, I wanted to play with the 5.0 beta connector/NET drivers from MySQL. Previously I've only used the ODBC drivers to connect to MySQL and wasn't impressed. I pretty much wanted to make sure it worked like I thought it should (which it does) and that there weren't gonna be any major performance pitfalls. Everything was running locally on my machine – I know, not ideal, but I figured it'd be good enough to catch anything specific I might need to look at more closely (and I was right).

I created a simple table with 3 columns : autoincrement int as the PK, varchar column, an datetime column. My first test was simple – loop 500 times and insert a record ala:

VALUES ("Random Name 34", Now())

I didn't see much of a difference between opening/closing the connection for each insert (thank you connection pooling), keeping the connection open outside of the loop or using a stored procedure. In all cases, the code ran….REALLY REALLY slowly – 13 seconds to be exact!.

I knew there was no way for 500 inserts to take 13 seconds, so I wrote the same code for SqlClient, set up SQL Server 2000 and the test ran in 0.3 seconds (again, all locally).

At this point, my Linux-friendly boss got very interested. I was really worried that the connector might be a POS, so I wrote the same code in PHP and got the same result – good news for .NET, but still baffled.

I finally noticed the table type was InnoDB. I switched it to MyISAM and all the problems went away. All in all, it's safe to say that locally on my windows machine, SQL Server ran a bit faster than MySQL (say 0.28 seconds vs 0.3)

We decided to test this in a linux environment. My boss wrote the same code for our powerful linux development server and got things rolling using PHP. MyISAM was running at a blazing 0.05, InnoDB at 0.2. Instead of being about 35x slower, the InnoDB storage type was only 4 times slower.

We were really interested to see how the .NET / SQL Server solution on the same hardware would compare. Luckily, that was easily possible on our Windows development server. Would we see the same 6x improvement in performance from the better hardware? Sadly not. The .NET / SQL Server code ran at 0.25 seconds…a very marginal increase.

What made MySQL run so fast? Was it the hardware? Why couldn't SQL Server (2005 Express on the dev machine) take advantage of the hardware as well?Was it because it was running on Linux (that certainly seems to be the case with InnoDB).

Given the same hardware, it would be nice to get SQL Server running in the 0.0X range like MySQL. Even if this was an accurate test (you know, for all I know it's 1 checkbox somewhere in SQL Server and zooom), each database will handle different tasks differently. Tomorrow we'll try connecting .NET with MySQL on the linux box and hopefully get the same results. So far so good for the Connector/NET :)

 

This entry was posted in Grab a coffee before reading. Bookmark the permalink. Follow any comments here with the RSS feed for this post.

9 Responses to Sidetracked with MySQL & SQL Server Performance

  1. karl says:

    We used a mix depending on the purpose and usage of the table. You’ll need to look carefully at the difference between the two, and possibly test both, but in short, MyISAM isn’t ACID which can be risk in some situations. However, it excels at read performance. MyISAM doesn’t have row-level locking, so for updates it needs to lock the entire table, and thus InnoDB is a better choice in this situation. Again, your mileage will vary and it’ll ultimately depend on what ur doing and what your needs are.

  2. Waleed Eissa says:

    Thanks a lot Karl, one last question (and sorry for taking advantage of your generosity), did you use MyISAM or InnoDB? MyISAM seems to be faster than InnoDB but I hear it can cause serious problems if MySQL crashes.

  3. karl says:

    Waleed:
    It worked great.

    Well, we had some huge problems, but they were MySQL specific, nothing to do with the connector (it’s pretty easy to make MySQL completely suck with normal queries, gotta watch out – crazy locking rules and horrible performance for subselects). All that might be fixed now though, haven’t worked with MySQL in a while.

    I’d have no problems recommending it. The team should just apply the necessary attention to performance. If it’s a relatively low-stress application, no worries. If it’s digg.com, you’ll obviously have to tweak and test every query. This is true regardless of your technology – you might run into some odd behavior with the connector, but no doubt it’ll be something you’ll be able to work around.

  4. Waleed Eissa says:

    Hi Karl, sorry for commenting on an old post but I’m going to use MySQL with ASP.NET and I’m worried about the performance, you said you would try connecting .NET with MySQL on the linux box, I wonder how that went.

    Thanks

  5. karl says:

    I ‘ve GREATLY sped up MS SQL Server by explicetly wrapping my call in a BEGIN TRANS and COMMIT TRANS. For 50 000 inserts, it’s gone from 30 seconds to 2. The MySQL test also benefited from this change, it’s gone from 8 seconds to 4. For those keeping track, that means SQL Server is now running faster (still getting kicked my MyISAM, but whatever).

  6. karl says:

    Damn…our windows dev machine is running Dev Edition x64..so it isn’t an Exress limitation issue.

    Aydende:
    I agree that MyISAM could make for a dangerous choice. We are looking into PostgreSQL, but have existing knowldge of achieving high availability with MySQL, so that’s why we are leaning that way.

    Baz:
    You can use Sql Server Express edition for anything non critical. I’ve deployed a number of sites with it. The only thing I can warn you against is using the InnoDB storage type with MySQL on windows -regardless of what technology you use (php, asp.net, .net). We’ve reproduced the problem independently now. Personally, I think programming against either is quite easy.

  7. Baz L says:

    I actually was thinking about performing the same test. Well minus the Linux portion. I need to do a C# database application and I was wondering what to use for my database server. C# 2005 comes with SQL Server 2005. I haven’t been using this much, but I was wondering whether it would be simpler to use this than a MySQL server.

    My problem lies in the production environment is Windows. So according to these tests, I’ll need to go ASP.NET?

    I’ll perform my own tests and see what’s happening.

  8. I would be wary of using this type of test for speed comparisions.
    MyISAM is NOT an ACID DB engine. Therefor, it can take a lot of shortcuts and be really really fast.
    If you are starting a new project, I would recommend PostgresSQL

  9. Brian Nesbitt says:

    The Linux friendly boss has some thoughts to help the lagging side.

    The hardware being tested on is exactly the same for the linux machine vs. the windows machine. However, the CPU’s are P4 D’s… ie. dual core. Since we were using SQL Server 2005 Express which seems to have a limit of 1 cpu and 1 GIG of RAM that might be contributing to the lack of performance increase. Might be something to add to the list for tomorrow !