And thanks for sharing the information about the encryption. As we know backup and restore times are not the only parameter that influences the decision of opting for a certain tool. I intend to make all these considerations as well.

Here is the information you requested:
mysql> SELECT table_schema AS “Database”, ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS “Size (MB)” FROM information_schema.TABLES where table_schema like ‘percona’GROUP BY table_schema;
+———-+———–+
| Database | Size (MB) |
+———-+———–+
| percona | 91626.28 |
+———-+———–+
1 row in set (0.00 sec)

And for each table (there are 90 tables):

+————–+———–+
| Table | Size (MB) |
+————–+———–+
| stock1 | 3560 |
| stock4 | 3560 |
| stock10 | 3559 |
| stock2 | 3559 |
| stock3 | 3559 |
| stock5 | 3559 |
| stock6 | 3559 |
| stock7 | 3559 |
| stock9 | 3559 |
| stock8 | 3559 |
| order_line4 | 3045 |
| order_line1 | 3040 |
| order_line8 | 3040 |
| order_line5 | 3039 |
| order_line10 | 3038 |
| order_line2 | 3035 |
| order_line3 | 3034 |
| order_line9 | 3032 |
| order_line7 | 3029 |
| order_line6 | 3025 |
| customer10 | 2013 |
| customer4 | 2012 |
| customer5 | 2012 |
| customer1 | 2012 |
| customer9 | 2012 |
| customer3 | 2011 |
| customer6 | 2011 |
| customer7 | 2011 |
| customer8 | 2011 |
| customer2 | 2011 |
| history1 | 314 |
| history5 | 313 |
| history6 | 313 |
| history7 | 313 |
| history2 | 313 |
| history9 | 312 |
| history8 | 312 |
| history4 | 312 |
| history10 | 312 |
| history3 | 312 |
| orders1 | 210 |
| orders2 | 210 |
| orders4 | 208 |
| orders10 | 208 |
| orders7 | 208 |
| orders8 | 208 |
| orders9 | 208 |
| orders5 | 208 |
| orders3 | 207 |
| orders6 | 207 |
| new_orders5 | 25 |
| new_orders8 | 25 |
| new_orders7 | 25 |
| new_orders6 | 25 |
| new_orders4 | 25 |
| new_orders3 | 25 |
| new_orders2 | 25 |
| new_orders10 | 25 |
| new_orders1 | 25 |
| new_orders9 | 25 |
| item6 | 11 |
| item1 | 11 |
| item10 | 11 |
| item2 | 11 |
| item3 | 11 |
| item4 | 11 |
| item5 | 11 |
| item7 | 11 |
| item8 | 11 |
| item9 | 11 |
| district9 | 0 |
| district1 | 0 |
| district10 | 0 |
| district2 | 0 |
| district3 | 0 |
| district4 | 0 |
| district5 | 0 |
| district6 | 0 |
| district7 | 0 |
| district8 | 0 |
| warehouse1 | 0 |
| warehouse10 | 0 |
| warehouse2 | 0 |
| warehouse3 | 0 |
| warehouse4 | 0 |
| warehouse5 | 0 |
| warehouse6 | 0 |
| warehouse7 | 0 |
| warehouse8 | 0 |
| warehouse9 | 0 |
+————–+———–+
90 rows in set (0.00 sec)

The bigger ones have 3 million rows, while the smallest one has 100 rows (hence the 0 MB used).

It is very interesting the article that you shared, I will release soon another post about restore performance and all considerations around this topic. Thanks for checking!

Could you provide information about the data ? I know you used tpcc, but how many tables and how big are they each ? Are they equivalent in size ? I’m asking as for large tables, MySQL Shell load utility uses a different approach to schedule the parallel ingestion that is optimized (see https://dev.mysql.com/blog-archive/mysql-shell-dump-load-part-3-load-dump/).
Cheers 😉

Thanks for reading! So, I have the plans to release a part 2 with restore time 🙂

About mysqldump, I tested with and without compression and both had similar performance. From what I understood, the fact that only one thread is being used, a lot of hardware horsepower is being lost in the process. Because even the usage of the storage is lower with mysqldump. Even if zstd or pigz allows parallization, the first pipe is still single threaded.

Is there any difference substituting mysqldump | gzip with a different compression tool?

Like zstd or even pigz for example.

Fast backups are a good thing of course, but most of times, it’s more important the restore time, so a follow-up article comparing the restore times of the tools would be super nice I think.

Again, thanks a lot!

###
One thing I also would be careful about with such benchmarks – it focuses only on time to backup which is important but considering it alone can lead you astray
###

I agree. In general, the ultimate goal of performing a backup is being able to restore it. Im planning to do a second part of this series about restore performance. I will also collect the size of the backups.

Also Xtrabackup uses by default quicklz(and there is a option of lz4):
https://www.percona.com/doc/percona-xtrabackup/LATEST/xtrabackup_bin/xbk_option_reference.html#cmdoption-compress

About Yoann comments:

Indeed this is another perspective. But most use cases that I see is DBA performing backups from a replica or a dedicated backup sever with no workload (apart from replication). Since covering all scenarios is an ungrateful task ( I got questions about using less cores, a less powerful storage, etc…), I decided to use a case where it will cover the majority of uses.

One thing I also would be careful about with such benchmarks – it focuses only on time to backup which is important but considering it alone can lead you astray

Also I wonder what was backup size in all those cases was it comparable ? This is also something one may consider especially if long retention is needed