From: Tom Lane <tgl@sss.pgh.pa.us>
To: Jeff Janes <jeff.janes@gmail.com>
Cc: Nick Eubank <nickeubank@gmail.com>
Cc: pgsql-performance@postgresql.org <pgsql-performance@postgresql.org>
Subject: Re: Tuning Postgres for Single connection use
Date: Tue, 15 Apr 2014 13:05:34 -0400
Message-ID: <30687.1397581534@sss.pgh.pa.us> (raw)
In-Reply-To: <CAMkU=1xf-bFze=tZvhXL853HeZ1VEKhyZv_F+GhFAC=MV89NTQ@mail.gmail.com>
References: <CAFWQgOmjM=KWxBwhK+gZtc6WeFKCYCPjAOg8P=wVs6buzQMoPQ@mail.gmail.com>
<CAMkU=1y40-aNTsoSYUfxF4z0MYmiSFYhk5YO4q6Kfqx2i3D+nA@mail.gmail.com>
<CAFWQgOndJji8WriY6_s_4HGQHVJDCntM_F5On81AO7aMjkTN_g@mail.gmail.com>
<CAMkU=1xf-bFze=tZvhXL853HeZ1VEKhyZv_F+GhFAC=MV89NTQ@mail.gmail.com>
List-Unsubscribe: <mailto:majordomo@postgresql.org?body=unsub%20pgsql-performance>
Jeff Janes <jeff.janes@gmail.com> writes:
> On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank <nickeubank@gmail.com> 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 I
>> tweak something else accordingly?
> What version are you using? What is the exact line you put in your config
> file? Did you get any errors when using that config file? Are you sure
> you actually reloaded the server, so that it reread the config file, rather
> than just changing the file and then not applying the change?
> I usually set work_mem within a psql connection, in which case you need to
> quote the setting if you use units:
> set work_mem="3GB";
FWIW, it's generally considered a seriously *bad* idea to set work_mem as
high as 1GB in postgresql.conf: you're promising that each query running
on the server can use 1GB per sort or hash step. You probably don't have
the machine resources to honor that promise. (If you do, I'd like to have
your IT budget ;-)) Recommended practice is to keep the global setting
conservatively small, and bump it up locally in your session (with SET)
for individual queries that need the very large value.
But having said that, Postgres doesn't try to enforce any such practice.
My money is on what Jeff is evidently thinking: you forgot to do "pg_ctl
reload", or else the setting is too large for your platform, in which case
there should have been a complaint in the postmaster log. As noted
elsewhere, the limit for Windows is a hair under 2GB even if it's 64-bit
Windows.
regards, tom lane
--
Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-performance
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: tgl@sss.pgh.pa.us, jeff.janes@gmail.com, nickeubank@gmail.com
Subject: Re: Tuning Postgres for Single connection use
In-Reply-To: <30687.1397581534@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox