MySqlDump vs MySqlBackup.NET - Performance Benchmark

Introduction

MySqlDump, MySql.exe and MySqlBackup.NET are tools used to backup and restore MySQL database.

MySqlDump and MySql.exe are developed by Oracle MySQL team, which are commonly known to MySQL programming world.

MySqlBackup.NET on the other hand, is an open source project written in C#. It serves as a direct plugin and supports most .NET platforms' projects, such as .NET Framework, .NET Core, .NET Standard, etc. It also supports VB.NET and J# as they share the same CLR (Common Language Runtime).

Since the launch of MySqlBackup.NET, there is a question about how well the performance of MySqlBackup.NET goes compared to MySqlDump.

The developer of MySqlBackup.NET has decided to take some time to setup the benchmark test.

The Benchmark Testing Environment / Setup

The benchmark was carried out in the following setup:

• OS: Windows 10 Pro x64 (version 21H1, build: 19043.1237)
• CPU: Intel Core i5-3570 CPU @ 3.40GHz
• RAM: 12GB DDR3
• Hard Disk: Samsung SSD Evo 860 (550 MB/sec read, 520 MB/sec write)
• MySQL Community Server v5.7.26, installed by using MySql Installer 5.7.26
• Default Character Set=utf8 (This will affect the file size generated by MySqlDump), as latin1 normally has a smaller size than utf8. On the other hand, MySqlBackup.NET uses UTF8 as text encoding for writing the dump content.
• MySql.EXE v5.7.26 (Import)
• MySqlDump v8.0.26 (Export)
• MySqlBackup 2.3.5, with mysql.data.dll v8.0.26 (start from 2.3.5, MySqlCommand is replacing MySqlScript resulting faster import execution)
• max_allowed_packet = 999M
• MySql server and backup/restore dump file saving location are both executed on the same hard disk.
• MySqlDump (Export) and MySql.exe (Import) are executed through MySqlWorkBench v8.0.26
• MySqlBackup is executed through a Console App (.NET Framework 4.8)
• Total database size: 3.50 GB (3,762,407,157 bytes)
• Total rows: 15,350,000 Rows (15 millions)

Notes: There are some other elements that have a great impact on the processing speed:

• The hard disk type and model
• The distance between MySql Server and the software application (that runs the backup/restore).

The Benchmark Result

Process     Tools              Time
-------     ---------          ------
Backup 1    MySqlDump         2m 36s
Backup 2    MySqlDump         2m 33s
Backup 3    MySqlDump         2m 35s

File size: 
4.66 GB (5,008,487,275 bytes)

Backup 4    MySqlBackup.NET   7m 48s
Backup 5    MySqlBackup.NET   7m 46s
Backup 6    MySqlBackup.NET   7m 50s

File size:
4.59 GB (4,931,743,894 bytes)

Restore 1   MySql.exe         8m 42s
Restore 2   MySql.exe         8m 23s
Restore 3   MySql.exe         8m 57s

Restore 4   MySqlBackup.NET   9m 44s
Restore 5   MySqlBackup.NET   9m 39s
Restore 6   MySqlBackup.NET   9m 39s

Server Default Character Set. UTF8 and LATIN1 are 2 commons character sets being used. MySqlDump might have a different performance benchmark on both character sets. In MySQL, UTF8 is generally referred to utf8bm3. In MySQL 8.0, utf8bm4 is used as the default character set.

Shown below is the character set being used in this test:

SHOW VARIABLES LIKE '%character%';

character_set_client      = utf8
character_set_connection  = utf8 
character_set_database    = utf8 
character_set_filesystem  = binary 
character_set_results     = utf8 
character_set_server      = utf8 
character_set_system      = utf8

The Code that Applied in the Benchmark Test

Here is the CREATE TABLE statement used in this test:

CREATE TABLE `tableA` (
  `int` INTEGER UNSIGNED NOT NULL AUTO_INCREMENT,
  `varchar` VARCHAR(45),
  `text` TEXT,
  `datetime` DATETIME,
  `date` DATE,
  `time` TIME,
  `decimal` DECIMAL(10,5),
  `tinyint` TINYINT UNSIGNED,
  `timestamp` TIMESTAMP,
  `char36` CHAR(36),
  `binary16` BInary(16),
  `float` FLOAT,
  `double` DOUBLE,
  `blob` BLOB,
  PRIMARY KEY (`int`)
)
ENGINE = InnoDB;

