Обсуждение: Memory tuning Postgres
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?
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.
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?