Обсуждение: PG8.1 - Config file check
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
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
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
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
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