Thanks Paul, We have a few systems with dumps over 2GB and unfortunately the lock holds out the system for some minutes. Replication may be a solution but obviously involves more cost!
Regards Barry Cannon Interleaf Technology http://www.interleaf.ie Tel: +35312865855 Email: b...@interleaf.ie Skype: bar.cannon ________________________________________ From: Paul A [pau...@navalmarinearchive.com] Sent: 07 January 2016 18:07 To: Barry Cannon; koha@lists.katipo.co.nz Subject: Re: [Koha] mysqldump (koha-dump) At 03:47 PM 1/7/2016 +0000, Barry Cannon wrote: >Hello, > I am curious how many people use the koha-dump script to dump > their Koha database? I am particularly interested to know how people are > dealing with larger databases where mysqldump causes table locking. We looked into this (not sure what your definition of "larger database" is -- our dumps are around 670 Megs) and decided to go the hardware route using solid state drives for both the db and the backups. End result, we have less than 4 seconds "downtime" for a daily cron-job which (so far, touch wood) has not given rise to any complaints. Note that we "clean" the db before dumping, specifically sessions (with the spiders we seem to get >100,000 / 24 hours despite blocking Baidu at the firewall) and imports (our cataloguers can get very busy) -- the mail, logs (we retain only 10 days) and zebraqueue appear to be less substantial -- which saves between 50 and 100 Megs. Best -- Paul >I would typically suggest using the '--single-transaction' option but I >notice there is still one table, pending_offline_operations, that isn't >using InnoDB as it's storage engine. Best practice is to use 'lock-tables' >in this instance (mysqldump locks by default) but with a larger database >this can cause a non responsive interface for the duration of the backup. >We have sites where the one system is used by users in different time >zones, this causes a headache as "out-of-hours" doesn't exist. > >LVM snapshots would be useful but prove tricky when using hosting providers. > >Any suggestions would be welcome! > >Regards >Barry Cannon >Interleaf Technology >http://www.interleaf.ie >Tel: +35312865855 >Email: b...@interleaf.ie >Skype: bar.cannon >_______________________________________________ >Koha mailing list http://koha-community.org >Koha@lists.katipo.co.nz >https://lists.katipo.co.nz/mailman/listinfo/koha --- Maritime heritage and history, preservation and conservation, research and education through the written word and the arts. <http://NavalMarineArchive.com> and <http://UltraMarine.ca> _______________________________________________ Koha mailing list http://koha-community.org Koha@lists.katipo.co.nz https://lists.katipo.co.nz/mailman/listinfo/koha