Re: tuning for TPC-C benchmark

"giuseppe-r(at)tiscali(dot)it" <giuseppe-r(at)tiscali(dot)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(at)collaborativefusion(dot)com
Phone: 412-422-3463x4023