Some notes about Newsql (DTCC2016)

Fortunately participated in the DTCC2016 meeting, this year's meeting is particularly hot, I would like to see every special session, but persisting. Made a lot of notes, here the part about the newsql into the KM, no DBA professional and in-depth, please make hard to shoot bricks! ...

I. Introduction of DTCC2016
Ii. 3 Stages of the evolution of a database
Third, complement the basis: a SQL execution process
Iv. Filling a foundation: the acid characteristics and implementation technology of the transaction
V. Several major problems to be solved by newsql
VI. Realization of data sub-database based on Mysql-handler
Vii. Distributed transaction-related protocols

======================================================

I. Introduction of DTCC2016

* 2016.05, Beijing, China Database technology Conference
* with "Data definition future" as the theme, gathered at home and abroad technology peers to explore MySQL, NoSQL, Oracle, cache technology, cloud database, intelligent data platform, big data security, data governance, big data and open source, big Data entrepreneurship, big data deep learning and other fields of forward-looking hot topics and technology
* Home: http://dtcc.it168.com/
* PPT resources: http://pan.baidu.com/s/1eSO6A4a (not very full, but quite a lot of ppt written great)
* 2 main venues and 21 sub-venues, the following is a summary of the theme of each venue. From the topic can be seen in the current industry more concerned about the database areas:

First day (morning) Home 1: Data definition for the future
First day (afternoon) Session 1: Database schema design
First day (afternoon) session 2:nosql technical Practice
First day (afternoon) Session 3: Data acceleration Technology
First day (afternoon) session 4: Real-time computing and stream computing
First day (afternoon) session 5:SAP Technical Exchange session
First day (afternoon) session 6: My path to the DBA
First day (afternoon) session 7: Jiaye Database Technology session

Next day (morning) Home 2: Focus on Big Data + leading industry change
Next day (afternoon) Session 8: Database performance optimization
Next day (afternoon) session 9: Cloud Database
Next day (afternoon) session 10: Application and practice of big data
Next day (afternoon) session 11: Database Kernel Technology
Next day (afternoon) session 12: Data Architecture & Governance
Next day (afternoon) session 13: Data Mining &bi
Next day (afternoon) session 14: NTU General Conference

The third day (morning) session 15:sql Beauty
Day three (morning) session 16: Memory Database Progress
Day three (morning) session 17: Big Data infrastructure
Third day (morning) session 18: Big Data security
Third day (morning) session 19: Application and exploration of big Data industry
Third day (afternoon) session 20: Database Automation operations
Third day (afternoon) session 21: Recommended system architecture and algorithms
Third day (afternoon) session 22: Big Data ecosystem and open source
Third day (afternoon) session 23: Database technology foresight
Third day (afternoon) session 24: Artificial Intelligence
Third day (afternoon) session 25: Big Data Entrepreneurship

Ii. 3 Stages of the evolution of a database

1, SQL RDBMS: Traditional relational database, widely used, but poor extensibility
2. NOSQL: Solving extensibility issues, but providing a limited data model and not supporting transactions
3. Newsql: Extensibility/availability/consistency, support transactions

The technology evolution comes from the demand, the user to the database demand is actually simpler:

* User needs: SQL, transaction (ACID)
* Users do not want to care: data partitioning, failover, data consistency, etc.

Third, complement the basis: a SQL execution process

Can better reflect a SQL in mysql-server execution process, in addition to basic network connectivity and process management, mysql-server after receiving an SQL statement of the main process:

1. SQL parsing: SQL parser (function entry sql_prase.cc) parses SQL statements into lexical and syntactic parsing, decomposes SQL into tokens, and makes up a syntax tree, such as:

There are also some examples of SQL statement parsing based on Flex and Bison tools, and this article is very concise: http://blog.csdn.net/qq910894904/article/details/34861173

2, SQL Optimization: Query optimizer (function entry sql_optimizer.cc) logical optimization and physical optimization of SQL, resulting from a better execution plan, which can be viewed with the explain command. Commonly used optimization points such as using index access, constant conversion, invalid conditions, or code filtering

