MySQL Bugs: #6359: driver 3.1.4-beta is very slow

Bug #6359	driver 3.1.4-beta is very slow
Submitted:	1 Nov 2004 10:40	Modified:	5 Nov 2004 16:36
Reporter:	Marwan Totah	Email Updates:
Status:	Closed	Impact on me:	None
Category:	Connector / J	Severity:	S3 (Non-critical)
Version:	3.1.4-beta	OS:	Windows (windows/XP)
Assigned to:	Mark Matthews	CPU Architecture:	Any

Description:
Ran the same test on driver:

version 3.0.15  : test time 1,351 seconds
verson 3.1.4-beta : test time 7,875 seconds  5.8 times slower

the test environment:

- DB Server : 4.1.7 standard on Linux.
- client machine: P4, 3GHz, WinXP on 100M LAN.
- java version: 1.5 release
- driver properties: none.

the test consists of about 1 million select on a table of 550k records, a combination of the following selects:

using a secondary numeric key returning about 5 rows with the primary field. followed by that many (5) selects using the primary key returning all fields of that row.

How to repeat:
I'll provide more info if needed.

We would need more details, as 3.1.4 consistently tests much faster than 3.0.15 in our performance regression tests.

Are you using prepared statements? If so, does adding 'useServerPrepStmts=false' to your URL give you similar performance to 3.0.x?

no prepared stmt's .. they are simple select, I'll write and upload a small test class that demonstrates the case, hopefully tonight.

From my local testing here, it appears that this is mostly related to our default 'safety' configuration to _not_ use BufferedInputStreams when reading from the server (see http://developer.java.sun.com/developer/bugParade/bugs/4401235.html).

If you add 'useUnbufferedInput=false' to your URL, you should get something much closer in performance (sans the overhead of checking for sqlstates, warnings and multiple result sets that the new driver has to do)...At least it does in my testing.

simulator 1

Attachment: MysqlTest.java (text/plain), 4.13 KiB.

the 'useUnbufferedInput=false'  did not make a diff.

I just uploaded MysqlTest.java that should simulate what my application is doing.

However, its showing similar preformance between both drivers... I am digging deaper to see what else I am doing.... I just thought this file may help you point me in the right direction if you have the time.

more later.

First, Thank you Mark for the excellent work on this driver, and your immediate response to our queries.

Regarding this Issue, I think I figured out what's happening.

In my application, the loadCustomer() method actually implements a cache based on java.lang.ref.SoftReference, and if a customer instance is already in the cache, it will simply return it saving the trip to the DB.

with the new driver, I noticed the cache-miss is 6 fold than the old driver, thus having to fetch it again from the DB.

I gave the application another 64m with -Xmx128m, and got similar performance and cache hit ratio between both drivers.

What's happening: It looks like the 3.1 driver is generating far more objects than the old one, causing the garbage collector to throw away my cache'd SoftReference's ...a run thru a profiler should verify this, but I don't have one on this machine to test...I'll get to it at some point.

Profiling caught a set of objects that were being created and never used to help with tracing. This really filled up the 'eden' heap.

These objects are now only created if profiling/tracing are enabled. The fix is already in the nightly snapshots.