Cobar + MySQL Technical Verification (Ali)

First, Introduction

Cobar is a distributed storage of data after the product, can support the use of the background of MySQL or Oracle database, through the configuration, the data in accordance with a certain rules stored in different databases. The distributed database is used instead of the centralized database. The traditional centralized database system has the following deficiencies: centralized processing, it is bound to cause performance bottlenecks; The application is centrally run on one computer, and if the computer fails, the whole system is affected, the reliability is not high, the scale and configuration of the system are not flexible and the scalability of the system is poor. In this situation, the centralized database will be developed to the distributed database.

Benefits of a distributed database system: Reduce costs. Distributed databases can be geographically distributed. The structure of the system conforms to the requirements of this distribution. Allow users in their own local recruitment, query, maintenance and other operations, the implementation of local control, reduce communication costs, to avoid the centralized need for higher requirements of hardware equipment. Moreover, the distributed database has less data on a single machine, and its response speed is significantly improved, and the overall availability of the system is increased. Avoid the consequences of total paralysis due to the failure of a single database; it is easy to expand processing power and system size. The structure of distributed database system can easily extend the system, add a new node in the distributed database, and not affect the normal operation of the existing system. This approach is more flexible and economical than expanding centralized systems. In the centralized system to expand the system and system upgrades, due to hardware incompatibility and software change difficulties and other shortcomings, the cost of upgrading is often expensive and not feasible.

//Advantages://The configuration is simple and can easily realize the distributed storage of data. //with transparency, there is little need for the client to make any special settings for this purpose. //extension is convenient, Cobar server can be extended by load balancing, the database can be expanded according to different pressure. ////Disadvantages://query restrictions, if the submitted request does not contain the limit of the table field, it may be executed in multiple partitions, the efficiency and feasibility are greatly reduced (encountered a common query throws an exception, the specific scenario needs to be verified)//Federated query limits, for data in different partitions, cannot be federated. //expansion, number of partitions at initial time if you cannot cope with subsequent requirements, you need to add partitions (for example, the initial design is divided into 64 partition tables, because a single table usually limits the amount of data in 20G,//late discovery does not meet capacity requirements and needs to be expanded to 128/256 partitions, no ready-made solution, only manual splitting of data

Cobar in the field of distributed databases will be committed to resolving data segmentation, to meet the client "centralized" processing of distributed data. Here the centralized is a relative concept, the client does not need to know the physical storage of some kind of data. Avoiding this logic on the business side greatly simplifies the complexity of how the client operates distributed data. Focus on distributed database proxy development. It is set up between client, DB Server (s), transparent to client, load balanced, high availability, SQL filtering, read/write separation, routing related query to target database, multiple database merge results can be requested concurrently.

Second, the use of Cobar verification

I am currently deploying the Cobar and MySQL test environments on the development environment 10.20.130.119: MySQL is divided into 128 DataBase, cobar_1 to cobar_128, Port 3306, username/password: root /password. Cobar is accessed using a MySQL connection with a URL of jdbc:mysql://10.20.130.119:8066/cobar, username/password: root/12345. The Test database table is q_reportkeywordsum, and the partition field is CustID, type int. If you use JDBC to connect to Cobar, you only need to use the MySQL JDBC driver ( Note that you cannot use the latest version 5.1.13, you need to use the Cobar 5.1.6 version of MySQL Connector), the normal connection can be. The sample code is as follows:

