From owner-freebsd-threads@FreeBSD.ORG Wed May 26 06:14:20 2004 Return-Path: Delivered-To: freebsd-threads@freebsd.org Received: from mx1.FreeBSD.org (mx1.freebsd.org [216.136.204.125]) by hub.freebsd.org (Postfix) with ESMTP id 228B916A4CE for ; Wed, 26 May 2004 06:14:20 -0700 (PDT) Received: from freefall.freebsd.org (freefall.freebsd.org [216.136.204.21]) by mx1.FreeBSD.org (Postfix) with ESMTP id 0184843D1D; Wed, 26 May 2004 06:14:20 -0700 (PDT) (envelope-from davidxu@freebsd.org) Received: from freebsd.org (davidxu@localhost [127.0.0.1]) i4QDDdYQ056079; Wed, 26 May 2004 06:13:41 -0700 (PDT) (envelope-from davidxu@freebsd.org) Message-ID: <40B497A6.6040307@freebsd.org> Date: Wed, 26 May 2004 21:12:06 +0800 From: David Xu User-Agent: Mozilla/5.0 (X11; U; FreeBSD i386; en-US; rv:1.6) Gecko/20040522 X-Accept-Language: en-us, en MIME-Version: 1.0 To: Daniel Eischen References: In-Reply-To: Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? X-BeenThere: freebsd-threads@freebsd.org X-Mailman-Version: 2.1.1 Precedence: list List-Id: Threading on FreeBSD List-Unsubscribe: , List-Archive: List-Post: List-Help: List-Subscribe: , X-List-Received-Date: Wed, 26 May 2004 13:14:20 -0000 Daniel Eischen wrote: >On Mon, 24 May 2004, Chris Elsworth wrote: > > > >>On Mon, May 24, 2004 at 11:51:27AM -0400, Daniel Eischen wrote: >> >> >>>On Mon, 24 May 2004, Chris Elsworth wrote: >>> >>> >>>>So, each of the smackfiles used below was changed to have sql_no_cache >>>>in the select. The _i smackfile simply has type=innodb in the create >>>>table statement. >>>> >>>> >>>I'm not a DB person; can you post a diff to the smackfile? >>> >>> > >Thanks. It wasn't clean; I had to also make mods to the smack >files to point to /tmp/mysql.sock instead of /var/lib/mysql/mysql.sock. >The source also needed tweaking (s/__FreeBSD/__FreeBSD__/ in >src/client.cc). You also have to create /var/smack-data/. > >Here's what I get on a dual 450MHz Xeon PIII (options >WITNESS and WITNESS_SKIPSPIN): > >Run: super-smack ../smacks/select-key.smack 4 10000 > >DB_type Query_type num_queries max_time min_time q_per_s >----------------------------------------------------------------- >myisam select_index 80000 4 2 348.56 >innodb select_index 80000 29 2 296.63 > >Run: super-smack ../smacks/update-select.smack 4 10000 > >DB_type Query_type num_queries max_time min_time q_per_s >----------------------------------------------------------------- >myisam select_index 40000 24 2 169.30 > update_index 40000 8 3 169.30 >innodb select_index 40000 5 2 164.64 > update_index 40000 11 1 164.64 > >I don't see much of a difference between myisam and innodb; the >former even seems to be faster for the select-key.smack test. > > Innodb still wins big because it supports transaction and logs everything. it is a bit out of topic... >I'm using my-large.cnf settings in /etc/my.cnf (I have 512MB RAM): > >[client] >port = 3306 >socket = /tmp/mysql.sock > >[mysqld] >port = 3306 >socket = /tmp/mysql.sock >skip-locking >key_buffer = 256M >max_allowed_packet = 1M >table_cache = 256 >sort_buffer_size = 1M >read_buffer_size = 1M >myisam_sort_buffer_size = 64M >thread_cache = 8 >query_cache_size= 16M >thread_concurrency = 8 >log-bin >server-id = 1 >innodb_flush_log_at_trx_commit = 0 > >[mysqldump] >quick >max_allowed_packet = 16M > >[mysql] >no-auto-rehash > >[isamchk] >key_buffer = 128M >sort_buffer_size = 128M >read_buffer = 2M >write_buffer = 2M > >[myisamchk] >key_buffer = 128M >sort_buffer_size = 128M >read_buffer = 2M >write_buffer = 2M > >[mysqlhotcopy] >interactive-timeout > > >