3, SQL execution: After the explicit SQL execution plan, the rest of the work is to call the storage engine read and write data (function entry sql_executor.cc). Here's one of the biggest features of MySQL-the plug-in storage engine, where you'll continue to analyze

Iv. Filling a foundation: the acid characteristics and implementation technology of the transaction

What is a transaction? A simple example: A has 200 dollars, B has 200 yuan, a to B to 50 dollars, either at the same time success (A=150&B=250), or failure (a=200&b=200), there is no other situation. Many students should have heard the example of this transfer, some of our internet services may not need to be used, but it is clear how important it is to the OLTP business in the financial world, known as the cornerstone of the stable operation of the business

ACID properties of a transaction: four basic elements used to determine whether a database transaction is performing correctly

* A atomicity (atomicity): either all executes, or none of them executes. Either a=150&b=250 or a=200&b=200.
*  C Conformance (consistency): The transaction runs without changing the consistency constraints of the data. That is, a+b=400, this constraint does not change
*  I Isolation (Isolation): Between two transactions, perform complementary interference. For example a at the same time give C turn 50 dollars, these 2 things will not affect each other
*  D Persistence (Durability): After the transaction is executed, the data will persist and will not return to the original state due to an accident such as power outage

Implementing one of the acid core technologies: concurrency control

* How to ensure that multiple transactions are executed correctly concurrently? The concurrency control algorithm is used to dispatch multiple transactions, making the execution serializable (serializable, which is equivalent to the effect and serial execution). Here the algorithm is based on strict mathematical theorem inference, does not discuss
* Concurrency control Technology-MVCC, multi-version concurrency control: The transaction execution process is read only to the database at a time/version of the data, even if the data is modified by other transactions. The biggest benefit is the read without lock, read and write conflict, suitable for short transactions, multi-query scenarios. The InnoDB of MySQL is to use this method
* Concurrency control Technology -2PL, two-phase lock: Classic concurrency control model, transaction execution in the first phase (expansion phase) to obtain all the read and write data locks, in the second phase (contraction phase) release all the added locks, this phase no longer request any locks
* How to weigh concurrency performance and correctness? Full serialization results in low performance, and the SQL standard defines 4 isolation levels depending on the consistency requirements of the different business. The weaker the isolation level, the better the concurrency of the transaction, and the more likely to cause inconsistencies and other exceptions
1, dirty read: READ UNCOMMITTED, read the other transaction uncommitted dirty data (inter-transaction)
2, non-repeatable READ: Read committed, the transaction process of a data read 2 times, may read a different value (within the transaction)
3, Magic read: Repeatable read, the transaction process for a data read 2 times the value is the same, but the last transaction commit found that the data is not the same, as before read the same as "phantom" (MySQL default isolation level)
4. Serialization

Two of the core technologies for acid: Log technology

* Exceptions that may be encountered in transaction processing:
1. Transaction failure: If the consistency constraint is violated, abort transaction is required to clear the transaction impact on the database
2, system failure: Restart or power outage, memory data loss, you need to ensure that the committed transaction changes are not lost, running or abort transaction impact on the database can be cleared
3, storage failure: such as disk corruption, need to ensure that data can be restored
* How to deal with various anomalies, to achieve abnormal recovery? It is common to use the WAL protocol-based logging technology to realize the Redo/undo of transactions. That is, the pre-write log, the log than the data first brush disk (whether Fsync configurable), through the checkpoint location update operation. Contains 3 rules and a lot of detail optimizations, not unfolding here

V. Several major problems to be solved by newsql

* Compatible with MySQL protocol
* Data automatic sharding, solve extensibility problem, transparent to user (core function)
* Dynamic Scaling capacity
* Distributed Transaction
* Data partition disaster recovery, automatic fault switching
* Unified deployment operation and maintenance monitoring

VI. Realization of data sub-database based on Mysql-handler

As I said earlier, one of the biggest features of MySQL is the plug-in storage engine, where a business with a specific need can skip the steps such as SQL resolution and query planning, and write a custom storage engine based on the defined interface (SQL/HANDLER.H). Several of the main Mysql-handler APIs are as follows:

