Re: Postgresql Configutation and overflow - Mailing list pgsql-performance

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