Bug #77220 MyODBC connector is VERY SLOW after 5.1.8
Submitted: 2 Jun 2015 7:56 Modified: 8 Apr 10:26
Reporter: Emmanuel KARTMANN Email Updates:
Status: Verified Impact on me:
None 
Category:Connector / ODBC Severity:S5 (Performance)
Version:after 5.1.8 OS:Microsoft Windows (Windows Server 2008/2012 R2)
Assigned to: CPU Architecture:Any
Tags: ODBC Connector Performance Regression

[2 Jun 2015 7:56] Emmanuel KARTMANN
Description:
We're using MySQL ODBC Connector version 5.1.8 ; we're trying to upgrade to a newer version, but can't, due to serious performance issues.

There's a huge difference for all connectors we tested after version 5.1.8 : opening and closing an ODBC connection to MySQL is at least 15 times slower, and much more when connecting/disconnecting a lot (like in a real web application).

We have a sample test script that connect 100x to a local server and immediately disconnects, using driver 5.1.8 and than the latest 5.3.4 driver. Here's the output:

CSCRIPT //NoLogo myodbc_performance.js
Performance ODBC 5.1.8 : 100xOpen/Close in : 194 ms
Performance ODBC 5.3.4 : 100xOpen/Close in : 101040 ms
Performance Comparison ODBC 5.1 is 520.8247422680413 times faster than ODBC 5.3
!!!!

Can you please advise on how to upgrade ODBC connector without a dramatic performance loss?

Regards,

E.

How to repeat:
Here's the test script:
================================================================
// myodbc_performance.js
// Please provide root password
var strRootPassword="";

// First loop with driver MySQL ODBC 5.1.8 (fast)
var objStart1 = new Date();
for (var n = 1; n <= 100 ; n++)
{
  var objODBC518Connection = WScript.CreateObject("ADODB.Connection");
  objODBC518Connection.Open("Driver={MySQL ODBC 5.1 Driver};Server=localhost;Database=mysql;UID=root;Password="+strRootPassword+";");
  objODBC518Connection.Close();
  objODBC518Connection = null;
  delete objODBC518Connection;
}
var objEnd1 = new Date();
var objDuration1 = objEnd1-objStart1;
WScript.Echo("Performance ODBC 5.1.8 : 100xOpen/Close in : " + objDuration1.toString(10) + " ms");

// Second loop with driver MySQL ODBC 5.3.4 (slow - just like ANY driver after 5.1.8)
var objStart2 = new Date();
for (var n = 1; n <= 100 ; n++)
{
  var objODBC534Connection = WScript.CreateObject("ADODB.Connection");
  objODBC534Connection.Open("Driver={MySQL ODBC 5.3 ANSI Driver};Server=localhost;Database=mysql;UID=root;Password="+strRootPassword+";");
  objODBC534Connection.Close();
  objODBC534Connection = null;
  delete objODBC534Connection;
}
var objEnd2 = new Date();
var objDuration2 = objEnd2 - objStart2;
WScript.Echo("Performance ODBC 5.3.4 : 100xOpen/Close in : " + objDuration2.toString(10) + " ms");

// Compare performance 5.1 to 5.3
WScript.Echo("Performance Comparison ODBC 5.1 is " + (objDuration2 / objDuration1).toString(10) + " times faster than ODBC 5.3 !!!!");
[5 Jun 2015 9:49] Chiranjeevi Battula
Hello Emmanuel KARTMANN,

