Re: Tuning Postgres for Single connection use

Поиск
Список
Период
Сортировка
От Jeff Janes
Тема Re: Tuning Postgres for Single connection use
Дата
Msg-id CAMkU=1xf-bFze=tZvhXL853HeZ1VEKhyZv_F+GhFAC=MV89NTQ@mail.gmail.com
обсуждение исходный текст
Ответ на Re: Tuning Postgres for Single connection use  (Nick Eubank <nickeubank@gmail.com>)
Ответы Re: Tuning Postgres for Single connection use  (Tom Lane <tgl@sss.pgh.pa.us>)
Список pgsql-performance
On Tue, Apr 15, 2014 at 9:12 AM, Nick Eubank <nickeubank@gmail.com> wrote:
On Mon, Apr 14, 2014 at 5:19 PM, Jeff Janes <jeff.janes@gmail.com> 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 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 to go 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 not system 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 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";

But if you set it system wide in the config file the quotes should not be needed.
 


Thanks!

Nick

(Properly bottom posted this time?) 

Looked good to me.

Cheers,

Jeff 

В списке pgsql-performance по дате отправления:

Предыдущее
От: Nick Eubank
Дата:
Сообщение: Re: Tuning Postgres for Single connection use
Следующее
От: "Schnabel, Robert D."
Дата:
Сообщение: Re: Tuning Postgres for Single connection use