From: Pierre C <lists@peufeu.com>
To: Nick Matheson <Nick.D.Matheson@noaa.gov>
Cc: pgsql-performance@postgresql.org
Subject: Re: Simple (hopefully) throughput question?
Date: Fri, 05 Nov 2010 10:05:25 +0100
Message-ID: <op.vlowvbdreorkce@apollo13> (raw)
In-Reply-To: <4CD2C640.8030303@noaa.gov>
References: <4CD1853F.2010800@noaa.gov>
<4CD19E84.7070204@squeakycode.net>
<4CD2C640.8030303@noaa.gov>
On Thu, 04 Nov 2010 15:42:08 +0100, Nick Matheson
<Nick.D.Matheson@noaa.gov> wrote:
> I think your comments really get at what our working hypothesis was, but
> given that our experience is limited compared to you all here on the
> mailing lists we really wanted to make sure we weren't missing any
> alternatives. Also the writing of custom aggregators will likely
> leverage any improvements we make to our storage throughput.
Quick test : SELECT sum(x) FROM a table with 1 INT column, 3M rows, cached
=> 244 MB/s
=> 6.7 M rows/s
Same on MySQL :
size SELECT sum(x) (cached)
postgres 107 MB 0.44 s
myisam 20 MB 0.42 s
innodb 88 MB 1.98 s
As you can see, even though myisam is much smaller (no transaction data to
store !) the aggregate performance isn't any better, and for innodb it is
much worse.
Even though pg's per-row header is large, seq scan / aggregate performance
is very good.
You can get performance in this ballpark by writing a custom aggregate in
C ; it isn't very difficult, the pg source code is clean and full of
insightful comments.
- take a look at how contrib/intagg works
- http://www.postgresql.org/files/documentation/books/aw_pgsql/node168.html
- and the pg manual of course
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: lists@peufeu.com, Nick.D.Matheson@noaa.gov
Subject: Re: Simple (hopefully) throughput question?
In-Reply-To: <op.vlowvbdreorkce@apollo13>
* 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