Thank you for the bug report.
I tried to reproduce the issue at my end using Visual Studio 2013 (C#.Net) with  MySQL Connector / ODBC 5.3.4 and ODBC 5.1.13. but not seeing any issues in performance side.
Could you please list out exact steps you tried out at your end, this would help us to reproduce the issue at our end.

Thanks,
Chiranjeevi.
[5 Jun 2015 9:49] Chiranjeevi Battula
screenshot

Attachment: 77220.PNG (image/png, text), 63.09 KiB.

[5 Jun 2015 13:07] Emmanuel KARTMANN
Sample test file (need to have MyODBC 5.1.8 AND MyODBC 5.3.4 installed)

Attachment: myodbc_performance.js.zip (application/x-zip-compressed, text), 677 bytes.

[5 Jun 2015 13:09] Emmanuel KARTMANN
I posted my sample test file (it's JScript, not .NET code). You MUST have MyODBC connecter version 5.1.8 AND version 5.3.4 installed to use it : the performance issue is visible when comparing connections delays in versions AFTER 5.1.8 (like 5.3.4). If you use ANY version after 5.1.8, it's slow - but you can't figure it out.
[10 Jun 2015 12:23] Chiranjeevi Battula
Hello Emmanuel KARTMANN,

Thank you for your feedback.
Could you please enable "Connection Pooling" for the Connector / ODBC driver 5.3.4, in fact the default pooling could have been enabled for the driver 5.1 and disabled for 5.3.

Thanks,
Chiranjeevi.
[10 Jun 2015 14:05] Emmanuel KARTMANN
It doesn't change anything - with connection pooling enabled, 5.3.4 is still very slow. Please note that we noticed the performance issue on a real life application that doesn't connect a hundred times to the MySQL server, but since the connection time is at least x100, it's easy to notice it.

Here's the output of the test script with Connection Pooling:
  Performance ODBC 5.1.8 : 100xOpen/Close in : 255 ms
  Performance ODBC ANSI 5.3.4 : 100xOpen/Close in : 101972 ms
  Performance ODBC Unicode 5.3.4 : 100xOpen/Close in : 101971 ms
[15 Jun 2015 15:07] Lawrin Novitsky
Hello,

This bug report can be simplified down to bug against libmysql/Connector/C.

If you take libmysql from 5.6 and try to connect to 5.5 server, you will observe the same abnormal performance drop on the testcase as simple as

  for (i= 0; i < NUMBER_OF_TEST_LOOPS; ++i)
  {
    mysql= mysql_init(NULL);
    if (!mysql_real_connect(mysql, "localhost", "root", "", "test", 3306, NULL, 0))
    {
      printf("Could not connect: %s\n", mysql_error(mysql));
      exit(1);
    }
    mysql_close(mysql);
  }

Best regards,
Lawrin
[17 Aug 2015 19:36] Edwin van Putten
At some point they added IPv6 (disclaimer: not sure if this is starting from 5.1.8 though!).

When DNS succeeds for the AAAA lookup, it will attempt a TCPv6 connection (twice in my case). When that fails, the connection falls back to normal TCP.

Meanwhile a lot of time has been wasted waiting...
[29 Jan 2016 7:19] Chiranjeevi Battula
Hello Emmanuel KARTMANN,

Thank you for your feedback and test case.
Verified this behavior on MySQL Connector / ODBC 5.1.8 and 5.3.4 version.

Thanks,
Chiranjeevi.
[29 Jan 2016 7:20] Chiranjeevi Battula
Test results:

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.3306690 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1745222 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:00.7825994 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1965249 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:00.9071152 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1690214 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:00.9261176 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.2185278 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.1816501 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1685214 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.2251556 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1765224 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.2031528 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1750222 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:00.6035766 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1795228 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.2256556 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1660210 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:00.6025765 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1680213 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.1971521 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1745221 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.0566341 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1640209 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.2521590 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1735221 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.1276432 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1950248 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.2431579 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1845234 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.0931388 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1700216 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.2586598 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1735221 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.0731363 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1640208 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.2056531 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1920244 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:00.6840869 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1670212 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.0036274 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1770225 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.2096536 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.2045260 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.2486585 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1705217 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:01.0081281 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1780226 ms

Performance ODBC 5.3.4 : 100xOpen/Close in : 00:00:00.9576216 ms
Performance ODBC 5.1.8 : 100xOpen/Close in : 00:00:00.1655210 ms
[16 Mar 2016 13:51] Zoltán Szalai
Same issue here. MS Access connects to a local MySQL server via ODBC, and it takes 1.2 sec to run a "select 1;" pass-trough query. After creating the connection, speed is normal, the same query finishes in less, than 16 msec. Acess keeps the connection alive, and reuses that connection. 

The adodb.Connection object open method finishes around 1030 ±15 msec. When Access has an open connection with the same connection string, that connection is reused, and the same open method finishes in less, than 16 msec. 

MySQLslap is able to open and close approx. 60 connection per second on the same PC. (i5 cpu)

See also: http://stackoverflow.com/questions/35975670/access-to-mysql-odbc-connection-opens-slowly
[16 Mar 2016 14:33] Zoltán Szalai
I have played a little with the connection string. Server address, username and password have been replaced with "XXX" in this post. 

With this connection string, the connection is opened in ~1030 msec:
AccessConnect = "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=XXX.XXX.XXX.COM;user=XXX;password=XXX;port=3309;DATABASE=movedb2"

With this connection string, the connection is opened in ~32 msec: 
AccessConnect = "DRIVER={MySQL ODBC 5.3 Unicode Driver};SERVER=localhost;user=XXX;password=XXX;port=3309;DATABASE=movedb2"

Once the connection is there, all queries are running at normal speed. So, changing the server name from full adress to "localhost" speeded up things. Unfortunately, I can't use "SERVER=localhost;" in production.
[29 Mar 8:58] Wijnand Hörchner
It has been a long time after the last comment, but...

We are in the process of migrating a VFP database to MySql. Unfortunately, we have to use the ODBC driver for most of the application. Some parts are rewritten in C# and the OLEDB driver can be used.

We first used the newest 8.x version. We noticed that only making the connection takes about 1 second! For a web application that's realy to slow. AFter making a connection, the reset of the commands are just fast.

After we found this bug report, we tried various older drivers.

Version 5.1.x and 5.2.x are fast (about 15ms)
Starting from 5.3.x it's slow (about 1 second)

First we thought it could be caused by the division of ANSI and Unicode drivers, but the 5.2.x has this division also. The other noticeable difference was that for the 5.3.x and later drivers we had to first install Visual Studio (C++) redistributables (2013, 2015).

We are forced to use the older drivers now.
Is there any progress on this issue?
[29 Mar 9:48] Wijnand Hörchner
As a follow-up.

We are unable to use the 5.2.x version in combination with the MySql8 database. Somehow, the data comes back scrambled and unreadable.

The 5.3 driver gets the data correctly.
It seems the 5.3 driver is not as slow as we thought. A connect takes about 30ms instead of the 1 second with the 8.x driver.
[29 Mar 9:54] Emmanuel KARTMANN
We still have the issue and can't upgrade our ODBC connectors (stuck to version 5.1.8). Our strategy is to get rid off ODBC and use another connector (NodeJS related).

From my understanding, the issue is related to IPV6. You may workaround the issue if you use IPV6 addresses in your connections string (e.g. ::1 instead of 12.0.0.01). We couldn't use this workaround as IPV6 is not enabled on our production servers, but maybe you can?

Regards,

E.
[29 Mar 12:27] Wijnand Hörchner
Hi Emmanuel,

Thanks for your suggestion. Unfortunately, using an IPv6 address to connect to the server did not help. It still takes about 1 second. Also binding the database to the IPv6 address did not help.

The latest 5.3.x driver version seems to be unstable. The application just crashes without providing any reason.
So, where are stuck using driver version 8 or swithing to MS Sql Server.
[8 Apr 10:26] Emmanuel KARTMANN
I adapted by batch to measure performance (will attach source code asap), here's the output:

Performance ODBC 5.1.8 : 1000xOpen/Close in : 2457 ms

Performance ODBC 5.3 ANSI : 1000xOpen/Close in : 7802 ms
 * MySQL ODBC 5.3 ANSI is 3.1754171754171754 times slower than MySQL ODBC 5.1.8 !
Performance ODBC 5.3 Unicode : 1000xOpen/Close in : 7734 ms
 * MySQL ODBC 5.3 Unicode is 3.1477411477411476 times slower than MySQL ODBC 5.1.8 !

Performance ODBC ANSI 8.0 : 1000xOpen/Close in : 7438 ms
 * MySQL ODBC 8.0 ANSI is 3.0272690272690274 times slower than MySQL ODBC 5.1.8 !
Performance ODBC 8.0 Unicode : 1000xOpen/Close in : 7597 ms
 * MySQL ODBC 8.0 Unicode is 3.091982091982092 times slower than MySQL ODBC 5.1.8 !

Performance MariaDB ODBC 3.0: 1000xOpen/Close in : 3013 ms
 * MariaDB 3.0 is 1.2262922262922262 times slower than MySQL ODBC 5.1.8 !

So MariaDB driver MAY BE an option (but still slower than the older driver - not sure if we can use it).

Regards,

E.
[8 Apr 10:30] Emmanuel KARTMANN
Batch script to test connection performance with ODBC drivers

Attachment: myodbc_performance.js (application/x-javascript, text), 4.95 KiB.

[8 Apr 11:12] Wijnand Hörchner
In a previous comment I wrote that the latest 5.3.x driver version seemed unstable. The application crashed without providing any reason.

After some searching I found to update the C++ 2013 runtime because of a SetLocale bug in the initial runtime. This update fixed the issue of crashing.
So, we now use the ODBC 5.3.x driver version with success. This driver is fast enough for us.

Thanks for the suggestions.
[9 Apr 9:45] Zoltán Szalai
The bind-address MySQL server parameter might affect connection speed. The new default since 5.6.6 is "bind-address=* ".
The new default seems to have better performance, compared to "bind-address=0.0.0.0". At least here.