MySQL Bugs: #482: ODBC and MySQL v4.1.0 on Windows gives ultraslow performance

Bug #482	ODBC and MySQL v4.1.0 on Windows gives ultraslow performance
Submitted:	23 May 2003 6:45	Modified:	8 Jul 2003 0:34
Reporter:	Joeri Willems	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / ODBC	Severity:	S1 (Critical)
Version:	4.1.0	OS:	Windows (Windows XP/2000)
Assigned to:	Bugs System	CPU Architecture:	Any

Description:
Case:

Visual C++ Program
Client PC: WinXP, Win98
Server PCs: WinXP, Win2000
Connector class: CRecordset derived class 
Connector: ODBC MyODBC 3.51

Problem:
Suddenly after i installed the new version 4.1.0 (upgraded from 4.0.12) to my server and ran my program, i noticed serious long load times for my program. After careful investigation i noticed that it takes about 0.3s for every Requery(), Update() Open() i perform (basic actions on the CRecordset class)etc, and an amazing 18s (!) to do the following query on a 1200-row table:
WHERE monitors.Qualesth > '0' AND monitors.Qualtech > '0' AND monitors.Status = '1' AND monitors.modelid IN ( SELECT ID FROM models WHERE 'H530 H530VI' LIKE CONCAT('%',Name,'%') )"  
This problem only occurs if the ODBC connection is to another host, in other words, testing it on the same computer(ODBC going to local ip or 'localhost') will NOT yield this problem. I'm still uncertain why this problem has occurred...
either - because i upgraded MyODBC to MyODBC 3.51.06
       - because i upgraded MySQL to MySQL 4.1
       - because some drivers were updated in any previous process.
I tried reinstalling MyODBC 3.51.04 but that doesnt help
And i also tried testing with the C++ API connector and that doesnt seem to give any overhead (or at least <0.1s for a simple query)(more testing needed)
Anyhow, it seems a serious problem and i've seen at least 1 other person here with exactly the same symptoms.
Regards to the crew, i hope this can be fixed!
 

How to repeat:
Install MySQL 4.1.0 on Server A. (windows)
Install MyODBC 3.51.06 on Client B. (windows)
Add a db to the mysql server with a table with 1000+ entries.
Create a small windows application that queries this table (preferably +1000 rows), by ODBC and make it a difficult query (WHERE x ABND y AND z AND ...)!Run the application from Client B.
Count the seconds.
Even a simple query will take about 0.3s
So you can also just write a small program that does 20 requery's on 1 table and check the total speed.

Suggested fix:
I have no clue why. Sorry :(

Okay, i just the same query but build with the mysql C++ connector (version 1.7.1 visual C++ , that was downloadable from the mysql website) and it unfortunately yielded the same results.
It seems it has nothing to do with the connector, but with the new database version.
:(
I believe it's a very urgent problem! Hope you guys can fix it, and reply on this soon, as i need to know what to do on monday (either wait an accepteable time for a fix or re-code my program without subqueries and use v4.0.12)

Would it be possible to get the dump of the 1200-row table (or other tables the behaviour appears 
on) to try to reproduce the bug? You can e-mail it to me at tfr@mysql.com, thanks (either the 
dump or a URL to retrieve it over the web, if it's over a couple of megs).

Test application submitted and bug has been confirmed. I'd like to thank the MySQL crew for the quick response and hope it can find this hairy bug soon and kill it ! :)

Joeri: 
 
I did a quick test for 3.51 w.r.t. 4.0 and 4.1; and couldn't able to see any major differences 
even though 4.1 is bit slower. But Connector/ODBC 3.52 acted fast enough with 4.1 which is 
almost ready for the alpha release. 
 
Could you please let me know how do I reproduce it ? or your table dump will be great.. I 
beleive you don't have any loggers enabled. 
 
Thanks, Venu

www.discopress.com/bugexample.zip

if you got visual c++ you can recreate the bug with this zip file
read readme inside for more details.

hope this helps.

by the way the bug seems to depend on TCP/IP

What is your models table schema ? 

Thanks, Venu

Joeri,

Thanks sending the good example to debug. Unfortunatly am not seeing the long delay when connected to localhost and/or with ip address when talking to 4.1. I even tried by building the Connector/ODBC 3.51.06 with 4.1 client library, but still it executes fast enough.

Here is what I get as an output:

Query:
Records Found: 5

ID               ModelID   Serial  Status Date

304       17      YEPF002235                    1   2001-08-06
305       17      YEPF513721                    1   2002-01-15
306       17      YEPF596515                    1   2002-12-22
307       17      YEPF596520                    1   2002-12-22
702       19      YECU432260                    1   2001-01-22

Note that I tested it on Windows XP professional as client and server and another case the 4.1 is running from Linux box.

There are couple of options that might be causing a long delay in your case.

1. Your network is slow:
   (Doesn't really seems to be a cause as MySQL++ runs fast enough)

2. ODBC Driver Manager and/or Connector/ODBC traces got enabled or debug version of the ODBC driver is used:
   Ensure that all ODBC trace options are disabled by following the instructions from below, incase if they were enabled accidentally:
   http://www.mysql.com/products/myodbc/faq_7.html#performance

Let me know your findings...
Thanks, Venu

It is not the ODBC driver that's faulty, as in my enclosed example i actually tried using the Connector/C++ API that resulted in the same results. The output you got is correct, but the timeframe is strange.
You do use two different machines for your test, right?
I added some timing to the example, just download 
www.discopress.com/bugexample2.zip
and try it out (the database remains same as the one last mailed)
i'm now going to the company to verify this and see exactly how long he does over the query.

Joeri,

I still don't have luck to reproduce this with your latest sample too (Here is the output and process time) for both localhost and ip_address.

--------------------------------------------------------
using ip
--------------------------------------------------------

Please give the server IP address (l=localhost):
172.24.1.27
 now performing
"select * from monitors WHERE monitors.Qualesth > '0' AND monitors.Qualtech > '0
' AND monitors.Status = '1' AND monitors.modelid IN ( SELECT ID FROM models WHER
E 'C551 151E' LIKE CONCAT('%',Name,'%') )"
 on 172.24.1.27
Query:
Records Found: 5

ID               ModelID   Serial  Status Date

304       17      YEPF002235                    1   2001-08-06
305       17      YEPF513721                    1   2002-01-15
306       17      YEPF596515                    1   2002-12-22
307       17      YEPF596520                    1   2002-12-22
702       19      YECU432260                    1   2001-01-22

Statistics:

Time to make connection   : 0.061 seconds
Time to store the query   : 0.000 seconds
Time to execute the query : 6.158 seconds
Time to output results    : 0.020 seconds
------------------------------------------
Total process time        : 6.239 seconds

Press any key to continue

--------------------------------------------------------
using localhost
--------------------------------------------------------
Please give the server IP address (l=localhost):
localhost
 now performing
"select * from monitors WHERE monitors.Qualesth > '0' AND monitors.Qualtech > '0
' AND monitors.Status = '1' AND monitors.modelid IN ( SELECT ID FROM models WHER
E 'C551 151E' LIKE CONCAT('%',Name,'%') )"
 on localhost
Query:
Records Found: 5

ID               ModelID   Serial  Status Date

304       17      YEPF002235                    1   2001-08-06
305       17      YEPF513721                    1   2002-01-15
306       17      YEPF596515                    1   2002-12-22
307       17      YEPF596520                    1   2002-12-22
702       19      YECU432260                    1   2001-01-22

Statistics:

Time to make connection   : 0.050 seconds
Time to store the query   : 0.000 seconds
Time to execute the query : 6.209 seconds
Time to output results    : 0.030 seconds
------------------------------------------
Total process time        : 6.289 seconds

So, you are see hardly any difference (note that are debug build version of your application).

In a side note, I noticed that you are using some old depricated functions still like mysql_connect. Better to avoid them as 4.1 will not have these in the normal distribution.

Could it be possible for you to test it on some other system and see if there us any problems with the current system ?

Thanks 
Venu

Are you performing the ip query from a computer A to a computer B over the network? A cannot be the same computer as B, and i assume you aren't doing that. Here it takes 20 ms for connecting on localhost and 120ms for connecting through the network. The bug doesnt get displayed if you just type the ip address of the server on the server. I can indeed try to run the example on my old work's network. Will go there on monday morning and see if it has same bug there.

The schema doesn't actually matter.  We can cross that off the list with this example (with the permission of everyone that has the problem) : SELECT (1+1);

Regardless of the schema type, this should take almost the minimal time.  Yet, it is still subject to the same delay.

I notice the delay only with 4.1 of the server, and only when connecting via (FROM) a Windows machine.  It's not ODBC because I use SQLYog and MySQLcc (and custom apps that use the client DLL).  And it only occurs from outside the machine over the network.

With version 4.0 of the server, the problem disappears.  Was the protocol changed?

thanks timothy. This proves the problem is very real and easy to trigger. I really hope Venu can now succesfully 'see' the problem and start finding a way to kill this bug. MySQL is completely useless with this bug on windows machines, so i believe it is of very high importance to get a solution as fast as possible.

Hi !!

Now, we could able to reproduce the problem, and people are working on it to trace the things. Once the problem is found and resolved, the status will be posted.

Thanks for the bug report and nice samples.
Venu

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

I just fixed this bug now.  
 
It was introduced accidently by Venu when he tried to optimize send_fields. 
 
The bug was that MySQL 4.1.0 did a flush on the TCP/IP channel after sending 
the field information, which apparently causes a bug in the Windows 
TCP/IP  to appear. 
 
Here is a patch for this: 
 
[(/home/my/mysql-4.1/sql)] bk diffs -c protocol.cc 
===== protocol.cc 1.62 vs edited ===== 
*** /tmp/protocol.cc-1.62-13133 Sat Jun 14 11:37:39 2003 
--- edited/protocol.cc  Tue Jun 24 20:01:25 2003 
*************** 
*** 572,578 **** 
  #endif 
    } 
 
!   send_eof(thd); 
    DBUG_RETURN(prepare_for_send(list)); 
 
  err: 
--- 572,578 ---- 
  #endif 
    } 
 
