Tuesday Aug 21, 2012

Parallel backup in MySQL Enterprise Backup v3.8.0

MySQL Enterprise Backup (MEB) supports backup and copyback of mysql database objects into/from different disks or tape drives. On backup, MEB reads the data from the disk, processes it in form of validation or compression(if requested by user) and writes to disk. These operations were performed in a serial fashion till 3.7.1, wherein the reading of data is completed, followed by processing, and then writing. The performance of this can be increased by pipelining the read, process and write operations to be run in parallel, and this is what exactly is done in the latest release MEB 3.8.0 so as to reduce the time taken for all kinds of backups and restore operations.

How does this pipeline parallelism work?

This pipelined parallel execution of read, process(if any) and write operations is achieved by invoking multiple threads for different operations and usage of buffers. Each operation is split into segments and threads operate on different segments from different iterations concurrently. Each segment is assigned one or more worker threads and in-queue buffers which stores the work to be processed by that stage.

Here is an example:

Each thread T1-T3 does the following repeatedly for 3 segments S1-S3, where:

S1: Read block from source directory/file
S2: Process (compress/validate)
S3: Write processed block to destination directory/file

T1 will run S1 and then insert the block in S2′s in-queue.
T2 will compress the block and enqueue it for writing by S3. 
T3 runs S3 to write the block. 
Thus, T1, T2, and T3 can be working concurrently on stages from different iterations, thereby increasing performance.

Now, let us take a look at the new options introduced in version 3.8.0 for this parallel execution using threads. The user can tune the performance of backup and restore operations for the particular configuration by specifying thread values for different options as follows:

--read-threads option controls the maximum number of READ threads to run in parallel.

--process-threads option controls the maximum number of PROCESS threads to run in parallel.

--write-threads option controls the maximum number of WRITE threads to run in parallel.

--number-of-buffers option is used to specify maximum number of buffers could be created and used for MEB operations.These buffers, each of size 16MB, are used by READER, PROCESSOR and WRITER thread pools. This value controls the level of parallelism in MEB operations. The value ‘1’ causes the threads to run in serial. If not specified by the user, number of buffers is usually calculated as the sum of number of READ, PROCESS and WRITE threads + MAX of (READ, PROCESS, WRITE) threads.

ie., Let (READ, PROCESS, WRITE) threads are (5,8,3). Then number-of-buffers needed are (5 + 8 + 3 + MAX(5,8,3)) ==> (5+8+3+8) = 24 buffers.These extra 8 buffers would be used for prefetching and pre-processing by faster thread pool.For eg., if READER has completed reading a block of data, and processing takes time to complete, then READER can use these extra buffers to read the next block of data instead of waiting on PROCESSOR, followed by WRITER, to put buffers into free buffer queue. You can read more about the thread options and number-of-buffers here

Lets look at an example to show the execution of backup operation using MEB 3.8.0:
====================================================================
bash$ ./mysqlbackup -uroot –backup-dir=/backups backup

Snippet of output:

mysqlbackup: INFO: Unique generated backup id for this is 13444082910667734
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: Creating 12 buffers each of size 16777216.
120716 08:05:55 mysqlbackup: INFO: Full Backup operation starts with following threads
3 read-threads 3 process-threads 3 write-threads
120716 08:05:55 mysqlbackup: INFO: System tablespace file format is Antelope.
120716 08:05:55 mysqlbackup: INFO: Starting to copy of all innodb files...
120716 08:05:55 mysqlbackup: (Antelope file format). INFO: Copying /data/ibdata1
mysqlbackup: Progress in MB: 200
120716 08:05:59 mysqlbackup: INFO: Found checkpoint at lsn 1600768722.
mysqlbackup: Progress in MB: 400 600 800 1000
...
...
...
120808 12:14:06 mysqlbackup: INFO: Completing the copy of all non-innodb files.
120808 12:14:06 mysqlbackup: INFO: A copied database page was modified at 1604792918.
(This is the highest lsn found on page)
Scanned log up to lsn 1604794840.
Was able to parse the log up to lsn 1604794840.
Maximum page number for a log record 16418
120808 12:14:07 mysqlbackup: INFO: All tables unlocked
120808 12:14:07 mysqlbackup: INFO: All MySQL tables were locked for 0.934 seconds.
120808 12:14:07 mysqlbackup: INFO: Full Backup operation completed successfully.
120808 12:14:07 mysqlbackup: INFO: Backup created in directory '/backups'
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 1604792832
End LSN : 1604794840
-------------------------------------------------------------
mysqlbackup completed OK!
====================================================================
As you can see, MEB 3.8 starts backup operation with 3 read, 3 process and 3 write threads by default respectively, though the thread values are not specified by the user. This is to ensure parallelism. The default values may/may not differ for various MEB operations. For eg, for apply-log, the default values are 1 read-thread and 1 process-thread.

