Hi all,

After a long time of reading the general list it's time to subscribe to
this one...

We have adapted our application (originally written for oracle) to
postgres, and switched part of our business to a postgres data base.

The data base has in the main tables around 150 million rows, the whole
data set takes ~ 30G after the initial migration. After ~ a month of
usage that bloated to ~ 100G. We installed autovacuum after ~ 2 weeks.

The main table is heavily updated during the active periods of usage,
which is coming in bursts.

Now Oracle on the same hardware has no problems handling it (the load),
but postgres comes to a crawl. Examining the pg_stats_activity table I
see the updates on the main table as being the biggest problem, they are
very slow. The table has a few indexes on it, I wonder if they are
updated too on an update ? The index fields are not changing. In any
case, I can't explain why the updates are so much slower on postgres.

Sorry for being fuzzy a bit, I spent quite some time figuring out what I
can do and now I have to give up and ask for help.

The machine running the DB is a debian linux, details:

$ cat /proc/cpuinfo
processor       : 0
vendor_id       : GenuineIntel
cpu family      : 6
model           : 11
model name      : Intel(R) Pentium(R) III CPU family      1266MHz
stepping        : 1
cpu MHz         : 1263.122
cache size      : 512 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 mmx fxsr sse
bogomips        : 2490.36
 
processor       : 1
vendor_id       : GenuineIntel
cpu family      : 6
model           : 11
model name      : Intel(R) Pentium(R) III CPU family      1266MHz
stepping        : 1
cpu MHz         : 1263.122
cache size      : 512 KB
fdiv_bug        : no
hlt_bug         : no
f00f_bug        : no
coma_bug        : no
fpu             : yes
fpu_exception   : yes
cpuid level     : 2
wp              : yes
flags           : fpu vme de pse tsc msr pae mce cx8 apic sep mtrr pge
mca cmov pat pse36 mmx fxsr sse
bogomips        : 2514.94
 

$ uname -a
Linux *** 2.6.12.3 #1 SMP Tue Oct 11 13:13:00 CEST 2005 i686 GNU/Linux


$ cat /proc/meminfo
MemTotal:      4091012 kB
MemFree:        118072 kB
Buffers:         18464 kB
Cached:        3393436 kB
SwapCached:          0 kB
Active:         947508 kB
Inactive:      2875644 kB
HighTotal:     3211264 kB
HighFree:          868 kB
LowTotal:       879748 kB
LowFree:        117204 kB
SwapTotal:           0 kB
SwapFree:            0 kB
Dirty:           13252 kB
Writeback:           0 kB
Mapped:         829300 kB
Slab:            64632 kB
CommitLimit:   2045504 kB
Committed_AS:  1148064 kB
PageTables:      75916 kB
VmallocTotal:   114680 kB
VmallocUsed:        96 kB
VmallocChunk:   114568 kB


The disk used for the data is an external raid array, I don't know much
about that right now except I think is some relatively fast IDE stuff.
In any case the operations should be cache friendly, we don't scan over
and over the big tables...

The postgres server configuration is attached.

I have looked in the postgres statistics tables, looks like most of the
needed data is always cached, as in the most accessed tables the
load/hit ratio is mostly something like 1/100, or at least 1/30.


Is anything in the config I got very wrong for the given machine, or
what else should I investigate further ? If I can't make this fly, the
obvious solution will be to move back to Oracle, cash out the license
and forget about postgres forever...

TIA,
Csaba.

# -----------------------------
# 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_address, 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'    # the host-based authentication file
# ident_file = 'ConfigDir/pg_ident.conf'  # the 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 interface(s) to listen on; 
                                # defaults to localhost, '*' = any
#port = 5432
max_connections = 400
        # 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
#unix_socket_directory = ''
#unix_socket_group = ''
#unix_socket_permissions = 0777 # octal
#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 -

shared_buffers = 50000          # min 16, at least max_connections*2, 8KB each
#work_mem = 1024                # min 64, size in KB
work_mem = 4096
#maintenance_work_mem = 16384   # min 1024, size in KB
maintenance_work_mem = 131072
#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_pages = 12000000
#max_fsm_relations = 1000       # min 100, ~50 bytes each
max_fsm_relations = 5000

# - 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_delay = 100
#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_percent = 1           # 0-100% of dirty buffers in each round
#bgwriter_maxpages = 100        # 0-1000 buffers max per round


