MySQL Bugs: #48243: sql insert is slow with connector 6.x and compression enabled

Bug #48243	sql insert is slow with connector 6.x and compression enabled
Submitted:	22 Oct 2009 19:28	Modified:	29 Jul 2010 15:41
Reporter:	Christian Mueller	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / NET	Severity:	S3 (Non-critical)
Version:	6.0.4, 6.1.2, 6.2.0	OS:	Any
Assigned to:	Vladislav Vaintroub	CPU Architecture:	Any

Description:
SQL INSERT is significant slower on connector 6.x compared to 5.x when compression is enabled.

How to repeat:
run the following code and compare the times between 6.x and 5.x

string connectString = "Database=test;Charset=utf8;Compress=true;Convert Zero Datetime=true";
MySqlConnection con = new MySqlConnection( connectString );
con.Open();
MySqlCommand cmd = new MySqlCommand();
cmd.Connection = con;
cmd.CommandText = "DROP TABLE IF EXISTS t1";
cmd.ExecuteNonQuery();
cmd.CommandText =
	@"CREATE TABLE t1 (
		id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
		ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
		dt DATETIME,
		ch VARCHAR(255) CHARACTER SET utf8,
		bn VARBINARY(255),
		tx TEXT CHARACTER SET utf8
	)"
;
cmd.ExecuteNonQuery();
cmd.CommandText = "INSERT INTO t1 (dt,ch,bn,tx) VALUES(?dt,?ch,?bn,?tx)";
cmd.Parameters.Clear();
cmd.Parameters.Add( "?dt", MySqlDbType.DateTime );
cmd.Parameters.Add( "?ch", MySqlDbType.VarChar );
cmd.Parameters.Add( "?bn", MySqlDbType.VarBinary );
cmd.Parameters.Add( "?tx", MySqlDbType.Text );
cmd.Prepare();
for( int i = 1; i <= 100; i++ ) {
	cmd.Parameters[0].Value = DateTime.Now;
	cmd.Parameters[1].Value = i.ToString() + new String( 'X', 200 );
	cmd.Parameters[2].Value = Encoding.ASCII.GetBytes( i.ToString() + new String( 'X', 200 ) );
	cmd.Parameters[3].Value = new String( 'X', 16384 );
	cmd.ExecuteNonQuery();
	Console.WriteLine( "insert row {0}", i );
}

Hi Christian and wow!

Verified just as described. Using same test case, c/NET 6 trunk driver is ~10 times slower than 5.2 one. Removing compression yields, in terms of speed, expected results (aprox. 20 times faster). Tests are done using freshly built debug and release libraries.

Test compr. 5.2: 00:00:00.6230070
Test compr. 5.2: 00:00:00.5692995
Test compr. 5.2: 00:00:00.5673465
Test compr. 5.2: 00:00:00.6034770

Test compr. 6.2: 00:00:07.0210350
Test compr. 6.2: 00:00:06.9790455
Test compr. 6.2: 00:00:06.9731865
Test compr. 6.2: 00:00:06.9790455

I can't reproduce this with the latest releases (6.0.5, 6.1.3, or 6.2.1).  If it can be reproduced with a latest release please reopen.

Sorry, but the problem still exists. I reproduced it with .Net Connector 6.2.2 and Mono 2.6.1

with compression:

real    0m5.569s
user    0m1.088s
sys     0m0.124s

without compression:

real    0m0.790s
user    0m0.664s
sys     0m0.032s

I will attach the complete test script.

compression speed test

Attachment: mysql.cs (text/plain), 1.35 KiB.

second test with connector version 5.2.7...

with compression:

real    0m0.995s
user    0m0.804s
sys     0m0.036s

without compression:

real    0m0.773s
user    0m0.620s
sys     0m0.036s

I could not find any slowdown related to compression, however I tried out the example in VS2010 Premium. It has a reasonable profiler, so I gave this example a try (increasing loop count to 10000, so it runs for couple of seconds)

The profiler shows 50% time spend in MySqlHelper.EscapeString() of which 37% goes to String.IndexOf(char). Looking more closely to this example, it looks like connector does not choose the best in terms of performance algorithm and does a bit more work than needed to escape strings.

First, there is no need to use StringBuilder and append, if none of the characters must be escaped. The function can just return the original string.

Second, the loop over all characters in inputString  combined with search for character in stringOfBackslashChars plus search for the character in stringOfQuoteChars has complexity 

O(inputString.Length * (stringOfBackSlashChars.Length + stringOfQuoteChars.Length))

, and this is going to slow down  with long stringOfBackSlashChars.

Better performance can be achieved by having a lookup table (simple array) that stores character property (quote, backslash, normal), and then we can access the properties instantly, saving stringOfFooChars.indexOf(c).

I will attach a patch for it against current trunk, which gives ~40% performance improvement for this case (yet it sacrifies 64K memory for the static lookup table)

But as I said, I've found nothing related to compression in trunk, the difference compression/non-compression for me is negligible.

patch to speed up MySqlHelper.EscapeString()

Attachment: perf.diff (application/octet-stream, text), 3.54 KiB.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114474

826 Vladislav Vaintroub	2010-07-27
      Bug#48243: 
      Improve performance of EscapeString() and QuoteString().
      Instead of 2 time consuming String.IndexOf done for each
      character in string, use lookup table to find out whether
      character needs to be quoted.
      
      Reduce string operations in this functions, check and  if 
      string does not need to be escaped, return the original.

A patch for this bug has been committed. After review, it may
be pushed to the relevant source trees for release in the next
version. You can access the patch from:

  http://lists.mysql.com/commits/114536

828 Vladislav Vaintroub	2010-07-28
      Bug #48243 : improve write performance of compressed stream.
      
      Reduce number of network writes in CompressAndSendCache() 
      from eight (a single write operation for  each  byte  in packet 
      length prefix plus single write for data itself) to one (send packet
      length prefix and data in a single buffer).
      
      This fixes was a regression from  5.2 (in 5.2, buffered output 
      was used, and writing bytes individually was not a problem)

Queued to 6.0.7, 6.1.5, 6.2.4, 6.3.4

An entry has been added to the 6.0.7, 6.1.5, 6.2.4, 6.3.4 changelogs:

The INSERT command was significantly slower with MySQL Connector/NET 6.x compared to 5.x, when compression was enabled.