From owner-freebsd-database@FreeBSD.ORG Mon Sep 18 11:19:51 2006 Return-Path: X-Original-To: freebsd-database@FreeBSD.ORG Delivered-To: freebsd-database@FreeBSD.ORG Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 262E516A403 for ; Mon, 18 Sep 2006 11:19:51 +0000 (UTC) (envelope-from olli@lurza.secnetix.de) Received: from lurza.secnetix.de (lurza.secnetix.de [83.120.8.8]) by mx1.FreeBSD.org (Postfix) with ESMTP id B385243DA1 for ; Mon, 18 Sep 2006 11:19:33 +0000 (GMT) (envelope-from olli@lurza.secnetix.de) Received: from lurza.secnetix.de (mlifor@localhost [127.0.0.1]) by lurza.secnetix.de (8.13.4/8.13.4) with ESMTP id k8IBJQM8092422; Mon, 18 Sep 2006 13:19:32 +0200 (CEST) (envelope-from oliver.fromme@secnetix.de) Received: (from olli@localhost) by lurza.secnetix.de (8.13.4/8.13.1/Submit) id k8IBJPJl092421; Mon, 18 Sep 2006 13:19:25 +0200 (CEST) (envelope-from olli) Date: Mon, 18 Sep 2006 13:19:25 +0200 (CEST) Message-Id: <200609181119.k8IBJPJl092421@lurza.secnetix.de> From: Oliver Fromme To: freebsd-database@FreeBSD.ORG, glenn@mail.more.net In-Reply-To: <20060915152405.GD8776@gkar.earthdome.org> X-Newsgroups: list.freebsd-database User-Agent: tin/1.8.0-20051224 ("Ronay") (UNIX) (FreeBSD/4.11-STABLE (i386)) X-Greylist: Sender IP whitelisted, not delayed by milter-greylist-2.1.2 (lurza.secnetix.de [127.0.0.1]); Mon, 18 Sep 2006 13:19:32 +0200 (CEST) Cc: Subject: Re: MySQL and FS softupdates X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list Reply-To: freebsd-database@FreeBSD.ORG, glenn@mail.more.net List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 18 Sep 2006 11:19:51 -0000 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