Skip site navigation (1) Skip section navigation (2)

tuning for TPC-C benchmark

From: "giuseppe-r(at)tiscali(dot)it" <giuseppe-r(at)tiscali(dot)it>
To: pgsql-performance(at)postgresql(dot)org
Subject: tuning for TPC-C benchmark
Date: 2007-11-22 14:09:52
Message-ID: 25297330.1195740592813.JavaMail.root@ps21 (view raw or flat)
Thread:
Lists: 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/


Responses

pgsql-performance by date

Next:From: Bill MoranDate: 2007-11-22 14:33:04
Subject: Re: tuning for TPC-C benchmark
Previous:From: Matthew SchumacherDate: 2007-11-21 04:14:31
Subject: Postgres ignoring index when using left outer join.

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group