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 11:40	Modified:	5 Nov 2004 17:36
Reporter:	Marwan Totah
Status:	Closed
Category:	Connector/J	Severity:	S3 (Non-critical)
Version:	3.1.4-beta	OS:	Microsoft Windows (windows/XP)
Assigned to:	Mark Matthews	Target Version:

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.