This is the official MySQL source code example (storage/example/ha_example.h), the general meaning from the function of the name of the basic can be seen. There are 2 main classes: Handlerton class is the main contract transaction operation interface, handler class main Contract table, index and record operation interface.

So as long as we put the function of the Sub-Library sub-table in these APIs, we can simply build a distributed database system, and the user to screen out the details of sharding, the user using SQL no sense, as follows:

After a partitioned table, it is relatively simple for the ddl/dml operation to fall within a single partition, such as SELECT * from UserInfo where uid=123; But if it is falling to multiple partitions, like select SUM (age) from UserInfo; You need to split the SQL to access multiple partitions on the backend, and then aggregate the intermediate results after the partition has been computed.
As in this statement: select Name, COUNT (*) from UserInfo where age>20 group by name; All partitions are required for execution: select name, COUNT (*) from userinfo_xxx where age>20 group by name; After getting the intermediate results for all partitions, the final result is the same as the count (*) of name.

Therefore, it is necessary to handle the sub-list implementation of the following major SQL elements separately. For example, complex joins or various exceptions can make query performance worse, and there are more optimization strategies to consider.

* Query: SELECT
* Updated: Insert/update/delete
* Aggregation: Min/max/count/sum/avg
* Association: Join
* Sort: ORDER BY
* Group: GROUP BY

Vii. Distributed transaction-related protocols

In distributed storage systems, distributed transactions are used to guarantee the consistency of data in different partitions. As an industry problem, many database systems are selected for temporary not supported. Sometimes it's a smarter choice to talk to business parties about how to circumvent distributed transactions. After all, most of the technology is hard to invest resources can be made, but to consider the problem of input-output ratio

Solve distributed data consistency issues, the most common two protocols: 2PC and Paxos

2PC: two-phase commit

2 roles involved in a transactional process:
1, Coordinator: The initiator of the transaction, that is, MySQL client or proxy
2, Participant: The transaction performer, namely the partition data node
2 Phases of a transaction:
1. Voting stage
* Coordinator: Notifies all participants to prepare for the execution of the transaction (Perpare T)
* Participant: Pre-write log (Undo+redo), returns the voting result, i.e. whether the transaction is executable (Ready T/notready T)
2. Submission Phase
* Coordinator: Notifies all participants to execute a transaction (Commit T), or send end transaction (Abort T), only when all participants return to ready t
* Participant: According to the Coordinator's notice commit or rollback

Obviously, there are a lot of exceptions to be handled here. For example, the Coordinator and the participants of the message because of failure to deal with the loss? It is necessary to introduce a timeout mechanism to define timeout operations for various types of timeouts. Some can be resolved by the abort transaction, but some are less simple, such as when a participant has not received a message from the coordinator to perform a transaction after the vote, and it is in an indeterminate state (possibly a commit or rollback). Need to initiate a consultation with the facilitator and other participants, execute a terminaion protocol and restore the transaction based on the previously written log

It can be seen that the implementation of 2PC is complex, and the throughput is not high in the case of abnormal (machine outage, network fluctuation, etc.). Since a participant votes to execute a transaction, it has to lock on the corresponding data until it is notified that the transaction commits or rollback

Paxos

Mike Burrows, author of Google Chubby (an open source version of Chubby), said: There is only one consistency algorithm in the world, and that is Paxos, and other consistency algorithms are incomplete versions of Paxos.

However, as a kind of consistency algorithm based on message passing and high fault tolerance, Paxos has always been considered too difficult to understand the algorithm theory. Here I have not fully understood, better study materials recommend a book from Paxos to Zookeeper, as well as some articles on the Internet:
The distributed Conformance Paxos algorithm learning Note (ii): Algorithmic details
Http://www.cnblogs.com/ychellboy/archive/2009/12/29/1634801.html
Paxos algorithm of Distributed system
Http://www.jdon.com/artichect/paxos.html

Some notes about Newsql (DTCC2016)