** Default values for various operations are defined after reviewing the performance test results on different machine configurations based on the number of CPUs, speed of RAM, etc.

Here’s another example to show the complete usage of thread options introduced in MEB 3.8.0:
====================================================================
bash$ ./mysqlbackup -uroot –backup-dir=/backups --read-threads=3 --process-threads=4 --write-threads=3 backup

Snippet of output:

mysqlbackup: INFO: Unique generated backup id for this is 13444081787458390
mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
mysqlbackup: INFO: Creating 14 buffers each of size 16777216.
120808 12:13:00 mysqlbackup: INFO: Full Backup operation starts with following threads
3 read-threads 4 process-threads 3 write-threads
120808 12:13:00 mysqlbackup: INFO: System tablespace file format is Antelope.
120808 12:13:00 mysqlbackup: INFO: Starting to copy of all innodb files...
...
...
...
120808 12:15:15 mysqlbackup: INFO: A copied database page was modified at 1604795408.
(This is the highest lsn found on page)
Scanned log up to lsn 1604797288.
Was able to parse the log up to lsn 1604797288.
Maximum page number for a log record 16418
120808 12:15:15 mysqlbackup: INFO: All tables unlocked
120808 12:15:15 mysqlbackup: INFO: All MySQL tables were locked for 0.912 seconds.
120808 12:15:15 mysqlbackup: INFO: Full Backup operation completed successfully.
120808 12:15:15 mysqlbackup: INFO: Backup created in directory '/backups'
-------------------------------------------------------------
Parameters Summary
-------------------------------------------------------------
Start LSN : 1604795392
End LSN : 1604797288
-------------------------------------------------------------
mysqlbackup completed OK!
====================================================================
As you can observe in both the examples, the number of buffers is calculated according to the thread values. User’s value for --number-of-buffers option will take priority over the default calculation of number of buffers.

How to choose the thread values for optimal performance:

For the same combination of thread values, performance has increased on a system with RAID configuration with multi-core CPUs compared to a machine with non-RAID storage configuration.

I would like to give you some guidelines based on the experimental executions that has been performed on our machines:

1. Monitor the resource utilization for determining the CPU usage, number of I/O requests, etc.

2. Always have the source and target directories/locations at different drives.

3. Increase the value of read/write threads if source/destination drives can handle more I/O requests respectively

4. Note that the extra performance is at the expense of enhanced CPU utilization. Please set the number of process-threads based on the CPU cycles that can be spared for backup on your setup.

5. The combination of read-threads=1, process-threads=6, write-threads=1 have given the optimal performance on the following configuration:

1. A non-RAID configuration having:
○ 64-bit Windows 7 Operating System
○ 8 GB RAM
○ Intel Core i5-2400 CPU @3.10 GHz

2. A RAID configuration having:

○ OEL6.2/64 Operating System
○ 16 GB RAM
○ 2 x 2000 MHz CPUs
○ 2 RAID DISKS (1027 GB,733.9GB)

as compared to the previous version 3.7.1

Note : The maximum value for each thread option is 15.

For more details on performance tuning, please take a look at the user manual.

You may also like:

https://blogs.oracle.com/MySQL/entry/new_parallel_backups_in_mysql
http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/meb-news-3-8-0.html
http://dev.mysql.com/doc/mysql-enterprise-backup/3.8/en/index.html

About

MySQL MEB Team Blog

Search

Categories
Archives
« April 2014
SunMonTueWedThuFriSat
  
1
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
   
       
Today