Re: Postgresql Configutation and overflow

On Thu, Dec 28, 2006 at 10:35:29PM -0500, Dave Cramer wrote:
> start with 25% of your 12G as shared buffers, and 75% of 12G for
> effective cache

I'm curious... why leave 3G for the kernel? Seems like overkill...

Granted, as long as you're in the ballpark on effective_cache_size
that's all that matters...

> You can go higher for shared buffers, but only do so with testing.
>
> Dave
> >
> >Features:
> >
> >- 4 Processsors Intel Xeon Dual 3.0Ghz
> >- 12 GB RAM
> >- 2 discos en RAID 1 for OS
> >- 4 discs RAID 5 for DB
> >- S.O Slackware 11.0 Linux 2.6.17.7
> >- Postgres 8.1.4
> >
> >
> >=====In internet i found this:
> >
> >Tuning PostgreSQL for performance
> >2 Some basic parameters
> >2.1 Shared buffers
> >
> ># Start at 4MB (512) for a workstation
> ># Medium size data set and 256-512MB available RAM: 16-32MB
> >(2048-4096)
> ># Large dataset and lots of available RAM (1-4GB): 64-256MB
> >(8192-32768)
> >======
> >
> >
> >My postgresql.conf configuration is:
> >
> >#---------------------------------------------------------------------
> >------
> ># FILE LOCATIONS
> >#---------------------------------------------------------------------
> >------
> >
> ># The default values of these variables are driven from the -D
> >command line
> ># switch or PGDATA environment variable, represented here as
> >ConfigDir.
> >
> >#data_directory = 'ConfigDir' # use data in another directory
> >#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication
> >file
> >#ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file
> >
> ># If external_pid_file is not explicitly set, no extra pid file is
> >written.
> >#external_pid_file = '(none)' # write an extra pid file
> >
> >
> >#---------------------------------------------------------------------
> >------
> ># CONNECTIONS AND AUTHENTICATION
> >#---------------------------------------------------------------------
> >------
> >
> ># - Connection Settings -
> >
> >listen_addresses = '*' # what IP address(es) to listen on;
> > # comma-separated list of addresses;
> > # defaults to 'localhost', '*' = all
> >port = 5432
> >max_connections = 3000
> ># note: increasing max_connections costs ~400 bytes of shared
> >memory per
> ># connection slot, plus lock space (see
> >max_locks_per_transaction). You
> ># might also need to raise shared_buffers to support more connections.
> >#superuser_reserved_connections = 2
> >#unix_socket_directory = ''
> >#unix_socket_group = ''
> >#unix_socket_permissions = 0777 # octal
> >#bonjour_name = '' # defaults to the computer name
> >
> >
> >
> >#---------------------------------------------------------------------
> >------
> ># RESOURCE USAGE (except WAL)
> >#---------------------------------------------------------------------
> >------
> >
> ># - Memory -
> >
> >
> >shared_buffers = 81920 # min 16 or max_connections*2,
> >8KB each
> >temp_buffers = 5000 # min 100, 8KB each
> >max_prepared_transactions = 1000 # can be 0 or more
> >
> ># note: increasing max_prepared_transactions costs ~600 bytes of
> >shared memory
> >
> ># per transaction slot, plus lock space (see
> >max_locks_per_transaction).
> >work_mem = 10240 # min 64, size in KB
> >maintenance_work_mem = 253952 # min 1024, size in KB
> >max_stack_depth = 4096 # min 100, size in KB
> >
> ># - Free Space Map -
> >
> >#max_fsm_pages = 20000 # min max_fsm_relations*16, 6
> >bytes each
> >#max_fsm_relations = 1000 # min 100, ~70 bytes each
> >
> ># - Kernel Resource Usage -
> >
> >#max_files_per_process = 1000 # min 25
> >#preload_libraries = ''
> >
> ># - Cost-Based Vacuum Delay -
> >
> >#vacuum_cost_delay = 0 # 0-1000 milliseconds
> >#vacuum_cost_page_hit = 1 # 0-10000 credits
> >#vacuum_cost_page_miss = 10 # 0-10000 credits
> >#vacuum_cost_page_dirty = 20 # 0-10000 credits
> >#vacuum_cost_limit = 200 # 0-10000 credits
> >
> ># - Background writer -
> >
> >#bgwriter_delay = 200 # 10-10000 milliseconds between
> >rounds
> >#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/
> >round
> >#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
> >#bgwriter_all_percent = 0.333 # 0-100% of all buffers
> >scanned/round
> >#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
> >
> >
> >#---------------------------------------------------------------------
> >------
> ># WRITE AHEAD LOG
> >#---------------------------------------------------------------------
> >------
> >
> ># - Settings -
> >
> >#fsync = on # turns forced synchronization on or off
> >#wal_sync_method = fsync # the default is the first option
> > # supported by the operating system:
> > # open_datasync
> > # fdatasync
> > # fsync
> > # fsync_writethrough
> > # open_sync
> >#full_page_writes = on # recover from partial page writes
> >#wal_buffers = 8 # min 4, 8KB each
> >#commit_delay = 0 # range 0-100000, in microseconds
> >#commit_siblings = 5 # range 1-1000
> >
> ># - Checkpoints -
> >
> >checkpoint_segments = 20 # in logfile segments, min 1, 16MB
> >each
> >#checkpoint_timeout = 300 # range 30-3600, in seconds
> >#checkpoint_warning = 30 # in seconds, 0 is off
> >
> ># - Archiving -
> >
> >#archive_command = '' # command to use to archive a logfile
> > # segment
> >
> >
> >#---------------------------------------------------------------------
> >------
> ># QUERY TUNING
> >#---------------------------------------------------------------------
> >------
> >
> ># - Planner Method Configuration -
> >
> >#enable_bitmapscan = on
> >#enable_hashagg = on
> >#enable_hashjoin = on
> >#enable_indexscan = on
> >#enable_mergejoin = on
> >enable_nestloop = off
> >enable_seqscan = off
> >#enable_sort = on
> >#enable_tidscan = on
> >
> ># - Planner Cost Constants -
> >
> >effective_cache_size = 65536 # typically 8KB each
> >#random_page_cost = 4 # units are one sequential page fetch
> > # cost
> >#cpu_tuple_cost = 0.01 # (same)
> >#cpu_index_tuple_cost = 0.001 # (same)
> >#cpu_operator_cost = 0.0025 # (same)
> >
> >
> >the sysctl.conf
> >
> >kernel.shmmax = 970170573
> >kernel.shmall = 970170573
> >kernel.sem = 400 42000 32 1024
> >vm.overcommit_memory = 2
> >
> >=========The configuration is correct?=======
> >
> >If you can help me i will be pleased, thanks.
> >
>

--
Jim Nasby jim(at)nasby(dot)net
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)