3

I have approx 90 GB sized database, and I need to schedule a mysqldump backup. How long will it take to perform a full hot backup approximately? I'm asking because I've never had to backup a database as big as this one yet.

  • You'll never know how long it takes with your hardware and your load unless you actually test it. – Mat Mar 4 '15 at 11:01
3

LE: giving the tips below I may estimate that your dump could take 20 minutes (and longer). But with these tips and high performance CPUs and lot of memory the time may be smaller.

You didn't provide enough informations regarding the specific of your data. Are there a single database or multiple? Is all data InnoDB or there are MyISAM tables around? If you have only InnoDB tables then you may use the "--single-transaction" option of mysqldump utility. According to manual:

--single-transaction This option sets the transaction isolation mode to REPEATABLE READ and sends a START TRANSACTION SQL statement to the server before dumping data. It is useful only with transactional tables such as InnoDB, because then it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

When using this option, you should keep in mind that only InnoDB tables are dumped in a consistent state. For example, any MyISAM or MEMORY tables dumped while using this option may still change state.

While a --single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates), no other connection should use the following statements: ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE. A consistent read is not isolated from those statements, so use of them on a table to be dumped can cause the SELECT that is performed by mysqldump to retrieve the table contents to obtain incorrect contents or fail.

The --single-transaction option and the --lock-tables option are mutually exclusive because LOCK TABLES causes any pending transactions to be committed implicitly.

This option is not supported for MySQL Cluster tables; the results cannot be guaranteed to be consistent due to the fact that the NDBCLUSTER storage engine supports only the READ_COMMITTED transaction isolation level. You should always use NDB backup and restore instead.

To dump large tables, combine the --single-transaction option with the --quick option.

Therefore, for a consistent snapshot, if there are any chance to get "ALTER TABLE, CREATE TABLE, DROP TABLE, RENAME TABLE, TRUNCATE TABLE" commands on the database, then you should use the "--lock-tables" option. Also, to get a fast restore you could use the "--disable-keys".

However, there are also other techniques to speed up the dumping, such as:
- increase the "key_buffer_size" at the maximum 4GB. The variable is dynamic so you can issue "*SET GLOBAL key_buffer_size=4*1024*1024*1024;*" (or whatever you can) just before the dump and revert the value after the dump.
- increase the "max_heap_table_size". If you have "--order-by-primary" option issued there may be a lot of temporary tables produced. I've found that setting max_heap_table_size larger than the most tables increases the speed.
- redirect the dump directly to an archive utility which support pipeline, such as: gzip, pigz, bzip2, pbzip2, 7zip, rar, etc. This way, you can avoid using the HDD during the dump.

Example of use (ordered by the speed!):

PIGZ: Parallel GZip, grade 6 from 9
PIGZ: 30 sec : 319976345 GZIP - multi-threading: 8 cpu by default
Arch: mysqldump -u root -p --opt DATABASE | pigz -6 -c > dumpname.sql.gz
Unarch: pigz -cdk dumpname.sql.gz | mysql -u root -p DATABASE

PBZIP2: Parallel BZip2. grade 6 from 9(my personal favourite)
PBZIP2: 45-60 sec : 231360931 BZ2 - multi-threading: maxim sau cu -p8
Arh: mysqldump -u root -p --opt DATABASE | pbzip2 -c >dumpname.sql.bz2
Unarch: pbzip2 -cdk dumpname.sql.bz2 | mysql -u root -p DATABASE

7Zip, container ZIP, metoda BZip2, grade 3 from 9
7ZA: 75 sec : 233503528 ZIP - multi-threading: maxim
Arch: mysqldump -u root -p --opt DATABASE | 7za a -tbzip2 -mx=3 -mmt=on -sidumpname.sql dumpname.sql.zip
Unarch: 7za e -tbzip2 -so dumpname.sql.gz | mysql -u root -p DATABASE

7Zip, container ZIP, metoda BZip2, grade 6 from 9
7ZA: 95 sec : 230986947 ZIP - multi-threading: maxim
Arch: mysqldump -u root -p --opt DATABASE | 7za a -tbzip2 -mx=6 -mmt=on -sidumpname.sql dumpname.sql.zip
Unarch: 7za e -tbzip2 -so dumpname.sql.zip | mysql -u root -p DATABASE

GZIP: container GZ, grade 6 from 9
GZIP: 95 sec : 318920792 - multi-threading: none
Arch: mysqldump -u root -p --opt DATABASE | gzip -6 --rsyncable > dumpname.sql.gz
Unarch: gzip -cdk dumpname.sql.gz | mysql -u root -p DATABASE

