MySQL server having following specifications:
- 8GB RAM
- Data & Index of size 49.64GB
- Dump file size is 17.24GB(18512936320 Bytes)
- CPU:
- Processor: Intel(R) Core(TM) i5-3330 CPU @ 3.00GHz
- Architecture: x86_64
- vendor: Intel Corp.
- size: 2400MHz
- capacity: 2400MHz
- width: 64 bits
- OS:
- Distributor ID: Ubuntu
- Description: Ubuntu 14.04.5 LTS
- Release: 14.04
- Codename: trusty
Free memory(RAM): this may be variable on specific time
total used free shared buffers cached
Mem: 7.7G 5.2G 2.5G 15M 44M 4.6G -/+ buffers/cache: 597M 7.1G Swap: 7.9G 61M 7.8G
Database 98% tables are InnoDB and rest of it are MyISAM.
I have used following query:
mysqldump -u root -p --single-transaction --routines --events --triggers --all-databases | gzip > all_databases_backup.sql.gz
I have tried following Global variables :
key_buffer_size = 512M
Innodb_buffer_pool_size = 1024M
innodb_io_capacity = 2000
innodb_read_io_threads = 64
innodb_write_io_threads = 64
innodb_thread_concurrency = 0
To optimize the backup total time but not only able to reduce the time even 1 min.
- With default settings it is taking min : 27mins
- With the setting shown above it takes : 26mins
Is there any thing else I have to configure to optimize mysqldump
?