pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedtuning for TPC-C benchmark
6+ messages / 5 participants
[nested] [flat]
* tuning for TPC-C benchmark
@ 2007-11-22 14:09 giuseppe-r@tiscali.it <giuseppe-r@tiscali.it>
2007-11-22 14:33 ` Re: tuning for TPC-C benchmark Bill Moran <wmoran@collaborativefusion.com>
2007-11-22 14:59 ` Re: tuning for TPC-C benchmark Jonah H. Harris <jonah.harris@gmail.com>
2007-11-22 19:22 ` Re: tuning for TPC-C benchmark Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 3 replies; 6+ messages in thread
From: giuseppe-r@tiscali.it @ 2007-11-22 14:09 UTC (permalink / raw)
To: pgsql-performance
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tuning for TPC-C benchmark
2007-11-22 14:09 tuning for TPC-C benchmark giuseppe-r@tiscali.it <giuseppe-r@tiscali.it>
@ 2007-11-22 14:33 ` Bill Moran <wmoran@collaborativefusion.com>
2 siblings, 0 replies; 6+ messages in thread
From: Bill Moran @ 2007-11-22 14:33 UTC (permalink / raw)
To: giuseppe-r@tiscali.it <giuseppe-r@tiscali.it>; +Cc: pgsql-performance
"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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tuning for TPC-C benchmark
2007-11-22 14:09 tuning for TPC-C benchmark giuseppe-r@tiscali.it <giuseppe-r@tiscali.it>
@ 2007-11-22 14:59 ` Jonah H. Harris <jonah.harris@gmail.com>
2 siblings, 0 replies; 6+ messages in thread
From: Jonah H. Harris @ 2007-11-22 14:59 UTC (permalink / raw)
To: giuseppe-r@tiscali.it <giuseppe-r@tiscali.it>; +Cc: pgsql-performance
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=4857...
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tuning for TPC-C benchmark
2007-11-22 14:09 tuning for TPC-C benchmark giuseppe-r@tiscali.it <giuseppe-r@tiscali.it>
@ 2007-11-22 19:22 ` Heikki Linnakangas <heikki@enterprisedb.com>
2 siblings, 0 replies; 6+ messages in thread
From: Heikki Linnakangas @ 2007-11-22 19:22 UTC (permalink / raw)
To: giuseppe-r@tiscali.it; +Cc: pgsql-performance
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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tuning for TPC-C benchmark
@ 2007-11-22 15:45 Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-11-22 17:38 ` Re: tuning for TPC-C benchmark Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 1 reply; 6+ messages in thread
From: Kevin Grittner @ 2007-11-22 15:45 UTC (permalink / raw)
To: pgsql-performance; giuseppe-r@tiscali.it
>>> "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
^ permalink raw reply [nested|flat] 6+ messages in thread
* Re: tuning for TPC-C benchmark
2007-11-22 15:45 Re: tuning for TPC-C benchmark Kevin Grittner <Kevin.Grittner@wicourts.gov>
@ 2007-11-22 17:38 ` Jonah H. Harris <jonah.harris@gmail.com>
0 siblings, 0 replies; 6+ messages in thread
From: Jonah H. Harris @ 2007-11-22 17:38 UTC (permalink / raw)
To: Kevin Grittner <Kevin.Grittner@wicourts.gov>; +Cc: pgsql-performance; giuseppe-r@tiscali.it
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/
^ permalink raw reply [nested|flat] 6+ messages in thread
end of thread, other threads:[~2007-11-22 19:22 UTC | newest]
Thread overview: 6+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-11-22 14:09 tuning for TPC-C benchmark giuseppe-r@tiscali.it <giuseppe-r@tiscali.it>
2007-11-22 14:33 ` Bill Moran <wmoran@collaborativefusion.com>
2007-11-22 14:59 ` Jonah H. Harris <jonah.harris@gmail.com>
2007-11-22 19:22 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-11-22 15:45 Re: tuning for TPC-C benchmark Kevin Grittner <Kevin.Grittner@wicourts.gov>
2007-11-22 17:38 ` Jonah H. Harris <jonah.harris@gmail.com>
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox