Date: Mon, 25 Sep 2006 14:35:56 -0500 From: Glenn Nielsen <glenn@mail.more.net> To: freebsd-database@FreeBSD.ORG Subject: Re: MySQL and FS softupdates Message-ID: <20060925193556.GK3812@gkar.earthdome.org> In-Reply-To: <200609181119.k8IBJPJl092421@lurza.secnetix.de> References: <20060915152405.GD8776@gkar.earthdome.org> <200609181119.k8IBJPJl092421@lurza.secnetix.de>
next in thread | previous in thread | raw e-mail | index | archive | help
Thanks for the thorough overview of softupdates and databases. Based on our use case and the problem we saw with soft updates we have disabled them on the partition where mysql data and binary logs are stored. Regards, Glenn On Mon, Sep 18, 2006 at 01:19:25PM +0200, Oliver Fromme wrote: > Glenn Nielsen wrote: > > I recently had a server running a MySQL master db lose power. When > > the server came back up the mysql binary log used for replication > > was missing the last 7-8 minutes of database changes. > > > > FS softupdates are being used. > > > > Are softupdates a good thing, a bad thing, or make no difference > > for recovery of a mysql database after a server failure? > > Whether soft-updates "good" or "bad" depends on your > requirements. > > Softupdates ensures that the file system (to be exact: its > meta data) is always in a consistent state, modulo write- > caching on the drives (which should be turned off). As a > side effect it makes the file system faster, because write > accesses are buffered and reordered. On the other hand, > turning the drives' write caches off lowers the performance > noticeably. > > However, because data is buffered, you can lose some of it > that hasn't been written to disk when the machine crashes. > Normally the write delay should be no more than 30 seconds, > but it's possible that a longer backlog of writes builds up > if there's high I/O load. But: A database should issue > fsync() calls, and soft-updates should honour them, unless > configured otherwise. > > There problem is that you will always have to trade speed > for reliability. You cannot have the best of both (at least > with a single machine). > > Another thing to consider is the fact that soft-updates is > not able to ensure consistency of application data, such as > SQL databases, because soft-updates doesn't know anything > about the inner structure and format of the database files, > of course. My personal experience is that PostgreSQL is > much more robust against such damage. It detects the un- > clean shutdown, replays the necessary WAL entries, and is > online pretty quickly. With mysql I've witnessed several > pretty bad cases where a database was not recoverable. > > Another way to prevent damage is to setup a read-only slave > to which all transactions are replicated. If the master > crashes, the slave database will still be in a consistent > state, because it commits only complete transactions. You > can then make the slave to a read/write master and continue > database services, while you rebuild the old master and > configure it to become the new backup slave. > > It might also be worth a try to use the gjournal feature > that has recently been committed to FreeBSD current and > 6-stable. With the journal on a separate disk, it should > be as fast as soft-updates or even faster, and any complete > transaction in the journal will be replayed after a crash, > so it should be pretty reliable (and recovery is _much_ > faster because no fsck is required at all). > > Best regards > Oliver > > -- > Oliver Fromme, secnetix GmbH & Co. KG, Marktplatz 29, 85567 Grafing > Dienstleistungen mit Schwerpunkt FreeBSD: http://www.secnetix.de/bsd > Any opinions expressed in this message may be personal to the author > and may not necessarily reflect the opinions of secnetix in any way. > > "Being really good at C++ is like being really good > at using rocks to sharpen sticks." > -- Thant Tessman ---------------------------------------------------------------------- Glenn Nielsen glenn@more.net | /* Spelin donut madder | MOREnet System Programming | * if iz ina coment. | Missouri Research and Education Network | */ | ----------------------------------------------------------------------
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?20060925193556.GK3812>