Date: Wed, 26 May 2004 21:12:06 +0800 From: David Xu <davidxu@freebsd.org> To: Daniel Eischen <eischen@vigrid.com> Cc: freebsd-threads@freebsd.org Subject: Re: Why is MySQL nearly twice as fast on Linux? Message-ID: <40B497A6.6040307@freebsd.org> In-Reply-To: <Pine.GSO.4.10.10405260008490.4290-100000@pcnet5.pcnet.com> References: <Pine.GSO.4.10.10405260008490.4290-100000@pcnet5.pcnet.com>
next in thread | previous in thread | raw e-mail | index | archive | help
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 > > >
Want to link to this message? Use this URL: <https://mail-archive.FreeBSD.org/cgi/mid.cgi?40B497A6.6040307>