Here's the INSERT statement:

INSERT INTO `tableA` (`varchar`,`text`,`datetime`,`date`,`time`,`decimal`,
`tinyint`,`timestamp`,`char36`,`binary16`,`float`,`double`,`blob`,`bool`) 
VALUES('GtEva4ijqGoLnFvffBO3dPK1cLT9dWpQ56pzrt4vAkJr0',
'10UQ0F9MDuUM95KOcrED4GoyndiCQcWmILLh2h0uIQtm6',
'2021-09-24 20:40:12','2021-09-24','20:40:12',3487.2398,1,CURRENT_TIMESTAMP,
'00000000000000000000000000000000',0x00000000000000000000000000000000,243.234,456.456,
0x00000000000000000000000000000000,1);

The code for executing Backup (MySqlBackup.NET) in Console App:

List<TimeSpan> lstDate = new List<TimeSpan>();

for (int i = 0; i < 3; i++)
{
    int round = 1 + i;
    Console.WriteLine($"round {round} started...");
    DateTime datestart = DateTime.Now;

    string constr = $"server=127.0.0.1;user=root;pwd=1234;
                    database=test1;sslmode=none;default command timeout=90000000;";

    using (MySqlConnection conn = new MySqlConnection(constr))
    {
        using (MySqlCommand cmd = new MySqlCommand())
        {
            conn.Open();
            cmd.Connection = conn;

            string filepath = System.IO.Path.Combine
                              (Environment.CurrentDirectory, $"{round}.sql");
            datestart = DateTime.Now;
            cmd.CommandTimeout = 9000;

            using (MySqlBackup mb = new MySqlBackup(cmd))
            {
                mb.ExportInfo.GetTotalRowsMode = GetTotalRowsMethod.Skip;
                mb.ExportToFile(filepath);

                conn.Close();
            }
        }
    }

    DateTime dateend = DateTime.Now;
    var ts = dateend - datestart;
    lstDate.Add(ts);
}

StringBuilder sb = new StringBuilder();

foreach (var ts in lstDate)
{
    string s = $"{ts.Hours}h {ts.Minutes}m {ts.Seconds}s {ts.Milliseconds}ms";
    Console.WriteLine($"{ts.Hours}h {ts.Minutes}m {ts.Seconds}s {ts.Milliseconds}ms");
    sb.AppendLine(s);
}

System.IO.File.WriteAllText("report_backup.txt", sb.ToString());

Here's the code for executing Restore (MySqlBackup.NET) in Console App:

List<TimeSpan> lstDate = new List<TimeSpan>();

for (int i = 0; i < 3; i++)
{
    Console.WriteLine($"round {1 + i} started...");
    DateTime datestart = DateTime.Now;
    string dbname = "test" + (5 + i);
    string constr = $"server=127.0.0.1;user=root;pwd=1234;sslmode=none;";

    using (MySqlConnection conn = new MySqlConnection(constr))
    {
        using (MySqlCommand cmd = new MySqlCommand())
        {
            conn.Open();
            cmd.Connection = conn;

            cmd.CommandText = $"drop database if exists `{dbname}`;";
            cmd.ExecuteNonQuery();

            cmd.CommandText = $"create database `{dbname}`;";
            cmd.ExecuteNonQuery();

            cmd.CommandText = $"use `{dbname}`;";
            cmd.ExecuteNonQuery();

            datestart = DateTime.Now;

            using (MySqlBackup mb = new MySqlBackup(cmd))
            {
                mb.ExportInfo.GetTotalRowsMode = GetTotalRowsMethod.Skip;
                mb.ImportFromFile(@"D:\dumps\mysql_data1.sql");

                conn.Close();
            }
        }
    }

    DateTime dateend = DateTime.Now;
    var ts = dateend - datestart;
    lstDate.Add(ts);
}

StringBuilder sb = new StringBuilder();

foreach (var ts in lstDate)
{
    string s = $"{ts.Hours}h {ts.Minutes}m {ts.Seconds}s {ts.Milliseconds}ms";
    Console.WriteLine($"{ts.Hours}h {ts.Minutes}m {ts.Seconds}s {ts.Milliseconds}ms");
    sb.AppendLine(s);
}

System.IO.File.WriteAllText("report.txt", sb.ToString());

Other

• This article is part of the study of MySqlBackup.NET.
• MySqlBackup.NET official project site (Github)

History

• 3^rd October, 2021: Initial version