LoadBalancingConnectionProxy and /* ping */ select 1
Posted by: Dominic Tootell
Date: March 08, 2013 08:29AM

Hi there,

Having read http://dev.mysql.com/doc/refman/5.5/en/connector-j-usagenotes-j2ee-concepts-connection-pooling.html, the document is a little confusing
when it comes to describing what occurs when a correctly formatted ping is issued and there is more than one slave connection.

The documentation seems to indicate that when a correctly formatted /* ping */ select 1 is issued, all the connections are
validated:

"All of the previous statements will issue a normal SELECT statement and will not be transformed
into the lightweight ping. Further, for load-balanced connections, the statement will be executed
against one connection in the internal pool, rather than validating each underlying physical connection."


The above seems to indicate that if I have more than one slave in the jdbc url, and the correct ping,
when the ping will be sent to all the slave connections. However, this does not seem to be the case, and
the docs isn't too clear on what "each underlying physical connection" actually means.


So to dechiper the above, if you have the following connection (and the ReplicationDriver set) configured:

jdbc:mysql:replication://master,slave1,slave2


You are going to get a LoadBalancedMySQLConnection that load balances over slave1, and slave2. When a connection given to the app, it is given a ReplicationConnection that is pointing at master, and the previous
LoadBalancedMySQLConection.

When the validation query is run, which of the following should be the case?:

1) Should all the connections be sent the Administrator Ping command: master, slave1, and slave2.
2) Should just master and 1 currently active slave connection get the ping


1 would be ideal, 2 is the current implementation it seems.

To validate this, I monitored the network traffic, and noticed that when the ping command is issued
it also goes to one slave connection, not all of them.

The /* ping */ select 1, validation query is ONLY going to be sent to master, and the currently
active slave connection i.e. slave1, if slave1 is the currently active slave connection. This can be
as can be seen below from a trace of network traffic on my local machine. The ping isn't sent to
all the slaves, just one of them.

- master Ping

# 2013-01-09 13:52:04.065269 0:00 1 use ?
# 2013-01-09 13:52:04.065409 0:00 1 select @@session.tx_isolation
# 2013-01-09 13:52:14.066863 0:00 1 administrator command: Ping
# 2013-01-09 13:52:14.068020 0:00 1 select @@session.tx_isolation
# 2013-01-09 13:52:14.069128 0:00 1 use ?
# 2013-01-09 13:52:14.069292 00:00 2 select @@session.tx_isolation

- slave1 Ping

# 2013-01-09 13:52:04.063467 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:04.063661 0:00 1 commit
# 2013-01-09 13:52:14.067116 0:00 1 administrator command: Ping
# 2013-01-09 13:52:14.068570 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:14.068911 0:00 1 commit
# 2013-01-09 13:52:14.070439 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:14.070659 0:00 1 commit
# 2013-01-09 13:52:14.073957 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:14.074149 0:00 1 commit

- slave2 No Ping

# 2013-01-09 13:52:04.058715 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:04.058999 0:00 1 commit
# 2013-01-09 13:52:04.064923 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:04.065104 0:00 1 commit
# 2013-01-09 13:52:14.072204 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:14.072462 0:00 1 commit
# 2013-01-09 13:52:14.076941 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:14.077132 0:00 1 commit
# 2013-01-09 13:52:14.080233 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:14.080439 0:00 1 commit
# 2013-01-09 13:52:14.081736 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:14.081943 0:00 1 commit
# 2013-01-09 13:52:14.083229 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:14.083450 0:00 1 commit
# 2013-01-09 13:52:14.084890 0:00 1 select count(*) from items where ?=?
# 2013-01-09 13:52:14.085093 0:00 1 commit
# 2013-01-09 13:52:14.088043 0:00 1 select count(*) from items where ?=?

(I validated the above against 5.1.23 connector/j today also)

- slave and master

# 2013-03-08 14:02:26.153069 00:00 2 select @@session.tx_isolation
# 2013-03-08 14:02:26.250658 0:00 1 use ?
# 2013-03-08 14:02:26.250845 00:00 2 select @@session.tx_isolation
# 2013-03-08 14:02:26.354493 0:00 1 use ?
# 2013-03-08 14:02:26.354737 0:00 1 select @@session.tx_isolation
# 2013-03-08 14:03:06.355238 00:00 2 administrator command: Ping
# 2013-03-08 14:03:06.355998 0:00 1 select @@session.tx_isolation
# 2013-03-08 14:03:06.481647 0:00 1 select count(*) from items where ?=?
# 2013-03-08 14:03:06.482130 0:00 1 commit
# 2013-03-08 14:03:06.482412 0:00 1 use ?
# 2013-03-08 14:03:06.482628 0:00 1 select @@session.tx_isolation

- slave2 (No Ping)

# 2013-03-08 14:02:26.136844 0:00 1 administrator command: Connect
# 2013-03-08 14:02:26.139105 0:00 1 show variables where variable_name =? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ? or variable_name = ?
# 2013-03-08 14:02:26.140235 0:00 1 select @@session.auto_increment_increment
# 2013-03-08 14:02:26.142008 0:00 1 show collation
# 2013-03-08 14:02:26.151043 0:00 1 set names utf?
# 2013-03-08 14:02:26.151393 0:00 1 set character_set_results = ?
# 2013-03-08 14:02:26.151770 0:00 1 set autocommit=?
# 2013-03-08 14:02:26.249067 0:00 1 select count(*) from items where ?=?
# 2013-03-08 14:02:26.249905 0:00 1 commit
# 2013-03-08 14:02:26.352923 0:00 1 select count(*) from items where ?=?
# 2013-03-08 14:02:26.353696 0:00 1 commit



The problem the above causes is that if an application is using the tomcat JDBC Connection Pool,
with testOnBorrow set to true, and a validationInterval of say 30 seconds. There is potential for
an application to be given back an idle slave connection. As the when the connection is borrowed from the pool,
it may or may not be validated depending upon the "validationInterval"

DBCP for instance, issues the validation query (when testOnBorrow is true), whenever a connection is
borrowed from the connection pool. If the mysql connector/j hands back a slave connection that is idle
the validation query catches that. However, what this results in, is a database whose job is handling a lot of Ping commands,
as one of it's top queries. The tomcat pool (http://www.tomcatexpert.com/blog/2010/04/01/configuring-jdbc-pool-high-concurrency)
performs this connection validation periodically (based on a validationInterval) so your database isn't constantly validating connections from applications.

I can see that there is "loadBalanceValidateConnectionOnSwapServer=true", so that the idle slave connection
would be caught before it is given back to the client as the active slave connection (useable in 5.1.23). Although,
this would result in what almost equivalent of the DBCP ping on borrow always semantics.


Should the ping query be going to all slaves (Is this a bug)?
If not, is this potentially a feature request to add a property that allows it to validate against all currently established slave connections?
Or Am I missing another connector/j url property to make the ping hit all slaves?


cheers
/dom

Options: ReplyQuote




Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.