From owner-freebsd-stable@FreeBSD.ORG Sat Jun 18 01:56:00 2005 Return-Path: X-Original-To: freebsd-stable@freebsd.org Delivered-To: freebsd-stable@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 926F016A41C for ; Sat, 18 Jun 2005 01:56:00 +0000 (GMT) (envelope-from PeterJeremy@optushome.com.au) Received: from mail24.syd.optusnet.com.au (mail24.syd.optusnet.com.au [211.29.133.165]) by mx1.FreeBSD.org (Postfix) with ESMTP id 0C11643D4C for ; Sat, 18 Jun 2005 01:55:59 +0000 (GMT) (envelope-from PeterJeremy@optushome.com.au) Received: from cirb503493.alcatel.com.au (c220-239-8-51.belrs4.nsw.optusnet.com.au [220.239.8.51]) by mail24.syd.optusnet.com.au (8.12.11/8.12.11) with ESMTP id j5I1tZI6005460 (version=TLSv1/SSLv3 cipher=EDH-RSA-DES-CBC3-SHA bits=168 verify=NO); Sat, 18 Jun 2005 11:55:36 +1000 Received: from cirb503493.alcatel.com.au (localhost.alcatel.com.au [127.0.0.1]) by cirb503493.alcatel.com.au (8.12.10/8.12.10) with ESMTP id j5I1tZRx062429; Sat, 18 Jun 2005 11:55:35 +1000 (EST) (envelope-from pjeremy@cirb503493.alcatel.com.au) Received: (from pjeremy@localhost) by cirb503493.alcatel.com.au (8.12.10/8.12.9/Submit) id j5I1tZ4o062428; Sat, 18 Jun 2005 11:55:35 +1000 (EST) (envelope-from pjeremy) Date: Sat, 18 Jun 2005 11:55:35 +1000 From: Peter Jeremy To: David Sze Message-ID: <20050618015535.GI50157@cirb503493.alcatel.com.au> References: <6.2.1.2.2.20050617103807.058c6fa8@mail.distrust.net> Mime-Version: 1.0 Content-Type: text/plain; charset=us-ascii Content-Disposition: inline In-Reply-To: <6.2.1.2.2.20050617103807.058c6fa8@mail.distrust.net> User-Agent: Mutt/1.4.2i Cc: freebsd-stable@freebsd.org Subject: Re: FreeBSD MySQL still WAY slower than Linux X-BeenThere: freebsd-stable@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Production branch of FreeBSD source code List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Sat, 18 Jun 2005 01:56:00 -0000 On Fri, 2005-Jun-17 10:38:34 -0400, David Sze wrote: >It turns out that the problem was the same thing everyone usually points >the finger at, but no one actually mentioned this time: Linux mounts its >partitions async by default. This shouldn't be an issue here. The FreeBSD default has always been that data is written asynchronously (see below) and there should be virtually no metadata updates in a database application. If an application needs control over when the data is physically written to the disk (eg a database server), it needs to use fsync(2) and/or msync(2) calls. If Linux (or FreeBSD) isn't complying with fsync(2) or msync(2) semantics then it has a serious problem. >super-smack select-key > 5.4-RELEASE ~20,000 queries/second > 6.0-CURRENT ~24,000 queries/second > CentOS w/async ~36,000 queries/second > CentOS w/sync ~26,000 queries/second I can't see where this benchmark is doing any writes so I'd like to see an explanation of the difference in CentOS performance figures before making any decision on CentOS vs FreeBSD. >super-smack update-select > 5.4-RELEASE ~4,000 queries/second > 6.0-CURRENT ~4,500 queries/second > CentOS w/async ~7,500 queries/second > CentOS w/sync ~750 queries/second > >That last CentOS number is not a typo, it was an order of magnitude >slower. That's a surprising difference - I wouldn't have expected it to be so large. A couple of people have mentioned the impact of journalling. Journalling improves performance by converting time-critical random I/Os into time-critical sequential I/Os and background random I/Os - and sequential I/O is many orders of magnitude faster than random I/O. All transactional databases do their own journalling (REDO logs). As long as the database correctly issues filesystem synchronisation commands and the filesystem correctly implements them, database application, a journalling filesystem provides no benefits. A more interesting test would be a client that issues a series of updates to a server and, immediately after receiving the acknowledge for the last update turns, off the server's power. After the server is rebooted, confirm that all the updates have been applied correctly. Filesystem I/O behaviour: Data Metadata Mount type async async async async sync 'traditional' UFS async async[*] UFS + softupdates sync sync sync 'Metadata' covers directory updates and some inode updates [*] softupdates controls write ordering to ensure on-disk consistency. -- Peter Jeremy