pg.ddx.io pgsql-admin@postgresql.org mailing list archive
help / color / mirror / Atom feedPG8.1 - Config file check
5+ messages / 3 participants
[nested] [flat]
* PG8.1 - Config file check
@ 2005-12-19 20:11 Chris Hoover <revoohc@gmail.com>
0 siblings, 2 replies; 5+ messages in thread
From: Chris Hoover @ 2005-12-19 20:11 UTC (permalink / raw)
To: pgsql-admin
We are in the process of upgrading our 7.3.4 servers to 8.1, and I
wanted to have a sanity check ran against my config file. Do you see
any issues with these configs?
Our servers are Dell 2650/2850's with dual processors and 8-12 GB memory.
postgresql.conf
listen_addresses = '*' # what IP address(es) to listen on;
max_connections = 256
shared_buffers = 32768 # min 16 or max_connections*2, 8KB each
max_prepared_transactions = 256 # can be 0 or more
work_mem = 4096 # min 64, size in KB
maintenance_work_mem = 1048576 # min 1024, size in KB
max_fsm_pages = 8000000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 5000 # min 100, ~70 bytes each
vacuum_cost_delay = 10 # 0-1000 milliseconds
vacuum_cost_limit = 1000 # 0-10000 credits
wal_buffers = 64 # min 4, 8KB each
checkpoint_segments = 256 # in logfile segments, min 1, 16MB each
checkpoint_timeout = 1800 # range 30-3600, in seconds
effective_cache_size = 766980 # typically 8KB each
random_page_cost = 2 # units are one sequential page fetch
constraint_exclusion = on
redirect_stderr = on # Enable capturing of stderr into log
log_directory = 'pg_log' # Directory where log files are written
log_truncate_on_rotation = on # If on, any existing log file
of the same
log_rotation_age = 1440 # Automatic rotation of logfiles will
log_rotation_size = 0 # Automatic rotation of logfiles will
log_min_duration_statement = 0 # -1 is disabled, 0 logs all statements
log_connections = on
log_disconnections = on
log_duration = on
log_line_prefix = '%d,%u,%p,%m,%c,%l,%s,%x,%i,' # Special values:
log_statement = 'all' # none, mod, ddl, all
stats_start_collector = on
stats_command_string = on
stats_block_level = on
stats_row_level = on
stats_reset_on_server_start = on
autovacuum = off # enable autovacuum subprocess?
lc_messages = 'C' # locale for system error message
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
add_missing_from = on
I have upped the following parameters to help speed up our
conversions, is there any issues leaving them set to these values
after our conversion to 8.1?
maintenance_work_mem to 1GB (1048576)
checkpoint_segements to 256
checkpoint_timeout to 1800 seconds (30 minutes)
Thanks,
Chris
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG8.1 - Config file check
@ 2005-12-19 20:50 Jim C. Nasby <jnasby@pervasive.com>
parent: Chris Hoover <revoohc@gmail.com>
1 sibling, 0 replies; 5+ messages in thread
From: Jim C. Nasby @ 2005-12-19 20:50 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
On Mon, Dec 19, 2005 at 03:11:04PM -0500, Chris Hoover wrote:
> I have upped the following parameters to help speed up our
> conversions, is there any issues leaving them set to these values
> after our conversion to 8.1?
> maintenance_work_mem to 1GB (1048576)
> checkpoint_segements to 256
> checkpoint_timeout to 1800 seconds (30 minutes)
During the conversion you might want to turn fsync off. AFAIK it's safe
to do that so long as you get a clean shutdown and then do a
sync; sync; sync
Don't run that way in production though...
--
Jim C. Nasby, Sr. Engineering Consultant jnasby@pervasive.com
Pervasive Software http://pervasive.com work: 512-231-6117
vcard: http://jim.nasby.net/pervasive.vcf cell: 512-569-9461
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG8.1 - Config file check
@ 2005-12-20 02:51 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Hoover <revoohc@gmail.com>
1 sibling, 1 reply; 5+ messages in thread
From: Tom Lane @ 2005-12-20 02:51 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover <revoohc@gmail.com> writes:
> We are in the process of upgrading our 7.3.4 servers to 8.1, and I
> wanted to have a sanity check ran against my config file. Do you see
> any issues with these configs?
> work_mem = 4096 # min 64, size in KB
This seems a bit low in context, although it depends heavily on what
sort of query mix you expect, which you said nada about.
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG8.1 - Config file check
@ 2005-12-20 15:12 Chris Hoover <revoohc@gmail.com>
parent: Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 5+ messages in thread
From: Chris Hoover @ 2005-12-20 15:12 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-admin
On 12/19/05, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Chris Hoover <revoohc@gmail.com> writes:
> > We are in the process of upgrading our 7.3.4 servers to 8.1, and I
> > wanted to have a sanity check ran against my config file. Do you see
> > any issues with these configs?
>
> > work_mem = 4096 # min 64, size in KB
>
> This seems a bit low in context, although it depends heavily on what
> sort of query mix you expect, which you said nada about.
>
> regards, tom lane
>
Sorry for got to mention that.
We are running a typical OLTP system with some reporting done against it.
What value would you recommend as a starting point for the work_mem.
This value has come over verbatim from the 7.3.4 installs.
Also, is it safe and/or advised to leave these at these high levels?
maintenance_work_mem to 1GB (1048576)
checkpoint_segements to 256
checkpoint_timeout to 1800 seconds (30 minutes)
Thanks,
Chris
^ permalink raw reply [nested|flat] 5+ messages in thread
* Re: PG8.1 - Config file check
@ 2005-12-20 15:35 Tom Lane <tgl@sss.pgh.pa.us>
parent: Chris Hoover <revoohc@gmail.com>
0 siblings, 0 replies; 5+ messages in thread
From: Tom Lane @ 2005-12-20 15:35 UTC (permalink / raw)
To: Chris Hoover <revoohc@gmail.com>; +Cc: pgsql-admin
Chris Hoover <revoohc@gmail.com> writes:
> We are running a typical OLTP system with some reporting done against it.
> What value would you recommend as a starting point for the work_mem.
You probably want small work_mem for the transactional sessions (4Mb
doesn't sound out of line, maybe even less), and then boost it up
locally in the reporting sessions. Some tens of meg will probably
help the reporting queries.
> Also, is it safe and/or advised to leave these at these high levels?
> checkpoint_segements to 256
> checkpoint_timeout to 1800 seconds (30 minutes)
If you don't mind a long time to recover from a crash ...
regards, tom lane
^ permalink raw reply [nested|flat] 5+ messages in thread
end of thread, other threads:[~2005-12-20 15:35 UTC | newest]
Thread overview: 5+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2005-12-19 20:11 PG8.1 - Config file check Chris Hoover <revoohc@gmail.com>
2005-12-19 20:50 ` Jim C. Nasby <jnasby@pervasive.com>
2005-12-20 02:51 ` Tom Lane <tgl@sss.pgh.pa.us>
2005-12-20 15:12 ` Chris Hoover <revoohc@gmail.com>
2005-12-20 15:35 ` Tom Lane <tgl@sss.pgh.pa.us>
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