R/W Splitting Problems

[Jim Carrothers]

I am currently running a MySQL proxy with 1 remote RW MySQL backend server and 1 local Read-only MySQL backend that replicates off of the remote master. Our MySQL servers host quite a few DBs for a variety of sites and services, most of which have their own individual users. We are using version 0.6.0 of the proxy.

I recently enabled the read-write splitting Lua script, and I ran into a problem where the less busy services were running into a problem with their read requests where the SELECT query was being denied because the user being used for the connection to the read-only backend did not have the appropriate rights to read from the DB. I checked the process list on the slave server, and discovered that some connections had been created using the users of our busiest services. I narrowed the problem down to the fact that while it appears the existing connections to the master server can get "re-auth'd" to the current user, the same is not true of the connections to the slave, and so because the current number of connections is above the idle minimum setting, a new connection for the current user is not created and the proxy tries to use an existing connection that is authenticated as the wrong user.

Am I doing something wrong, or is this a problem with the proxy itself?



Edited 1 time(s). Last edit at 01/18/2008 03:09PM by Jim Carrothers.