Thread: PG defaults and performance (was Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum))

On Sat, Nov 10, 2012 at 12:26 PM, Steve Crawford
<scrawford@pinpointresearch.com> wrote:
> Don't do that. Defaults are good for ensuring that PostgreSQL will start on
> the widest reasonable variety of systems. They are *terrible* for
> performance and are certainly wrong for the system you describe.

Tuning a PostgreSQL database is a major science, but is there a
reasonably easy way to get a stable baseline for comparison? We've
been exploring different hosting options recently, and one thing we
want to know is how well Postgres will perform. To that end, we've
been using pgbench on a default configuration Postgres, on the
expectation that that'll at least be consistent (that is, if a Cloud
Host A instance does X tps and Cloud Host B does 2*X, then we can
expect host B to deliver roughly double performance in production).
How valid is this assumption? Broadly, or totally not?

ChrisA

On Fri, Nov 9, 2012 at 6:17 PM, Chris Angelico <rosuav@gmail.com> wrote:
> On Sat, Nov 10, 2012 at 12:26 PM, Steve Crawford
> <scrawford@pinpointresearch.com> wrote:
>> Don't do that. Defaults are good for ensuring that PostgreSQL will start on
>> the widest reasonable variety of systems. They are *terrible* for
>> performance and are certainly wrong for the system you describe.
>
> Tuning a PostgreSQL database is a major science, but is there a
> reasonably easy way to get a stable baseline for comparison? We've
> been exploring different hosting options recently, and one thing we
> want to know is how well Postgres will perform. To that end, we've
> been using pgbench on a default configuration Postgres, on the
> expectation that that'll at least be consistent (that is, if a Cloud
> Host A instance does X tps and Cloud Host B does 2*X, then we can
> expect host B to deliver roughly double performance in production).
> How valid is this assumption? Broadly, or totally not?

Totally not.  With default settings and default pgbench, the easiest
way for host B to beat host A is by lying about the durability of
fsync.

Cheers,

Jeff

On Sun, Nov 11, 2012 at 8:05 AM, Jeff Janes <jeff.janes@gmail.com> wrote:
> Totally not.  With default settings and default pgbench, the easiest
> way for host B to beat host A is by lying about the durability of
> fsync.

True. Without the ability to brutally cut the power to a cloud
instance or other remote (and in some cases possibly virtualized)
server, it's practically impossible to test that. We're basically
relying on replication and hoping that three instances (master and two
slaves) don't go down simultaneously, which is hardly a guarantee.

ChrisA