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@xxxxxxxxx EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
