Thread: Memory tuning Postgres

Memory tuning Postgres

From
Robert James
Date:
I have a 4 core, 4 GB server dedicated to running Postgres (only other
thing on it are monitoring, backup, and maintenance programs).  It
runs about 5 databases, backing up an app, mainly ORM queries, but
some reporting and more complicated SQL JOINs as well.

I'm currently using the out-of-the box postgresql.conf, but I get the
sense that isn't using all the resources it should.

What parameters should I change to use the server best? What are good
starting points or them? What type of performance increase should I
see?

From basic research, it seems shared_buffers should be 1G, and
effective_cache_size 3G.  Is that correct?  Can you suggest what other
params I should change, and to what?

Re: Memory tuning Postgres

From
"Tomas Vondra"
Date:
On 15 Únor 2012, 15:20, Robert James wrote:
> I have a 4 core, 4 GB server dedicated to running Postgres (only other
> thing on it are monitoring, backup, and maintenance programs).  It
> runs about 5 databases, backing up an app, mainly ORM queries, but
> some reporting and more complicated SQL JOINs as well.
>
> I'm currently using the out-of-the box postgresql.conf, but I get the
> sense that isn't using all the resources it should.
>
> What parameters should I change to use the server best? What are good
> starting points or them? What type of performance increase should I
> see?
>
> From basic research, it seems shared_buffers should be 1G, and
> effective_cache_size 3G.  Is that correct?  Can you suggest what other
> params I should change, and to what?

Actually there's a nice wiki page about tuning:

  http://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server

Yes, 1GB (25%) of RAM is generally the recommended value. But you haven't
mentioned which version of PostgreSQL is used and what is the size of the
database. Older version / small database would work fine even with smaller
shared buffers.

The other parameters that may be interesting are probably
checkpoint_segments, checkpoint_completion_target, work_mem,
maintenance_work_mem (read the wiki, it's described there).

You should also tweak the log_line_prefix (use something that works with
pgfounie), log_checkpoints/log_temp_files and maybe set reasonable value
to log_statement_min_duration.

T.


Re: Memory tuning Postgres

From
Robert James
Date:
On 2/15/12, Tomas Vondra <tv@fuzzy.cz> wrote:
> On 15 Únor 2012, 15:20, Robert James wrote:
>> What parameters should I change to use the server best? What are good
>> starting points or them? What type of performance increase should I
>> see?

...
> But you haven't
> mentioned which version of PostgreSQL is used and what is the size of the
> database.


Pg 8.3, Linux.  Each individual database when dumped to a .sql file
takes between 1 - 3 GB.

> The other parameters that may be interesting are probably
> checkpoint_segments, checkpoint_completion_target, work_mem,
> maintenance_work_mem (read the wiki, it's described there).
>
> You should also tweak the log_line_prefix (use something that works with
> pgfounie), log_checkpoints/log_temp_files and maybe set reasonable value
> to log_statement_min_duration.


Thanks.  Can you give me starting points for those for a 4 core 4 GB
Ram server with about 10 GB of data?