About a year ago we decided to migrate our central database that powers various
intranet tools from MySQL to PostgreSQL. We have about 130 tables and about
10GB of data that stores various status information for a variety of services
for our intranet. We generally have somewhere between 150-200 connections to
the database at any given time and probably anywhere between 5-10 new
connections being made every second and about 100 queries per second. Most
of the queries and transactions are very small due to the fact that the tools
were designed to work around the small functionality of MySQL 3.23 DB.
Our company primarily uses FreeBSD and we are stuck on FreeBSD 4.X series due
to IT support issues, but I believe I may be able to get more performance out
of our server by reconfiguring and setting up the postgresql.conf file up
better. The performance is not as good as I was hoping at the moment and
it seems as if the database is not making use of the available ram.
snapshot of active server:
last pid: 5788; load averages: 0.32, 0.31, 0.28 up 127+15:16:08 13:59:24
169 processes: 1 running, 168 sleeping
CPU states: 5.4% user, 0.0% nice, 9.9% system, 0.0% interrupt, 84.7% idle
Mem: 181M Active, 2632M Inact, 329M Wired, 179M Cache, 199M Buf, 81M Free
Swap: 4096M Total, 216K Used, 4096M Free
PID USERNAME PRI NICE SIZE RES STATE C TIME WCPU CPU COMMAND
14501 pgsql 2 0 254M 242M select 2 76:26 1.95% 1.95% postgre
5720 root 28 0 2164K 1360K CPU0 0 0:00 1.84% 0.88% top
5785 pgsql 2 0 255M 29296K sbwait 0 0:00 3.00% 0.15% postgre
5782 pgsql 2 0 255M 11900K sbwait 0 0:00 3.00% 0.15% postgre
5772 pgsql 2 0 255M 11708K sbwait 2 0:00 1.54% 0.15% postgre
Here is my current configuration:
Dual Xeon 3.06Ghz 4GB RAM
Adaptec 2200S 48MB cache & 4 disks configured in RAID5
FreeBSD 4.11 w/kernel options:
options SHMMAXPGS=65536
options SEMMNI=256
options SEMMNS=512
options SEMUME=256
options SEMMNU=256
options SMP # Symmetric MultiProcessor Kernel
options APIC_IO # Symmetric (APIC) I/O
The OS is installed on the local single disk and postgres data directory
is on the RAID5 partition. Maybe Adaptec 2200S RAID5 performance is not as
good as the vendor claimed. It was my impression that the raid controller
these days are optimized for RAID5 and going RAID10 would not benefit me much.
Also, I may be overlooking a postgresql.conf setting. I have attached the
config file.
In summary, my questions:
1. Would running PG on FreeBSD 5.x or 6.x or Linux improve performance?
2. Should I change SCSI controller config to use RAID 10 instead of 5?
3. Why isn't postgres using all 4GB of ram for at least caching table for reads?
4. Are there any other settings in the conf file I could try to tweak?
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the postmaster.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload". Some settings, such as listen_address, require
# a postmaster shutdown and restart to take effect.
#---------------------------------------------------------------------------
# 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' # the host-based authentication file
# ident_file = 'ConfigDir/pg_ident.conf' # the 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 interface(s) to listen on;
# defaults to localhost, '*' = any
port = 5432
max_connections = 450
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
superuser_reserved_connections = 2
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#rendezvous_name = '' # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 30000 # min 16, at least max_connections*2, 8KB each
#shared_buffers = 40000 # min 16, at least max_connections*2, 8KB each
work_mem = 16384 # min 64, size in KB
maintenance_work_mem = 256000 # min 1024, size in KB
max_stack_depth = 16384 # 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, ~50 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_percent = 1 # 0-100% of dirty buffers in each round
#bgwriter_maxpages = 100 # 0-1000 buffers max per round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = true # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or open_datasync
#wal_buffers = 8 # min 4, 8KB each
#commit_delay = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_segments = 12 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
checkpoint_timeout = 600 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
checkpoint_warning = 60 # 0 is off, in seconds
# - Archiving -
#archive_command = '' # command to use to archive a logfile segment
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 300000 # typically 8KB each
random_page_cost = 2.5 # 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)
# - Genetic Query Optimizer -
#geqo = true
#geqo_threshold = 12
#geqo_effort = 5 # range 1-10
#geqo_pool_size = 0 # selects default based on effort
#geqo_generations = 0 # selects default based on effort
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'stderr' # Valid values are combinations of stderr,
# syslog and eventlog, depending on
# platform.
# This is relevant when logging to stderr:
#redirect_stderr = false # Enable capturing of stderr into log files.
# These are only relevant if redirect_stderr is true:
log_directory = 'pg_log' # Directory where log files are written.
# May be specified absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
log_filename = 'postgresql-%Y%m%d.log' # Log file name pattern.
# May include strftime() escapes
log_truncate_on_rotation = false # If true, any existing log file of the
# same name as the new log file will be truncated
# rather than appended to. But such truncation
# only occurs on time-driven rotation,
# not on restarts or size-driven rotation.
# Default is false, meaning append to existing
# files in all cases.
log_rotation_age = 0 # Automatic rotation of logfiles will happen after
# so many minutes. 0 to disable.
log_rotation_size = 500000 # Automatic rotation of logfiles will happen after
# so many kilobytes of log output. 0 to disable.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
#client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, notice, warning, error
#log_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log, fatal,
# panic
#log_error_verbosity = default # terse, default, or verbose messages
log_min_error_statement = notice # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)
log_min_duration_statement = 2000 # -1 is disabled, in milliseconds.
#silent_mode = false # DO NOT USE without syslog or redirect_stderr
# - What to Log -
#debug_print_parse = true
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = true
#log_disconnections = false
#log_duration = false
log_line_prefix = '%t %u %d %r ' # e.g. '<%u%%%d> '
# %u=user name %d=database name
# %r=remote host and port
# %p=PID %t=timestamp %i=command tag
# %c=session id %l=session line number
# %s=session start timestamp %x=transaction id
# %q=stop here in non-session processes
# %%='%'
#log_statement = 'all' # none, mod, ddl, all
log_hostname = true
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false
# - Query/Index Statistics Collector -
# defaults, kenji turned it on 2005 Aug 2
stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
stats_row_level = true
stats_reset_on_server_start = true
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#default_tablespace = '' # a tablespace name, or '' for default
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they might be changed
lc_messages = 'C' # locale for system error message strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~200*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
#default_with_oids = true
# - Other Platforms & Clients -
#transform_null_equals = false