!   send_eof(thd, 1); 
    DBUG_RETURN(prepare_for_send(list)); 
 
  err: 
 
This should fix the problem with slow response for simple queries.  I don't however know if this 
also affects other things, like the 18 second query as when I tested this in an 
windows-to-windows environment I only got a slow response (0.20 seconds) for queries that 
returned less than about 1500 bytes). 
 
Thanks 
Monty

Thank you for your bug report. This issue has been fixed in the latest
development tree for that product. You can find more information about
accessing our development trees at 
    http://www.mysql.com/doc/en/Installing_source_tree.html

Is it possible that this very bug has surfaced again in 4.1.7 and 4.1.8?

Hi,

the bug description comes close to what I experience with MySQL 4.1.8 on Win 2000 and a remote client (4.1.8) on Win NT. Compared to MySQL 4.0.20, the version I used before, establishing a connection is approximately 10 times slower. 
Once the connection is established, I cannot notice any serious response time differences between the two. Local communication is not affected.

Thomas

hi i got the same problem but using Redhat Linux 4.0ES and 9 using MySQL 4.17 and 4.1.9
whenever i connect using MyODBC (visual basic application or query browser or phpmyadmin.) it's taking too long for a simple query, but when i installed it in my windows 4.1.7 (the query is fast).. got any idea what's taking it too long on a linux/mysql installed server? and why fast in windows xp/Mysql ?