Performance Benchmark (MySqlDump vs MySqlBackup.NET)

Date: Sep 24, 2021

This benchmark test was carried out in the following setup/environment/parameters:

• 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 is normally has 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)

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).

Here is 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 set being used. MySqlDump might have different performance benchmark on both character set. In MySQL, UTF8 is generally refers to utf8bm3. In MySQL 8.0, utf8bm4 is used as the default character set.

Below shows 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 

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());

MySqlCommand vs MySqlScript

Before MySqlBackup.NET 2.3.5, MySqlScript is used as the primary tool to send import/restore queries to MySQL database. The reason of doing this is MySqlScript can handle both INSERTs and routines creation (like CREATE STORE PROCEDURE, CREATE FUNCTIONS, etc).

I decided to carry out another benchmark between the performance of MySqlCommand and MySqlScript. I found that MySqlCommand is faster than MySqlScript. Therefore, start from v2.3.5, MySqlCommand is used to handle INSERTs and MySqlScript will handle routine creations which requires delimeter changed.

Here's the benchmark results:

Database Size: 380MB
Process: Restore Database

Time(Avr)   Class - DLL
---         ---
57s         mysql.exe (MySqlWorkbench)
1m 5s       MySqlCommand - MySql.Data.DLL
1m 5s       MySqlCommand - MySqlConnector.DLL
1m 5s       MySqlCommand - Devart.Express.MySql.DLL
1m 16s      MySqlScript  - MySql.Data.DLL