From owner-freebsd-threads@FreeBSD.ORG Tue May 25 21:27:56 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 2B87A16A4CE for ; Tue, 25 May 2004 21:27:56 -0700 (PDT) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mx1.FreeBSD.org (Postfix) with ESMTP id B7AC243D41 for ; Tue, 25 May 2004 21:27:55 -0700 (PDT) (envelope-from eischen@vigrid.com) Received: from mail.pcnet.com (mail.pcnet.com [204.213.232.4]) by mail.pcnet.com (8.12.10/8.12.1) with ESMTP id i4Q4Rk8Q009670; Wed, 26 May 2004 00:27:47 -0400 (EDT) Date: Wed, 26 May 2004 00:27:46 -0400 (EDT) From: Daniel Eischen X-Sender: eischen@pcnet5.pcnet.com To: Chris Elsworth In-Reply-To: <20040524155705.GA39441@shagged.org> Message-ID: MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII 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 04:27:56 -0000 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. 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 -- Dan Eischen