Re: Postgresql Configutation and overflow

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.