Re: Tuning Postgres for Single connection use

From: pgsql-performance-owner@postgresql.org [mailto:pgsql-performance-owner@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, 2014 at 5:19 PM, Jeff Janes <jeff.janes@gmail.com> wrote:
On Mon, Apr 14, 2014 at 2:46 PM, Nick Eubank <nickeubank@gmail.com> wrote:
Any rules of thumb for work_mem, maintenance_work_mem, shared_buffer, etc. for a database that DOESN'T anticipate
concurrentconnections and that is doing lots of aggregate functions on large tables? All the advice I can find online
ontuning (this, this, this etc.) is written for people anticipating lots of concurrent connections.
 
I'm a social scientist looking to use Postgres not as a database to be shared by multiple users, but rather as my own
toolfor manipulating a massive data set (I have 5 billion transaction records (600gb in csv) and want to pull out
uniqueuser pairs, estimate aggregates for individual users, etc.). This also means almost no writing, except to
creationof new tables based on selections from the main table. 
 
I'm on a Windows 8 VM with 16gb ram, SCSI VMware HD, and 3 cores if that's important.

I'd go with a small shared_buffers, like 128MB, and let the OS cache as much as possible.  This minimizes the amount of
doublebuffering.
 

And set work_mem to about 6GB, then bump it up if that doesn't seem to cause problems.

In the scenario you describe, it is probably no big deal if you guess too high.  Monitor the process, if it it starts
togo nuts just kill it and start again with a lower work_mem.  If it is a single user system, you can afford to be
adventurous.

If you need to build indexes, you should bump up maintenance_work_mem, but I just would do that in the local session
notsystem wide.
 

Cheers,

Jeff
 


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
valuesjust don't change in "SHOW ALL" -- integer constraint?). Is there a work around, or should I tweak something else
accordingly? 

Thanks!

Nick

(Properly bottom posted this time?) 

[Schnabel, Robert D.] 

Nick,

I asked the same question a while ago about work_mem on Windows.  See this thread:
http://www.postgresql.org/message-id/17895.1315869622@sss.pgh.pa.us

Bob