Date: Mon, 18 Sep 2006 13:19:25 +0200 (CEST) From: Oliver Fromme <olli@lurza.secnetix.de> To: freebsd-database@FreeBSD.ORG, glenn@mail.more.net Subject: Re: MySQL and FS softupdates Message-ID: <200609181119.k8IBJPJl092421@lurza.secnetix.de> In-Reply-To: <20060915152405.GD8776@gkar.earthdome.org>
next in thread | previous in thread | raw e-mail | index | archive | help
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
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?200609181119.k8IBJPJl092421>