Amoeba for MySQL-Distributed Database proxy Solution

Amoeba for MySQL is located between the client and database server (s), with load balancing, high availability, SQL filtering, high concurrency, read/write splitting, query route (parsing SQL query statements, request to the specified target database based on the conditions and preset rules. Allows concurrent requests to merge results from multiple databases) and is transparent to clients. This reduces the impact of complicated multi-database structures and data splitting rules on applications caused by data splitting. Applicable to MySQL 4.1 or later versions (MySQL Protocol Version: 10). Currently, transactions and DDL statements are not supported and are only allocated to the default database for execution. The runtime environment must at least run.
MySQL 4.1 or later, Java 1.5 or later.
There are some differences between amoeba and MySQL proxy. If you want to read/write splitting on the MySQL proxy and read clusters and write cluster machines frequently, you need to write a Lua script by yourself using MySQL proxy. Currently, MySQL proxy does not have a good Lua script. Amoeba only needs to be configured to meet the requirements.

1. read/write splitting under the MySQL Master/Slave structure:
MASTER: serverm (master database, receiving write operations)
Slaves: servera, serverb, and serverc (three slave databases, read-only operations)

Amoeba provides configuration related to the read/write splitting pool. It can be configured with servera, serverb, and serverc to form a virtual virtualslave. This configuration provides LB, failover, and failbackup functions.
 
<Dbserver name = "virtualslave" virtual = "true">
   <Poolconfig>
       <Classname> com. meidusa. amoeba. server. multipleserverpool </classname>
       <! -- Server Load balancer parameter 1 = roundrobin, 2 = weightbased -->
       <Property name = "loadbalance"> 1 </property>
                  
       <! -- The poolname list participating in the pool Server Load balancer is separated by commas -->
       <Property name = "poolnames"> servera, serverb, serverc </property>
   </Poolconfig>
</Dbserver>

<Dbserver name = "virtualslave" virtual = "true">
 <Poolconfig>
  <Classname> com. meidusa. amoeba. server. multipleserverpool </classname>
  <! -- Server Load balancer parameter 1 = roundrobin, 2 = weightbased -->
  <Property name = "loadbalance"> 1 </property>
    
  <! -- The poolname list participating in the pool Server Load balancer is separated by commas -->
  <Property name = "poolnames"> servera, serverb, serverc </property>
 </Poolconfig>
</Dbserver>

 

If the data splitting function is not enabled, you only need to configure the queryrouter attribute.
Wirtepool = serverm
Readpool = virtualslave

<Queryrouter>
   <Classname> com. meidusa. amoeba. MySQL. parser. mysqlqueryrouter </classname>
   <Property name = "lrumapsize"> 1500 </property>
   <Property name = "defaultpool"> serverm </property>
 
   <Property name = "writepool"> serverm </property>
   <Property name = "readpool"> virtualslave </property>
 
   <Property name = "needparse"> true </property>
</Queryrouter>

 <Queryrouter>
  <Classname> com. meidusa. amoeba. MySQL. parser. mysqlqueryrouter </classname>
  <Property name = "lrumapsize"> 1500 </property>
  <Property name = "defaultpool"> serverm </property>

  <Property name = "writepool"> serverm </property>
  <Property name = "readpool"> virtualslave </property>

  <Property name = "needparse"> true </property>
 </Queryrouter>

The update/insert/delete statements sent by the client are sent to the wirtepool and the SELECT statement is sent to the readpool machine for execution.

 

Ii. Data Splitting:
In this regard, amoeba is obviously quite easy. For example, data Splitting:

Select * From user_event where user_id = 'test' andGmt_create between sysdate ()-1 and sysdate ()

If data is sharded Based on the gmt_create time, for example, once every six months, amoeba provides data sharding using SQL expressions like this:

Rule 1: gmt_create> to_date ('2017-01-01 ', 'yyyy-mm-dd') and gmt_create <to_date ('2017-05-31', 'yyyy-mm-dd ')

Rule 1 corresponds to Server 1

Rule 2: gmt_create> to_date ('2017-06-01 ', 'yyyy-mm-dd') and gmt_create <to_date ('2017-12-31', 'yyyy-mm-dd ')

Rule 2 corresponds to server 2
The preceding SQL condition gmt_create is used to determine the intersection between gmt_create and gmt_create in the rule. If the intersection exists, it indicates that the rule is met, and the SQL statement is transferred to the corresponding server of rule 1 for execution.

It is easy to use amoeba to write such a rule. However, to achieve linear resizing after data splitting, such a rule needs to be set based on actual business conditions. Amoeba can concurrently distribute SQL statements to multiple servers and then merge the results to the client. In addition, the internal use of the non-blocking mode in Amoeba will not wait, when multiple database servers are concurrently requested, the client wait time is basically the same as that of the database server + amoeba internal parsing protocol with the worst performance.

English document address: http://amoeba.sourceforge.net/amoeba.pdf http://docs.hexnova.com/amoeba/
Amoeba future development direction: http://amoeba.sourceforge.net/amoeba-big-picture.pdf

Http://www.sourceforge.net/projects/amoeba download
Amoeba developer blog: http://amoeba.meidusa.com