Обсуждение: Benchmarking PGSQL?

Hello All,
I'm a performance engineer, quite interested in getting deep into the PGSQL performance enhancement effort. In that regard, I have the following questions :
1. Is there a benchmarking setup, that I can access online?
2. What benchmarks are we running , for performance numbers?
3. What are the current issues, related to performance?
4. Where can I start, with the PGSQL performance effort?

Thanks a lot,
Krishna

Have you tried pgbench yet?

--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)

Here’s one:

Insert performance is limited to about 10-12 MB/s no matter how fast the underlying I/O hardware.  Bypassing the WAL (write ahead log) only boosts this to perhaps 20 MB/s.  We’ve found that the biggest time consumer in the profile is the collection of routines that “convert to datum”.

You can perform the test using any dataset, you might consider using the TPC-H benchmark kit with a data generator available at www.tpc.org.  Just generate some data, load the schema, then perform some COPY statements, INSERT INTO SELECT FROM and CREATE TABLE AS SELECT.

- Luke


On 2/14/07 2:00 AM, "Krishna Kumar" <kumar.ramanathan@gmail.com> wrote:

Hello All,
I'm a performance engineer, quite interested in getting deep into the PGSQL performance enhancement effort. In that regard, I have the following questions :
1. Is there a benchmarking setup, that I can access online?
2. What benchmarks are we running , for performance numbers?
3. What are the current issues, related to performance?
4. Where can I start, with the PGSQL performance effort?

Thanks a lot,
Krishna

On 2/14/07, Luke Lonergan <llonergan@greenplum.com> wrote:
>
>  Here's one:
>
>  Insert performance is limited to about 10-12 MB/s no matter how fast the
> underlying I/O hardware.  Bypassing the WAL (write ahead log) only boosts
> this to perhaps 20 MB/s.  We've found that the biggest time consumer in the
> profile is the collection of routines that "convert to datum".
>
>  You can perform the test using any dataset, you might consider using the
> TPC-H benchmark kit with a data generator available at www.tpc.org.  Just
> generate some data, load the schema, then perform some COPY statements,
> INSERT INTO SELECT FROM and CREATE TABLE AS SELECT.

I am curious what is your take on the maximum insert performance, in
mb/sec of large bytea columns (toasted), and how much if any greenplum
was able to advance this over the baseline.  I am asking on behalf of
another interested party.  Interested in numbers broken down per core
on 8 core quad system and also aggreate.

merlin

Hi Merlin,

On 2/14/07 8:20 AM, "Merlin Moncure" <mmoncure@gmail.com> wrote:

> I am curious what is your take on the maximum insert performance, in
> mb/sec of large bytea columns (toasted), and how much if any greenplum
> was able to advance this over the baseline.  I am asking on behalf of
> another interested party.  Interested in numbers broken down per core
> on 8 core quad system and also aggreate.

Our approach is to attach a segment to each core, so we scale INSERT
linearly on number of cores.  So the per core limit we live with is the
10-20MB/s observed here.  We'd like to improve that so that we get better
performance with smaller machines.

We have demonstrated insert performance of 670 MB/s, 2.4TB/hour for
non-toasted columns using 3 load machines against 120 cores.  This rate was
load machine limited.

WRT toasted bytea columns we haven't done any real benchmarking of those.
Do you have a canned benchmark we can run?

- Luke

Krisna,

> I'm a performance engineer, quite interested in getting deep into the
> PGSQL performance enhancement effort. In that regard, I have the
> following questions :
> 1. Is there a benchmarking setup, that I can access online?
> 2. What benchmarks are we running , for performance numbers?
> 3. What are the current issues, related to performance?
> 4. Where can I start, with the PGSQL performance effort?

Hey, I work for Sun and we've been working on PostgreSQL & benchmarks.
Hopefully we will soon have a server which runs Spec benchmarks which the
community can legally use for testing (it's waiting on some setup issues).

Help we could use right now includes work on an open source TPCE-like
workload, being run by Rilson and Mark Wong.   Another issue which could
use help is reducing our WAL transaction log volume; it's higher than any
other database.   Or you could work on multi-processor scalability; we are
still trying to identify the bottlenecks which are holding us back there.

--
--Josh

Josh Berkus
PostgreSQL @ Sun
San Francisco

On 2/14/07, Luke Lonergan <llonergan@greenplum.com> wrote:
> Hi Merlin,
>
> On 2/14/07 8:20 AM, "Merlin Moncure" <mmoncure@gmail.com> wrote:
>
> > I am curious what is your take on the maximum insert performance, in
> > mb/sec of large bytea columns (toasted), and how much if any greenplum
> > was able to advance this over the baseline.  I am asking on behalf of
> > another interested party.  Interested in numbers broken down per core
> > on 8 core quad system and also aggreate.
>
> Our approach is to attach a segment to each core, so we scale INSERT
> linearly on number of cores.  So the per core limit we live with is the
> 10-20MB/s observed here.  We'd like to improve that so that we get better
> performance with smaller machines.
>
> We have demonstrated insert performance of 670 MB/s, 2.4TB/hour for
> non-toasted columns using 3 load machines against 120 cores.  This rate was
> load machine limited.
>
> WRT toasted bytea columns we haven't done any real benchmarking of those.
> Do you have a canned benchmark we can run?

Interested in how fast you can insert binary objects (images, files,
etc). into the database as a file storage system.  Ultimately the
insertions would all be done via libpq ExecPrepared/Params.  A simple
benchmark such as insert a 1mb object via pg_bench over 20 or so
connections would be fine.  Mostly interested in raw throughput per
core and especially interested if you can beat stock pg on the same
hardware.

merlin

Thanks for the tips, Josh. Would you know of where I can find the TpC-E effort online? I've looked, and have only found references to the 'summer of code' project that Riklas was doing/mentoring.

Also, I'm going to spend some time digging into the code, for the WAL log issue you mentioned. Let's see what I can come up with... (I must confess I'm not a Guru with Database internals, but I'll give this my best...)

Krishna