Re: Postgresql Configutation and overflow

["Adam Rich" <adam(dot)r(at)sbcglobal(dot)net>]

Title: Message

What are your table sizes?  What are your queries like?  (Mostly read, mostly write?)

Can you post the "analyze" output for some of the slow queries? 

 

The three things that stand out for me is your disk configuration (RAID 5 is not ideal for databases,

you really want RAID 1 or 1+0) and also that you have enable_seqscan set to off.   I would leave

that turned on.    Lastly, your effective_cache_size looks low.  Your OS is probably caching more

than 512 MB, I know mine is usually 1-2 GB and I don't have 12 GB of ram available.

 

-----Original Message-----
From: pgsql-performance-owner(at)postgresql(dot)org [mailto:pgsql-performance-owner(at)postgresql(dot)org] On Behalf Of fabrix peñuelas
Sent: Thursday, December 28, 2006 7:58 PM
To: pgsql-performance(at)postgresql(dot)org
Subject: [PERFORM] Postgresql Configutation and overflow

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

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.

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.