Continuent Community

Hello,

Our Drupal-5-based site is experiencing growing pains with a single Mysql database host and we're looking to test a RW-splitting proxy service. We currently have a replicated mysql slave that we use for backups, and we'd like to start using it for read-only queries. I gave up on mysql-proxy after struggling with its rw-splitting implementation, which is incomplete.

Is this something Tungsten Connector can do? I read the wiki and pdf documentation, and understand the concepts at a high level, but I'm unfamiliar with the JDBC ecosystem. Is it possible to configure Connector to talk directly to Mysql 5.0 hosts (1 master for RW, and 1 slave for RO)? Or, must it talk to another JDBC data source?

Sorry if this question is slightly ignorant. Please point me at the docs if this is covered somewhere.

With appreciation,
Dane

Hi Dane!

Good news! This is not an silly question _and_ the connector will do exactly what you need

Here is what you'll need to do:
1/ get the connector either from the tungsten-community tarball or from svn directly (head version is stable at the moment
2/ configure the connector to use direct jdbc driver. Edit connector.properties:
jdbc.driver=com.mysql.jdbc.Driver
jdbc.driver.base.url=jdbc:mysql://
jdbc.direct.driver=com.mysql.jdbc.Driver
3/ add mysql connector/j jdbc driver in the lib/ directory (you can download it on http://www.mysql.fr/downloads/connector/j/). Note that if you choose to take the connector from the community tarball, take care about the version of connector/j you get: must be < 5.1.7, see http://forge.continuent.org/jira/browse/MYO-107
4/ edit user.map and define, in addition to the regular connections, a "direct" connection pointing to your r/o database
5/ Make sure you have passThrough mode on in your connector.properties: this will evict most part of the jdbc layer (while still using it), you will then get direct connections to the database
6/ Report back here and ask questions if any!

Cheers,
Gilles.

Thanks Gilles. It works! I used the community tarball and version 5.0.8 of Mysql's Connector/J. My application points to the IP: Port of Tungsten-Connector, and queries get split among the Mysql master and slave databases. I enabled Mysql's general query log on each machine and verified that SELECTs get sent to the slave, not the master.

One oddity: Tungsten-Connentor appears to be continuously creating connections with the read-only slave. At any given time there are 25 open connections to the slave, all running the following queries:

QUOTE:

# grep 9670 /tmp/all-mysql.log
9670 Connect myROuser@domU-12-31-39-02-F6-18.compute-1.internal on db_drupal
9670 Query SHOW SESSION VARIABLES
9670 Query SHOW COLLATION
9670 Query SET character_set_results = NULL
9670 Query SET autocommit=1
9670 Query SET sql_mode='STRICT_TRANS_TABLES'
9670 Query SELECT @@session.tx_isolation
100221 17:21:51 9670 Quit

Here are the contents of my config files (with username/password/domains changed):

conf/connector.properties:


server.protocol = mysql
server.version = 5.0.45
server.port = 9999

jdbc.driver = com.mysql.jdbc.Driver
jdbc.driver.base.url = jdbc:mysql://

jdbc.direct-connection.driver = com.mysql.jdbc.Driver
jdbc.driver.options = ?user=myRWuser&password=myRWpass

user.map.filename = /opt/tungsten/tungsten-connector/conf/user.map
connection.close.idle.timeout = 0
statement.fetch.size = 0
selective.rwsplitting=false

passThroughMode=true

rw.splitting.mode=safe
rw.splitting.scope=direct



conf/user.map

myRWuser myRWpass db_drupal dbmaster.mydomain.com:3306

@direct myRWuser db_drupal jdbc:mysql://dbmaster.mydomain.com:3306/db_drupal?user=myROuser& password=myROpass

Hi Dane!
Glad it worked !!!
The behavior you described is expected, the connector keeps a bunch of direct connections behind a connection pool (c3p0), in order to avoid the connection overhead.
If this is a problem for you, it is possible to reduce the number of connections as well as their time-before-renewal
Cheers,
Gilles