Thanks in advance for anything you can do to help.
The real issue is this, we have THE SAME queries taking anywhere from .001 - 90.0 seconds... the server is using 98% of the available RAM at all times (because of the persistant connections via php), and I don't know what to do. Every time I change a .conf setting I feel like it slows it down even more.... and I can't seem to find the balance. I'll give you everything I've got, and I hope to god someone can point out some areas where I could improve the speed of queries overall.
For months I've been optimizing my queries, and working around places that I don't need them. They are so incredibly honed, I couldn't even begin to explain.... and when there are less than 10 users browsing our sites, they are LIGHTENING fast... even with 5x the amount of dummy data in the database(s)... so basically the LARGEST factor in this whole performance issue that I can find is the number of users browsing the sites at all times... but lowering shared_buffers to raise max_connections is hurting performance immensley... so I"m totally lost.... please help!! Bless you!
THE DETAILS:
(for the databases, i'll list only the 'main' tables... as the others are fairly small)
Database 1:
5000 'users'
20,000 'threads'
500,000 'posts'
...
Database 2: (just starting out)
150 'users'
150 'entries'
...
Hardware : Pentium 4 2.44ghz 1.5gb RAM 7200rpm SATA
Software: Redhat Linux (kernel v. 2.4.21-9.EL) Postgresql 7.4.2 PHP 4.3.6 (using persistant connections to pgsql)
Usage:
uptime: 12:23:08 up 132 days, 19:16, 2 users, load average: 19.75, 17.34, 18.86
roughly 100-200 users connected to our server at any given moment
roughly 10-15 queries per HTTP page load
----------------------------------------------------------
# ----------------------------- # 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. # # 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".
#--------------------------------------------------------------------------- # CONNECTIONS AND AUTHENTICATION #---------------------------------------------------------------------------
# - Connection Settings -
tcpip_socket = true
max_connections = 75
# 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
port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = '' # what interface to listen on; defaults to any
#rendezvous_name = '' # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds ssl = true password_encryption = true #krb_server_keyfile = '' #db_user_namespace = false
#--------------------------------------------------------------------------- # RESOURCE USAGE (except WAL) #---------------------------------------------------------------------------
# - Memory -
shared_buffers = 8192 # min 16, at least max_connections*2, 8KB each
sort_mem = 8192 # min 64, size in KB
vacuum_mem = 4096 # min 1024, 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 = 3052 # min 25 #preload_libraries = ''
#--------------------------------------------------------------------------- # 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 = 192 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each #checkpoint_timeout = 300 # range 30-3600, in seconds #checkpoint_warning = 30 # 0 is off, in seconds #commit_delay = 0 # range 0-100000, in microseconds #commit_siblings = 5 # range 1-1000
#--------------------------------------------------------------------------- # QUERY TUNING #---------------------------------------------------------------------------
# - Planner Method Enabling -
#enable_hashagg = true #enable_hashjoin = true #enable_indexscan = true #enable_mergejoin = true #enable_nestloop = true enable_seqscan = false #enable_sort = true #enable_tidscan = true
# - Planner Cost Constants -
effective_cache_size = 131072 # typically 8KB each
random_page_cost = 2 # units are one sequential page fetch cost
cpu_tuple_cost = .01 # (same) default .01 cpu_index_tuple_cost = .001 # (same) default .001 cpu_operator_cost = 0.0025 # (same) default .0025
# - Genetic Query Optimizer -
geqo = true
geqo_threshold = 20
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#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 #---------------------------------------------------------------------------
# - Syslog -
#syslog = 0 # range 0-2; 0=stdout; 1=both; 2=syslog #syslog_facility = 'LOCAL0' #syslog_ident = 'postgres'
# - When to Log -
client_min_messages = error # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, errorlog_min_messages = error # 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 = panic # Values in order of increasing severity:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, panic(off)
log_min_duration_statement = -1 # Log all statements whose
# execution time exceeds the value, in
# milliseconds. Zero prints all queries.
# Minus-one disables.#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
debug_print_parse = false debug_print_rewritten = false debug_print_plan = false debug_pretty_print = false log_connections = false log_duration = false log_pid = false log_statement = false log_timestamp = false log_hostname = false log_source_port = false
#--------------------------------------------------------------------------- # RUNTIME STATISTICS #---------------------------------------------------------------------------
# - Statistics Monitoring -
log_parser_stats = false log_planner_stats = false log_executor_stats = false log_statement_stats = false
# - Query/Index Statistics Collector -
stats_start_collector = false stats_command_string = false stats_block_level = false stats_row_level = false stats_reset_on_server_start = false
#--------------------------------------------------------------------------- # CLIENT CONNECTION DEFAULTS #---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names #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 may be changed
lc_messages = 'en_US.UTF-8' # locale for system error message strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# - Other Defaults -
explain_pretty_print = true #dynamic_library_path = '$libdir' #max_expr_depth = 10000 # min 10
#--------------------------------------------------------------------------- # LOCK MANAGEMENT #---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds #max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#--------------------------------------------------------------------------- # VERSION/PLATFORM COMPATIBILITY #---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true regex_flavor = advanced # advanced, extended, or basic #sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
------
I've been tweaking the postgresql.conf file for about 5 hours... just today. We've had problems in the past (and I've also emailed this list in the past, but perhaps I failed to ask the right questions)....
I guess I need some help with the postgresql configuration file. I would like to start off by asking that you not link me to the same basic .CONF overview, as what I really need at this point is real-world experience and wisdom, as opposed to cold, poorly documented, and incredibly abstract (trial-and-error) type manual entries.
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
