Thread: tuning for TPC-C benchmark
Hello all,
I'm doing tests on various Database and in particular I'm running a
comparison between Oracle 10g and Postgres 8.1 on a dedicated server
with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory
and Debian GNU / Linux version 2.6.18-5. Performance is very similar up
to 30 users, but from 40 onwards with Postgres fall quickly. That is
not what happens with Oracle that comes to 600 users. Can you help me
with the tuning ?
Thanks a lot
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 = '/etc/postgresql/8.1/main/pg_hba.conf' # host-based
authentication file
ident_file = '/etc/postgresql/8.1/main/pg_ident.conf' # IDENT
configuration file
# If external_pid_file is not explicitly set, no extra pid file is
written.
external_pid_file = '/var/run/postgresql/8.1-main.pid' #
write an extra pid file
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
#listen_addresses = 'localhost' # what IP address(es) to
listen on;
# comma-
separated list of addresses;
# defaults to
'localhost', '*' = all
listen_addresses = '*'
port = 5432
max_connections = 220
# 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 = '/var/run/postgresql'
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#bonjour_name = '' # defaults to the computer
name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
ssl = true
#password_encryption = on
#db_user_namespace = off
# Kerberos
#krb_server_keyfile = ''
#krb_srvname = 'postgres'
#krb_server_hostname = '' # empty string matches any
keytab entry
#krb_caseins_users = off
# - TCP Keepalives -
# see 'man 7 tcp' for details
#tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds;
# 0 selects the
system default
#tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds;
# 0 selects
the system default
#tcp_keepalives_count = 0 # TCP_KEEPCNT;
# 0 selects
the system default
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
shared_buffers = 49152 # min 16 or max_connections*2,
8KB each, 384MB
temp_buffers = 1000 # min 100, 8KB each
max_prepared_transactions = 350 # 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 = 1024 # min 64, size in
KB
maintenance_work_mem = 524288 # min 1024, size in KB, -512
MB-
max_stack_depth = 6144 # min 100, size in KB
# - Free Space Map -
max_fsm_pages = 58000 # min max_fsm_relations*16, 6
bytes each
max_fsm_relations = 3000 # 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 = 5000 # 10-10000 milliseconds
between rounds
bgwriter_lru_percent = 0 # 0-100% of LRU buffers
scanned/round
bgwriter_lru_maxpages = 0 # 0-1000 buffers max
written/round
bgwriter_all_percent = 0 # 0-100% of all buffers
scanned/round
bgwriter_all_maxpages = 0 # 0-1000 buffers max
written/round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
fsync = off # 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 = 5 # range 0-100000, in
microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
checkpoint_segments = 100 # in logfile segments, min 1,
16MB each
checkpoint_timeout = 1800 # 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 = on
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
effective_cache_size = 196608 # 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 = on
#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
#constraint_exclusion = off
#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 used when logging to stderr:
#redirect_stderr = off # Enable capturing of stderr
into log
# files
# These are only used if redirect_stderr is on:
#log_directory = 'pg_log' # Directory where log files
are written
# Can be absolute or relative
to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name
pattern.
# Can include strftime()
escapes
#log_truncate_on_rotation = off # If on, 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
# off, meaning append to
existing files
# in all cases.
#log_rotation_age = 1440 # Automatic rotation of
logfiles will
# happen after so many
minutes. 0 to
# disable.
#log_rotation_size = 10240 # 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 = panic # Values in order of
increasing severity:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# panic(off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all
statements
# and their durations, in
milliseconds.
#silent_mode = off # DO NOT USE without syslog or
# redirect_stderr
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = off
#log_connections = off
#log_disconnections = off
#log_duration = off
log_line_prefix = '%t ' # Special values:
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = PID
# %t = timestamp (no
milliseconds)
# %m = timestamp with
milliseconds
# %i = command tag
# %c = session id
# %l = session line number
# %s = session start
timestamp
# %x = transaction id
# %q = stop here in non-
session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_statement = 'none' # none, mod, ddl, all
#log_hostname = off
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
# - Query/Index Statistics Collector -
stats_start_collector = off
stats_command_string = off
stats_block_level = off
stats_row_level = off
stats_reset_on_server_start = off
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
autovacuum = off # enable autovacuum
subprocess?
#autovacuum_naptime = 60 # time between autovacuum
runs, in secs
#autovacuum_vacuum_threshold = 1000 # min # of tuple updates
before
# vacuum
#autovacuum_analyze_threshold = 500 # min # of tuple updates
before
# analyze
#autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before
# vacuum
#autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before
# analyze
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay
for
# autovac, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit
for
# autovac, -1 means use
# vacuum_cost_limit
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#default_tablespace = '' # a tablespace name, '' uses
# the default
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#statement_timeout = 0 # 0 is disabled, in
milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ
# environment setting
#australian_timezones = off
#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 = 'it_IT.UTF-8' # locale for system
error message
# strings
lc_monetary = 'it_IT.UTF-8' # locale for monetary
formatting
lc_numeric = 'it_IT.UTF-8' # locale for number
formatting
lc_time = 'it_IT.UTF-8' # locale for time
formatting
# - Other Defaults -
#explain_pretty_print = on
#dynamic_library_path = '$libdir'
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10
# note: each lock table slot uses ~220 bytes of shared memory, and
there are
# max_locks_per_transaction * (max_connections +
max_prepared_transactions)
# lock table slots.
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = off
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = off
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on
# - Other Platforms & Clients -
#transform_null_equals = off
#---------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#---------------------------------------------------------------------------
#custom_variable_classes = '' # list of custom variable
class names
________________________________________________
Tiscali Voce 8 Mega (Telefono+Adsl). Attiva entro il 22/11/07: chiami in tutta Italia e navighi SENZA LIMITI A SOLI
4,95€AL MESE FINO AL 31/03/2008!
Dal 1° aprile 2008 paghi 28,95 € al mese.
http://abbonati.tiscali.it/telefono-adsl/prodotti/tc/voce8mega/
"giuseppe-r@tiscali.it" <giuseppe-r@tiscali.it> wrote: > > Hello all, > I'm doing tests on various Database and in particular I'm running a > comparison between Oracle 10g and Postgres 8.1 on a dedicated server > with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory > and Debian GNU / Linux version 2.6.18-5. Performance is very similar up > to 30 users, but from 40 onwards with Postgres fall quickly. That is > not what happens with Oracle that comes to 600 users. Can you help me > with the tuning ? If you're doing perf comparisons, you should start out with the latest PostgreSQL: 8.2.5 Also, beware that you may violate license agreements if you publish benchmarks of Oracle ... and posting partial results to a mailing list could potentially be considered "publishing benchmarks" to Oracle's lawyers. I've added a few more comments inline, but overall it looks like you've done a good job tuning. In order to tweak it any further, we're probably going to need more details, such as iostat output during the run, details of the test you're running, etc. > Thanks a lot > 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 = '/etc/postgresql/8.1/main/pg_hba.conf' # host-based > authentication file > ident_file = '/etc/postgresql/8.1/main/pg_ident.conf' # IDENT > configuration file > > # If external_pid_file is not explicitly set, no extra pid file is > written. > external_pid_file = '/var/run/postgresql/8.1-main.pid' # > write an extra pid file > > > #--------------------------------------------------------------------------- > # CONNECTIONS AND AUTHENTICATION > #--------------------------------------------------------------------------- > > # - Connection Settings - > > #listen_addresses = 'localhost' # what IP address(es) to > listen on; > # comma- > separated list of addresses; > # defaults to > 'localhost', '*' = all > listen_addresses = '*' > port = 5432 > max_connections = 220 > # 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 = '/var/run/postgresql' > #unix_socket_group = '' > #unix_socket_permissions = 0777 # octal > #bonjour_name = '' # defaults to the computer > name > > # - Security & Authentication - > > #authentication_timeout = 60 # 1-600, in seconds > ssl = true > #password_encryption = on > #db_user_namespace = off > > # Kerberos > #krb_server_keyfile = '' > #krb_srvname = 'postgres' > #krb_server_hostname = '' # empty string matches any > keytab entry > #krb_caseins_users = off > > # - TCP Keepalives - > # see 'man 7 tcp' for details > > #tcp_keepalives_idle = 0 # TCP_KEEPIDLE, in seconds; > # 0 selects the > system default > #tcp_keepalives_interval = 0 # TCP_KEEPINTVL, in seconds; > # 0 selects > the system default > #tcp_keepalives_count = 0 # TCP_KEEPCNT; > # 0 selects > the system default > > > #--------------------------------------------------------------------------- > # RESOURCE USAGE (except WAL) > #--------------------------------------------------------------------------- > > # - Memory - > > shared_buffers = 49152 # min 16 or max_connections*2, > 8KB each, 384MB With 4G of ram, you might want to try this closer to 1G and see if it helps. You may want to install the pg_buffercache module to monitor shared_buffer usage. I doubt you want to use it during actual timing of the test, but it should help you get a feel for what the best setting is for shared_buffers. > temp_buffers = 1000 # min 100, 8KB each > max_prepared_transactions = 350 # 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 = 1024 # min 64, size in > KB > maintenance_work_mem = 524288 # min 1024, size in KB, -512 > MB- > max_stack_depth = 6144 # min 100, size in KB > > # - Free Space Map - > max_fsm_pages = 58000 # min max_fsm_relations*16, 6 > bytes each > max_fsm_relations = 3000 # 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 = 5000 # 10-10000 milliseconds > between rounds > bgwriter_lru_percent = 0 # 0-100% of LRU buffers > scanned/round > bgwriter_lru_maxpages = 0 # 0-1000 buffers max > written/round > bgwriter_all_percent = 0 # 0-100% of all buffers > scanned/round > bgwriter_all_maxpages = 0 # 0-1000 buffers max > written/round It looks like you're trying to disable the background writer. This will cause checkpoints to be more expensive. Can you verify that the perf problems that you're seeing aren't the result of checkpoints? > #--------------------------------------------------------------------------- > # WRITE AHEAD LOG > #--------------------------------------------------------------------------- > > # - Settings - > > fsync = off # 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 Turn this off. > #wal_buffers = 8 # min 4, 8KB each While it's difficult to know whether it will help, I'd bump this up to 16 or 32 and see if it helps. > #commit_delay = 5 # range 0-100000, in > microseconds > #commit_siblings = 5 # range 1-1000 > # - Checkpoints - > > checkpoint_segments = 100 # in logfile segments, min 1, > 16MB each > checkpoint_timeout = 1800 # range 30-3600, in seconds > #checkpoint_warning = 30 # in seconds, 0 is off Are you seeing checkpoint warnings in the log? > # - 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 = on > #enable_seqscan = on > #enable_sort = on > #enable_tidscan = on > > # - Planner Cost Constants - > > effective_cache_size = 196608 # typically 8KB each What else is running on this system? 4G - 400M shared buffers - 100M for other OS activities = 3G. > #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 = on > #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 > #constraint_exclusion = off > #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 used when logging to stderr: > #redirect_stderr = off # Enable capturing of stderr > into log > # files > > # These are only used if redirect_stderr is on: > #log_directory = 'pg_log' # Directory where log files > are written > # Can be absolute or relative > to PGDATA > #log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name > pattern. > # Can include strftime() > escapes > #log_truncate_on_rotation = off # If on, 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 > # off, meaning append to > existing files > # in all cases. > #log_rotation_age = 1440 # Automatic rotation of > logfiles will > # happen after so many > minutes. 0 to > # disable. > #log_rotation_size = 10240 # 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 = panic # Values in order of > increasing severity: > # debug5 > # debug4 > # debug3 > # debug2 > # debug1 > # info > # notice > # warning > # error > # panic(off) > #log_min_duration_statement = -1 # -1 is disabled, 0 logs all > statements > # and their durations, in > milliseconds. > > #silent_mode = off # DO NOT USE without syslog or > # redirect_stderr > > # - What to Log - > > #debug_print_parse = off > #debug_print_rewritten = off > #debug_print_plan = off > #debug_pretty_print = off > #log_connections = off > #log_disconnections = off > #log_duration = off > log_line_prefix = '%t ' # Special values: > # %u = user name > # %d = database name > # %r = remote host and port > # %h = remote host > # %p = PID > # %t = timestamp (no > milliseconds) > # %m = timestamp with > milliseconds > # %i = command tag > # %c = session id > # %l = session line number > # %s = session start > timestamp > # %x = transaction id > # %q = stop here in non- > session > # processes > # %% = '%' > # e.g. '<%u%%%d> ' > #log_statement = 'none' # none, mod, ddl, all > #log_hostname = off > > > #--------------------------------------------------------------------------- > # RUNTIME STATISTICS > #--------------------------------------------------------------------------- > > # - Statistics Monitoring - > > #log_parser_stats = off > #log_planner_stats = off > #log_executor_stats = off > #log_statement_stats = off > > # - Query/Index Statistics Collector - > > stats_start_collector = off > stats_command_string = off > stats_block_level = off > stats_row_level = off > stats_reset_on_server_start = off > > > #--------------------------------------------------------------------------- > # AUTOVACUUM PARAMETERS > #--------------------------------------------------------------------------- > > autovacuum = off # enable autovacuum > subprocess? > #autovacuum_naptime = 60 # time between autovacuum > runs, in secs > #autovacuum_vacuum_threshold = 1000 # min # of tuple updates > before > # vacuum > #autovacuum_analyze_threshold = 500 # min # of tuple updates > before > # analyze > > #autovacuum_vacuum_scale_factor = 0.4 # fraction of rel size before > # vacuum > #autovacuum_analyze_scale_factor = 0.2 # fraction of rel size before > # analyze > #autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay > for > # autovac, -1 means use > # vacuum_cost_delay > #autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit > for > # autovac, -1 means use > # vacuum_cost_limit > > > #--------------------------------------------------------------------------- > # CLIENT CONNECTION DEFAULTS > #--------------------------------------------------------------------------- > > # - Statement Behavior - > > #search_path = '$user,public' # schema names > #default_tablespace = '' # a tablespace name, '' uses > # the default > #check_function_bodies = on > #default_transaction_isolation = 'read committed' > > #default_transaction_read_only = off > #statement_timeout = 0 # 0 is disabled, in > milliseconds > > # - Locale and Formatting - > > #datestyle = 'iso, mdy' > #timezone = unknown # actually, defaults to TZ > # environment setting > #australian_timezones = off > #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 = 'it_IT.UTF-8' # locale for system > error message > # strings > lc_monetary = 'it_IT.UTF-8' # locale for monetary > formatting > lc_numeric = 'it_IT.UTF-8' # locale for number > formatting > lc_time = 'it_IT.UTF-8' # locale for time > formatting > > # - Other Defaults - > > #explain_pretty_print = on > #dynamic_library_path = '$libdir' > > > #--------------------------------------------------------------------------- > # LOCK MANAGEMENT > #--------------------------------------------------------------------------- > > #deadlock_timeout = 1000 # in milliseconds > #max_locks_per_transaction = 64 # min 10 > # note: each lock table slot uses ~220 bytes of shared memory, and > there are > # max_locks_per_transaction * (max_connections + > max_prepared_transactions) > # lock table slots. > > > #--------------------------------------------------------------------------- > # VERSION/PLATFORM COMPATIBILITY > #--------------------------------------------------------------------------- > > # - Previous Postgres Versions - > > #add_missing_from = off > #backslash_quote = safe_encoding # on, off, or safe_encoding > #default_with_oids = off > #escape_string_warning = off > #regex_flavor = advanced # advanced, extended, or basic > #sql_inheritance = on > > # - Other Platforms & Clients - > > #transform_null_equals = off > > > #--------------------------------------------------------------------------- > # CUSTOMIZED OPTIONS > #--------------------------------------------------------------------------- > > #custom_variable_classes = '' # list of custom variable > class names -- Bill Moran Collaborative Fusion Inc. wmoran@collaborativefusion.com Phone: 412-422-3463x4023
On Nov 22, 2007 9:09 AM, giuseppe-r@tiscali.it <giuseppe-r@tiscali.it> wrote: > I'm doing tests on various Database and in particular I'm running a > comparison between Oracle 10g and Postgres 8.1 on a dedicated server As Bill said, do not publish any part of the Oracle result anywhere. > with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory > and Debian GNU / Linux version 2.6.18-5. Performance is very similar up > to 30 users, but from 40 onwards with Postgres fall quickly. That is > not what happens with Oracle that comes to 600 users. Can you help me > with the tuning ? I'm not sure which TPC-C kit you're using, but you should probably use DBT-2. http://sourceforge.net/project/showfiles.php?group_id=52479&package_id=54389&release_id=485705 http://oss.oracle.com/projects/olt/ As for parameters, I'd start with: - Make sure wal and data are split and their RAIDs (if any) are configured properly. shared_buffers = 98304 (this may need to stay at your current one depending on the cost of checkpoints) max_prepared_transactions = 5 (this doesn't have anything to do with what it sounds like) max_fsm_relations = 1000 bgwriter_delay = 500 wal_sync_method = open_sync (or try open_datasync) wal_buffers = 256 checkpoint_segments = 256 (if you have the space) checkpoint_timeout = 1800 checkpoint_warning = 1740 effective_cache_size = 346030 default_statistics_target = 100 I'm not sure whether DBT-2 supports it out-of-the-box, but you should also look at changing default_transaction_isolation to serializable. Keep in mind that DBT-2 has several bugs in it. Though, I'm not sure whether Oracle fixed them on their version either. It also looks like you have fsync turned off, which means commits are not guaranteed (unlike your Oracle configuration). If you want apples-to-apples, you need to turn fsync on. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
>>> "giuseppe-r@tiscali.it" <giuseppe-r@tiscali.it> 11/22/07 8:09 AM >>> > Performance is very similar up > to 30 users, but from 40 onwards with Postgres fall quickly. I suggest testing with some form of connection pooling. Many database products will queue requests in those situations; with PostgreSQL it is up to you to arrange that. -Kevin
On Nov 22, 2007 10:45 AM, Kevin Grittner <Kevin.Grittner@wicourts.gov> wrote: > I suggest testing with some form of connection pooling. Yeah, that's one of the reasons I suggested DBT-2. It pools connections and is the most mature TPC-C-like test for Postgres. -- Jonah H. Harris, Sr. Software Architect | phone: 732.331.1324 EnterpriseDB Corporation | fax: 732.331.1301 499 Thornall Street, 2nd Floor | jonah.harris@enterprisedb.com Edison, NJ 08837 | http://www.enterprisedb.com/
giuseppe-r@tiscali.it wrote: > Hello all, > I'm doing tests on various Database and in particular I'm running a > comparison between Oracle 10g and Postgres 8.1 on a dedicated server > with 2 processors Dual-Core AMD Opteron 2218 2.6 GHz, 4GB of memory > and Debian GNU / Linux version 2.6.18-5. Performance is very similar up > to 30 users, but from 40 onwards with Postgres fall quickly. That is > not what happens with Oracle that comes to 600 users. Can you help me > with the tuning ? The fact that you didn't give any details on your I/O configuration tells me that you don't have much experience with TPC-C. TPC-C is basically limited by random I/O. That means that a good RAID controller and a lot of disks is a must. Looking at some of the results at www.tpc.org, systems with 4 cores have multiple RAID controllers and about a hundred hard drives. You can of course run smaller tests, but those 4 cores are going spend all their time waiting for I/O. See for example these old DBT-2 results I ran to test the Load Distributed Checkpoints feature in 8.3. Now that we got that out of the way, what kind of a test configuration are you using? How many warehouses? Are you using the think-times, per the spec, or are you running something like BenchmarkSQL which just pushes as many queries it can to the server? I'm not sure what you mean by # of users, but you shouldn't use more than 10-30 connections on a test like that. More won't help, because they'll all have to queue for the same resources, whether it's I/O or CPU. How long tests are you running? After some time, you'll need to run vacuums, which make a big difference. 8.3 will perform better, thanks to HOT which reduces the need to vacuum, varvarlen which reduces storage size, leading to better use of the cache and less I/O, and Load Distributed Checkpoints, which reduce the checkpoint spikes which otherwise throw you over the response time requirements. And last but not least, why are you running the benchmark? It's going to be practically irrelevant for any real application. You should benchmark with your application, and your data, to get a comparison that matters for you. -- Heikki Linnakangas EnterpriseDB http://www.enterprisedb.com