Re: when to checkpoint?

[Buchan Milne <bgmilne@staff.telkomsa.net>]

On Wednesday 20 February 2008 21:00:42 Thomas Ledbetter wrote:
>   So.. Ive been reading up hardcore on Berkeley database administration
> regarding how to do archival and recovery, and its pretty cool stuff.
>
>   Yet I have the following questions:
>
>   Database archival will be done on one of our master servers, so its
> not necessary to keep alot of old transaction logfiles on the replicas,
> but in order to clean out them out, a periodic checkpoint is necessary.
>
>   Initially, I was thinking that we should do this once per day, off
> hours,

How would you ensure that the checkpoint occurs in off-hours ?

> but would it make more sense to do this more frequently over the 
> course of the day to keep the checkpoint process less expensive per
> iteration?

IMHO, yes.

>   What kinds of metrics should be used to determine how frequently this
> should be done?

This would depend on the frequency of changes, and how long you can afford to 
have database recovery run for. I usually go with something around 5-10 
minutes.

>   Second, would there be that great of a performance gain when disabling
> transaction logging altogether?

Yes, but if you ever have an unclean shutdown, you may have to reload/resync 
from scratch. But, how much performance do you need, and how much are you 
getting now?

>   If I have two master servers keeping, say, a week's worth of
> transaction logs, a sound archival process, and a backup LDIF, would it
> make sense to just disable transaction logging altogether across the
> replicas?

If you can afford taking a slave down for a re-import or re-sync, maybe. 
However, I would sacrifice a bit of performance to avoid this myself.

>   Third, I now understand how to do archival and recovery using the
> Berkeley tools, but is there a 'best practice' document out there
> anywhere from someone who utilizes this method that I could take a look
> at to make sure Im not missing anything?

Well, I wrote these a long time ago, and while they worked fine when testing 
them for what I needed, I haven't needed them for anything (besides removing 
the old transaction logs):

http://svn.mandriva.com/cgi-bin/viewvc.cgi/packages/cooker/openldap/current/SOURCES/ldap-common?view=log
http://svn.mandriva.com/cgi-bin/viewvc.cgi/packages/cooker/openldap/current/SOURCES/ldap-hot-db-backup?view=log

Regards,
Buchan