Thread: 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
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)
--
Shoaib Mir
EnterpriseDB (www.enterprisedb.com)
On 2/14/07, 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
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:
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
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
On 2/15/07, Josh Berkus <josh@agliodbs.com> wrote:
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