pg.ddx.io pgsql-performance@postgresql.org mailing list archive help / color / mirror / Atom feed From: Tom Lane <tgl@sss.pgh.pa.us>
To: Steve Woodcock <steve.woodcock@gmail.com>
Cc: Scott Sipe <cscotts@mindspring.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Super-smack?
Date: Mon, 01 May 2006 13:43:16 -0400
Message-ID: <6879.1146505396@sss.pgh.pa.us> (raw )
In-Reply-To: <5882.1146496519@sss.pgh.pa.us >
References: <92F10BE1-B193-4EEA-877A-9260B55C2844@mindspring.com >
<ff68c4ad0605010606k1f8147d8ue6b650b05958d562@mail.gmail.com >
<5882.1146496519@sss.pgh.pa.us >
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
view thread (8+ messages) latest in thread
Message-ID: <6879.1146505396@sss.pgh.pa.us>
Permalink: ../6879.1146505396@sss.pgh.pa.us/
Also on: postgresql.org/message-id/6879.1146505396@sss.pgh.pa.us
copy link · copy postgr.es
reply Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, steve.woodcock@gmail.com, cscotts@mindspring.com
Subject: Re: Super-smack?
In-Reply-To: <6879.1146505396@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox