The integrated real-time HTAP database stonedb, how to replace MySQL and achieve nearly 100 times the improvement of analysis performance

The industry revolves around MySQL build HTAP Mainstream programs

1. MySQL + Hadoop

2. MySQL +   Data Lake

3. MySQL + ClickHouse/Greenplum

4. Based on multiple copies of Divergent Design

1. The system architecture is too heavy , The complexity of operation and maintenance is high ;
2. TP Data is passed through ETL Mode to AP In the system , The data delay is large , It is difficult to meet the real-time requirements of services for analysis ;
3. Heterogeneous database combination , Technically, two sets of database systems need to be maintained , It involves many technology stacks , Higher requirements for technical personnel ;
4. NewSQL System , Various compatibility adaptations are required , Adaptation work will be more complex , The requirements for technicians are also relatively high .
5. So , We brought in HTAP The solution to the problem ：StoneDB, An open source integrated real-time HTAP database .

StoneDB Plug in access to MySQL, By inquiring / Write interface and MySQL server Layers interact , The main features of the current integrated architecture are ：

• Organize data by column storage , Combined with efficient compression algorithm , bring StoneDB While obtaining high performance, it also has the advantage of storage cost .
• Based on knowledge grid （Knowledge Grid） Approximate query and parallel processing mechanism , bring StoneDB When dealing with massive data and complex queries , It can minimize irrelevant data IO.
• Using histogram , Data block bitmap and many other statistical information to further accelerate the speed of query processing .
• Adopt Column-at-a-time An execution engine for columnar storage , And further improve the efficiency of the execution engine .
• Provide high-speed data loading capability .

Let's take a look StoneDB Architecture design ：

Architecture design ： Data organization form

stay StoneDB in , Data is organized in columns . This form of data organization , Friendly to all kinds of compression algorithms , According to the type of each column 、 Data and other factors, choose the appropriate efficient compression algorithm , To save IO and Memory Purpose of resources . It also has the following advantages ：

• Cache Line friendly .
• During query , The operations for each column are performed concurrently , Finally, aggregate the complete recordset in memory .
• When querying ad hoc , Just scan specific columns , No need to consume IO Resources to read the values of other columns .
• No need to maintain index , Support ad hoc query of any column combination .
• It can provide knowledge-based grid capabilities ,   Improve data search efficiency .

Architecture design ： Column based data compression

As mentioned above , Data is organized in columns , All records in the column have the same type , You can choose the corresponding efficient compression algorithm according to the data type , because ：

• The probability of duplicate values in the column is high , The compression effect is obvious .
• The data node size is fixed , Maximize compression performance and efficiency .
• Compress according to a specific numeric type （int,float,date/time,string etc. ）.

StoneDB It can support up to 20+ An adaptive compression algorithm , At present, it mainly uses ：

• PPM
• LZ4
• B2
• Delta wait

Architecture design ： Data organization structure and knowledge grid

1. Physical data is divided into fixed data blocks , For storage , Usually called ：Data Node, Usually it is ：128KB, The system is convenient for IO Optimization of efficiency . meanwhile , It can also provide block based （Block） Efficient compression / encryption algorithm .
2. Knowledge grids can be query optimizers , Implementation and compression algorithm support . for example ： Query based on knowledge grid , The optimizer will use the knowledge grid to decide what to grab Data Node To perform data operations .

• Data nodes （Data Node,DN）： The data block size is fixed （ Typical values 128KB）, Optimize IO efficiency , Provide block based （Block） Efficient compression / encryption algorithm .
• Knowledge grid （Knowledge Grid,KG）： For metadata storage .
• Metadata node （Metadata Node,MDN）： Metadata information describing the data node . By the knowledge node （Knowledge Node,KN） form , For the query optimizer , Support plan execution and compression algorithm .

Architecture design - Inquire about ： Knowledge grid （ Knowlegde Grid ） overview

Architecture design - Inquire about ： be based on Knowlegde Grid The optimizer for

Architecture design - Inquire about ： Processing flow

1. Implementation plan optimization and implementation ：

• Based on knowledge grid Cost-based Optimize
• IO Thread pool maintenance
• Memory allocation and management

2. SMP Support （ Concurrent query ）
3. Vectorization execution

• Fully compatible with MySQL. Whether it's grammar or ecology MySQL Users can seamlessly switch to StoneDB.
• Business 、 Integration of analysis . There is no need to ETL, Transactional data is synchronized to the analysis engine in real time . It enables users to obtain real-time business analysis results .
• Fully open source .
• Compare with MySQL Provide 10-100 Times AP Ability . Hundred million level multi meter correlation rapid response , There is no need to wait for the result of the decision .
• 10 Times the import speed . because AP scenario , The amount of analysis data is huge , Efficient import speed , It can bring you a good user experience .
• 1/10 Of TCO cost ,StoneDB Have efficient compression algorithm , Seamless business migration capability , And its simple architecture , Can bring to users TCO Reduction .

StoneDB 2.0 Will bring a new architecture

StoneDB Open source warehouse

https://github.com/stoneatom/stonedb

Gao riyao

StoneDB PMC、HTAP Kernel architect

Graduated from Huazhong University of science and technology , Like to study the mainstream database architecture and source code .8 Years of experience in database kernel development , Once engaged in distributed database CirroData 、RadonDB and TDengine Kernel Research and development , I am now in the position of StoneDB Kernel architects and StoneDB project PMC.