ITPub博客

首页 > Linux操作系统 > Linux操作系统 > MySQL Proxy RW Splitting

MySQL Proxy RW Splitting

Linux操作系统 作者:season0891 时间:2013-11-10 23:33:00 0 删除 编辑

Read/Write Splitting

The MySQL Replication allows to scale out read-load.

To use it nicely your application has to be aware of reading data from slaves and writing data to the master. Read/Write splitting wants to automate that process inside the MySQL Proxy.

In

Proof of Concept of that idea is presented.

Limitations

There are a few problems left before that feature is in a acceptable state:

Connection State

Up to now the example script only knows about:

  • transactions go to the master
  • auto-increment INSERTs will wait for the next SELECT INSERT_ID()
  • SELECT SQL_CALC_FOUND_ROWS will wait for the next SELECT FOUND_ROWS()
  • LOCK TABLES

Currently most of the other commands that change the connection state are ignored.

  • SET NAMES utf8
  • CREATE TEMPORARY TABLE ...
  • SET @a = 1; SELECT @a;
  • Stored Procedures
  • SHOW SESSION STATUS

The rw-splitting.lua script has to be extended to track those statements and either:

  • replicate the state to the new connection (SET NAMES utf8)
  • don't switch the connection

Connection Pool

The MySQL Proxy maintains a connection pool if the scripting layer disconnects the backend from the client connection:

proxy.connection.backend_ndx = 0

The backend connection can be taken from the pool by other client connections afterwards.

For the R/W-Splitting you need 2 open connections for each client connection:

  • one to the master
  • one to the slave

As the authentication protocol never transmits the real password nor anything we can use to authenticate against another server, we need a internal storage of credentials to open connections to the backend-servers when we are short of connections in the pool.

That's currently missing.

Bugs

Yes, we know that RW-Splitting is not for production use yet, it is a Proof of Concept. To keep track of the known issues:

Labels:
None

 Sign up or Log in to add a comment or watch this page.

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/90618/viewspace-776258/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2008-06-10

  • 博文量
    791
  • 访问量
    1935683