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

This article is to share with you about how to compare the performance of mysqlpump and mysqldump. The editor thinks it is very practical, so I share it with you. I hope you can get something after reading this article.

I tested mysqlpump yesterday, and today I'm going to finish the rest of the supplement, which is a small series.

Mysqlpump

In MySQL 5.7to do logical backup and restore there is a new tool mysqlpump, if you master mysqldump, then using mysqlpump is a minute thing, because many parameters are very similar, it can be understood that it is an enhanced version of mysqldump, a bright spot is the parallel option, making the performance of data backup more powerful.

It is worth mentioning that in order to ensure data consistency, we usually use the option of-single-transaction for backups. Before 5.7.11, there was a conflict between mysqlpump and parallel parameters, which was fixed after this version.

But what about mysqlpump? I did some simple tests in version 5.7.17, and you can see some performance differences.

And mysqldump is the most familiar tool, if not used, are embarrassed to say that they can MySQL, such a tool and Oracle exp tools in general, classic and feature-rich.

Test environment description

In order to ensure that the exported data backup takes up less disk space as much as possible, we often use gzip to compress. We compare the performance differences between compressed, uncompressed and parallelized data backups in several scenarios.

The size of the dataset I selected is about 30G. It contains 5 databases, the amount of data in a single table is more than 2 million, and the number of tables in a single database is more than 10.

Test results of data backup

The test scenarios of data backup have been done a little more by themselves, of course, the compression at the backup level has not been tested yet, and other scenarios

Optionrealidle%dump_size (byte) mysqlpumpcompress=false6m52.232s85.9226199028017

Compress=false | gzip43m12.574s90.7212571701197

Compress=true19m24.541s80.4826199028017

Compress=true | gzip43m12.515s84.9412571200219

Parallelism=45m30.005s76.4326199028017

Parallelism=4 | gzip42m41.433s90.5112575331504

Parallelism=84m44.177s66.7326199028017

Parallelism=8 | gzip42m50.417s90.3812574079375

Parallelism=165m19.060s90.3826199028017

Parallelism=16 | gzip42m50.939s89.6512577618359

Parallelism=325m10.220s89.2326199028017

Parallelism=32 | gzip45m47.022s89.712577618359mysqldumpcompress=false9m19.785s87.3326176062499

Compress=false | gzip43m23.036s90.9712524413896

Compress=true37m42.052s90.126176062499

Compress=true | gzip43m17.755s85.8912524413896

Compress=true38m55.968s90.2226176062499

Compress=true | gzip43m1.672s85.7712524413896

You can see that by default, it takes nearly 7 minutes to export a 30G dump. When parallelism is enabled, when parallelism is 4, the export time is 5 and a half minutes, an increase of 1.5 minutes (20%), and an increase of 2 minutes (30%) after parallelism is 8. When the compression is done at the system level, the compression ratio reaches nearly 48%, and when the parallelism is greater, the backup speed is not much different. On the one hand, it is also related to the situation of CPU. Overall, the effect of parallelism is good. In compress=true, packet compression is only used in server-client interactions, and there is no change in the final backup set size. The performance differences of backups using different compression algorithms will be tested later.

Compressed backups at the system level

If the backup is not compressed through the gzip pipeline, but directly generates backup compression, what about the efficiency? A backup of about 26G, the gzip compression time is about 43m18.974s, in fact, it is not short, much longer than expected.

Data import efficiency

Data import, I simply tested two scenarios, mysqlpump parallel backup export, import, mysqldump backup export import

Mysqlpumpexport parallelism=47m

Import85m4.574smysqldumpexport9m8.420s

Import97m9.760s above is how to compare the performance of mysqlpump and mysqldump. The editor believes that there are some knowledge points that we may see or use in our daily work. I hope you can learn more from this article. For more details, please follow the industry information channel.

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.