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

How to analyze the performance test of mysql pump, 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.

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.

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.

A basic test result is as follows, and the subsequent test results will be added together.

Optionrealidle%dump_size (byte) compress=false6m52.232s85.9226199028017compress=false | gzip43m12.574s90.7212571701197compress=true19m24.541s80.4826199028017compress=true | gzip43m12.515s84.9412571200219parallelism=45m30.005s76.4326199028017parallelism=4 | gzip42m41.433s90.5112575331504parallelism=84m44.177s66.7326199028017

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%. It's pretty 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.

This is the answer to the question on how to parse the performance test of mysql pump. 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.