MySQL Enterprise Backup (MEB) 3.8 and Parallel Performance

I am currently looking at the newest version of MEB.  I have some experience of 3.7 and was keen to experiment the new version

MEB 3.8 provides a substantial improvement in backup operation performance by implementing parallel processing of related internal operations. In this release, during both backup and restore operations, certain operations are now performed in parallel: reading/writing data, compression/decompression, and page checksum validation during backup.-

This can be defined when running the backup:
Parallelization Options [PARALLELIZATION-OPTIONS]:
              Specifies the number of read-threads for the backup operation.


              Specifies the number of write-threads for the backup operation.


              Specifies the number of process-threads for the backup operation.


              Specifies the exact number of memory buffers to be used for the backup operation. 

When --limit- memory option is also used, only as many buffers are created as are permissible by the  --limit- memory option.
              Specify maximum memory in megabytes that can be used by the mysqlbackup command.  Formerly applied only to apply-log operation, but in MySQL Enterprise Backup 3.8 and higher it applies to all operations. Do not include any suffixes such as mb or kb in the option value.  Default: 100 for apply-log operations; 300 for all other operations. megabytes).

The default looks to be 3:
 mysqlbackup: INFO: Uses posix_fadvise() for performance optimization.
 mysqlbackup: INFO: Creating 12 buffers each of size 16777216.
 mysqlbackup: INFO: Copy-back operation starts with following threads
                3 read-threads    3 write-threads

Currently MySQL believe the benefits to be good
Internal benchmarks show that performance gains range from 20% to 1000% depending on the size and content of the data involved, so users should fully expect to see incremental performance improvements in specific use cases.

Further Testing
I have tested a 3.7 backup against a 3.8 with a 30gb instance including a large parition table.  The backup in 3.7 took 24 mins in 3.8, using the default settings, it went down to 9 mins.  The default settings for 3.8 are 3 read thread, 3 write threads and 3 process threads.  As for the restore times the difference is minimum.

Further tests include adjusting the the threads whilst running the backup and restore and this depends on your storage subsystem.  The disk configuration was a raid 5 and the best backup results was using 2 read and write threads with 8 process threads but this needs testing.  A point to mention here is machine resources if the machine has few CPU cores then try get the process threads very high.  It will slow the backup by starving restores.

It generally works by allocating threads and using buffers during runtime.  The more threads and the more (16MB) buffers the faster the backup will go.  For a great explanation on buffers, see
When you run a backup it tells you in the header what configuration you are running:

 mysqlbackup: INFO: Creating 12 buffers each of size 16777216.

 mysqlbackup: INFO: Copy-back operation starts with following threads
                3 read-threads    3 write-threads 

 So the default uses 3 write, read and process threads but creates 12 buffers all 16MBThe formula for working out the number of buffers is read threads + write threads + process threads +
max(read threads, write threads, process threads).  An example I have which help me reduce the backup time of a database is 2 read and write threads and 8 process threads, this resulted in 20 16MB buffers.

Backup - uses all parameters including read-threads, write-threads, process-threads and limit-memory
Restore - uses the read-threads, write-thread and limit-memory, the process-threads are ignored (It is ignored when you use any of the options --incremental-with-redo-log-only, apply-incremental-backup, copy-back, or backup-dir-to-image.)

Once last thing the number of buffers is limited by the memory limit (--limit-memory=MB) configured for backup (default 300MB). Please ensure that you configure enough memory to be able to distribute it to the buffers you have configured. If the memory limit configured is less then what is required for the configured number of buffers; MEB will automatically decrease the number of buffers to fit into the memory limit. Based on the default values, if you are configuring more than 18 buffers you will need to increase the memory limit.

No comments:

Post a Comment