Thread: PG defaults and performance (was Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum))
PG defaults and performance (was Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum))
From
Chris Angelico
Date:
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
Re: PG defaults and performance (was Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum))
From
Jeff Janes
Date:
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
Re: PG defaults and performance (was Re: Unexpectedly high disk space usage RESOLVED (Manual reindex/vacuum))
From
Chris Angelico
Date:
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