Shulou(Shulou.com)05/31 Report--

How to design the time series storage engine in MySQL? in view of this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and feasible method.

First, let's take a look at what OpenTSDB looks like. The following figure is a diagram of the deployment and operation of OpenTSDB. The back-end storage of OpenTSDB is HBASE, and the front end of information collection needs to be deployed on each server to display information through dashboard. For the existing time series database, each has its own deployment and operation plan. However, these schemes do not have architectural versatility.

The architecture we expect is that there is no difference for MySQL users except for the underlying storage components. Most of the existing operation and maintenance experience (HA, replication, backup, etc.) and existing development experience (insert, delete, and update operations) can be inherited from previous accumulation, which is what any company using MySQL wants. As shown in the following figure, for upper-level users, there are not many changes. Users can write their own application client through the standard SQL to complete the data collection and display, and improve flexibility.

After the above considerations, we have produced our experimental product, storage engine CFL (ctrip fast log), which can record data in a fast log way. When it is completed, it is shown in the following figure to meet the various situations previously envisaged.

Technical introduction

From a hierarchical point of view, MySQL's storage engine is divided into two parts. One is to implement the components related to the storage function, which provides the specific functions of the storage engine (addition, deletion, modification and query), which we call the functional layer; the other is the component docking with the interface of the MySQL plug-in, which converts the functional calls of the MySQL into the functional calls of the storage engine, which we call the interface layer. As shown in the following figure, the operations of the database (such as additions, deletions, modifications, queries, etc.) will reach the interface layer of the storage engine through the engine management layer, and then from the interface layer to the function layer.

Functional layer

The function layer is the core of the storage engine, and the function provided by the function layer of the storage engine is also different because of the different design objectives. For example, the functional layer of the innobase engine provides a series of functions such as transaction ACID/ data storage / meta-information management / MVCC, providing complete database functions; and the CSV storage engine, which only provides row storage of character types. These storage engine components with different functions provide a variety of services under the framework of MySQL.

The functional layer of Ctrip time series storage engine will be introduced from two main aspects. One is the architecture of the functional layer, that is, the objects involved in the runtime and the role of these objects; the other is persistent storage, which will explain how the data is stored at the file level.

Architecture

The architectural design goal of CFL is to improve the efficiency of data insertion as much as possible, so the idea of parallel processing needs to run through all the time. The mechanism is that different sessions insert data into the table object in parallel; the table object stores the inserted data through the buffer. When the buffer is full, the buffer joins the disk write queue and writes to the disk concurrently through a special write disk thread.

The following figure shows the architecture of Ctrip time series storage engine:

Storage

When designing the storage, according to the characteristics of the time series database, the first consideration is the insertion efficiency, and then the fast fault recovery. In view of the insertion efficiency, a strict sequential writing strategy is adopted when designing the data structure, so as to ensure the efficiency of continuous insertion. In this way, it can be written efficiently no matter on the traditional hard disk or on the SSD hard disk. For fast fault recovery, fast recovery is achieved by controlling the writing order (writing data, index and control information in turn). * considering the simplicity of implementation, the index and data are stored separately to reduce the control of storage management in the same file.

Interface layer

Program fragment

The following code snippet is the interface layer part.

The storage engine needs to provide a class that inherits from the base class handler provided by MySQL and implements functions such as ha_open/ha_close in the base class.

Inherit the class of Ctrip time series database of handler class.

Interface layer architecture

This section will take ha_cfl as an example to illustrate the relationship between the management of the MySQL storage engine and the engine interface.

After the operation is sent to the session, the session obtains the object of the ha_cfl from the engine management layer and converts the operation into a call to the ha_cfl interface, which completes the docking of the SQL to the storage engine interface.

After receiving the call, the ha_cfl interface converts the call into the operation of the table object, and completes the docking of the function of the handler interface to the implementation of the table object.

Effect.

Through the targeted development of time series data, the insertion performance of CFL storage engine is greatly improved compared with InnoDB and MyISAM engine.

Engine\ insert thread

1 thread (ips)

3 threads (ips)

6 threads (ips)

CFL

3700

5700

8400

MyISAM

3300

4500

6000

InnoDB

1900

2100

3000

2-core ssd virtual machine

Ips:insert per second

Summary

Due to the exploratory nature of this development, the product is not perfect due to time or manpower constraints, and there is room for improvement both in design and implementation. For example, there are restrictions on the use of timestamp types and the specification of index columns when creating a table, so that multiple column indexes cannot be created, only time indexes can be created. Because of the limitation of storage structure, deletions and updates can not be carried out quickly and flexibly.

However, in the case of limited resources, complete a complete concept and implementation of a complete product. And it is through this exploratory development that it has opened a door to the MySQL storage engine, no matter from the overall architecture to the implementation details have been in-depth study, accumulated a lot of experience. The DBA team of Ctrip Technical Assurance Center hopes that these experiences will help the domestic MySQL community in the future.

This is the answer to the question about how to design the time series storage engine in MySQL. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel for more related knowledge.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.