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.

Monday, May 12, 2008

How I found out that mixed mode is cool

We ship a DDEX provider with Connector/Net.  This provider plugs into Visual Studio and integrates into server explorer allowing a user to create data connections to MySQL from within the IDE.  This integration is handled, in large part, by a lengthy series of registry entries.  Until 5.2.2, these entries were made by my installer which is written in WiX.  Having the registry changes made in the installer has two problems.  First, I often need to register the provider during debugging and I don't want to do a full install of the product so I end up hand editing a registry file and manually merging that file.  This a awkward at best.  Second I plan to ship a stand-alone configuration utility in 5.3 that will allow the user to configure which installed version of Connector/Net should be used for VS integration. Currently you can only have one instance installed at a time.

My approach to solving this was to write an installer class that made the registry changes for me and just use installutil to register the assembly.  I was already doing this with my core and web assemblies so this is nothing new.  The problem is that installutil will scan the assembly and all referenced assemblies for types looking for installer classes.  This fails when some of the Microsoft assemblies are scanned.  After much effort I gave up and decided to write my own installutil that I would ship with my installer. 

I had no trouble creating this application and then set about executing it from within my installer.  I attempted to use the CAQuietExec custom action available with WiX v3 but just couldn't make it work right.  So I gave up and decided to write my own execute custom action.

So I cracked open Visual Studio 2008 and read a couple of blogs about custom action writing.  One of them mentioned mixing managed and unmanaged code, the unmanaged code being necessary for the proper DLL exports.  I decided I had to see this work.

Within minutes I had a DLL project setup with the /clr option and had hacked out the following code:

   1: extern "C" __declspec(dllexport) UINT InstallAssembly(MSIHANDLE hMSI)


   2: {


   3:     System::Windows::Forms::MessageBox::Show("boo");


   4:     TCHAR name[261]={0};


   5:     DWORD len=261;


   6:  


   7:     UINT result = ::MsiGetProperty(hMSI, TEXT("CustomActionData"), name, &len);


   8:     InstallVSAssembly(name, true);


   9:     return ERROR_SUCCESS;


  10: }


  11:  


  12: bool InstallVSAssembly(char *assemblyName)


  13: {


  14:     String^ str = gcnew String(assemblyName);


  15:     bool uninstalling = false;


  16:     IDictionary mySavedState = new Hashtable();


  17:     int arg = 0;


  18:  


  19:     InstallContext context = new InstallContext();


  20:     while (arg < args.Length)


  21:     {


  22:         string[] parts = args[arg++].Split('=');


  23:         context.Parameters.Add(parts[0], parts[1]);


  24:     }


  25:  


  26:     Installer installer = null;


  27:     try


  28:     {


  29:         Assembly assem = Assembly.LoadFrom(file);


  30:         installer = (Installer)assem.CreateInstance("MySql.Data.VisualStudio.MyInstaller");


  31:         if (installer == null)


  32:         {


  33:             Console.WriteLine("Unable to find an installer in that assembly.");


  34:             return;


  35:         }


  36:         installer.Context = context;


  37:         if (uninstalling)


  38:             installer.Uninstall(mySavedState);


  39:         else


  40:             installer.Install(mySavedState);


  41:     }


  42:     catch (Exception e)


  43:     {


  44:         Console.WriteLine(e.Message);


  45:     }


  46:     return true;


47: }





Yes, that is managed code and unmanaged code *in the same function*!  Now this code might not compile as it is not what I wound up using and I just grabbed it out of an old folder but you get the idea (and yes I did test a version of this so I know the concept works).  I didn't use this approach because the /clr switch requires the dynamic CRT and I didn't feel like bundling that up.



And, in case you were wondering about the "boo" messagebox on line 3, that is my debugging trap.  You run the installer until that pops up, attach to the proper msiexec using Visual Studio, set a break point, and go.  Yup, that's cool.

MySQL Connector/Net 5.1.6 has been released

Hi,
MySQL Connector/Net 5.1.6 a new version of the all-managed .NET driver for MySQL has been released. This is a minor release involving mainly bug fixes.
Version 5.1.6 works with all versions of MySQL including MySQL-4.1,  MySQL-5.0, MySQL-5.1 beta or the MySQL-6.0 Falcon "Preview".


It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/5.1.html] and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.)


Bugs fixed

  • Fixed problem where parameters lists were not showing when you tried to alter a routine in server explorer.  (bug #34359)
  • Fixed a problem in procedure cache where it was possible to get into a race condition and cause a memory leak (bug #34338)
  • Fixed problem where attempting to use an isolation level other than the default with a transaction scope would use the default instead (bug #34448)
  • Fixed problem that causes the TableAdapter wizard to only generate insert statements.  The problem was that our code to retrieve index columns was broken. (bug #31338)
  • Fixed problem with connections staying open after being used with SqlDataSource.  The problem was that we were not returning an enumerator for our reader with the  closeReader option set to true when we were supposed to.  (Bug #34460)
  • Fixed problem where the bit data type would continue to return null values once it saw a null value in a previous row (bug #36313)     
  • Fixed problem with MembershipUser.GetPassword where attempting to retrieve a  password on a user where password Q&A is not required would throw an exception (bug #36159)     
  • Fixed a problem with MembershipUser.GetNumberOfUsersOnline.  It actually works now  :) (bug #36157)
  • Fixed documentation that still stated that setting port to -1 was necessary for a named pipe connection (bug #35356)     
  • Fixed data type processing so that geometry fields are returned as binary.  (bug #36081)     
  • Fixed problem that kept our provider from showing up in the provider list when configuring a new connection from a SqlDataSource     
  • Fixed problem where setting the ConnectionString property of MySqlConnection to null would throw an exception (bug #35619)

Enjoy and thanks for the support!

MySQL Connector/Net 5.2.2 beta has been released

Hi,
MySQL Connector/Net 5.2.2, a new version of the all-managed .NET driver for MySQL has been released.  This release is a beta and may contain bugs with some of them possibly being severe.  You are strongly urged to not use this release in production environments.  This release is intended only for testing and feedback purposes.


Version 5.2.2 works with all versions of MySQL including MySQL-4.1,  MySQL-5.0, MySQL-5.1 beta or the MySQL-6.0 Falcon "Preview".


It is now available in source and binary form from [http://dev.mysql.com/downloads/connector/net/5.2.html] and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.)


Features or behavior changes

  • Added support for using the new PARAMETERS I_S view when running against a 6.0 server
  • Implemented interactive session connection string option 
  • The procedure parameters schema collection has been altered to match what is coming with MySQL 6.0.  Some fields have been removed and others combined.  Please review your application for incompatibilities.


Bugs fixed

  • Fixed profile provider that would throw an exception if you were updating a profile that already existed.
  • Fixed problem where new parameter code prevented stored procedures from being altered in Visual Studio (bug #34940)
  • Fixed problem where the TableAdapter wizard was no longer able to generate commands using stored procedures because of our change to using @ instead of ? (bug #34941)
  • Fixed problem in datagrid code related to creating a new table.  This problem may have been introduced with .NET 2.0 SP1.  
  • Fixed guid type so that a null value is still returned as guid type (bug #35041)
  • Fixed bug with the membership provider where the min non alpha numeric option was not working correctly.   
  • Fixed bug where calling GetPassword on a membership user when the password answer is null would cause an exception (bug #35332)
  • Fixed bug where retrieving passwords that are encrypted was not returning proper passwords (bug #35336)   
  • Fixed problem with profile provider where properties that were specified without a given type could not be retrieved properly (bug #36000) 
  • Removed some unnecessary locking from the pool manager and also reworked the pooling code to not use a semaphore (bug #34001)


Enjoy and thanks for the support!