Understanding the Parallel Export Mechanism in MySqlBackup.NET
Report Date: July 6, 2025
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");
}Prior to version 2.6, MySqlBackup.NET performed database exports using a single-threaded process. As illustrated in the first diagram titled "MySqlBackup.NET – Single Thread Export," the export workflow progressed sequentially through three distinct phases: fetching MySQL raw bytes and converting them to .NET objects (Phase 1), converting .NET objects to SQL strings (Phase 2), and writing SQL strings to the output stream or disk I/O operation (Phase 3). Each phase had to complete before the next could begin, which could lead to inefficiencies, especially with larger databases.
With the introduction of parallel processing in version 2.6, as depicted in the second diagram titled "v2.6 - MySqlBackup.NET – Parallel Processing," MySqlBackup.NET now leverages multiple threads to handle these phases concurrently. This advancement allows for a more efficient use of system resources, potentially reducing the time required for backups and improving overall performance.
The parallel export mechanism divides the export process into the same three phases but executes them simultaneously across different threads. This is achieved by introducing Intermediary Buffer Zones using C# .NET's BlockingCollection. These buffer zones act as queues that temporarily hold data as it moves between phases, ensuring a smooth and continuous flow of work.
-
Phase 1 (Data Reading): This phase involves fetching raw data from the MySQL database and converting it into .NET objects. Multiple threads can read data from different tables concurrently, populating the first
BlockingCollectionwith these objects. -
Phase 2 (Data Conversion): Here, .NET objects are transformed into SQL strings. Another set of threads processes the objects from the first
BlockingCollection, converting them and queuing the resulting SQL strings in a secondBlockingCollection. -
Phase 3 (Data Writing): Finally, the SQL strings are written to the output stream or disk. Additional threads handle this task, retrieving data from the second
BlockingCollectionand performing the write operations.
The BlockingCollection serves as a critical component, acting as a synchronized, thread-safe buffer. It allows producer threads (those generating data in Phases 1 and 2) to add items without overwhelming consumer threads (those processing data in Phases 2 and 3). This buffering ensures that no phase is bottlenecked by the others, optimizing the use of multi-core processors and enhancing throughput.
The shift to parallel processing offers several advantages:
- Improved Performance: By utilizing multiple threads, the export process can leverage the full potential of modern multi-core CPUs, significantly reducing backup times.
- Scalability: Larger databases benefit more from parallelization, as the workload is distributed across threads rather than handled sequentially.
- Resource Efficiency: The system makes better use of available hardware, minimizing idle time and maximizing productivity.
To highlight the practical impact of this feature, consider the following benchmark results, conducted on various hardware configurations with MySQL Server v8.4.5. These tests compare the single-threaded and parallel-threaded export times for databases of different sizes.
This benchmark is run using this version of source code.
Read more: Performance Benchmark (MySqlDump vs MySqlBackup.NET)
The data clearly demonstrates that 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.
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.
The parallel export mechanism in MySqlBackup.NET v2.6 represents a significant step forward in database backup technology. By employing a multi-threaded approach and leveraging C# .NET's BlockingCollection for intermediary buffering, this feature enhances performance and efficiency, making it an invaluable tool for managing MySQL backups. We encourage users to explore this capability and provide feedback to further refine its implementation. For more details and the latest updates, please visit the official MySqlBackup.NET repository at https://github.com/MySqlBackupNET/MySqlBackup.Net.
