[Koha-devel] incremental backup of the DB

[Paul A]

At 12:15 PM 11/19/2015 +1300, Robin Sheat wrote:
>Paul A schreef op wo 18-11-2015 om 17:49 [-0500]:
> > Very interesting, can you share more details?
>
>Not really, I didn't implement it.
>
>But essentially it provides an offsite database replicant that can then
>be backed up using snapshotting or whatever. I think regularly dumping a
>running database would not be best practice, as it puts a significant
>load on the server, and also may lock certain actions (don't quote me on
>that though.)

Robin -- first and foremost, my best wishes for your move and new job, I'm 
sure that everyone here will miss your enthusiasm, experience and wisdom.

As to the intricacies of backing up a MySQL database, we're probably going 
outside the "Koha envelope" but Mark T. and David C. both echoed your 
concern on "load on the server."  I am of the opinion that this is purely 
hardware related, and server investment should reflect in-production use 
plus a suitable margin. We HAProxy two servers, each with 8-core CPUs and 
16G RAM; the swap files have never been written to. The "system", all Koha 
files and MySQL + InnoDB are on raided solid state drives.

As I wrote originally, the Koha routines to "clean" the db (especially 
sessions, unused Z39-50 data etc more than a day old) plus the dump takes 
~2 seconds. I'm fairly certain that the lock time on the db is less than 
this (as the lock is released before the write happens -- I think as soon 
as dump has read binlog, but I'd have to immerse myself again in the 
intricacies, possibly of flag --single-transaction.)

So, what we see in about 4 years of production practice is (a) that we have 
never found any backup corruption, (b) our staff and cataloguers have never 
noticed any slow-downs that substantially differ from normal 
LAN|router|cable latency, and (c) even under extreme conditions (Bing, 
Google and Baidu once hit between them 186,000 requests/hour) the limiting 
factor is never MySQL but always Zebra (with or without ZEBRAOPTIONS "-T" 
flag.)

Hoping this response wasn't too long, and again, best wishes for your future...

Paul

---
Maritime heritage and history, preservation and conservation,
research and education through the written word and the arts.
<http://NavalMarineArchive.com> and <http://UltraMarine.ca>