Research on MySQL database middleware

With the development of the Internet, the volume of data is increasing. A single instance is far from being able to meet the needs of the business. The increasing demand for database sub-list is the development of database middleware.

One, a single instance of the main face the following issues:

1. Data volume too large single machine cannot be hosted

2. Data query efficiency is too low, single-table data to achieve a certain amount of business performance can not meet

3. Bottlenecks in database optimization

4. The problem of data security, a large number of data placed on a machine if the data problem response cycle will be particularly long, the business impact too much.

What comes with this is the need for a sub-database table.

But the sub-list of four words to do it is really not so simple.

Ii. problems faced by sub-database sub-tables

1. Transaction support, the expansion/expansion of the table after the transaction is distributed, the difficulty of the problem obviously rose a level

2. Query results merged, this looks not difficult, but the order by/limit/query without the table field, and so on, to solve the problem is also a lot

3. Join, this is more difficult

4. Sub-Library? A sub-table? or a sub-database? This needs to be considered and made a decision.

5. Can the merger be completed after the completion of the division? Easy, and hard

6 ..... Wait a lot of questions

Encounter so many problems must be solved, these problems can be slowly one to solve, database middleware does not need to all the functions are supported, for a company to be fully business-oriented;

What features do I need for my business I can provide those features.

Third, open source implementation of database middleware

The following brief talk about the current market database middleware, concrete can use their own projects need to research. Can be landed also need to go business-oriented, currently we use Cobar two times developed (now completely different) database middleware has been in the company (JD), and can be very good to support the large amount of data sub-database sub-table requirements.

There are several types of open source implementations currently known:

Atlas,cobar,tddl,mycat,heisenberg, Oceanus, vitess

These solutions in MySQL FABRIC/TDDL two are non-proxy, directly provide language level support, the advantage is that performance will be very good, disadvantage is the support of the language is very limited, TDDL support Java,mysql faric support Java/php/python, Want to implement other language support, equivalent to once again, but the current Java/php/python very popular, able to cover a lot of users, TDDL for external users, the first use is not so easy, and Ali platform tightly bound, if there is time to configure the diamond, It can be studied, not in-depth discussion here.

The other four are all proxy mode, with some performance for flexibility, this is the decision of the benevolent see, it is worth mentioning that Bat B also implemented a Heisenberg middleware, from the part of the code, configuration and file structure, this is from Cobar, Should be optimized for some features, do not repeat, interested in their own research

MySQL Fabric
This is the official MySQL expansion program, known as the management goal is: Farm of mysqld, in fact, I am more optimistic about this thing, the reason is a person like concise things, always feel proxy way more than a layer of forwarding brought trouble, in addition to performance and complexity, operation and maintenance of a lot of things, After all, the process is down, usability is affected, simplicity is beauty, hopefully MySQL fabric can provide more language support

Cobar
Cobar is Ali's middleware, proxy way to provide services, in Ali internal use, has been open source in GitHub, like the spirit of open source, it is said that in many external companies have some successful use of the case, the configuration is relatively easy, do not rely on other things, there is a Java environment OK, small tried a , transaction support is cumbersome, need to be implemented through set autocommit=0, if you want to perfect support, still need to modify the JDBC or corresponding language MySQL library, sub-Library (Cobar in d.t to two libraries: d1.t,d2.t):

Cobar supports distributed sub-tables, but does not support the Library sub-table, the front (docking app) and the backend (docking MySQL) have implemented the MySQL client protocol, relatively friendly, for the expansion of the table result set merge also support, write its internal SQL Parse Engineer Foundation should be relatively deep, Display the transaction will be error, should be "BEGIN" or "Start transaction" does not contain the reason for the table information, but this can be defer to a later containing the table information of the SQL to be sent to the target library to execute or through a distributed transaction, such as 2PC to support

Atlas
Although the individual on the digital company is not good, but this Atlas is very characteristic, it is based on the Mysql-proxy two times developed, mainly supports two features: sub-table and read-write separation, but the sub-table only supports the Library multi-table, that is, in fact, does not support distributed sub-table, All the sub-tables are in the same library, a small try, the following is the Sub-Library (four sub-table T_0,t_1,t_2,t_3 in the same library):

Atlas-frame
Atlas Extensibility is limited to single-instance maximum performance, but the benefit is: Transaction support is very good, because there is no cross-library transactions, some things become simple, read and write separate this thing actually does not scale so difficult, after all, MySQL is very good to build a library, have to spit groove, The atlas is not a complete table, but the individual tables, only through the field of the table to locate and manipulate the data, otherwise it will be error, compared with Cobar, Atlas This product is actually simple and rough, but there is a unique application scenario:

Atlas-cmd
In addition, the Atlas configuration is slightly simpler, but the table-based algorithm supports a cobar that seems imperfect

vitess
Vitess is an open-source database extension and high-availability program for YouTube, which has been used in production environments and is powerful, but with complex architectures, high deployment and operational costs
Vitess between the app and database exist Vtgate and Vttablet two server,vttablet realize the MySQL connection pool and row cache, and MySQL instance is a one-to-two relationship, can be regarded as the MySQL front-end, Generally with MySQL deployed together, all the query to MySQL will need to go through Vttablet,vtgate is a proxy, responsible for the routing calculation and the application and vttablet between the data forwarding, The table rules and configuration data are stored in topology (zookeeper implementation), so each query executes a longer path, although the connection pool and row cache implemented by Vttablet can speed up query execution, but zookeeper access The app and Vtgage, Vtgate and Vttablet between the two data forwarding network, if the network is not stable enough to foresee a large loss of performance

Research on MySQL database middleware