pg.ddx.io pgsql-performance@postgresql.org mailing list archive
help / color / mirror / Atom feedupgraded to pgsql 8.2.4, getting worse performance then 7.4.x
24+ messages / 13 participants
[nested] [flat]
* upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
@ 2007-06-02 13:13 Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Rafael Martinez <r.m.guerrero@usit.uio.no>
2007-06-02 15:25 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Tom Lane <tgl@sss.pgh.pa.us>
2007-06-02 15:45 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Kenneth Marshall <ktm@rice.edu>
2007-06-05 17:01 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Hanu Kurubar <hanu.kurubar@gmail.com>
0 siblings, 5 replies; 24+ messages in thread
From: Douglas J Hunley @ 2007-06-02 13:13 UTC (permalink / raw)
To: pgsql-performance
Hello great gurus of performance:
Our 'esteemed' Engr group recently informed a customer that in their testing,
upgrading to 8.2.x improved the performance of our J2EE
application "approximately 20%", so of course, the customer then tasked me
with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4
rpms from postgresql.org, did an initdb, and the pg_restored their data. It's
been about a week now, and the customer is complaining that in their testing,
they are seeing a 30% /decrease/ in general performance. Of course, our Engr
group is being less than responsive, and I have a feeling all they're doing
is googling for answers, so I'm turning to this group for actual
assistance :)
I'd like to start by examining the poistgresql.conf file. Under 7.4.x, we had
spent the better part of their 2 years as a customer tuning and tweaking
setting. I've attached the file that was in place at the time of upgrade. I
did some cursory googling of my own, and quickly realized that enough has
changed in v8 that I'm not comfortable making the exact same modification to
their new config file as some options are new, some have gone away, etc. I've
attached the existing v8 conf file as well.
I'd really like it if someone could assist me in determining which of the v8
options need adjusted to be 'functionally equivalent' to the v7 file. Right
now, my goal is to get the customer back to the previous level of
performance, and only then pursue further optimization. I can provide any and
all information needed, but didn't know what to include initially, so I've
opted to include the minimal :)
The DB server in question does nothing else, is running CentOS 4.5, kernel
2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon
3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.
Thank you in advance for any and all assistance you can provide.
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
Handy Guide to Modern Science:
1. If it's green or it wiggles, it's biology.
2. If it stinks, it's chemistry.
3. If it doesn't work, it's physics.
Attachments:
postgresql.conf.v7 (text/plain, 8.1K) download
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload".
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
tcpip_socket = true
max_connections = 220
# note: increasing max_connections costs about 500 bytes of shared
# memory per connection slot, in addition to costs from shared_buffers
# and max_locks_per_transaction.
#superuser_reserved_connections = 2
#port = 5432
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#virtual_host = '' # what interface to listen on; defaults to any
#rendezvous_name = '' # defaults to the computer name
# - Security & Authentication -
#authentication_timeout = 60 # 1-600, in seconds
#ssl = false
#password_encryption = true
#krb_server_keyfile = ''
#db_user_namespace = false
#---------------------------------------------------------------------------
# RESOURCE USAGE (except WAL)
#---------------------------------------------------------------------------
# - Memory -
# Needs an actual restart to change!!
shared_buffers = 25000 # min 16, at least max_connections*2, 8KB each
#shared_buffers = 75000 # min 16, at least max_connections*2, 8KB each
#shared_buffers = 200000 # min 16, at least max_connections*2, 8KB each
sort_mem = 15000 # min 64, size in KB
vacuum_mem = 100000 # min 1024, size in KB
#vacuum_mem = 32768 # min 1024, size in KB
# - Free Space Map -
max_fsm_pages = 200000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # min 100, ~50 bytes each
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
#preload_libraries = ''
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
fsync = false # turns forced synchronization on or off
#wal_sync_method = fsync # the default varies across platforms:
# fsync, fdatasync, open_sync, or open_datasync
wal_buffers = 4096 # min 4, 8KB each
#wal_buffers = 8 # min 4, 8KB each
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # range 30-3600, in seconds
#checkpoint_warning = 30 # 0 is off, in seconds
commit_delay = 20000 # range 0-100000, in microseconds
commit_siblings = 3 # range 1-1000
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Enabling -
#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
enable_mergejoin = false
#enable_nestloop = false
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true
# - Planner Cost Constants -
# Needs an actual restart to change!!
effective_cache_size = 196608 # typically 8KB each
#effective_cache_size = 68192 # 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 = false
#geqo_threshold = 11
#geqo_effort = 1
#geqo_generations = 0
#geqo_pool_size = 0 # default based on tables in statement,
# range 128-1024
#geqo_selection_bias = 2.0 # range 1.5-2.0
# - Other Planner Options -
#default_statistics_target = 10 # range 1-1000
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit JOINs
#---------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#---------------------------------------------------------------------------
# - Syslog -
#syslog = 2 # range 0-2; 0=stdout; 1=both; 2=syslog
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
# - When to Log -
#client_min_messages = notice # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# log, info, notice, warning, error
#log_min_messages = info # Values, in order of decreasing detail:
# debug5, debug4, debug3, debug2, debug1,
# info, notice, warning, error, log, fatal,
# panic
#log_error_verbosity = 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 # Log all statements whose
# execution time exceeds the value, in
# milliseconds. Zero prints all queries.
# Minus-one disables.
#silent_mode = false # DO NOT USE without Syslog!
# - What to Log -
#debug_print_parse = false
#debug_print_rewritten = false
#debug_print_plan = false
#debug_pretty_print = false
#log_connections = false
#log_duration = false
#log_pid = false
#log_statement = false
#log_timestamp = true
#log_hostname = false
#log_source_port = false
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Statistics Monitoring -
#log_parser_stats = false
#log_planner_stats = false
#log_executor_stats = false
#log_statement_stats = false
# - Query/Index Statistics Collector -
#stats_start_collector = true
#stats_command_string = false
#stats_block_level = false
#stats_row_level = false
#stats_reset_on_server_start = true
#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------
# - Statement Behavior -
#search_path = '$user,public' # schema names
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
statement_timeout = 300000 # 0 is disabled, in milliseconds
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ environment setting
#australian_timezones = false
#extra_float_digits = 0 # min -15, max 2
#client_encoding = sql_ascii # actually, defaults to database encoding
# These settings are initialized by initdb -- they may be changed
lc_messages = 'en_US.UTF-8' # locale for system error message strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = true
#dynamic_library_path = '$libdir'
#max_expr_depth = 10000 # min 10
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~260*max_connections bytes each
#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------
# - Previous Postgres Versions -
#add_missing_from = true
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = true
# - Other Platforms & Clients -
#transform_null_equals = false
postgresql.conf.v8 (text/plain, 15.0K) download
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the server.
#
# Any option can also be given as a command line switch to the server,
# e.g., 'postgres -c log_connections=on'. Some options can be changed at
# run-time with the 'SET' SQL command.
#
# This file is read on server startup and when the server receives a
# SIGHUP. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# settings, which are marked below, require a server shutdown and restart
# to take effect.
#
# Memory units: kB = kilobytes MB = megabytes GB = gigabytes
# Time units: ms = milliseconds s = seconds min = minutes h = hours d = days
#---------------------------------------------------------------------------
# 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
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)' # write an extra PID file
# (change requires restart)
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 200 # (change requires restart)
# 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 = 3 # (change requires restart)
#unix_socket_directory = '' # (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # octal
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - Security & Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#password_encryption = on
#db_user_namespace = off
# Kerberos
#krb_server_keyfile = '' # (change requires restart)
#krb_srvname = 'postgres' # (change requires restart)
#krb_server_hostname = '' # empty string matches any keytab entry
# (change requires restart)
#krb_caseins_users = off # (change requires restart)
# - 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 = 32MB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
# (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - Free Space Map -
#max_fsm_pages = 1638400 # min max_fsm_relations*16, 6 bytes each
# (change requires restart)
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# (change requires restart)
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)
# - 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 = 200ms # 10-10000ms between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 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 = 64kB # min 32kB
# (change requires restart)
commit_delay = 20000 # range 0-100000, in microseconds
commit_siblings = 3 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_warning = 30s # 0 is off
# - Archiving -
#archive_command = '' # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
# many seconds; 0 is off
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
enable_mergejoin = off
#enable_nestloop = off
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#effective_cache_size = 128MB
# - Genetic Query Optimizer -
geqo = off
#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
# (change requires restart)
# 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 = 1d # Automatic rotation of logfiles will
# happen after that time. 0 to
# disable.
#log_rotation_size = 10MB # Automatic rotation of logfiles will
# happen after that much 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 = error # Values in order of increasing severity:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations.
#silent_mode = off # DO NOT USE without syslog or
# redirect_stderr
# (change requires restart)
# - 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 = '' # 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, ddl, mod, all
#log_hostname = off
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#stats_command_string = on
#update_process_title = on
#stats_start_collector = on # needed for block or row stats
# (change requires restart)
#stats_block_level = off
#stats_row_level = off
#stats_reset_on_server_start = off # (change requires restart)
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
#autovacuum = off # enable autovacuum subprocess?
# 'on' requires stats_start_collector
# and stats_row_level to also be on
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 500 # min # of tuple updates before
# vacuum
#autovacuum_analyze_threshold = 250 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
# vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
# analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -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 = 300000 # 0 is disabled
#vacuum_freeze_min_age = 100000000
# - Locale and Formatting -
#datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ
# environment setting
#timezone_abbreviations = 'Default' # select the set of available timezone
# abbreviations. Currently, there are
# Default
# Australia
# India
# However you can also create your own
# file in share/timezonesets/.
#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 = 'C' # locale for system error message
# strings
#lc_monetary = 'C' # locale for monetary formatting
#lc_numeric = 'C' # locale for number formatting
#lc_time = 'C' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = on
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: each lock table slot uses ~270 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
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#standard_conforming_strings = 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
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-02 15:21 ` Michael Fuhr <mike@fuhr.org>
2007-06-02 15:31 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
2007-06-03 17:24 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 20:01 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
4 siblings, 3 replies; 24+ messages in thread
From: Michael Fuhr @ 2007-06-02 15:21 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
On Sat, Jun 02, 2007 at 09:13:32AM -0400, Douglas J Hunley wrote:
> Our 'esteemed' Engr group recently informed a customer that in their testing,
> upgrading to 8.2.x improved the performance of our J2EE
> application "approximately 20%", so of course, the customer then tasked me
> with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4
> rpms from postgresql.org, did an initdb, and the pg_restored their data. It's
> been about a week now, and the customer is complaining that in their testing,
> they are seeing a 30% /decrease/ in general performance.
After the restore, did you ANALYZE the entire database to update
the planner's statistics? Have you enabled autovacuum or are you
otherwise vacuuming and analyzing regularly? What kind of queries
are slower than desired? If you post an example query and the
EXPLAIN ANALYZE output then we might be able to see if the slowness
is due to query plans.
A few differences between the configuration files stand out. The
7.4 file has the following settings:
shared_buffers = 25000
sort_mem = 15000
effective_cache_size = 196608
The 8.2 config has:
#shared_buffers = 32MB
#work_mem = 1MB
#effective_cache_size = 128MB
To be equivalent to the 7.4 config the 8.2 config would need:
shared_buffers = 195MB
work_mem = 15000kB
effective_cache_size = 1536MB
With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB
(less if the entire database isn't that big) and effective_cache_size
to 5GB - 6GB. You might have to increase the kernel's shared memory
settings before increasing shared_buffers.
Some of the other settings are the same between the configurations
but deserve discussion:
fsync = off
Disabling fsync is dangerous -- are all parties aware of the risk
and willing to accept it? Has the risk been weighed against the
cost of upgrading to a faster I/O subsystem? How much performance
benefit are you realizing by disabling fsync? What kind of activity
led to the decision to disable fynsc? Are applications doing
anything like executing large numbers of insert/update/delete
statements outside of a transaction block when they could be done
in a single transaction?
commit_delay = 20000
commit_siblings = 3
What kind of activity led to the above settings? Are they a guess
or were they determined empirically? How much benefit are they
providing and how did you measure that?
enable_mergejoin = off
geqo = off
I've occasionally had to tweak planner settings but I prefer to do
so for specific queries instead of changing them server-wide.
--
Michael Fuhr
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
@ 2007-06-02 15:31 ` Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
2 siblings, 0 replies; 24+ messages in thread
From: Stefan Kaltenbrunner @ 2007-06-02 15:31 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: Douglas J Hunley <doug@hunley.homeip.net>; pgsql-performance
Michael Fuhr wrote:
> On Sat, Jun 02, 2007 at 09:13:32AM -0400, Douglas J Hunley wrote:
>> Our 'esteemed' Engr group recently informed a customer that in their testing,
>> upgrading to 8.2.x improved the performance of our J2EE
>> application "approximately 20%", so of course, the customer then tasked me
>> with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4
>> rpms from postgresql.org, did an initdb, and the pg_restored their data. It's
>> been about a week now, and the customer is complaining that in their testing,
>> they are seeing a 30% /decrease/ in general performance.
>
> After the restore, did you ANALYZE the entire database to update
> the planner's statistics? Have you enabled autovacuum or are you
> otherwise vacuuming and analyzing regularly? What kind of queries
> are slower than desired? If you post an example query and the
> EXPLAIN ANALYZE output then we might be able to see if the slowness
> is due to query plans.
>
> A few differences between the configuration files stand out. The
> 7.4 file has the following settings:
>
> shared_buffers = 25000
> sort_mem = 15000
> effective_cache_size = 196608
>
> The 8.2 config has:
>
> #shared_buffers = 32MB
> #work_mem = 1MB
> #effective_cache_size = 128MB
>
> To be equivalent to the 7.4 config the 8.2 config would need:
>
> shared_buffers = 195MB
> work_mem = 15000kB
> effective_cache_size = 1536MB
>
> With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB
> (less if the entire database isn't that big) and effective_cache_size
> to 5GB - 6GB. You might have to increase the kernel's shared memory
> settings before increasing shared_buffers.
some testing here has shown that while it is usually a good idea to set
effective_cache_size rather optimistically in versions <8.2 it is
advisable to make it accurate or even a bit less than that in 8.2 and up.
Stefan
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
@ 2007-06-03 17:24 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-03 22:30 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Greg Smith <gsmith@gregsmith.com>
2 siblings, 1 reply; 24+ messages in thread
From: Douglas J Hunley @ 2007-06-03 17:24 UTC (permalink / raw)
To: Michael Fuhr <mike@fuhr.org>; +Cc: pgsql-performance
On Saturday 02 June 2007 11:21:41 Michael Fuhr wrote:
> After the restore, did you ANALYZE the entire database to update
> the planner's statistics? Have you enabled autovacuum or are you
> otherwise vacuuming and analyzing regularly? What kind of queries
> are slower than desired? If you post an example query and the
> EXPLAIN ANALYZE output then we might be able to see if the slowness
> is due to query plans.
I forgot to mention that. Yes, we did:
vacuumdb -a -f -v -z
We have not yet turned on autovacuum. That was next on our list, and then
customer started in w/ the performance. We are doing an 'analyze table'
followed by 'vacuum table' on a periodic basis, but I'll have to wait till
I'm in the office on Monday to see what that schedule is (customer only
allows us to VPN from work)
>
> A few differences between the configuration files stand out. The
> 7.4 file has the following settings:
>
> shared_buffers = 25000
> sort_mem = 15000
> effective_cache_size = 196608
>
> The 8.2 config has:
>
> #shared_buffers = 32MB
> #work_mem = 1MB
> #effective_cache_size = 128MB
>
> To be equivalent to the 7.4 config the 8.2 config would need:
>
> shared_buffers = 195MB
> work_mem = 15000kB
> effective_cache_size = 1536MB
>
> With 8GB of RAM you might try increasing shared_buffers to 400MB - 800MB
> (less if the entire database isn't that big) and effective_cache_size
> to 5GB - 6GB. You might have to increase the kernel's shared memory
> settings before increasing shared_buffers.
>
We have the following in sysctl.conf:
kernel.shmmax=2147483648
kernal.shmall=2097152
kernel.sem = 250 32000 100 128
which should be sufficient, no?
> Some of the other settings are the same between the configurations
> but deserve discussion:
>
> fsync = off
>
> Disabling fsync is dangerous -- are all parties aware of the risk
> and willing to accept it? Has the risk been weighed against the
> cost of upgrading to a faster I/O subsystem? How much performance
> benefit are you realizing by disabling fsync? What kind of activity
> led to the decision to disable fynsc? Are applications doing
> anything like executing large numbers of insert/update/delete
> statements outside of a transaction block when they could be done
> in a single transaction?
Yes, they're aware. This is a temporary setting while they order upgraded SAN
devices. Currently, the I/O on the boxes is horrific.
>
> commit_delay = 20000
> commit_siblings = 3
>
> What kind of activity led to the above settings? Are they a guess
> or were they determined empirically? How much benefit are they
> providing and how did you measure that?
Those are based on a thread their (non-pgsql) DBA found online. I'm perfectly
willing to discount him if so advised.
>
> enable_mergejoin = off
> geqo = off
>
> I've occasionally had to tweak planner settings but I prefer to do
> so for specific queries instead of changing them server-wide.
I concur. Unfortunately, our Engr group don't actually write the SQL for the
app. It's generated, and is done in such a fashion as to work on all our
supported dbs (pgsql, oracle, mysql).
Thanks a ton for the input thus far
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
Anything worth shooting is worth shooting twice. Ammo is cheap. Life is
expensive.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
2007-06-03 17:24 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-03 22:30 ` Greg Smith <gsmith@gregsmith.com>
2007-06-04 17:05 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
0 siblings, 1 reply; 24+ messages in thread
From: Greg Smith @ 2007-06-03 22:30 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
On Sun, 3 Jun 2007, Douglas J Hunley wrote:
>> commit_delay = 20000
>> commit_siblings = 3
> Those are based on a thread their (non-pgsql) DBA found online. I'm perfectly
> willing to discount him if so advised.
Those likely came indirectly from the otherwise useful recommendations at
http://www.wlug.org.nz/PostgreSQLNotes , that's the first place I saw that
particular combination recommended at. The fact that you mention a thread
makes me guess your DBA found
https://kb.vasoftware.com/index.php?x=&mod_id=2&id=20 , which is a
completely bogus set of suggestions. Anyone who gives out a blanket
recommendation for any PostgreSQL performance parameter without asking
questions first about things like your memory and your disk setup doesn't
really know what they're doing, and I'd suggest discounting the entirety
of that advice.
Those commit_ values are completely wrong for many workloads; they're
introducing a 20ms delay into writes as soon as there are more then 3
clients writing things at once. If someone just took those values from a
web page without actually testing them out, you'd be better off turning
both values back to the defaults (which disables the feature) and waiting
until you have some time to correctly determine useful settings for your
system.
Note that with fsync=off, I don't think that's actually doing anything
right now so it's kind of irrelevant to get excited about; should be
addressed before fsync gets turned back on though.
Also: some of the recommendations you've been getting for shared_buffers
are on the low side as far as I'm concerned. You should consider maxing
that value out at 262143 (2GB of RAM) on your server with 8GB of RAM
available, then putting effective_cache_size at 5GB or so. That may
require just a bit more upward tweaking of your kernel parameters to
support.
--
* Greg Smith gsmith@gregsmith.com http://www.gregsmith.com Baltimore, MD
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
2007-06-03 17:24 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-03 22:30 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Greg Smith <gsmith@gregsmith.com>
@ 2007-06-04 17:05 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 18:08 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 1 reply; 24+ messages in thread
From: Douglas J Hunley @ 2007-06-04 17:05 UTC (permalink / raw)
To: pgsql-performance
On Sunday 03 June 2007 18:30:17 Greg Smith wrote:
> To be equivalent to the 7.4 config the 8.2 config would need:
I've taken all the wonderful advise offered thus far, and put the attached
into use. Our initial testing shows a 66% improvement in page load times for
our app. I have the customer beating on things and noting anything that is
still slow.
On a side note, is there any real benefit to using autovacuum over a
periodically scheduled vacuum? I ask because we have the latter already coded
up and cron'd and it seems to keep things fairly optimized.
BTW, I'm on the list, so there's no need to reply direct. I can get the
replies from the list
Thanks again for everyone's assistance thus far. Y'all rock!
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
I feel like I'm diagonally parked in a parallel universe...
Attachments:
postgresql.conf.v8 (text/plain, 15.2K) download
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the server.
#
# Any option can also be given as a command line switch to the server,
# e.g., 'postgres -c log_connections=on'. Some options can be changed at
# run-time with the 'SET' SQL command.
#
# This file is read on server startup and when the server receives a
# SIGHUP. If you edit the file on a running system, you have to SIGHUP the
# server for the changes to take effect, or use "pg_ctl reload". Some
# settings, which are marked below, require a server shutdown and restart
# to take effect.
#
# Memory units: kB = kilobytes MB = megabytes GB = gigabytes
# Time units: ms = milliseconds s = seconds min = minutes h = hours d = days
#---------------------------------------------------------------------------
# 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
# (change requires restart)
#hba_file = 'ConfigDir/pg_hba.conf' # host-based authentication file
# (change requires restart)
#ident_file = 'ConfigDir/pg_ident.conf' # ident configuration file
# (change requires restart)
# If external_pid_file is not explicitly set, no extra PID file is written.
#external_pid_file = '(none)' # write an extra PID file
# (change requires restart)
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
#port = 5432 # (change requires restart)
max_connections = 200 # (change requires restart)
# 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 = 3 # (change requires restart)
#unix_socket_directory = '' # (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # octal
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - Security & Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#password_encryption = on
#db_user_namespace = off
# Kerberos
#krb_server_keyfile = '' # (change requires restart)
#krb_srvname = 'postgres' # (change requires restart)
#krb_server_hostname = '' # empty string matches any keytab entry
# (change requires restart)
#krb_caseins_users = off # (change requires restart)
# - 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 = 195MB
#shared_buffers = 32MB # min 128kB or max_connections*16kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 5 # can be 0 or more
# (change requires restart)
# Note: increasing max_prepared_transactions costs ~600 bytes of shared memory
# per transaction slot, plus lock space (see max_locks_per_transaction).
#work_mem = 1MB # min 64kB
work_mem = 15000kB
#maintenance_work_mem = 16MB # min 1MB
maintenance_work_mem = 256MB
#max_stack_depth = 2MB # min 100kB
# - Free Space Map -
#max_fsm_pages = 1638400 # min max_fsm_relations*16, 6 bytes each
max_fsm_pages = 204800
# (change requires restart)
#max_fsm_relations = 1000 # min 100, ~70 bytes each
# (change requires restart)
# - Kernel Resource Usage -
#max_files_per_process = 1000 # min 25
# (change requires restart)
#shared_preload_libraries = '' # (change requires restart)
# - 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 = 200ms # 10-10000ms between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = on # 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 = 64kB # min 32kB
# (change requires restart)
#commit_delay = 20000 # range 0-100000, in microseconds
#commit_siblings = 3 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
checkpoint_segments = 128
#checkpoint_timeout = 5min # range 30s-1h
#checkpoint_warning = 30s # 0 is off
# - Archiving -
#archive_command = '' # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
# many seconds; 0 is off
#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------
# - Planner Method Configuration -
#enable_bitmapscan = on
#enable_hashagg = on
#enable_hashjoin = on
#enable_indexscan = on
enable_mergejoin = off
#enable_nestloop = off
#enable_seqscan = on
#enable_sort = on
#enable_tidscan = on
# - Planner Cost Constants -
#seq_page_cost = 1.0 # measured on an arbitrary scale
#random_page_cost = 4.0 # same scale as above
random_page_cost = 2.0
#cpu_tuple_cost = 0.01 # same scale as above
#cpu_index_tuple_cost = 0.005 # same scale as above
#cpu_operator_cost = 0.0025 # same scale as above
#effective_cache_size = 128MB
effective_cache_size = 1536MB
# - Genetic Query Optimizer -
geqo = off
#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 = on
#redirect_stderr = off # Enable capturing of stderr into log
# files
# (change requires restart)
# 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 = on # 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 = 1d # Automatic rotation of logfiles will
# happen after that time. 0 to
# disable.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much 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 = error # Values in order of increasing severity:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations.
#silent_mode = off # DO NOT USE without syslog or
# redirect_stderr
# (change requires restart)
# - 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 = '' # 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, ddl, mod, all
#log_hostname = off
#---------------------------------------------------------------------------
# RUNTIME STATISTICS
#---------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#stats_command_string = on
#update_process_title = on
#stats_start_collector = on # needed for block or row stats
# (change requires restart)
#stats_block_level = off
#stats_row_level = off
#stats_reset_on_server_start = off # (change requires restart)
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
#autovacuum = off # enable autovacuum subprocess?
# 'on' requires stats_start_collector
# and stats_row_level to also be on
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 500 # min # of tuple updates before
# vacuum
#autovacuum_analyze_threshold = 250 # min # of tuple updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of rel size before
# vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of rel size before
# analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = -1 # default vacuum cost delay for
# autovacuum, -1 means use
# vacuum_cost_delay
#autovacuum_vacuum_cost_limit = -1 # default vacuum cost limit for
# autovacuum, -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 = 300000 # 0 is disabled
#vacuum_freeze_min_age = 100000000
# - Locale and Formatting -
datestyle = 'iso, mdy'
#timezone = unknown # actually, defaults to TZ
# environment setting
#timezone_abbreviations = 'Default' # select the set of available timezone
# abbreviations. Currently, there are
# Default
# Australia
# India
# However you can also create your own
# file in share/timezonesets/.
#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 = 'en_US.UTF-8' # locale for system error message
# strings
lc_monetary = 'en_US.UTF-8' # locale for monetary formatting
lc_numeric = 'en_US.UTF-8' # locale for number formatting
lc_time = 'en_US.UTF-8' # locale for time formatting
# - Other Defaults -
#explain_pretty_print = on
#dynamic_library_path = '$libdir'
#local_preload_libraries = ''
#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------
#deadlock_timeout = 1s
#max_locks_per_transaction = 64 # min 10
# (change requires restart)
# Note: each lock table slot uses ~270 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
#array_nulls = on
#backslash_quote = safe_encoding # on, off, or safe_encoding
#default_with_oids = off
#escape_string_warning = on
#standard_conforming_strings = 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
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
2007-06-03 17:24 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-03 22:30 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Greg Smith <gsmith@gregsmith.com>
2007-06-04 17:05 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-04 18:08 ` Alvaro Herrera <alvherre@commandprompt.com>
0 siblings, 0 replies; 24+ messages in thread
From: Alvaro Herrera @ 2007-06-04 18:08 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
Douglas J Hunley wrote:
> On a side note, is there any real benefit to using autovacuum over a
> periodically scheduled vacuum? I ask because we have the latter already coded
> up and cron'd and it seems to keep things fairly optimized.
No, not really. Maybe autovacuum could get to specific highly-updated
tables quickier than the cron job, or slow down when there's no
activity; but your current setup is good enough for you there's no
reason to change.
> BTW, I'm on the list, so there's no need to reply direct. I can get the
> replies from the list
Huh, sorry, this is just the customary way to use these lists.
Personally, I prefer to get several copies of each message and have my
software (procmail) deliver only one to me discarding the duplicates.
That way, if one is lost or takes long to get home, I don't even notice
it (it used to happen a lot on the lists). Look at the "eliminatecc"
option in the Majordomo user web pages.
--
Alvaro Herrera http://www.CommandPrompt.com/
The PostgreSQL Company - Command Prompt, Inc.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
@ 2007-06-04 20:01 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 21:11 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Gregory Stark <stark@enterprisedb.com>
2 siblings, 1 reply; 24+ messages in thread
From: Douglas J Hunley @ 2007-06-04 20:01 UTC (permalink / raw)
To: pgsql-performance
On Saturday 02 June 2007 11:21:41 Michael Fuhr wrote:
> If you post an example query and the
> EXPLAIN ANALYZE output then we might be able to see if the slowness
> is due to query plans.
Query 1:
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
project project,
role role,
role_default_user role_default_user,
role_operation role_operation
WHERE
role.id=role_default_user.role_id
AND role_default_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND role_default_user.default_user_class_id='1'
)
UNION
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
project project,
role role,
role_default_user role_default_user,
role_operation role_operation
WHERE
role.id=role_default_user.role_id
AND role_default_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND role_default_user.default_user_class_id='2'
)
UNION
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
project project,
role role,
role_default_user role_default_user,
role_operation role_operation
WHERE
role.id=role_default_user.role_id
AND role_default_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND role_default_user.default_user_class_id='3'
)
UNION
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
sfuser sfuser,
project project,
role role,
projectmembership projectmembership,
role_default_user role_default_user,
role_operation role_operation
WHERE
role.id=role_default_user.role_id
AND role_default_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND role_default_user.default_user_class_id='4'
AND projectmembership.member_id=sfuser.id
AND role_default_user.project_id=projectmembership.project_id
AND sfuser.username='rtrejo'
)
UNION
(SELECT
project.path AS rbac_project_path_string,
role_operation.resource_name AS rbac_resource_name,
role_operation.resource_value AS rbac_resource_value
FROM
sfuser sfuser,
project project,
role role,
role_user role_user,
role_operation role_operation
WHERE
role.id=role_user.role_id
AND role_user.project_id=project.id
AND role.id=role_operation.role_id
AND role.is_deleted=false
AND role_operation.object_type_id='Scm.Repository'
AND role_operation.operation_category='use'
AND role_operation.operation_name='access'
AND role_user.user_id=sfuser.id
AND project.path='projects.barnes_and_nobles_college_bookse_3'
AND project.is_deleted=false
AND sfuser.username='rtrejo'
);
take 0m1.693s according to 'time'
Explain attached as explain1
Query 2:
SELECT
artifact.id AS id,
artifact.priority AS priority,
project.path AS projectPathString,
project.title AS projectTitle,
folder.project_id AS projectId,
folder.path AS folderPathString,
folder.title AS folderTitle,
item.folder_id AS folderId,
item.title AS title,
item.name AS name,
artifact.description AS description,
field_value.value AS artifactGroup,
field_value2.value AS status,
field_value2.value_class AS statusClass,
field_value3.value AS category,
field_value4.value AS customer,
sfuser.username AS submittedByUsername,
sfuser.full_name AS submittedByFullname,
item.date_created AS submittedDate,
artifact.close_date AS closeDate,
sfuser2.username AS assignedToUsername,
sfuser2.full_name AS assignedToFullname,
item.date_last_modified AS lastModifiedDate,
artifact.estimated_hours AS estimatedHours,
artifact.actual_hours AS actualHours,
item.version AS version
FROM
relationship relationship,
sfuser sfuser,
sfuser sfuser2,
field_value field_value3,
item item,
project project,
field_value field_value2,
field_value field_value,
artifact artifact,
folder folder,
field_value field_value4
WHERE
artifact.id=item.id
AND item.folder_id=folder.id
AND folder.project_id=project.id
AND artifact.group_fv=field_value.id
AND artifact.status_fv=field_value2.id
AND artifact.category_fv=field_value3.id
AND artifact.customer_fv=field_value4.id
AND item.created_by_id=sfuser.id
AND relationship.is_deleted=false
AND relationship.relationship_type_name='ArtifactAssignment'
AND relationship.origin_id=sfuser2.id
AND artifact.id=relationship.target_id
AND item.is_deleted=false
AND ((project.path='projects.union_gas_gdar_ebt' AND ((folder.path IN
('tracker.cutover_tasks', 'tracker.peer_review_tracker', 'tracker.tars_0', 'tracker.reviews', 'tracker.defects', 'tracker.tars', 'tracker.database_change_requests'))
OR folder.path LIKE 'tracker.cutover_tasks.%' OR folder.path
LIKE 'tracker.peer_review_tracker.%' OR folder.path LIKE 'tracker.tars_0.%'
OR folder.path LIKE 'tracker.reviews.%' OR folder.path LIKE 'tracker.defects.
%' OR folder.path LIKE 'tracker.tars.%' OR folder.path
LIKE 'tracker.database_change_requests.%')))
AND folder.project_id='proj1775'
AND item.folder_id='tracker11923'
AND folder.path='tracker.defects'
AND (sfuser2.username='nobody' AND field_value2.value_class='Open');
takes 0m9.506s according to time.. it's attached as explain2
TIA, again
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
It's not the pace of life that concerns me, it's the sudden stop at the end.
Attachments:
explain1 (text/plain, 10.8K) download
QUERY PLAN
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Unique (cost=101.98..102.03 rows=5 width=56) (actual time=3049.469..3049.479 rows=1 loops=1)
-> Sort (cost=101.98..101.99 rows=5 width=56) (actual time=3049.461..3049.465 rows=1 loops=1)
Sort Key: rbac_project_path_string, rbac_resource_name, rbac_resource_value
-> Append (cost=0.00..101.92 rows=5 width=56) (actual time=2101.795..3049.325 rows=1 loops=1)
-> Nested Loop (cost=0.00..13.35 rows=1 width=56) (actual time=505.275..505.275 rows=0 loops=1)
-> Nested Loop (cost=0.00..12.97 rows=1 width=30) (actual time=505.268..505.268 rows=0 loops=1)
-> Nested Loop (cost=0.00..8.68 rows=1 width=42) (actual time=0.347..346.759 rows=7294 loops=1)
-> Index Scan using role_oper_obj_oper on role_operation (cost=0.00..4.40 rows=1 width=30) (actual time=0.235..71.840 rows=7294 loops=1)
Index Cond: (((object_type_id)::text = 'Scm.Repository'::text) AND ((operation_category)::text = 'use'::text) AND ((operation_name)::text = 'access'::text))
-> Index Scan using role_pk on "role" (cost=0.00..4.27 rows=1 width=12) (actual time=0.020..0.024 rows=1 loops=7294)
Index Cond: (("role".id)::text = (role_operation.role_id)::text)
Filter: (NOT is_deleted)
-> Index Scan using role_def_u_prj_idx on role_default_user (cost=0.00..4.27 rows=1 width=24) (actual time=0.015..0.015 rows=0 loops=7294)
Index Cond: ((("role".id)::text = (role_default_user.role_id)::text) AND (role_default_user.default_user_class_id = 1))
-> Index Scan using project_pk on project (cost=0.00..0.37 rows=1 width=50) (never executed)
Index Cond: ((role_default_user.project_id)::text = (project.id)::text)
Filter: (((path)::text = 'projects.barnes_and_nobles_college_bookse_3'::text) AND (NOT is_deleted))
-> Nested Loop (cost=0.00..13.35 rows=1 width=56) (actual time=442.680..442.680 rows=0 loops=1)
-> Nested Loop (cost=0.00..12.97 rows=1 width=30) (actual time=442.674..442.674 rows=0 loops=1)
-> Nested Loop (cost=0.00..8.68 rows=1 width=42) (actual time=0.124..289.008 rows=7294 loops=1)
-> Index Scan using role_oper_obj_oper on role_operation (cost=0.00..4.40 rows=1 width=30) (actual time=0.093..35.719 rows=7294 loops=1)
Index Cond: (((object_type_id)::text = 'Scm.Repository'::text) AND ((operation_category)::text = 'use'::text) AND ((operation_name)::text = 'access'::text))
-> Index Scan using role_pk on "role" (cost=0.00..4.27 rows=1 width=12) (actual time=0.018..0.021 rows=1 loops=7294)
Index Cond: (("role".id)::text = (role_operation.role_id)::text)
Filter: (NOT is_deleted)
-> Index Scan using role_def_u_prj_idx on role_default_user (cost=0.00..4.27 rows=1 width=24) (actual time=0.014..0.014 rows=0 loops=7294)
Index Cond: ((("role".id)::text = (role_default_user.role_id)::text) AND (role_default_user.default_user_class_id = 2))
-> Index Scan using project_pk on project (cost=0.00..0.37 rows=1 width=50) (never executed)
Index Cond: ((role_default_user.project_id)::text = (project.id)::text)
Filter: (((path)::text = 'projects.barnes_and_nobles_college_bookse_3'::text) AND (NOT is_deleted))
-> Nested Loop (cost=0.00..13.35 rows=1 width=56) (actual time=184.063..184.063 rows=0 loops=1)
-> Nested Loop (cost=0.00..9.07 rows=1 width=80) (actual time=184.055..184.055 rows=0 loops=1)
-> Nested Loop (cost=0.00..8.69 rows=1 width=54) (actual time=8.814..183.848 rows=4 loops=1)
-> Index Scan using role_oper_obj_oper on role_operation (cost=0.00..4.40 rows=1 width=30) (actual time=0.095..34.567 rows=7294 loops=1)
Index Cond: (((object_type_id)::text = 'Scm.Repository'::text) AND ((operation_category)::text = 'use'::text) AND ((operation_name)::text = 'access'::text))
-> Index Scan using role_def_u_prj_idx on role_default_user (cost=0.00..4.27 rows=1 width=24) (actual time=0.014..0.014 rows=0 loops=7294)
Index Cond: (((role_operation.role_id)::text = (role_default_user.role_id)::text) AND (role_default_user.default_user_class_id = 3))
-> Index Scan using project_pk on project (cost=0.00..0.37 rows=1 width=50) (actual time=0.042..0.042 rows=0 loops=4)
Index Cond: ((role_default_user.project_id)::text = (project.id)::text)
Filter: (((path)::text = 'projects.barnes_and_nobles_college_bookse_3'::text) AND (NOT is_deleted))
-> Index Scan using role_pk on "role" (cost=0.00..4.27 rows=1 width=12) (never executed)
Index Cond: (("role".id)::text = (role_default_user.role_id)::text)
Filter: (NOT is_deleted)
-> Nested Loop (cost=0.00..16.02 rows=1 width=56) (actual time=202.991..202.991 rows=0 loops=1)
-> Nested Loop (cost=0.00..15.73 rows=1 width=42) (actual time=202.984..202.984 rows=0 loops=1)
-> Nested Loop (cost=0.00..11.45 rows=1 width=66) (actual time=202.978..202.978 rows=0 loops=1)
Join Filter: ((projectmembership.member_id)::text = (sfuser.id)::text)
-> Nested Loop (cost=0.00..10.13 rows=4 width=78) (actual time=10.425..190.540 rows=437 loops=1)
-> Nested Loop (cost=0.00..8.69 rows=1 width=54) (actual time=8.747..182.982 rows=10 loops=1)
-> Index Scan using role_oper_obj_oper on role_operation (cost=0.00..4.40 rows=1 width=30) (actual time=0.093..34.379 rows=7294 loops=1)
Index Cond: (((object_type_id)::text = 'Scm.Repository'::text) AND ((operation_category)::text = 'use'::text) AND ((operation_name)::text = 'access'::text))
-> Index Scan using role_def_u_prj_idx on role_default_user (cost=0.00..4.27 rows=1 width=24) (actual time=0.014..0.014 rows=0 loops=7294)
Index Cond: (((role_operation.role_id)::text = (role_default_user.role_id)::text) AND (role_default_user.default_user_class_id = 4))
-> Index Scan using pjmb_projmember on projectmembership (cost=0.00..1.13 rows=25 width=24) (actual time=0.053..0.453 rows=44 loops=10)
Index Cond: ((role_default_user.project_id)::text = (projectmembership.project_id)::text)
-> Index Scan using sfuser_username on sfuser (cost=0.00..0.32 rows=1 width=12) (actual time=0.014..0.018 rows=1 loops=437)
Index Cond: ((username)::text = 'rtrejo'::text)
-> Index Scan using role_pk on "role" (cost=0.00..4.27 rows=1 width=12) (never executed)
Index Cond: (("role".id)::text = (role_default_user.role_id)::text)
Filter: (NOT is_deleted)
-> Index Scan using project_pk on project (cost=0.00..0.28 rows=1 width=50) (never executed)
Index Cond: ((role_default_user.project_id)::text = (project.id)::text)
Filter: (((path)::text = 'projects.barnes_and_nobles_college_bookse_3'::text) AND (NOT is_deleted))
-> Nested Loop (cost=2.40..45.80 rows=1 width=56) (actual time=766.764..1714.288 rows=1 loops=1)
Join Filter: ((role_user.user_id)::text = (sfuser.id)::text)
-> Nested Loop (cost=2.40..45.49 rows=1 width=68) (actual time=762.629..1712.222 rows=82 loops=1)
-> Nested Loop (cost=2.40..44.63 rows=3 width=42) (actual time=0.356..944.773 rows=37814 loops=1)
-> Nested Loop (cost=0.00..8.68 rows=1 width=42) (actual time=0.134..272.836 rows=7294 loops=1)
-> Index Scan using role_oper_obj_oper on role_operation (cost=0.00..4.40 rows=1 width=30) (actual time=0.098..34.408 rows=7294 loops=1)
Index Cond: (((object_type_id)::text = 'Scm.Repository'::text) AND ((operation_category)::text = 'use'::text) AND ((operation_name)::text = 'access'::text))
-> Index Scan using role_pk on "role" (cost=0.00..4.27 rows=1 width=12) (actual time=0.016..0.019 rows=1 loops=7294)
Index Cond: (("role".id)::text = (role_operation.role_id)::text)
Filter: (NOT is_deleted)
-> Bitmap Heap Scan on role_user (cost=2.40..35.73 rows=18 width=36) (actual time=0.029..0.050 rows=5 loops=7294)
Recheck Cond: (("role".id)::text = (role_user.role_id)::text)
-> Bitmap Index Scan on role_user_proj_idx (cost=0.00..2.39 rows=18 width=0) (actual time=0.021..0.021 rows=5 loops=7294)
Index Cond: (("role".id)::text = (role_user.role_id)::text)
-> Index Scan using project_pk on project (cost=0.00..0.27 rows=1 width=50) (actual time=0.014..0.014 rows=0 loops=37814)
Index Cond: ((role_user.project_id)::text = (project.id)::text)
Filter: (((path)::text = 'projects.barnes_and_nobles_college_bookse_3'::text) AND (NOT is_deleted))
-> Index Scan using sfuser_username on sfuser (cost=0.00..0.30 rows=1 width=12) (actual time=0.012..0.015 rows=1 loops=82)
Index Cond: ((username)::text = 'rtrejo'::text)
Total runtime: 3050.582 ms
(83 rows)
explain2 (text/plain, 6.7K) download
QUERY PLAN
-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Nested Loop (cost=6.67..616.24 rows=1 width=629) (actual time=11870.870..11870.870 rows=0 loops=1)
-> Nested Loop (cost=6.67..611.96 rows=1 width=555) (actual time=11870.863..11870.863 rows=0 loops=1)
-> Nested Loop (cost=6.67..607.64 rows=1 width=492) (actual time=11870.857..11870.857 rows=0 loops=1)
-> Nested Loop (cost=6.67..606.26 rows=1 width=471) (actual time=11870.850..11870.850 rows=0 loops=1)
-> Nested Loop (cost=6.67..605.92 rows=1 width=473) (actual time=11870.844..11870.844 rows=0 loops=1)
-> Nested Loop (cost=6.67..605.58 rows=1 width=475) (actual time=11870.838..11870.838 rows=0 loops=1)
-> Nested Loop (cost=6.67..605.23 rows=1 width=477) (actual time=11870.831..11870.831 rows=0 loops=1)
-> Nested Loop (cost=6.67..602.90 rows=3 width=393) (actual time=62.032..10750.797 rows=29905 loops=1)
-> Nested Loop (cost=6.67..595.64 rows=21 width=387) (actual time=61.558..6905.828 rows=126625 loops=1)
-> Nested Loop (cost=6.67..585.73 rows=21 width=46) (actual time=61.399..1590.547 rows=126625 loops=1)
-> Index Scan using sfuser_username on sfuser sfuser2 (cost=0.00..4.27 rows=1 width=45) (actual time=0.122..0.127 rows=1 loops=1)
Index Cond: ((username)::text = 'nobody'::text)
-> Bitmap Heap Scan on relationship (cost=6.67..577.58 rows=311 width=25) (actual time=61.260..783.652 rows=126625 loops=1)
Recheck Cond: ((relationship.origin_id)::text = (sfuser2.id)::text)
Filter: ((NOT is_deleted) AND ((relationship_type_name)::text = 'ArtifactAssignment'::text))
-> Bitmap Index Scan on relation_origin (cost=0.00..6.62 rows=311 width=0) (actual time=57.009..57.009 rows=220230 loops=1)
Index Cond: ((relationship.origin_id)::text = (sfuser2.id)::text)
-> Index Scan using artifact_pk on artifact (cost=0.00..0.46 rows=1 width=341) (actual time=0.025..0.029 rows=1 loops=126625)
Index Cond: ((artifact.id)::text = (relationship.target_id)::text)
-> Index Scan using field_value_pk on field_value field_value2 (cost=0.00..0.33 rows=1 width=32) (actual time=0.021..0.022 rows=0 loops=126625)
Index Cond: ((artifact.status_fv)::text = (field_value2.id)::text)
Filter: ((value_class)::text = 'Open'::text)
-> Index Scan using item_pk on item (cost=0.00..0.76 rows=1 width=110) (actual time=0.031..0.031 rows=0 loops=29905)
Index Cond: ((artifact.id)::text = (item.id)::text)
Filter: ((NOT is_deleted) AND ((folder_id)::text = 'tracker11923'::text))
-> Index Scan using field_value_pk on field_value field_value4 (cost=0.00..0.33 rows=1 width=24) (never executed)
Index Cond: ((artifact.customer_fv)::text = (field_value4.id)::text)
-> Index Scan using field_value_pk on field_value (cost=0.00..0.33 rows=1 width=24) (never executed)
Index Cond: ((artifact.group_fv)::text = (field_value.id)::text)
-> Index Scan using field_value_pk on field_value field_value3 (cost=0.00..0.33 rows=1 width=24) (never executed)
Index Cond: ((artifact.category_fv)::text = (field_value3.id)::text)
-> Index Scan using sfuser_pk on sfuser (cost=0.00..1.37 rows=1 width=45) (never executed)
Index Cond: ((item.created_by_id)::text = (sfuser.id)::text)
-> Index Scan using folder_pk on folder (cost=0.00..4.31 rows=1 width=77) (never executed)
Index Cond: ('tracker11923'::text = (id)::text)
Filter: ((((path)::text = ANY (('{tracker.cutover_tasks,tracker.peer_review_tracker,tracker.tars_0,tracker.reviews,tracker.defects,tracker.tars,tracker.database_change_requests}'::character varying[])::text[])) OR ((path)::text ~~ 'tracker.cutover_tasks.%'::text) OR ((path)::text ~~ 'tracker.peer_review_tracker.%'::text) OR ((path)::text ~~ 'tracker.tars_0.%'::text) OR ((path)::text ~~ 'tracker.reviews.%'::text) OR ((path)::text ~~ 'tracker.defects.%'::text) OR ((path)::text ~~ 'tracker.tars.%'::text) OR ((path)::text ~~ 'tracker.database_change_requests.%'::text)) AND ((project_id)::text = 'proj1775'::text) AND ((path)::text = 'tracker.defects'::text))
-> Index Scan using project_pk on project (cost=0.00..4.27 rows=1 width=86) (never executed)
Index Cond: ('proj1775'::text = (id)::text)
Filter: ((path)::text = 'projects.union_gas_gdar_ebt'::text)
Total runtime: 11871.582 ms
(40 rows)
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
2007-06-04 20:01 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-04 21:11 ` Gregory Stark <stark@enterprisedb.com>
2007-06-04 21:17 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Heikki Linnakangas <heikki@enterprisedb.com>
2007-06-05 14:34 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
0 siblings, 2 replies; 24+ messages in thread
From: Gregory Stark @ 2007-06-04 21:11 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
Those plans look like they have a lot of casts to text in them. How have you
defined your indexes? Are your id columns really text?
And you don't have a 7.4 install around to compare the plans do you?
--
Gregory Stark
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
2007-06-04 20:01 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 21:11 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Gregory Stark <stark@enterprisedb.com>
@ 2007-06-04 21:17 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-06-05 14:25 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
1 sibling, 1 reply; 24+ messages in thread
From: Heikki Linnakangas @ 2007-06-04 21:17 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: Gregory Stark <stark@enterprisedb.com>; pgsql-performance
Gregory Stark wrote:
> Those plans look like they have a lot of casts to text in them. How have you
> defined your indexes? Are your id columns really text?
And did you use the same encoding and locale? Text operations on
multibyte encodings are much more expensive.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
2007-06-04 20:01 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 21:11 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Gregory Stark <stark@enterprisedb.com>
2007-06-04 21:17 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Heikki Linnakangas <heikki@enterprisedb.com>
@ 2007-06-05 14:25 ` Douglas J Hunley <doug@hunley.homeip.net>
0 siblings, 0 replies; 24+ messages in thread
From: Douglas J Hunley @ 2007-06-05 14:25 UTC (permalink / raw)
To: Heikki Linnakangas <heikki@enterprisedb.com>; +Cc: Gregory Stark <stark@enterprisedb.com>; pgsql-performance
On Monday 04 June 2007 17:17:03 Heikki Linnakangas wrote:
> And did you use the same encoding and locale? Text operations on
> multibyte encodings are much more expensive.
The db was created as:
createdb -E UNICODE -O <user> <dbname>
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
Linux is the answer, now what was your question?
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
2007-06-04 20:01 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 21:11 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Gregory Stark <stark@enterprisedb.com>
@ 2007-06-05 14:34 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-06 14:35 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
1 sibling, 1 reply; 24+ messages in thread
From: Douglas J Hunley @ 2007-06-05 14:34 UTC (permalink / raw)
To: Gregory Stark <stark@enterprisedb.com>; +Cc: pgsql-performance
On Monday 04 June 2007 17:11:23 Gregory Stark wrote:
> Those plans look like they have a lot of casts to text in them. How have
> you defined your indexes? Are your id columns really text?
project table:
Indexes:
"project_pk" PRIMARY KEY, btree (id)
"project_path" UNIQUE, btree (path)
role table:
Indexes:
"role_pk" PRIMARY KEY, btree (id)
role_default_user table:
Indexes:
"role_def_user_pk" PRIMARY KEY, btree (id)
"role_def_u_prj_idx" UNIQUE, btree (role_id, default_user_class_id,
project_id)
role_operation table:
Indexes:
"role_operation_pk" PRIMARY KEY, btree (id)
"role_oper_obj_oper" btree (object_type_id, operation_category,
operation_name)
"role_oper_role_id" btree (role_id)
sfuser table:
Indexes:
"sfuser_pk" PRIMARY KEY, btree (id)
"sfuser_username" UNIQUE, btree (username)
projectmembership table:
Indexes:
"pjmb_pk" PRIMARY KEY, btree (id)
"pjmb_projmember" UNIQUE, btree (project_id, member_id)
"pjmb_member" btree (member_id)
relationship table:
Indexes:
"relationship_pk" PRIMARY KEY, btree (id)
"relation_origin" btree (origin_id)
"relation_target" btree (target_id)
"relation_type" btree (relationship_type_name)
field_value table:
Indexes:
"field_value_pk" PRIMARY KEY, btree (id)
"f_val_fid_val_idx" UNIQUE, btree (field_id, value)
"field_class_idx" btree (value_class)
"field_value_idx" btree (value)
item table:
Indexes:
"item_pk" PRIMARY KEY, btree (id)
"item_created_by_id" btree (created_by_id)
"item_folder" btree (folder_id)
"item_name" btree (name)
and yes, the 'id' column is always: character varying type
> And you don't have a 7.4 install around to compare the plans do you?
I have a 7.3.19 db, if that would be useful
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
Whose cruel idea was it for the word "lisp" to have an "s" in it?
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Michael Fuhr <mike@fuhr.org>
2007-06-04 20:01 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 21:11 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Gregory Stark <stark@enterprisedb.com>
2007-06-05 14:34 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-06 14:35 ` Douglas J Hunley <doug@hunley.homeip.net>
0 siblings, 0 replies; 24+ messages in thread
From: Douglas J Hunley @ 2007-06-06 14:35 UTC (permalink / raw)
To: pgsql-performance; +Cc: Gregory Stark <stark@enterprisedb.com>
On Tuesday 05 June 2007 10:34:04 Douglas J Hunley wrote:
> On Monday 04 June 2007 17:11:23 Gregory Stark wrote:
> > Those plans look like they have a lot of casts to text in them. How have
> > you defined your indexes? Are your id columns really text?
>
> project table:
> Indexes:
> "project_pk" PRIMARY KEY, btree (id)
> "project_path" UNIQUE, btree (path)
>
> role table:
> Indexes:
> "role_pk" PRIMARY KEY, btree (id)
>
> role_default_user table:
> Indexes:
> "role_def_user_pk" PRIMARY KEY, btree (id)
> "role_def_u_prj_idx" UNIQUE, btree (role_id, default_user_class_id,
> project_id)
>
> role_operation table:
> Indexes:
> "role_operation_pk" PRIMARY KEY, btree (id)
> "role_oper_obj_oper" btree (object_type_id, operation_category,
> operation_name)
> "role_oper_role_id" btree (role_id)
>
> sfuser table:
> Indexes:
> "sfuser_pk" PRIMARY KEY, btree (id)
> "sfuser_username" UNIQUE, btree (username)
>
> projectmembership table:
> Indexes:
> "pjmb_pk" PRIMARY KEY, btree (id)
> "pjmb_projmember" UNIQUE, btree (project_id, member_id)
> "pjmb_member" btree (member_id)
>
> relationship table:
> Indexes:
> "relationship_pk" PRIMARY KEY, btree (id)
> "relation_origin" btree (origin_id)
> "relation_target" btree (target_id)
> "relation_type" btree (relationship_type_name)
>
> field_value table:
> Indexes:
> "field_value_pk" PRIMARY KEY, btree (id)
> "f_val_fid_val_idx" UNIQUE, btree (field_id, value)
> "field_class_idx" btree (value_class)
> "field_value_idx" btree (value)
>
> item table:
> Indexes:
> "item_pk" PRIMARY KEY, btree (id)
> "item_created_by_id" btree (created_by_id)
> "item_folder" btree (folder_id)
> "item_name" btree (name)
>
> and yes, the 'id' column is always: character varying type
>
> > And you don't have a 7.4 install around to compare the plans do you?
>
> I have a 7.3.19 db, if that would be useful
Any insight given the above?
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
"It is our moral duty to corrupt the young"
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-02 15:21 ` Rafael Martinez <r.m.guerrero@usit.uio.no>
4 siblings, 0 replies; 24+ messages in thread
From: Rafael Martinez @ 2007-06-02 15:21 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
Douglas J Hunley wrote:
Hello
> The DB server in question does nothing else, is running CentOS 4.5, kernel
> 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon
> 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.
>
After a very quick read of your configuration files, I found some
paramaters that need to be change if your server has 8GB of RAM. The
values of these parameters depend a lot of how much RAM you have, what
type of database you have (reading vs. writing) and how big the database is.
I do not have experience with 8.2.x yet, but with 8.1.x we are using as
defaults in out 8GB RAM servers these values in some of the paramaters
(they are not the only ones, but they are the minimum to change):
25% of RAM for shared_buffers
2/3 of ram for effective_cache_size
256MB for maintenance_work_mem
32-64MB for work_mem
128 checkpoint_segments
2 random_page_cost
And the most important of all:
fsync should be ***ON*** if you appreciate your data.
It looks like you are using default values ....
>
> #shared_buffers = 32MB # min 128kB or max_connections*16kB
> #work_mem = 1MB # min 64kB
> #maintenance_work_mem = 16MB # min 1MB
> fsync = off # turns forced synchronization on or off
> #effective_cache_size = 128MB
[........................]
--
Rafael Martinez, <r.m.guerrero@usit.uio.no>
Center for Information Technology Services
University of Oslo, Norway
PGP Public Key: http://folk.uio.no/rafael/;
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-02 15:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-03 17:29 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
4 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2007-06-02 15:25 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
Douglas J Hunley <doug@hunley.homeip.net> writes:
> ... We dumped their db, removed pgsql, installed the 8.2.4
> rpms from postgresql.org, did an initdb, and the pg_restored their data. It's
> been about a week now, and the customer is complaining that in their testing,
> they are seeing a 30% /decrease/ in general performance.
Well, you've definitely blown it on transferring the config-file
settings --- a quick look says that shared_buffers, work_mem, and
max_fsm_pages are all still default in the 8.2 config file.
Don't be frightened off by the "KB/MB" usages in the 8.2 file ---
you can still write "shared_buffers = 25000" if you'd rather specify
it in number of buffers than in megabytes.
There are some things you *did* transfer that I find pretty
questionable, like "enable_mergejoin = false". There are very major
differences between the 7.4 and 8.2 planners, so you need to revisit
the tests that led you to do that.
Another thing that seems strange is that the 8.2 config file does not
seem to have been processed by initdb --- or did you explicitly comment
out the settings it made?
Another thing to check is whether you ANALYZEd the new database after
loading data; a pg_dump/reload sequence doesn't do that.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:25 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-06-03 17:29 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 03:17 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Tom Lane <tgl@sss.pgh.pa.us>
0 siblings, 1 reply; 24+ messages in thread
From: Douglas J Hunley @ 2007-06-03 17:29 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
On Saturday 02 June 2007 11:25:11 Tom Lane wrote:
> Another thing that seems strange is that the 8.2 config file does not
> seem to have been processed by initdb --- or did you explicitly comment
> out the settings it made?
I don't understand this comment. You are saying 'initdb' will make changes to
the file? The file I sent is the working copy from the machine in question.
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
"Does it worry you that you don't talk any kind of sense?"
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:25 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Tom Lane <tgl@sss.pgh.pa.us>
2007-06-03 17:29 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-04 03:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-04 14:28 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Reid Thompson <Reid.Thompson@ateb.com>
0 siblings, 1 reply; 24+ messages in thread
From: Tom Lane @ 2007-06-04 03:17 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
Douglas J Hunley <doug@hunley.homeip.net> writes:
> On Saturday 02 June 2007 11:25:11 Tom Lane wrote:
>> Another thing that seems strange is that the 8.2 config file does not
>> seem to have been processed by initdb --- or did you explicitly comment
>> out the settings it made?
> I don't understand this comment. You are saying 'initdb' will make changes to
> the file? The file I sent is the working copy from the machine in question.
Yeah --- in a normal installation, initdb will provide un-commented
entries for these postgresql.conf parameters:
max_connections
shared_buffers
max_fsm_pages
datestyle
lc_messages
lc_monetary
lc_numeric
lc_time
(The first three are set dependent on SHMMAX probing, the others
dependent on locale.) Your conf file doesn't seem to have been through
that autoconfiguration step, which suggests someone poking at things
they should have left alone.
regards, tom lane
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:25 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Tom Lane <tgl@sss.pgh.pa.us>
2007-06-03 17:29 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 03:17 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-06-04 14:28 ` Reid Thompson <Reid.Thompson@ateb.com>
0 siblings, 0 replies; 24+ messages in thread
From: Reid Thompson @ 2007-06-04 14:28 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Douglas J Hunley <doug@hunley.homeip.net>; pgsql-performance
On Sun, 2007-06-03 at 23:17 -0400, Tom Lane wrote:
> Douglas J Hunley <doug@hunley.homeip.net> writes:
> > On Saturday 02 June 2007 11:25:11 Tom Lane wrote:
> >> Another thing that seems strange is that the 8.2 config file does not
> >> seem to have been processed by initdb --- or did you explicitly comment
> >> out the settings it made?
>
> > I don't understand this comment. You are saying 'initdb' will make changes to
> > the file? The file I sent is the working copy from the machine in question.
>
> Yeah --- in a normal installation, initdb will provide un-commented
> entries for these postgresql.conf parameters:
>
> max_connections
> shared_buffers
> max_fsm_pages
> datestyle
> lc_messages
> lc_monetary
> lc_numeric
> lc_time
>
> (The first three are set dependent on SHMMAX probing, the others
> dependent on locale.) Your conf file doesn't seem to have been through
> that autoconfiguration step, which suggests someone poking at things
> they should have left alone.
>
> regards, tom lane
>
> ---------------------------(end of broadcast)---------------------------
> TIP 4: Have you searched our list archives?
>
> http://archives.postgresql.org
A WAG, but perhaps the new conf file was overwritten after installation
with the one from the 'old' installation '..because that's the
configuration that we've already tweaked and was working...'
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-02 15:45 ` Kenneth Marshall <ktm@rice.edu>
4 siblings, 0 replies; 24+ messages in thread
From: Kenneth Marshall @ 2007-06-02 15:45 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
On Sat, Jun 02, 2007 at 09:13:32AM -0400, Douglas J Hunley wrote:
> Hello great gurus of performance:
> Our 'esteemed' Engr group recently informed a customer that in their testing,
> upgrading to 8.2.x improved the performance of our J2EE
> application "approximately 20%", so of course, the customer then tasked me
> with upgrading them. We dumped their db, removed pgsql, installed the 8.2.4
> rpms from postgresql.org, did an initdb, and the pg_restored their data. It's
> been about a week now, and the customer is complaining that in their testing,
> they are seeing a 30% /decrease/ in general performance. Of course, our Engr
> group is being less than responsive, and I have a feeling all they're doing
> is googling for answers, so I'm turning to this group for actual
> assistance :)
> I'd like to start by examining the poistgresql.conf file. Under 7.4.x, we had
> spent the better part of their 2 years as a customer tuning and tweaking
> setting. I've attached the file that was in place at the time of upgrade. I
> did some cursory googling of my own, and quickly realized that enough has
> changed in v8 that I'm not comfortable making the exact same modification to
> their new config file as some options are new, some have gone away, etc. I've
> attached the existing v8 conf file as well.
> I'd really like it if someone could assist me in determining which of the v8
> options need adjusted to be 'functionally equivalent' to the v7 file. Right
> now, my goal is to get the customer back to the previous level of
> performance, and only then pursue further optimization. I can provide any and
> all information needed, but didn't know what to include initially, so I've
> opted to include the minimal :)
> The DB server in question does nothing else, is running CentOS 4.5, kernel
> 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2 Xeon
> 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.
>
> Thank you in advance for any and all assistance you can provide.
> --
> Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
> http://doug.hunley.homeip.net
>
Douglas,
If these are the current config files, it is no wonder that the performance
is worse. Here are the things that need to be changed right from the start.
The old 7.x is on the left and the 8.2 value is on the right. Make them
the same to start and see how it looks then.
setting 7.x current 8.2
------------------------------------------------------
shared_buffers = 25000 / 32MB (=3906)
sort_mem/work_mem = 15000/ 1MB (=122)
vacuum_mem/maint_work_mem = 100000 / 16MB (=1950)
effective_cache = 196608 / 128MB (=15600) should start between 200k-500k
These changes alone should get you back to the performance point you are
expecting. It would also be worth re-evaluating whether or not you should
be disabling enable_mergehashjoin in general, and not just for specific
problem queries. I would also tend to start with an effective_cache at
the higher end on a dedicated DB server. Good luck with your tuning. If
the 8.2 config file you posted is the one that has been in use, these few
changes will restore your performance and then some.
Ken
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-05 17:01 ` Hanu Kurubar <hanu.kurubar@gmail.com>
4 siblings, 0 replies; 24+ messages in thread
From: Hanu Kurubar @ 2007-06-05 17:01 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: pgsql-performance
Find the attached Postgres.conf file. I am using 8.1 Version in Lab.
I haven't done any changes to this conf file to improve the performance.
What are the attributes needs to be modified in the conf file to improve the
performance?
I am looking forward for your assistance.
Regards,
Hanu
On 6/2/07, Douglas J Hunley <doug@hunley.homeip.net> wrote:
>
> Hello great gurus of performance:
> Our 'esteemed' Engr group recently informed a customer that in their
> testing,
> upgrading to 8.2.x improved the performance of our J2EE
> application "approximately 20%", so of course, the customer then tasked me
> with upgrading them. We dumped their db, removed pgsql, installed the
> 8.2.4
> rpms from postgresql.org, did an initdb, and the pg_restored their data.
> It's
> been about a week now, and the customer is complaining that in their
> testing,
> they are seeing a 30% /decrease/ in general performance. Of course, our
> Engr
> group is being less than responsive, and I have a feeling all they're
> doing
> is googling for answers, so I'm turning to this group for actual
> assistance :)
> I'd like to start by examining the poistgresql.conf file. Under 7.4.x, we
> had
> spent the better part of their 2 years as a customer tuning and tweaking
> setting. I've attached the file that was in place at the time of upgrade.
> I
> did some cursory googling of my own, and quickly realized that enough has
> changed in v8 that I'm not comfortable making the exact same modification
> to
> their new config file as some options are new, some have gone away, etc.
> I've
> attached the existing v8 conf file as well.
> I'd really like it if someone could assist me in determining which of the
> v8
> options need adjusted to be 'functionally equivalent' to the v7 file.
> Right
> now, my goal is to get the customer back to the previous level of
> performance, and only then pursue further optimization. I can provide any
> and
> all information needed, but didn't know what to include initially, so I've
> opted to include the minimal :)
> The DB server in question does nothing else, is running CentOS 4.5, kernel
> 2.6.9-55.ELsmp. Hyperthreading is disabled in the BIOS and there are 2
> Xeon
> 3.4Ghz cpus. There is 8Gb of RAM in the machine, and another 8Gb of swap.
>
> Thank you in advance for any and all assistance you can provide.
> --
> Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
> http://doug.hunley.homeip.net
>
> Handy Guide to Modern Science:
> 1. If it's green or it wiggles, it's biology.
> 2. If it stinks, it's chemistry.
> 3. If it doesn't work, it's physics.
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 3: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faq
>
>
>
--
With best regards,
Hanumanthappa Kurubar
Mobile: 98 801 800 65
Attachments:
postgresql.conf (text/plain, 13.7K) download
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The '=' is optional.) White space may be used. Comments are introduced
# with '#' anywhere on a line. The complete list of option names and
# allowed values can be found in the PostgreSQL documentation. The
# commented-out settings shown in this file represent the default values.
#
# Please note that re-commenting a setting is NOT sufficient to revert it
# to the default value, unless you restart the postmaster.
#
# Any option can also be given as a command line switch to the
# postmaster, e.g. 'postmaster -c log_connections=on'. Some options
# can be changed at run-time with the 'SET' SQL command.
#
# This file is read on postmaster startup and when the postmaster
# receives a SIGHUP. If you edit the file on a running system, you have
# to SIGHUP the postmaster for the changes to take effect, or use
# "pg_ctl reload". Some settings, such as listen_addresses, require
# a postmaster shutdown and restart to take effect.
#---------------------------------------------------------------------------
# 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 = 'ConfigDir/pg_hba.conf' # host-based authentication file
#ident_file = 'ConfigDir/pg_ident.conf' # IDENT configuration file
# If external_pid_file is not explicitly set, no extra pid file is written.
#external_pid_file = '(none)' # write an extra pid file
#---------------------------------------------------------------------------
# CONNECTIONS AND AUTHENTICATION
#---------------------------------------------------------------------------
# - Connection Settings -
listen_addresses = '*' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
port = 5432
max_connections = 100
# 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 = ''
#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 = off
#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 = 1000 # min 16 or max_connections*2, 8KB each
#temp_buffers = 1000 # min 100, 8KB each
#max_prepared_transactions = 5 # 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 = 16384 # min 1024, size in KB
#max_stack_depth = 2048 # min 100, size in KB
# - Free Space Map -
#max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each
#max_fsm_relations = 1000 # 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 = 200 # 10-10000 milliseconds between rounds
#bgwriter_lru_percent = 1.0 # 0-100% of LRU buffers scanned/round
#bgwriter_lru_maxpages = 5 # 0-1000 buffers max written/round
#bgwriter_all_percent = 0.333 # 0-100% of all buffers scanned/round
#bgwriter_all_maxpages = 5 # 0-1000 buffers max written/round
#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------
# - Settings -
#fsync = on # 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 = 0 # range 0-100000, in microseconds
#commit_siblings = 5 # range 1-1000
# - Checkpoints -
#checkpoint_segments = 3 # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300 # 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 = 1000 # typically 8KB each
#random_page_cost = 4 # units are one sequential page fetch
# cost
#cpu_tuple_cost = 0.01 # (same)
#cpu_index_tuple_cost = 0.001 # (same)
#cpu_operator_cost = 0.0025 # (same)
# - Genetic Query Optimizer -
#geqo = 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 = on # 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 = on
#stats_command_string = off
#stats_block_level = off
stats_row_level = on
#stats_reset_on_server_start = off
#---------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#---------------------------------------------------------------------------
autovacuum = on # 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 = 'C' # locale for system error message
# strings
lc_monetary = 'C' # locale for monetary formatting
lc_numeric = 'C' # locale for number formatting
lc_time = 'C' # 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
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on
#default_with_oids = off
#escape_string_warning = off
# - Other Platforms & Clients -
#transform_null_equals = off
#---------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#---------------------------------------------------------------------------
#custom_variable_classes = '' # list of custom variable class names
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
@ 2007-06-03 20:39 Luke Lonergan <LLonergan@greenplum.com>
2007-06-04 12:40 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
0 siblings, 1 reply; 24+ messages in thread
From: Luke Lonergan @ 2007-06-03 20:39 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; Tom Lane <tgl@sss.pgh.pa.us>; +Cc: pgsql-performance
When you initdb, a config file is edited from the template by initdb to reflect your machine config.
- Luke
Msg is shrt cuz m on ma treo
-----Original Message-----
From: Douglas J Hunley [mailto:doug@hunley.homeip.net]
Sent: Sunday, June 03, 2007 02:30 PM Eastern Standard Time
To: Tom Lane
Cc: pgsql-performance@postgresql.org
Subject: Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
On Saturday 02 June 2007 11:25:11 Tom Lane wrote:
> Another thing that seems strange is that the 8.2 config file does not
> seem to have been processed by initdb --- or did you explicitly comment
> out the settings it made?
I don't understand this comment. You are saying 'initdb' will make changes to
the file? The file I sent is the working copy from the machine in question.
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
"Does it worry you that you don't talk any kind of sense?"
---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-03 20:39 Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Luke Lonergan <LLonergan@greenplum.com>
@ 2007-06-04 12:40 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 12:44 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 1 reply; 24+ messages in thread
From: Douglas J Hunley @ 2007-06-04 12:40 UTC (permalink / raw)
To: Luke Lonergan <LLonergan@greenplum.com>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
On Sunday 03 June 2007 16:39:51 Luke Lonergan wrote:
> When you initdb, a config file is edited from the template by initdb to
> reflect your machine config.
I didn't realize that. I'll have to harass the rest of the team to see if
someone overwrote that file or not. In the interim, I did an 'initdb' to
another location on the same box and then copied those values into the config
file. That's cool to do, I assume?
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
Cowering in a closet is starting to seem like a reasonable plan.
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
2007-06-03 20:39 Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Luke Lonergan <LLonergan@greenplum.com>
2007-06-04 12:40 ` Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
@ 2007-06-04 12:44 ` Heikki Linnakangas <heikki@enterprisedb.com>
0 siblings, 0 replies; 24+ messages in thread
From: Heikki Linnakangas @ 2007-06-04 12:44 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: Luke Lonergan <LLonergan@greenplum.com>; Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Douglas J Hunley wrote:
> On Sunday 03 June 2007 16:39:51 Luke Lonergan wrote:
>> When you initdb, a config file is edited from the template by initdb to
>> reflect your machine config.
>
> I didn't realize that. I'll have to harass the rest of the team to see if
> someone overwrote that file or not. In the interim, I did an 'initdb' to
> another location on the same box and then copied those values into the config
> file. That's cool to do, I assume?
Yeah, that's ok.
--
Heikki Linnakangas
EnterpriseDB http://www.enterprisedb.com
^ permalink raw reply [nested|flat] 24+ messages in thread
* Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
@ 2007-06-04 12:51 Luke Lonergan <LLonergan@greenplum.com>
0 siblings, 0 replies; 24+ messages in thread
From: Luke Lonergan @ 2007-06-04 12:51 UTC (permalink / raw)
To: Douglas J Hunley <doug@hunley.homeip.net>; +Cc: Tom Lane <tgl@sss.pgh.pa.us>; pgsql-performance
Absolutely!
A summary of relevant comments so far are:
- enable-mergejoin
- shared-buffers
- fsync
Another to consider if you use indexes is random-page-cost.
What would be helpful is if you could identify a slow query and post the explain analyze here.
The concurrent performance of many users should just be faster with 8.2, so I'd think it's a problem with plans.
- Luke
Msg is shrt cuz m on ma treo
-----Original Message-----
From: Douglas J Hunley [mailto:doug@hunley.homeip.net]
Sent: Monday, June 04, 2007 08:40 AM Eastern Standard Time
To: Luke Lonergan
Cc: Tom Lane; pgsql-performance@postgresql.org
Subject: Re: [PERFORM] upgraded to pgsql 8.2.4, getting worse performance then 7.4.x
On Sunday 03 June 2007 16:39:51 Luke Lonergan wrote:
> When you initdb, a config file is edited from the template by initdb to
> reflect your machine config.
I didn't realize that. I'll have to harass the rest of the team to see if
someone overwrote that file or not. In the interim, I did an 'initdb' to
another location on the same box and then copied those values into the config
file. That's cool to do, I assume?
--
Douglas J Hunley (doug at hunley.homeip.net) - Linux User #174778
http://doug.hunley.homeip.net
Cowering in a closet is starting to seem like a reasonable plan.
^ permalink raw reply [nested|flat] 24+ messages in thread
end of thread, other threads:[~2007-06-06 14:35 UTC | newest]
Thread overview: 24+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-06-02 13:13 upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Michael Fuhr <mike@fuhr.org>
2007-06-02 15:31 ` Stefan Kaltenbrunner <stefan@kaltenbrunner.cc>
2007-06-03 17:24 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-03 22:30 ` Greg Smith <gsmith@gregsmith.com>
2007-06-04 17:05 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 18:08 ` Alvaro Herrera <alvherre@commandprompt.com>
2007-06-04 20:01 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 21:11 ` Gregory Stark <stark@enterprisedb.com>
2007-06-04 21:17 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-06-05 14:25 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-05 14:34 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-06 14:35 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-02 15:21 ` Rafael Martinez <r.m.guerrero@usit.uio.no>
2007-06-02 15:25 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-03 17:29 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 03:17 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-06-04 14:28 ` Reid Thompson <Reid.Thompson@ateb.com>
2007-06-02 15:45 ` Kenneth Marshall <ktm@rice.edu>
2007-06-05 17:01 ` Hanu Kurubar <hanu.kurubar@gmail.com>
2007-06-03 20:39 Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Luke Lonergan <LLonergan@greenplum.com>
2007-06-04 12:40 ` Douglas J Hunley <doug@hunley.homeip.net>
2007-06-04 12:44 ` Heikki Linnakangas <heikki@enterprisedb.com>
2007-06-04 12:51 Re: upgraded to pgsql 8.2.4, getting worse performance then 7.4.x Luke Lonergan <LLonergan@greenplum.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