Not the answer you need?
Register and ask your own question!

Lock-Optimization for archived MyISAM tables

dcw1dcw1 EntrantInactive User Role Beginner
Lets say we have 100GB of data stored in MyISAM tables. 90GB will never be changed (archived tables) and 10GB are changed every minute.

-> Backup will lock all tables for ~10 minutes and also replication will have some delay.


I made some modifications to the innobackupex-script (used with parameter --checkfiles) so that it will first copy all tables (~10 Minutes), then lock all tables und recopy only changed tables (10GB) in ~1 Minute.
-> Backup is 10% slower but table-lock is also minimized to 10%

So you only have to save some file-information like 'date changed' and 'filesize' for every file you copy - and on second copy you simply compare that filesizes and dates on the local server (not as rsync does on the remote side).

I did that for cp and scp (remote-host)... works quite well.

Comments

  • gmousegmouse Mod Squad Inactive User Role Beginner
    An alternative approach would be to make innobackupex release a lock to a certain table as soon as the files of that table are copied. If you could make it backup the active tables first, you have the same downtime but not the extra minute delay.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.