Need for speed 2

From: Ulrich Wisser <ulrich(dot)wisser(at)relevanttraffic(dot)se>
To: pgsql-performance(at)postgresql(dot)org
Subject: Need for speed 2
Date: 2005-08-25 07:10:37
Message-ID: 430D6EED.10603@relevanttraffic.se
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-performance

Hello,

I realize I need to be much more specific. Here is a more detailed
description of my hardware and system design.

Pentium 4 2.4GHz
Memory 4x DIMM DDR 1GB PC3200 400MHZ CAS3, KVR
Motherboard chipset 'I865G', two IDE channels on board
2x SEAGATE BARRACUDA 7200.7 80GB 7200RPM ATA/100
(software raid 1, system, swap, pg_xlog)
ADAPTEC SCSI RAID 2100S ULTRA160 32MB 1-CHANNEL
2x SEAGATE CHEETAH 15K.3 73GB ULTRA320 68-PIN WIDE
(raid 1, /var/lib/pgsql)

Database size on disc is 22GB. (without pg_xlog)

Please find my postgresql.conf below.

Putting pg_xlog on the IDE drives gave about 10% performance
improvement. Would faster disks give more performance?

What my application does:

Every five minutes a new logfile will be imported. Depending on the
source of the request it will be imported in one of three "raw click"
tables. (data from two months back, to be able to verify customer complains)
For reporting I have a set of tables. These contain data from the last
two years. My app deletes all entries from today and reinserts updated
data calculated from the raw data tables.

The queries contain no joins only aggregates. I have several indexes to
speed different kinds of queries.

My problems occur when one users does a report that contains to much old
data. In that case all cache mechanisms will fail and disc io is the
limiting factor.

If one query contains so much data, that a full table scan is needed, I
do not care if it takes two minutes to answer. But all other queries
with less data (at the same time) still have to be fast.

I can not stop users doing that kind of reporting. :(

I need more speed in orders of magnitude. Will more disks / more memory
do that trick?

Money is of course a limiting factor but it doesn't have to be real cheap.

Ulrich

# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------

# - Connection Settings -

tcpip_socket = true
max_connections = 100
# 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 = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false

#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------

# - Memory -

shared_buffers = 20000 # min 16, at least max_connections*2,
8KB each
sort_mem = 4096 # min 64, size in KB
vacuum_mem = 8192 # min 1024, size in KB

# - Free Space Map -

max_fsm_pages = 200000 # min max_fsm_relations*16, 6 bytes each
max_fsm_relations = 10000 # min 100, ~50 bytes each

# - Kernel Resource Usage -

#max_files_per_process = 1000 # min 25
#preload_libraries = ''

#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

fsync = false # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or
open_datasync
wal_buffers = 128 # min 4, 8KB each

# - Checkpoints -

checkpoint_segments = 16 # 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 = true
#enable_sort = true
#enable_tidscan = true

# - Planner Cost Constants -

#effective_cache_size = 1000 # 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)

# - Genetic Query Optimizer -

#geqo = true
#geqo_threshold = 11
#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 = 2 # range 0-2; 0=stdout; 1=both; 2=syslog
syslog_facility = 'LOCAL0'
syslog_ident = 'postgres'

# - When to Log -

client_min_messages = info # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, error

log_min_messages = info # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log,
fatal,
# panic

log_error_verbosity = verbose # terse, default, or verbose messages

log_min_error_statement = info # Values in order of increasing severity:
# debug5, debug4, debug3, debug2,
debug1,
# info, notice, warning, error,
panic(off)

log_min_duration_statement = 1000 # 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 = true
#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 = true
#stats_command_string = false
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true

#---------------------------------------------------------------------------
# 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' # locale for system error message strings
lc_monetary = 'en_US' # locale for monetary formatting
lc_numeric = 'en_US' # locale for number formatting
lc_time = 'en_US' # 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

In response to

Responses

Browse pgsql-performance by date

  From Date Subject
Next Message Jens-Wolfhard Schicke 2005-08-25 07:18:22 Re: Some ideas for comment
Previous Message mark 2005-08-25 00:21:05 Re: Caching by Postgres