[PERFORM] Tuning Postgres for Single connection use

2014-04-15 Thread Nick Eubank
> > > On Tuesday, April 15, 2014, Tom Lane wrote: > Jeff Janes writes: > > On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank > wrote: > >> Quick followup Jeff: it seems that I can't set work_mem above about 1gb > >> (can't get to 2gb. When I update config, the values just don't change in > >> "SHOW

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-15 Thread Tom Lane
Jeff Janes writes: > On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank wrote: >> Quick followup Jeff: it seems that I can't set work_mem above about 1gb >> (can't get to 2gb. When I update config, the values just don't change in >> "SHOW ALL" -- integer constraint?). Is there a work around, or should

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-15 Thread Schnabel, Robert D.
From: pgsql-performance-ow...@postgresql.org [mailto:pgsql-performance-ow...@postgresql.org] On Behalf Of Nick Eubank Sent: Tuesday, April 15, 2014 11:12 AM To: Jeff Janes Cc: pgsql-performance@postgresql.org Subject: Re: [PERFORM] Tuning Postgres for Single connection use On Mon, Apr 14

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-15 Thread Jeff Janes
On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank wrote: > On Mon, Apr 14, 2014 at 5:19 PM, Jeff Janes wrote: > >> >> I'd go with a small shared_buffers, like 128MB, and let the OS cache as >> much as possible. This minimizes the amount of double buffering. >> >> And set work_mem to about 6GB, then

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-15 Thread Nick Eubank
On Mon, Apr 14, 2014 at 5:19 PM, Jeff Janes wrote: > On Mon, Apr 14, 2014 at 2:46 PM, Nick Eubank wrote: > >> Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, >> etc. for a database that DOESN'T anticipate concurrent connections and that >> is doing lots of aggregate functio

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-14 Thread Nick Eubank
Terrific -- thanks Gavin and Jeff! That's incredibly helpful for a n00b like me! On Mon, Apr 14, 2014 at 5:29 PM, Gavin Flower wrote: > In this list, please bottom post! > > I've added potentially useful advice below. > > > On 15/04/14 11:39, Nick Eubank wrote: > > Thanks Gavin -- would LOVE t

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-14 Thread Gavin Flower
In this list, please bottom post! I've added potentially useful advice below. On 15/04/14 11:39, Nick Eubank wrote: Thanks Gavin -- would LOVE to. Sadly I'm in a weird situation where my hardware is not under my control, so I'm stuck making the best of what I have. Next time though! :) On Mo

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-14 Thread Jeff Janes
On Mon, Apr 14, 2014 at 2:46 PM, Nick Eubank wrote: > Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, > etc. for a database that DOESN'T anticipate concurrent connections and that > is doing lots of aggregate functions on large tables? All the advice I > can find online on t

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-14 Thread Andrew Dunstan
On 04/14/2014 05:46 PM, Nick Eubank wrote: Any rules of thumb for |work_mem|, |maintenance_work_mem|, |shared_buffer|, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-14 Thread Nick Eubank
Thanks Gavin -- would LOVE to. Sadly I'm in a weird situation where my hardware is not under my control, so I'm stuck making the best of what I have. Next time though! :) On Monday, April 14, 2014, Gavin Flower wrote: > On 15/04/14 09:46, Nick Eubank wrote: > > Any rules of thumb for work_mem,

Re: [PERFORM] Tuning Postgres for Single connection use

2014-04-14 Thread Gavin Flower
On 15/04/14 09:46, Nick Eubank wrote: Any rules of thumb for |work_mem|, |maintenance_work_mem|, |shared_buffer|, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning (this

[PERFORM] Tuning Postgres for Single connection use

2014-04-14 Thread Nick Eubank
Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate concurrent connections and that is doing lots of aggregate functions on large tables? All the advice I can find online on tuning (this