Re: Postgresql Configutation and overflow

Hi,

On 28-Dec-06, at 8:58 PM, fabrix peñuelas wrote:

> Good day,
>
> I have been reading about the configuration of postgresql, but I
> have a server who does not give me the performance that should. The
> tables are indexed and made vacuum regularly, i monitor with top,
> ps and pg_stat_activity and when i checked was slow without a heavy
> load overage.
>
> Before, the server reached 2000 connections to postgresql (with
> max_connections=3000 in it for future workflow).
Why would you need 2000 connections ?
>
> I divided the load with another server for better performance, and
> now reach 500 connections, but yet is overflow.
>
>
> My question is about how much memory should i configure in
> shared_buffers and effective_cache_size.

start with 25% of your 12G as shared buffers, and 75% of 12G for
effective cache

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.
>