RAR: container RAR, grade 3 from 5, test -mt16
RAR: 95 sec : 248149972 RAR - multi-threading: auto - maxim 5 cpu
Arch: mysqldump -u root -p --opt DATABASE | rar a -m3 -sidumpname.sql dumpname.sql.rar
Unarch: unrar p -inul dumpname.sql.rar | mysql -u root -p DATABASE
"-si" switch undocumented in manual; may be found in rar.txt (/usr/share/doc/rar/)

RAR: container RAR, grade 4 from 5
RAR: 125 sec : 246759443 RAR - multi-threading: auto - maxim 6 cpu
Arch: mysqldump -u root -p --opt DATABASE | rar a -m4 -sidumpname.sql dumpname.sql.rar
Unarch: unrar p -inul dumpname.sql.rar | mysql -u root -p DATABASE

**** RAR: container RAR, grade 5 from 5 ****
RAR: 140 sec : 246242715 RAR, grade 5 from 5 - multi-threading: auto - maxim 6 cpu
Arch: mysqldump -u root -p --opt DATABASE | rar a -m5 -sidumpname.sql dumpname.sql.rar
Unarch: unrar p -inul dumpname.sql.rar | mysql -u root -p DATABASE

**** 7Zip, container 7Z, method LZMA default, grade 3 from 9 ****
7ZA: 145 sec : 266638306 7ZA - multi-threading: none (b'cose method)
Arch: mysqldump -u root -p --opt DATABASE | 7za a -mmt=on -mx3 -sidumpname.sql dumpname.sql.7z
Unarch: 7za e -so dumpname.sql.7z | mysql -u root -p DATABASE

**** 7Zip, container ZIP, metoda BZip2, grade 9 from 9 ****
7ZA: 330 sec : 228609475 ZIP(bzip2) - multi-threading: maxim - 16 cpu
Arch: mysqldump -u root -p --opt DATABASE | 7za a -tbzip2 -mx=9 -mmt=on -sidumpname.sql dumpname.sql.zip
Unarch: 7za e -tbzip2 -so dumpname.sql.7z | mysql -u root -p DATABASE

The tests are made on a 4+2GB (of data+indexes) database, with a total of 8 CPU cores and 32 GB RAM.
There are other solutions too: nanozip, zpaq, lbzip2, pXZ, pIXZ, plzip, lrzip, which some of them I've tried out (like nanozip and zpaq), but even if the dump is smaller, the time increased significantly.

You have some good clues now, but, as a conclusion, you need to check out some points before establish the right measures you need to take, like if there are moments when nobody is querying the data, the storage type (MyISAM is not transactional, but InnoDB is), amount of RAM (for key_buffer_size and max_heap_table_size), number of CPU's (to take advantage of multi-threaded archiving).

LE2: Consider taking the dump over a replication slave, if available.

  • "4+2GB (of data+indexes) database, with a total of 8 CPU cores and 32 GB RAM" is in no way representative of 90 GB that may not be in RAM. You are basically reading and writing to memory (filesystem cache), so do not expect to scale linearly- a 90 GB backup could easily take days in a low-memory, low IOPS system. The only interesting part is when you said "You (s/he) didn't provide enough informations". – jynus Mar 4 '15 at 14:38
  • Actully my system configuration is I have 23 gb of ram,4 cpu(s) and in my database there is total 268 tables all are using innodb engine.But the thing is that my server is utilizing complete memory – Rk Singh Mar 5 '15 at 6:03
  • @Rk Singh: of course, MySQL tries to keep the used tables in the Innodb Buffer Pool, so your 90 GB database is overwhelming the system memory. Meanwhile, all reads and writes are disk bound so the overall performance is largely degraded. You must put your database on a 128GB (or more) system and setting the innodb_buffer_pool_size variable large enough to keep all data and metadata information (110-120G). Let me know if you successfully completed the backup task. – Tinel Barb Mar 5 '15 at 7:52
  • @jynus: I agree, we can't linearize. There is not math here, just guess. But with "flush-tables" before the dump he will have the same start as anyone, and "flush-tables" is (by some) recommended. – Tinel Barb Mar 5 '15 at 8:01
  • @TinelBarb:-Ohk thank u so much, I definitely let u know. – Rk Singh Mar 5 '15 at 8:43
1

You have to go with Incremental Backup. First you must have take full backup of all the database. Then you can set the incremental backup with regular schedule.

  • Thats correct @Krunal Patel but for taking a full backup first I required some approx time limit Idea because for cold backup we couldn't afford down time. – Rk Singh Mar 4 '15 at 11:36
  • @RkSingh Then you have to perform Backup when less DML operations execute on your database. – Krunal Patel Mar 4 '15 at 11:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.