From owner-freebsd-performance@FreeBSD.ORG Tue Jun 27 02:58:36 2006 Return-Path: X-Original-To: freebsd-performance@freebsd.org Delivered-To: freebsd-performance@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id E284016A400 for ; Tue, 27 Jun 2006 02:58:35 +0000 (UTC) (envelope-from anderson@centtech.com) Received: from mh1.centtech.com (moat3.centtech.com [207.200.51.50]) by mx1.FreeBSD.org (Postfix) with ESMTP id 8E99443D6A for ; Tue, 27 Jun 2006 02:58:35 +0000 (GMT) (envelope-from anderson@centtech.com) Received: from [192.168.42.24] (andersonbox4.centtech.com [192.168.42.24]) by mh1.centtech.com (8.13.1/8.13.1) with ESMTP id k5R2wYdT063052; Mon, 26 Jun 2006 21:58:34 -0500 (CDT) (envelope-from anderson@centtech.com) Message-ID: <44A09EE7.1030405@centtech.com> Date: Mon, 26 Jun 2006 21:58:47 -0500 From: Eric Anderson User-Agent: Thunderbird 1.5.0.4 (X11/20060612) MIME-Version: 1.0 To: leo huang References: <14a4a8480606261918q39b51f7bkd69958c5a7b05021@mail.gmail.com> In-Reply-To: <14a4a8480606261918q39b51f7bkd69958c5a7b05021@mail.gmail.com> Content-Type: text/plain; charset=ISO-8859-1; format=flowed Content-Transfer-Encoding: 8bit X-Virus-Scanned: ClamAV 0.87.1/1564/Mon Jun 26 09:55:16 2006 on mh1.centtech.com X-Virus-Status: Clean Cc: freebsd-performance@freebsd.org Subject: Re: Is the fsync() fake on FreeBSD6.1? X-BeenThere: freebsd-performance@freebsd.org X-Mailman-Version: 2.1.5 Precedence: list List-Id: Performance/tuning List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Tue, 27 Jun 2006 02:58:36 -0000 leo huang wrote: > Hi, > > I benchmarked MySQL 4.1.18 on FreeBSD 6.1 and Debian 3.1 using Super Smack > 1.3 some days ago. > > The benchmark table is > CREATE TABLE `Account` ( > `aid` int(11) NOT NULL auto_increment, > `name` char(20) NOT NULL default '', > `flag` int(11) NOT NULL default '0', > `uidcount` int(11) NOT NULL default '0', > `balance` int(11) NOT NULL default '0', > `point` int(11) NOT NULL default '0', > `blocktm` int(11) NOT NULL default '0', > `ipnum` int(10) unsigned default NULL, > `newdate` datetime default NULL, > PRIMARY KEY (`aid`), > UNIQUE KEY `name` (`name`) > ) ENGINE=InnoDB DEFAULT CHARSET=latin1; > > And it has 10,000,000 rows. > > The SQL statement is > update Account set balance= balance + 1 where aid=?; > > The result is followed: > OS Clients Result(queries per second) TPS(got > from iostat) > FreeBSD6.1 50 516.1 > about 2000 > Debian3.1 50 49.8 > about 200 > > The result surprise me. The MySQL Performance on FreeBSD6.1 is about 10 > times of on Debian3.1,and the output of iostat also shows it. > > I know that MySQL uses fsync() to flush both the data and log files at > default when using innodb engine( > http://dev.mysql.com/doc/refman/4.1/en/innodb-parameters.html). Our > evaluating computer only has a 10000RPM SCSI hard disk. I think it can do > about 200 sequential fsync() calls per second if the fsync() is real. > > Is the fsync() on FreeBSD6.1 fake? I mean than the data is only written to > the drives memory and so can be lost if power goes down. And how I can > confirm this? > > If the fsync() is fake, how can I get the real fsync? > > Any comment is welcome! > > PS: > 1. Our evaluating computer is DELL PowerEdge 1650?Its hardware > configuration > is followed: > CPU: 2 * Intel Pentium III 1.33GHz 512KB Level 2 Cache(smp) > Memory: 1024MB ECC SDRAM > HD: SEAGATE ST336706LC(36GB Ultra160 SCSI 10000RPM) > NIC : Intel(R) PRO/1000 Network Connection > > 2. Some important parameters in MySQL configuration file are here: > log-bin > sync_binlog=1 > innodb_safe_binlog > innodb_buffer_pool_size = 384M > innodb_additional_mem_pool_size = 20M > innodb_log_file_size = 100M > innodb_log_buffer_size = 8M > innodb_flush_log_at_trx_commit = 1 > innodb_lock_wait_timeout = 50 Hi Leo, I think we've all received this message twice now, however if you are resending because you didn't get any responses, it's probably because most people on this list are here to discuss performance, and while this is related, it is really a filesystem question, and so many people here just won't know the answer for you. You might get a better response on the freebsd-fs@ list. Anyway, there are a lot of variables here, but it could be softupdates. Have you tried turning softupdates off on the filesystem you are running this on, and/or enabling the 'sync' option to the filesystem? Also, you didn't mention anything about which filesystems these were using on both occasions. Eric -- ------------------------------------------------------------------------ Eric Anderson Sr. Systems Administrator Centaur Technology Anything that works is better than anything that doesn't. ------------------------------------------------------------------------