Shulou(Shulou.com)06/01 Report--

The author: Wu Bingxi

Source: https://yq.aliyun.com/articles/278034

I. demand

A friend received a demand and received a data written in 2 billion + from big data platform, which needs to be quickly loaded into MySQL for business presentation the next day.

Second, the reanalysis of realization

For single table 2 billion, in MySQL operation and maintenance, to be honest, this piece is currently less involved and has little experience, but for InnoDB single table Insert, if the memory is larger than the data, it can be maintained at 100000-150000 rows. But most of the time we accept projects with more data than memory. Here use XeLabs TokuDB to do a test.

III. Introduction to XeLabs TokuDB

Project address: https://github.com/XeLabs/tokudb

Optimization relative to the official TokuDB:

Built-in jemalloc memory allocation

Introduce more built-in TokuDB performance metrics

Support for Xtrabackup backup

Introduce ZSTD compression algorithm

Support for binlog_group_commit features of TokuDB

Fourth, test table

TokuDB core configuration:

Table structure:

Write data using load data:

Calculate the write speed per second:

File size:

The actual file is 8.5 gigabytes, and the written TokuDB size is 3.5 gigabytes, which is only close to half of the compression of more than half of the points. For 2 billion data writes, the actual test can be completed in more than 58 minutes. It can meet the actual needs. In addition, for machines with good disk IO (SSD disk, cloud disk), if the memory is similar to the data, the data volume test of this order of magnitude needs to be added in Innodb, which can be completed in a little more than 3 small. From the best practical point of view, Innodb and TokuDB both write the same data, and InnoDB takes about 4 times as long as TokuDB3. File size difference, same 2 billion data:

The file size is 5 times the size of the difference.

Test conclusion:

Using TokuDB in a cloud environment with 8 cores of 8 gigabytes of memory and 500 gigabytes of high-speed cloud disk, you can easily achieve 570000 writes per second through multiple tests.

In addition, several testing scenarios can also be used for your reference: if the primary key with self-increment is used in TokuDB, the write speed is reduced obviously because the primary key has no value, and 200 million data is also written with a self-built primary key:

When the same data is written in the primary key, the Bulk loader data feature of TokuDB cannot be used, which is equivalent to the conversion to a single Insert implementation, so the effect is much slower.

With regard to TokuDB Bulk Loader prerequisites, this table is empty and can also be used for self-incrementing columns, such as if they have values. It is suggested that in practical use, if the self-increasing column has a value, you can consider removing the self-increasing attribute and changing it to a unique index, so as to reduce some processing logic of self-increasing, so that TokuDB can run faster. In addition, in order to pursue faster write in Bulk Loader processing, compression is not very good.

About TokuDB Bulk Loader:

Https://github.com/percona/PerconaFT/wiki/TokuFT-Bulk-Loader

5. Description of the test environment

Test the Baidu Cloud address of the compiled XeLabs TokuDB version using CentOS7 environment:

Https://pan.baidu.com/s/1cxoarGW8psQfQpJnpXq09Q

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.