Importjava.sql.Connection;ImportJava.sql.DriverManager;Importjava.sql.SQLException;Importjava.sql.Statement; Public classCobarhelloworld { Public voidMian (string[] args) {String URL= "Jdbc:mysql://10.20.130.119:8066/cobar"; String Driver= "Com.mysql.jdbc.Driver"; String User= "Root"; String pwd= "12345"; Connection Con=NULL; Try{class.forname (driver). newinstance (); Con=drivermanager.getconnection (URL, user, pwd); Statement stmt=con.createstatement (); //TODOStmt.close (); } Catch(Exception e) {// ...}finally {            if(Con! =NULL) {                Try{con.close (); } Catch(SQLException e) {e.printstacktrace (); }            }        }    }}

If you use Spring + iBatis access, you need to make a few changes to the configuration file, replacing the original org.springframework.orm.ibatis.SqlMapClientTemplate with the Cobar Client provided by the Com.alibaba.cobar.client.CobarSqlMapClientTemplate. At the same time, replace the TransactionManager from the original Org.springframework.jdbc.datasource.DataSourceTransactionManager Com.alibaba.cobar.client.transaction.MultipleDataSourcesTransactionManager, the other parts do not need to be changed, the modified configuration code resembles the following example:

<BeanID= "Sqlmapclienttemplate"class= "Com.alibaba.cobar.client.CobarSqlMapClientTemplate">< Propertyname= "Sqlmapclient"ref= "Sqlmapclient"/>...</Bean> <BeanID= "TransactionManager"class= "Com.alibaba.cobar.client.transaction.MultipleDataSourcesTransactionManager">...</Bean> <BeanID= "Sqlmapclient"class= "Org.springframework.orm.ibatis.SqlMapClientFactoryBean">< Propertyname= "DataSource"ref= "DataSource" />< Propertyname= "Configlocation"value= "Classpath:meta-inf/ibatis/sqlmap-config.xml" /></Bean> <BeanID= "DataSource" ...>...</Bean>

Development Note: If the query condition does not contain a partition field condition, the request will return all result sets after all partitions have been executed , such as execution:

Select Count (* from Q_reportkeywordsum

Returns the results of a query in all partitions, such as 128 partitions, with 128 records in the result set. Moreover, in such cases, the efficiency will be poor, waiting for 128 partitions to be completed before returning the result set. WORKAROUND: All queries must be qualified in a specified partition field, such as

Select Count (*fromwhere custid=1

However, it is important to note that select COUNT (*) from Q_reportkeyworsum where CustID < 10; The code does not return the result correctly. Similarly, if you do not specify a partition field when inserting records, records are inserted in all partitioned tables, such as execution:

Insert  into Values (2, ...)

This record will be inserted within all 128 partitions. Workaround: For fields that are used as partition rules (such as the CustID used in the example) must be set to a non-null (NOT NULL) field to avoid such problems. For different partition tables, the database primary key can be duplicated, as seen from the previous paragraph, the ID as the primary key field, the insertion process will insert a record ID 2 in all partitions, therefore, additional use of the primary key generation mechanism to ensure that the primary key within the different tables will not be duplicated.

Third, Cobar use constraints

The horizontal split of the Cobar table, in which the database table is split horizontally into 2 parts, and placed in two libraries respectively. F (x) is a split function that determines the library to which to split the record, based on the value of the split field for each record. The split function can be a multivariate function, i.e. F (x1,x2,.. xn). However, for any split function, there are no two arguments xi,xj the function, so that Xi and XJ are split fields from different split tables.

As shown in the diagram on the left, Cobar is located between the application and the database, and Cobar interacts with the application via MySQL protocol. If the applied SQL statement contains a split field (the argument of the Split function), it works as left: otherwise, it works on the right, or directly, according to the configuration.

//If the connection between the app and the Cobar is F5, the connection hold time for F5 and Cobar is set to 1 hours. //the application must be connected via mysql-connector-5.0.4 Cobar, Split function The maximum number of elements in the range of//DDL is not supported, transactions are not supported, cross-Library joins, subqueries across libraries, paging across libraries are not allowed//for SQL statements that manipulate a split table, include the Split field as follows://1. For SELECT, delete, UPDATE statement, the split field exists in where, in the form of c[i]=xx (the operator must be =, XX must be a specific value, cannot be a field name or subquery result)//2.insert statement (INSERT into table (c1, c2 ...) values (value1,value2); INSERT into table set//c1=value1,c2= .... The Split field appears in the Red section, and the Valuexx corresponding to the split field must be a specific value. //Recommendation://1. The number of records is less than 10 million or table space is less than 10G, do not split. //2. F (the value of the Split field) follows a uniform distribution, where f is the split function. 

Note:

Use Redis to solve the problem of MySQL table self-increment ID. Using the ID of Redis from the proliferation generator on the line, before each insertion, to call a self-growing function, the returned value is always unique, globally unique.

Cobar + MySQL Technical Verification (Ali)