X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (mx1.hub.org [200.46.208.251]) by postgresql.org (Postfix) with ESMTP id D29229FA36E for ; Mon, 1 May 2006 14:43:29 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.208.251]) (amavisd-new, port 10024) with ESMTP id 07315-03 for ; Mon, 1 May 2006 14:43:20 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id A2E649FA2DB for ; Mon, 1 May 2006 14:43:20 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.13.6/8.13.6) with ESMTP id k41HhHc9006880; Mon, 1 May 2006 13:43:17 -0400 (EDT) To: "Steve Woodcock" cc: "Scott Sipe" , pgsql-performance@postgresql.org Subject: Re: Super-smack? In-reply-to: <5882.1146496519@sss.pgh.pa.us> References: <92F10BE1-B193-4EEA-877A-9260B55C2844@mindspring.com> <5882.1146496519@sss.pgh.pa.us> Comments: In-reply-to Tom Lane message dated "Mon, 01 May 2006 11:15:19 -0400" Date: Mon, 01 May 2006 13:43:16 -0400 Message-ID: <6879.1146505396@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200605/9 X-Sequence-Number: 18796 I wrote: > FWIW, my own experiments with tests like this suggest that PG is at > worst about 2x slower than mysql for trivial queries. If you'd reported > a result in that ballpark I'd have accepted it as probably real. 6x I > don't believe though ... Just for amusement's sake, I tried compiling up super-smack on my own machine, and got results roughly in line with what I would've expected. Machine: dual Xeon EM64T, forget the clock rate at the moment, running Fedora Core 4 (kernel 2.6.15-1.1831_FC4smp); hyperthreading enabled Postgres: fairly recent CVS tip, no special build options except --enable-debug, no changes to default runtime configuration options MySQL: 5.0.18, current Red Hat RPMs, no changes to default configuration The "select" test, with 1 and 10 clients: $ super-smack -d pg select-key.smack 1 10000 Query Barrel Report for client smacker1 connect: max=0ms min=-1ms avg= 3ms from 1 clients Query_type num_queries max_time min_time q_per_s select_index 20000 0 0 3655.24 $ super-smack -d pg select-key.smack 10 10000 Query Barrel Report for client smacker1 connect: max=54ms min=4ms avg= 12ms from 10 clients Query_type num_queries max_time min_time q_per_s select_index 200000 0 0 7431.20 $ super-smack -d mysql select-key.smack 1 10000 Query Barrel Report for client smacker1 connect: max=0ms min=-1ms avg= 0ms from 1 clients Query_type num_queries max_time min_time q_per_s select_index 20000 0 0 6894.03 $ super-smack -d mysql select-key.smack 10 10000 Query Barrel Report for client smacker1 connect: max=14ms min=0ms avg= 5ms from 10 clients Query_type num_queries max_time min_time q_per_s select_index 200000 0 0 16798.05 The "update" test, with 1 and 10 clients: $ super-smack -d pg update-select.smack 1 10000 Query Barrel Report for client smacker connect: max=0ms min=-1ms avg= 4ms from 1 clients Query_type num_queries max_time min_time q_per_s select_index 10000 0 0 1027.49 update_index 10000 0 0 1027.49 $ super-smack -d pg update-select.smack 10 10000 Query Barrel Report for client smacker connect: max=13ms min=5ms avg= 8ms from 10 clients Query_type num_queries max_time min_time q_per_s select_index 100000 1 0 1020.96 update_index 100000 28 0 1020.96 The above is with fsync on (though I think this machine's disk lies about write complete so I'd not trust it as production). With fsync off, $ super-smack -d pg update-select.smack 1 10000 Query Barrel Report for client smacker connect: max=0ms min=-1ms avg= 3ms from 1 clients Query_type num_queries max_time min_time q_per_s select_index 10000 0 0 1478.25 update_index 10000 0 0 1478.25 $ super-smack -d pg update-select.smack 10 10000 Query Barrel Report for client smacker connect: max=35ms min=5ms avg= 21ms from 10 clients Query_type num_queries max_time min_time q_per_s select_index 100000 1 0 3067.68 update_index 100000 1 0 3067.68 versus mysql $ super-smack -d mysql update-select.smack 1 10000 Query Barrel Report for client smacker connect: max=0ms min=-1ms avg= 0ms from 1 clients Query_type num_queries max_time min_time q_per_s select_index 10000 0 0 4101.43 update_index 10000 0 0 4101.43 $ super-smack -d mysql update-select.smack 10 10000 Query Barrel Report for client smacker connect: max=3ms min=0ms avg= 0ms from 10 clients Query_type num_queries max_time min_time q_per_s select_index 100000 1 0 5388.31 update_index 100000 6 0 5388.31 Since mysql is using myisam tables (ie not transaction safe), I think the fairest comparison is to the fsync-off numbers. regards, tom lane