#---------------------------------------------------------------------------
# WRITE AHEAD LOG
#---------------------------------------------------------------------------

# - Settings -

#fsync = true                   # turns forced synchronization on or off
#wal_sync_method = fsync        # the default varies across platforms:
                                # fsync, fdatasync, fsync_writethrough,
                                # open_sync, open_datasync
#wal_buffers = 8                # min 4, 8KB each
wal_buffers = 64
#commit_delay = 500             # range 0-100000, in microseconds
#commit_siblings = 5            # range 1-1000

# - Checkpoints -

checkpoint_segments = 64        # in logfile segments, min 1, 16MB each
#checkpoint_timeout = 300       # range 30-3600, in seconds
checkpoint_timeout = 600
#checkpoint_warning = 30        # 0 is off, in seconds

# - Archiving -

archive_command = 'scp %p [EMAIL PROTECTED]:/sbarchlogs/%f'

#---------------------------------------------------------------------------
# QUERY TUNING
#---------------------------------------------------------------------------

# - Planner Method Configuration -

#enable_hashagg = true
#enable_hashjoin = true
#enable_indexscan = true
#enable_mergejoin = true
#enable_nestloop = true
#enable_seqscan = true
#enable_sort = true
#enable_tidscan = true

# - Planner Cost Constants -

effective_cache_size = 350000   # typically 8KB each
random_page_cost = 1.5          # 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 = true
#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
#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 relevant when logging to stderr:
#redirect_stderr = false    # Enable capturing of stderr into log files.
# These are only relevant if redirect_stderr is true:
#log_directory = 'pg_log'   # Directory where log files are written.
                            # May be specified absolute or relative to PGDATA
#log_filename = 'postgresql-%Y-%m-%d_%H%M%S.log' # Log file name pattern.
                            # May include strftime() escapes
#log_truncate_on_rotation = false  # If true, 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 false, 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 = info         # Values, in order of decreasing detail:        
       # Dragos: debug1
                                #   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, in milliseconds.
log_min_duration_statement = 2000

#silent_mode = false             # DO NOT USE without syslog or redirect_stderr

# - What to Log -

debug_print_parse = false 
debug_print_rewritten = false 
debug_print_plan = false
debug_pretty_print = false
#log_connections = false
#log_disconnections = false
log_duration = false
#log_line_prefix = ''           # e.g. '<%u%%%d> ' 
                                # %u=user name %d=database name
                                # %r=remote host and port
                                # %p=PID %t=timestamp %i=command tag
                                # %c=session id %l=session line number
                                # %s=session start timestamp %x=transaction id
                                # %q=stop here in non-session processes
                                # %%='%'
log_line_prefix = '%d:%p:%r:%t:'
log_statement = 'none'          # none, mod, ddl, all
#log_hostname = 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 = true
stats_block_level = true
stats_row_level = true
stats_reset_on_server_start = false


#---------------------------------------------------------------------------
# CLIENT CONNECTION DEFAULTS
#---------------------------------------------------------------------------

# - Statement Behavior -

#search_path = '$user,public'   # schema names
#default_tablespace = ''        # a tablespace name, or '' for default
#check_function_bodies = true
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = false
#statement_timeout = 0          # 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 might be changed
lc_messages = '[EMAIL PROTECTED]'               # locale for system error 
message strings
lc_monetary = '[EMAIL PROTECTED]'               # locale for monetary formatting
lc_numeric = '[EMAIL PROTECTED]'                # locale for number formatting
lc_time = '[EMAIL PROTECTED]'                   # locale for time formatting

# - Other Defaults -

#explain_pretty_print = true
#dynamic_library_path = '$libdir'


#---------------------------------------------------------------------------
# LOCK MANAGEMENT
#---------------------------------------------------------------------------

deadlock_timeout = 2000 # in milliseconds
#max_locks_per_transaction = 64 # min 10, ~200*max_connections bytes each


#---------------------------------------------------------------------------
# VERSION/PLATFORM COMPATIBILITY
#---------------------------------------------------------------------------

# - Previous Postgres Versions -

add_missing_from = true
#regex_flavor = advanced        # advanced, extended, or basic
#sql_inheritance = true
#default_with_oids = true

# - Other Platforms & Clients -

#transform_null_equals = false
---------------------------(end of broadcast)---------------------------
TIP 6: explain analyze is your friend

Reply via email to