From owner-freebsd-database@FreeBSD.ORG Mon Sep 25 19:37:59 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 CE29816A403 for ; Mon, 25 Sep 2006 19:37:59 +0000 (UTC) (envelope-from glenn@mail.more.net) Received: from libby.lpmo.org (libby.lpmo.org [216.106.2.138]) by mx1.FreeBSD.org (Postfix) with ESMTP id 7E8E343D68 for ; Mon, 25 Sep 2006 19:37:59 +0000 (GMT) (envelope-from glenn@mail.more.net) Received: from gkar.earthdome.org (81.mnetw.more.net [207.160.138.81]) by libby.lpmo.org (Postfix) with ESMTP id 367D12841F for ; Mon, 25 Sep 2006 14:29:02 -0500 (CDT) Received: by gkar.earthdome.org (Postfix, from userid 503) id 03C237D339D; Mon, 25 Sep 2006 14:35:56 -0500 (CDT) Date: Mon, 25 Sep 2006 14:35:56 -0500 From: Glenn Nielsen To: freebsd-database@FreeBSD.ORG Message-ID: <20060925193556.GK3812@gkar.earthdome.org> References: <20060915152405.GD8776@gkar.earthdome.org> <200609181119.k8IBJPJl092421@lurza.secnetix.de> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <200609181119.k8IBJPJl092421@lurza.secnetix.de> User-Agent: Mutt/1.4.2.2i Cc: Subject: Re: MySQL and FS softupdates X-BeenThere: freebsd-database@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Database use and development under FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Mon, 25 Sep 2006 19:38:00 -0000 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 | */ | ----------------------------------------------------------------------