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:
Category:Connector / J Severity:S3 (Non-critical)
Version:3.1.4-beta OS:Windows (windows/XP)
Assigned to: Mark Matthews CPU Architecture:Any

[1 Nov 2004 10:40] Marwan Totah
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.
[1 Nov 2004 14:31] Mark Matthews
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?
[1 Nov 2004 16:08] Marwan Totah
no prepared stmt's .. they are simple select, I'll write and upload a small test class that demonstrates the case, hopefully tonight.
[1 Nov 2004 19:22] Mark Matthews
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.
[1 Nov 2004 20:53] Marwan Totah
simulator 1

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

[1 Nov 2004 20:58] Marwan Totah
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.
[1 Nov 2004 22:58] Marwan Totah
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.
[5 Nov 2004 16:36] Mark Matthews
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.