Performance Benchmark (MySqlBackup.NET, MySqlDump, MySQL.EXE)
This benchmark is run using this version of source code.
Read more: Understanding the Parallel Export Mechanism in MySqlBackup.NET
The parallel processing mode consistently outperforms the single-threaded mode, with time savings becoming more pronounced as database size increases. For instance, with a 2.773 GB database, parallel processing reduced export time by approximately 35~40% compared to the single-threaded approach.
One interesting side observation is that the Samsung Evo 870 SSD demonstrated nearly identical performance to the RAM disk test, which indirectly confirms that this SSD operates with near-RAM efficiency. This suggests either that the SSD achieves DDR3 RAM-level performance, or that the RAM disk implementation cannot deliver 100% of the theoretical RAM speed due to driver limitations. Another theory is that the disk I/O write operations are actually faster than SQL data generation, which means .NET object conversion becomes the bottleneck.
The parallel implementation significantly narrows the performance gap between MySqlBackup.NET and native MySQL tools like mysqldump.exe, while preserving the flexibility and integration advantages of a .NET library. While MySqlBackup.NET's parallel processing remains slower than mysqldump.exe, this difference is likely attributed to the additional overhead of converting MySQL raw bytes to .NET objects before generating SQL strings.
Future Performance Optimization Potential: A promising avenue for further performance enhancement would involve bypassing the intermediate .NET object conversion stage entirely. By directly referencing MySQL column metadata for data type information, the library could potentially manipulate raw bytes directly into SQL strings, eliminating the costly conversion step. This optimization could significantly reduce CPU cycles and potentially achieve another performance milestone, bringing MySqlBackup.NET's speed even closer to native MySQL tools while maintaining its .NET ecosystem benefits.
Note: Start from v2.6, MySqlBackup.NET is running in parallel mode by default.
using (var conn = new MySqlConnection(constr))
using (var cmd = conn.CreateCommand())
using (var mb = new MySqlBackup(cmd))
{
conn.Open();
// enabled by default in 2.6, turn off this to run in legacy single thread
mb.ExportInfo.EnableParallelProcessing = true;
mb.ExportToFile(@"C:\backup.sql");
}Disk I/O Activity Observation
The following test carried out on the following parameters:
Intel Core i7-4770S (3.10GHz) 4 core 8 thread
16GB RAM DDR3 1600 MHz
SSD Samsung 870 Evo 500GB
Windows 10 Pro x64
InnoDB: 3 tables
Database Size: 312 MB
Total Rows: 300000
MySqlDump: 8.0.34 (Export)
MySql: 8.0.34 (Import)
All processes run through Windows IIS ASP.NET WebForms, .NET Framework 4.8, App Built in Release Profile
| Disk | Description |
|---|---|
 |
Backup/Export - MySqlBackup.NET - Single Thread The initial disk I/O activity reaches 100% around 10 seconds at startup and then falls to 15%. This is the slowest method, but surprisingly it shows quite busy disk activity. It's unclear what intensive disk operations are occurring during this process. |
 |
Backup/Export - MySqlBackup.NET - Parallel Processing The initial disk I/O activity reaches 100% around 5 seconds at startup and then falls to 30%. This shows a shorter duration of disk activity spikes compared to single-threaded processing. |
 |
Backup/Export - mysqldump.exe mysqldump does not experience an initial spike. It fluctuates between 20% - 40% disk I/O operations throughout the process. This is the fastest method with the lowest resource utilization, which is surprising. |
 |
Restore/Import - MySqlBackup.NET Maintains steady progress at 50% disk I/O throughout the entire process. |
 |
Restore/Import - mysql.exe Maintains steady progress at 50% disk I/O throughout the entire process. |
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());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