From: | "Tomas Vondra" <tv(at)fuzzy(dot)cz> |
---|---|
To: | "Robert James" <srobertjames(at)gmail(dot)com> |
Cc: | "Postgres General" <pgsql-general(at)postgresql(dot)org> |
Subject: | Re: Memory tuning Postgres |
Date: | 2012-02-15 14:31:45 |
Message-ID: | 0c3f9471be136617904dcc4a063fe1bc.squirrel@sq.gransy.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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.
From | Date | Subject | |
---|---|---|---|
Next Message | Tom Lane | 2012-02-15 15:18:02 | Re: Postgresql 9.0.6 backends pruning process environment? |
Previous Message | Bruce Momjian | 2012-02-15 14:28:02 | Re: Rules of Thumb for Autovaccum |