Monday, May 19, 2008

Racing a jet engine


I've spent the last few days working on a support case where a customer was comparing the speed of Connector/Net with an application that uses our native C api.  His test was using a server side prepared statement to insert 3 sets of values in a loop that executed 600,000 times.  The table is a simple 3 column table and the SQL looks like this:



INSERT INTO test VALUES (@p1, @p2, @p3), (@p4, @p5, @p6), (@p7, @p8, @p9)



He was seeing 34,529 records / second using libmysql.dll but only about 24,000 records / second using Connector/Net.  He  understandably wanted to know why there was such a difference and if we could do something about it.  Absolutely!



We solved this problem through a combination of optimizations made to our code and some changes made to the testing application.  I won't say much about the optimizations.  We made some obvious changes like moving a lot more of the prepared statement work to the prepare phase and out of the execute phase.  We also discovered that BitConverter.ToBytes() is a lot slower than our hand-coded methods for reading and writing integer values.  We replaced usages of ArrayList with generic equivalents (it's pretty amazing how much faster generics are actually).



One of the bigger changes we made was to use a second hash for parameter name lookups.  When you are talking about loops with lots of iterations even the smallest inefficiencies can kill you.  Since .NET supports name lookup for parameters we use a hashtable to map parameter names to indices (we made this change when one of our customer was complaining that parameter lookup was slow on one of his commands that had > 30,000 parameters.  Yeah.).  Name lookup should be case-insensitive so we use a case-insensitive comparer with our hashtable.  So the optimization we made this time is to use two hash tables.  The first is case sensitive and is checked first.  If that fails then we check the second and return failure only if both fail.



That's what we did in our code.  We also made some changes to the testing app to make sure the comparison is truly apples to apples.  Let's first look at the loop code in each case.















C API




Connector/Net





while (count < 600000)


{


for (int row = 0; row < 3; ++row)


{


*(p1 + row) = count;


*(p2 + row) = count;


*(p3 + row) = count;


++count;


}


mysql_stmt_execute(stmt);


}






while (count < numberOfRows)


{


for (int row = 0; row < 3; ++row)


{


cmd.Parameters[row].Value = count;


cmd.Parameters[row+3].Value = count;


cmd.Parameters[row+6].Value = count++;


}


cmd.ExecuteNonQuery();


}





Don't get hung up on the output of these being slightly different.  The point here is that the C code is using simple pointer arithmetic during each loop where the C# code is doing array lookup.  The array lookup code is quite a bit slower because the get accessor for the parameter collection does some bounds checking so that a nicer exception will be thrown in the event of an out-of-bounds index.


Another change that was made to the testing app was to disable the command timeout in the C# code.  This is a small point but when the command timeout is greater than zero then a timer is constructed and started during each execution.  In this case that is 600,000 timers!  Again, since the C code doesn't provide any type of command timeout it's not a true apples-to-apples comparison.



So, in a nutshell, benchmarking against libmysql is sort of like racing a jet engine.  Very fast but not a great deal of "safety features".  Still, after many hours with dotTrace the results speak for themselves.     


C app = 35,294 records per second.


C# app = 35,321 records per second.

25 comments:

  1. Reggie, good deal!

    30,000 params?!?!

    ReplyDelete
  2. Great new Reggie,

    What version of the .net connector will these changes be in?

    Pleb,

    ReplyDelete
  3. These changes will appear in 5.3 which we will push out on an accelerated timeframe. 5.2 is too close to GA to include this many changes.

    ReplyDelete
  4. nice one... lolz... hehehe. great codes.

    ReplyDelete
  5. Hello Reggie,

    I've tested the perf of the connection pooling in 5.2.3 and was a little badly surprised. So that everybody can "enjoy" the discussion, I've left a post on the Mysql connector/net forum (see http://forums.mysql.com/read.php?38,224630,224630#msg-224630 ) and was wondering if the change you speak in this post would improve things...

    If possible, I'd be willing to test connector/net v5.3 in our app to see the impact.
    As well, if you want more details on our code, feel free (it's a log analysis application and so we need to write records at a minimum rate of thousands of records by second).

    ReplyDelete
  6. Hello again Reggie,

    Still looking for some perf improvment, I've run a test to compare the insertion of records in batch between MySqlDataAdapter and a custom query I build using a StringBuilder.

    Comparison gives the single INSERT statement with multiple values 2.5 faster that MySqlAdapter... Do I do something wrong? I've left a post with more details on http://forums.mysql.com/read.php?38,224747,224747#msg-224747 .

    Thanks in advance for your time and help!

    Christian

    ReplyDelete
  7. Thanks in advance for your time and help!

    ReplyDelete
  8. pay as you go mobile phonesNovember 19, 2008 at 9:47 AM

    Great comparison reggie.
    This concept comes under advance engineering..50% of Instrumentation Engineer will involve in this concept..
    regards,
    <a href="http://www.best-mobile-contracts.co.uk/pay-as-you-go.aspx">pay as you go mobile phones</a>

    ReplyDelete
  9. Sir i am having a small doubt..
    Is count++ or count+=1 are same??
    If not can you please explain me the difference?
    thank you sir..

    ReplyDelete
  10. I am surprised you willing took that on. But, I am sure it was a interesting experience. My what fun we have these days debugging the code. I know I wouldn't of lasted 20 minutes. That's why I have pay genius' like you to do it for me. You learn what you do best and do it and ask others to help for the thins you dont.

    Good job guys.

    ReplyDelete
  11. Thank you very much..
    If possible, I'd be willing to test connector/net v5.3 in our app to see the impact.
    As well, if you want more details on our code, feel free (it's a log analysis application and so we need to write records at a minimum rate of thousands of records by second).

    ReplyDelete
  12. Hello again Reggie,

    Still looking for some perf improvment, I've run a test to compare the insertion of records in batch between MySqlDataAdapter and a custom query I build using a StringBuilder.

    ReplyDelete
  13. Hello..
    This concept is from advanced engineering.These changes will appear in 5.3 which we will push out on an accelerated timeframe. 5.2 is too close to GA to include this many changes
    <a href="http://www.ultimatetruck.com/departments/Tonneau_Covers.aspx">Tonneau Covers</a>

    ReplyDelete
  14. thanks for nice post
    loves it

    ReplyDelete
  15. Culprits of the Bad and Dirty tricks in Busby SEO Test ContestJanuary 11, 2009 at 2:40 PM

    They are racing but Google is still the Top of all.

    ReplyDelete
  16. great post, i always looking for this kind of post.

    ReplyDelete
  17. Good post.

    ReplyDelete
  18. hello sir
    i am having one doubt in connectivity between oracle and vb,when i try to connect the database its asking Service name.how can i find out my service name sir
    Thanks
    findoptimalhealth
    <a href="http://www.findoptimalhealth.com/hawaiian-noni-juice.php">findoptimalhealth</a>

    ReplyDelete
  19. Excellent job. Race that darn jet engine! I'd have to argue with you on some points but all in all very well structured article. Cheers and regards.

    ReplyDelete
  20. I'd be willing to test connector/net v5.3 in our app to see the impact.
    As well, if you want more details on our code, feel free (it's a log analysis application and so we need to write records at a minimum rate of thousands of records by second).

    ReplyDelete
  21. wedding invitation boxesFebruary 18, 2009 at 12:03 AM

    Excellent job. Race that darn jet engine! I'd have to argue with you on some points but all in all very well structured article. Cheers and regards.

    ReplyDelete
  22. Thanks so much for sharing and making us all think a little more about your nice content

    ReplyDelete
  23. Criminal Background CheckApril 15, 2009 at 4:50 PM

    Wow, this article really makes sense especially for helping us for showing some codes and I really appreciate it. Thanks!

    ReplyDelete
  24. Hoping that this will not be the lat post that I could be read written by you. Thanks for this information.

    ReplyDelete
  25. Microgaming CasinosJuly 23, 2009 at 3:34 AM

    Thank you for sharing. This was a very interesting read.

    ReplyDelete