MySQL Bugs: #38755: Fail-over connection performance is sluggish

Bug #38755	Fail-over connection performance is sluggish
Submitted:	12 Aug 2008 19:00	Modified:	12 Sep 2008 15:19
Reporter:	Nam Nguyen	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	Connector / J	Severity:	S5 (Performance)
Version:	mysql-connector-java-5.1.6-bin	OS:	Solaris
Assigned to:		CPU Architecture:	Any

Description:
Performance of fail-over connection is much lower (20 %) than that of direct connection to the same MySQL instance.

How to repeat:
1. use a GlassFish connection pool with a fail-over URL like:
jdbc:mysql://localhost,dbs03/test to run some performance test that would populate the pool with connections to localhost.
2. shutdown the mysql on localhost to trigger the fail-over connections
3. rerun the performance test and see the fail-over to dbs03 did happen, but the performance is degraded to 20 % compared to the performance of direct connection to dbs03 (meaning a connection pool with just jdbc:mysql://dbs03/test).

Many thanks for writing a bug report.

Besides the Java connector which version of MySQL did you use?

We use:
5.0.56-enterprise-gpl-log MySQL Enterprise Server (GPL)

Hi Nam and thanks for your report.

This is a known problem described several times in BugsDB. Some of the discussions you can find in Bug#33861, Bug#32877, Bug#31053 and many more.

Also, if you're using a connection pool that _doesn't_ use the ConnectionPoolDataSource API to create connections that have lifecycle callbacks, then it is the _pool's_ responsibility to cleanup "logical" pooled connections when they're returned to the pool. Otherwise there is no way for a JDBC driver to tell that a connection is being pooled "above" it, and thus
no event/method call where one can hook in to determine that you've been "logically" closed and returned to the pool.

Further more, c/J works in such way that each connection evaluates failover by itself (we are working on this right now).

Until such time this code is rewritten, please make use of several timeout values available in c/J configuration.

I have to reopen this bug.

We do use ConnectionPooledDataSource which validate and discard bad connections every 60 seconds.  But the c/J connection manages the fail-over itself, so the pool would not discard any bad connection (there supposed to be no bad pooled-connection as far as the pool concerned).  So I think that there should be something happening within the c/J connection that seems to retry the bad server before actually sending request to the good server?

Nam, you are not reading what I said:
--<quote>--
Further more, c/J works in such way that each connection evaluates failover by itself (we
are working on this right now).

Until such time this code is rewritten, please make use of several timeout values
available in c/J configuration.
--<quote>--

So, until such time code is rewritten and patch pushed please use other workarounds.

This is !Bg as the behaviour is intentional, although not very good performance-wise.
Also, the patch for this behaviour is already in testing in version 5.1.7.

Thanks for explaining.  I did see that, but had doubt that the behavior is "Not a Bug" due to explanations in the listed related bugs.