Thread: exponential performance decrease in ISD transaction
Hello,
We are doing some testing and have a very strange behaviour in the
performance obtained with postgres while executing a
Insert/select/delete transaction.
Software and Hardware details:
- O.S = Red hat 6.2
$ uname -a
Linux localhost.localdomain 2.6.32-220.el6.x86_64 #1 SMP Wed Nov 9
08:03:13 EST 2011 x86_64 x86_64 x86_64 GNU/Linux
- RAM = 25 GB (resources are guaranteed)
- 4CPU's
- Machine is running in an ESX Vsphere
-Postgresql version installed is : postgresql-9.1.3 although when
querying the database we retrieve this output.
postgres=# select * from version();
version
-----------------------------------------------------------------------------------------------------------------
PostgreSQL 8.4.9 on x86_64-redhat-linux-gnu, compiled by GCC gcc
(GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit
(1 row)
postgres=#
We are connecting to the database with 2 simple java programs.
Program 1: dbtransfromfile: this program creates a simple table
consisting of a one int column table. After the creation, the program
inserts 1000 tuples in the table, which are never deleted, after that
the program reads a transaction pattern from a given file and executes
it a number of times determined when the program is launched.
The transaction we are launching is (INSERT/SELECT/DELETE) the following:
insert into T_TEST values (1);select * from T_TEST where
c1=1000;delete from T_TEST where c1=1;commit;
Program 2: dbtransperf: this program measures the number of new
transactions that have been commited since the dbtransperf program was
launched.
We get the number of transactions done in the rdbms up to that moment
in the target database by means of the following query:
String sentencia = "select now(), xact_commit from pg_stat_database
where datid=" +ps_oid;
Later on, the program makes its own calculations to get de number of
commits per second.
Our Test consists of:
Launching dbtransperf in order to start measuring performance
(monitoring), and while running we concurrently launch the
dbtransfromfile java program, which is the one which will execute the
transaction indicated in the file.
For instance for a concrete test of 50.000 transactions we obtain the
following results with the monitoring program (if you plot these
results into an Excelworksheet you'll see an exponetial decreasing
behaviour) :
PostgreSQL
438
617
490
469
420
381
363
335
311
303
285
275
260
251
251
239
227
221
221
212
207
207
200
193
189
187
183
178
176
173
167
169
165
164
159
158
154
155
154
148
149
147
141
143
141
141
137
138
134
133
133
133
130
131
125
127
126
120
125
123
124
123
118
119
118
118
118
116
112
112
112
113
110
112
111
111
109
108
108
107
108
107
105
105
107
104
103
103
102
100
102
100
100
101
98
99
97
97
97
96
96
95
94
94
94
94
93
93
92
92
92
91
92
91
69
108
87
66
88
88
88
86
86
86
84
86
86
84
83
81
84
83
83
84
81
82
82
82
80
80
80
80
80
80
81
80
79
80
80
78
78
78
78
78
78
77
78
77
77
76
74
76
76
76
75
74
74
74
74
56
74
72
74
74
75
72
71
72
72
72
72
71
71
70
70
70
70
70
70
70
70
70
70
70
67
68
68
68
68
68
68
54
We have run another similar program running simple insert massive
transactions, also with simple massive select transactions, and simple
massive deletes as trasactions. The results for isolated type
transactions don't show this behaviour, in fact they are very stable
and fast results, but when executing a compounded
INSERT/SELECT/DELETE/COMMIT transaction, the results show this odd
performance behaviour, which we find unsatisfactory, we undestand this
behaviour shouldn't be a normal one.
Are we missing something? Is the configuration incorrect? This is our
config file:
[postgsql@localhost data]$ cat postgresql.conf
# -----------------------------
# PostgreSQL configuration file
# -----------------------------
#
# This file consists of lines of the form:
#
# name = value
#
# (The "=" is optional.) Whitespace may be used. Comments are introduced with
# "#" anywhere on a line. The complete list of parameter names and allowed
# values can be found in the PostgreSQL documentation.
#
# The commented-out settings shown in this file represent the default values.
# Re-commenting a setting is NOT sufficient to revert it to the default value;
# you need to reload the server.
#
# This file is read on server startup and when the server receives a SIGHUP
# signal. 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
# parameters, which are marked below, require a server shutdown and restart to
# take effect.
#
# Any parameter can also be given as a command-line option to the server, e.g.,
# "postgres -c log_connections=on". Some parameters can be changed at run time
# with the "SET" SQL command.
#
# Memory units: kB = kilobytes Time units: ms = milliseconds
# MB = megabytes s = seconds
# GB = gigabytes min = minutes
# h = hours
# d = days
#------------------------------------------------------------------------------
# FILE LOCATIONS
#------------------------------------------------------------------------------
# The default values of these variables are driven from the -D command-line
# option 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 = 'localhost' # what IP address(es) to listen on;
# comma-separated list of addresses;
# defaults to 'localhost', '*' = all
# (change requires restart)
port = 50008 # (change requires restart)
max_connections = 100 # (change requires restart)
# Note: Increasing max_connections costs ~400 bytes of shared memory per
# connection slot, plus lock space (see max_locks_per_transaction).
#superuser_reserved_connections = 3 # (change requires restart)
#unix_socket_directory = '' # (change requires restart)
#unix_socket_group = '' # (change requires restart)
#unix_socket_permissions = 0777 # begin with 0 to use octal notation
# (change requires restart)
#bonjour_name = '' # defaults to the computer name
# (change requires restart)
# - Security and Authentication -
#authentication_timeout = 1min # 1s-600s
#ssl = off # (change requires restart)
#ssl_ciphers = 'ALL:!ADH:!LOW:!EXP:!MD5:@STRENGTH' # allowed SSL ciphers
# (change requires restart)
#ssl_renegotiation_limit = 512MB # amount of data between renegotiations
#password_encryption = on
#db_user_namespace = off
# Kerberos and GSSAPI
#krb_server_keyfile = ''
#krb_srvname = 'postgres' # (Kerberos only)
#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 = 5120MB # min 128kB
# (change requires restart)
#temp_buffers = 8MB # min 800kB
#max_prepared_transactions = 0 # zero disables the feature
# (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).
# It is not advisable to set max_prepared_transactions nonzero unless you
# actively intend to use prepared transactions.
#work_mem = 1MB # min 64kB
#maintenance_work_mem = 16MB # min 1MB
#max_stack_depth = 2MB # min 100kB
# - 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 = 0ms # 0-100 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 # 1-10000 credits
# - Background Writer -
#bgwriter_delay = 200ms # 10-10000ms between rounds
#bgwriter_lru_maxpages = 100 # 0-1000 max buffers written/round
#bgwriter_lru_multiplier = 2.0 # 0-10.0 multipler on buffers
scanned/round
# - Asynchronous Behavior -
#effective_io_concurrency = 1 # 1-1000. 0 disables prefetching
#------------------------------------------------------------------------------
# WRITE AHEAD LOG
#------------------------------------------------------------------------------
# - Settings -
#fsync = on # turns forced synchronization on or off
#synchronous_commit = on # immediate fsync at commit
#wal_sync_method = fsync # the default is the first option
# supported by the operating system:
# open_datasync
# fdatasync (default on Linux)
# fsync
# fsync_writethrough
# open_sync
#full_page_writes = on # recover from partial page writes
wal_buffers = 16000kB # min 32kB
# (change requires restart)
#wal_writer_delay = 200ms # 1-10000 milliseconds
#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 = 5min # range 30s-1h
#checkpoint_completion_target = 0.5 # checkpoint target duration, 0.0 - 1.0
#checkpoint_warning = 30s # 0 disables
# - Archiving -
#archive_mode = off # allows archiving to be done
# (change requires restart)
#archive_command = '' # command to use to archive a logfile segment
#archive_timeout = 0 # force a logfile segment switch after this
# number of seconds; 0 disables
#------------------------------------------------------------------------------
# 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 -
#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 = 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 = 100 # range 1-10000
#constraint_exclusion = partition # on, off, or partition
#cursor_tuple_fraction = 0.1 # range 0.0-1.0
#from_collapse_limit = 8
#join_collapse_limit = 8 # 1 disables collapsing of explicit
# JOIN clauses
#------------------------------------------------------------------------------
# ERROR REPORTING AND LOGGING
#------------------------------------------------------------------------------
# - Where to Log -
#log_destination = 'stderr' # Valid values are combinations of
# stderr, csvlog, syslog and eventlog,
# depending on platform. csvlog
# requires logging_collector to be on.
# This is used when logging to stderr:
logging_collector = on # Enable capturing of stderr and csvlog
# into log files. Required to be on for
# csvlogs.
# (change requires restart)
# These are only used if logging_collector is on:
log_directory = 'pg_log' # directory where log files are written,
# can be absolute or relative to PGDATA
log_filename = 'postgresql-%a.log' # log file name pattern,
# can include strftime() escapes
log_truncate_on_rotation = on # If on, an 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 disables.
log_rotation_size = 0 # Automatic rotation of logfiles will
# happen after that much log output.
# 0 disables.
# These are relevant when logging to syslog:
#syslog_facility = 'LOCAL0'
#syslog_ident = 'postgres'
#silent_mode = off # Run server silently.
# DO NOT USE without syslog or
# logging_collector
# (change requires restart)
# - 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 = warning # 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 decreasing detail:
# debug5
# debug4
# debug3
# debug2
# debug1
# info
# notice
# warning
# error
# log
# fatal
# panic (effectively off)
#log_min_duration_statement = -1 # -1 is disabled, 0 logs all statements
# and their durations, > 0 logs only
# statements running at least
this number
# of milliseconds
# - What to Log -
#debug_print_parse = off
#debug_print_rewritten = off
#debug_print_plan = off
#debug_pretty_print = on
#log_checkpoints = off
#log_connections = off
#log_disconnections = off
#log_duration = off
#log_hostname = off
#log_line_prefix = '' # special values:
# %u = user name
# %d = database name
# %r = remote host and port
# %h = remote host
# %p = process ID
# %t = timestamp without milliseconds
# %m = timestamp with milliseconds
# %i = command tag
# %c = session ID
# %l = session line number
# %s = session start timestamp
# %v = virtual transaction ID
# %x = transaction ID (0 if none)
# %q = stop here in non-session
# processes
# %% = '%'
# e.g. '<%u%%%d> '
#log_lock_waits = off # log lock waits >= deadlock_timeout
#log_statement = 'none' # none, ddl, mod, all
#log_temp_files = -1 # log temporary files equal or larger
# than the specified size in kilobytes;
# -1 disables, 0 logs all temp files
#log_timezone = unknown # actually, defaults to TZ environment
# setting
#------------------------------------------------------------------------------
# RUNTIME STATISTICS
#------------------------------------------------------------------------------
# - Query/Index Statistics Collector -
#track_activities = on
#track_counts = on
#track_functions = none # none, pl, all
#track_activity_query_size = 1024
#update_process_title = on
#stats_temp_directory = 'pg_stat_tmp'
# - Statistics Monitoring -
#log_parser_stats = off
#log_planner_stats = off
#log_executor_stats = off
#log_statement_stats = off
#------------------------------------------------------------------------------
# AUTOVACUUM PARAMETERS
#------------------------------------------------------------------------------
#autovacuum = on # Enable autovacuum subprocess? 'on'
# requires track_counts to also be on.
#log_autovacuum_min_duration = -1 # -1 disables, 0 logs all actions and
# their durations, > 0 logs only
# actions running at least this number
# of milliseconds.
#autovacuum_max_workers = 3 # max number of autovacuum subprocesses
#autovacuum_naptime = 1min # time between autovacuum runs
#autovacuum_vacuum_threshold = 50 # min number of row updates before
# vacuum
#autovacuum_analyze_threshold = 50 # min number of row updates before
# analyze
#autovacuum_vacuum_scale_factor = 0.2 # fraction of table size before vacuum
#autovacuum_analyze_scale_factor = 0.1 # fraction of table size before analyze
#autovacuum_freeze_max_age = 200000000 # maximum XID age before forced vacuum
# (change requires restart)
#autovacuum_vacuum_cost_delay = 20ms # default vacuum cost delay for
# autovacuum, in milliseconds;
# -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
#temp_tablespaces = '' # a list of tablespace names, '' uses
# only default tablespace
#check_function_bodies = on
#default_transaction_isolation = 'read committed'
#default_transaction_read_only = off
#session_replication_role = 'origin'
#statement_timeout = 0 # in milliseconds, 0 is disabled
#vacuum_freeze_min_age = 50000000
#vacuum_freeze_table_age = 150000000
#xmlbinary = 'base64'
#xmloption = 'content'
# - Locale and Formatting -
datestyle = 'iso, mdy'
#intervalstyle = 'postgres'
#timezone = unknown # actually, defaults to TZ environment
# setting
#timezone_abbreviations = 'Default' # Select the set of available time zone
# abbreviations. Currently, there are
# Default
# Australia
# India
# You can 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, but they can 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
# default configuration for text search
default_text_search_config = 'pg_catalog.english'
# - Other Defaults -
#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 PostgreSQL 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
#regex_flavor = advanced # advanced, extended, or basic
#sql_inheritance = on
#standard_conforming_strings = off
#synchronize_seqscans = on
# - Other Platforms and Clients -
#transform_null_equals = off
#------------------------------------------------------------------------------
# CUSTOMIZED OPTIONS
#------------------------------------------------------------------------------
#custom_variable_classes = '' # list of custom variable class names
We would be very thankful if you could help us solve this worrying issue.
Thanks in advance and regards,
dbaneedshelp
On 31.08.2012 15:27, John Nash wrote: > Program 1: dbtransfromfile: this program creates a simple table > consisting of a one int column table. After the creation, the program > inserts 1000 tuples in the table, which are never deleted, after that > the program reads a transaction pattern from a given file and executes > it a number of times determined when the program is launched. > > The transaction we are launching is (INSERT/SELECT/DELETE) the following: > > insert into T_TEST values (1);select * from T_TEST where > c1=1000;delete from T_TEST where c1=1;commit; Sounds like the table keeps growing when rows are inserted and subsequently deleted. PostgreSQL doesn't immediately remove deleted tuples from the underlying file, but simply marks them as deleted. The rows are not physically removed until autovacuum kicks in and cleans it up, or the table is vacuumed manually. I'd suggest creating an index on t_test(c1), if there isn't one already. It's not helpful when the table is small, but when the table is bloated with all the dead tuples from the deletions, it should help to keep the access fast despite the bloat. - Heikki
On Fri, Aug 31, 2012 at 5:27 AM, John Nash <postgres.dba.needs.help@gmail.com> wrote: > -Postgresql version installed is : postgresql-9.1.3 although when > querying the database we retrieve this output. > > postgres=# select * from version(); > version > ----------------------------------------------------------------------------------------------------------------- > PostgreSQL 8.4.9 on x86_64-redhat-linux-gnu, compiled by GCC gcc > (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit Hi John, You have two versions of pgsql installed, and the one you are running is not the one you think you are running. That's probably the first thing to sort out--repeat the experiment with the correct version. Also, rather than posting the entire config file, you can get just the non-default settings: https://wiki.postgresql.org/wiki/Server_Configuration Cheers, Jeff
Hi, We can try installing version 8.4.9, but when downloading the software, we installed the binaries and files given in postgresql web page named as version 9.1.3. This is the tar file downloaded: postgresql-9.1.3.tar Containing the following when un-tar-ed: [postgsql@localhost postgresql-9.1.3]$ pwd /postgresql/postgresql-9.1.3 [postgsql@localhost postgresql-9.1.3]$ ll total 2528 -rwxrwxrwx 1 postgsql gpostgre 385 Feb 23 2012 aclocal.m4 drwxrwxrwx 2 postgsql gpostgre 4096 Feb 24 2012 config -rwxrwxrwx 1 postgsql gpostgre 326754 Jun 4 10:30 config.log -rwxrwxrwx 1 postgsql gpostgre 37900 Jun 4 10:30 config.status -rwxrwxrwx 1 postgsql gpostgre 866562 Feb 23 2012 configure -rwxrwxrwx 1 postgsql gpostgre 63599 Feb 23 2012 configure.in drwxrwxrwx 51 postgsql gpostgre 4096 Feb 24 2012 contrib -rwxrwxrwx 1 postgsql gpostgre 1192 Feb 23 2012 COPYRIGHT drwxrwxrwx 3 postgsql gpostgre 4096 Feb 24 2012 doc -rw-r--r-- 1 postgsql gpostgre 3741 Jun 4 10:30 GNUmakefile -rwxrwxrwx 1 postgsql gpostgre 3741 Feb 23 2012 GNUmakefile.in -rwxrwxrwx 1 postgsql gpostgre 1165183 Feb 24 2012 HISTORY -rwxrwxrwx 1 postgsql gpostgre 76550 Feb 24 2012 INSTALL -rwxrwxrwx 1 postgsql gpostgre 1489 Feb 23 2012 Makefile -rwxrwxrwx 1 postgsql gpostgre 1284 Feb 23 2012 README drwxrwxrwx 14 postgsql gpostgre 4096 Jun 4 10:30 src 2012/8/31 Jeff Janes <jeff.janes@gmail.com>: > On Fri, Aug 31, 2012 at 5:27 AM, John Nash > <postgres.dba.needs.help@gmail.com> wrote: > >> -Postgresql version installed is : postgresql-9.1.3 although when >> querying the database we retrieve this output. >> >> postgres=# select * from version(); >> version >> ----------------------------------------------------------------------------------------------------------------- >> PostgreSQL 8.4.9 on x86_64-redhat-linux-gnu, compiled by GCC gcc >> (GCC) 4.4.5 20110214 (Red Hat 4.4.5-6), 64-bit > > Hi John, > > You have two versions of pgsql installed, and the one you are running > is not the one you think you are running. > > That's probably the first thing to sort out--repeat the experiment > with the correct version. > > Also, rather than posting the entire config file, you can get just the > non-default settings: > > https://wiki.postgresql.org/wiki/Server_Configuration > > > Cheers, > > Jeff
On Mon, Sep 3, 2012 at 4:22 AM, John Nash <postgres.dba.needs.help@gmail.com> wrote: > Hi, > > We can try installing version 8.4.9, but when downloading the > software, we installed the binaries and files given in postgresql web > page named as version 9.1.3. This is the tar file downloaded: > > postgresql-9.1.3.tar > > Containing the following when un-tar-ed: > > [postgsql@localhost postgresql-9.1.3]$ pwd > /postgresql/postgresql-9.1.3 > [postgsql@localhost postgresql-9.1.3]$ ll > total 2528 > -rwxrwxrwx 1 postgsql gpostgre 385 Feb 23 2012 aclocal.m4 > drwxrwxrwx 2 postgsql gpostgre 4096 Feb 24 2012 config > -rwxrwxrwx 1 postgsql gpostgre 326754 Jun 4 10:30 config.log > -rwxrwxrwx 1 postgsql gpostgre 37900 Jun 4 10:30 config.status > -rwxrwxrwx 1 postgsql gpostgre 866562 Feb 23 2012 configure > -rwxrwxrwx 1 postgsql gpostgre 63599 Feb 23 2012 configure.in > drwxrwxrwx 51 postgsql gpostgre 4096 Feb 24 2012 contrib > -rwxrwxrwx 1 postgsql gpostgre 1192 Feb 23 2012 COPYRIGHT > drwxrwxrwx 3 postgsql gpostgre 4096 Feb 24 2012 doc > -rw-r--r-- 1 postgsql gpostgre 3741 Jun 4 10:30 GNUmakefile > -rwxrwxrwx 1 postgsql gpostgre 3741 Feb 23 2012 GNUmakefile.in > -rwxrwxrwx 1 postgsql gpostgre 1165183 Feb 24 2012 HISTORY > -rwxrwxrwx 1 postgsql gpostgre 76550 Feb 24 2012 INSTALL > -rwxrwxrwx 1 postgsql gpostgre 1489 Feb 23 2012 Makefile > -rwxrwxrwx 1 postgsql gpostgre 1284 Feb 23 2012 README > drwxrwxrwx 14 postgsql gpostgre 4096 Jun 4 10:30 src Hi John, downloading and untarring is not enough, you have to configure, compile, and install it as well. (Which you may have done) In any case, it is perfectly possible to have multiple versions installed simultaneously. If I had to guess, I would say that 8.4.9 came already installed with your OS, and you accidentally started up that preinstalled version instead of the one you intended. You can often find the absolute path to the binary that is actually running by doing: ps -efl|fgrep /postg And then make sure that that is the one you think it is. You can also look in the file "PG_VERSION" in the data directory. In any case, the behavior you report is exactly would would be expected if autovacuum is not running. The config file you posted shows autovac is turned on, but I suspect that is not the config file actually being used by the running server. Cheers, Jeff
On 09/03/2012 01:27 PM, Jeff Janes wrote: > In any case, the behavior you report is exactly would would be > expected if autovacuum is not running. The config file you posted > shows autovac is turned on, but I suspect that is not the config file > actually being used by the running server. It's also important to note that: 1) autovacuum doesn't kick in until a moderate number of changes have been made. Having it turned on doesn't mean it runs continuously. The table can accumulate a lot of dead junk before autovacuum decides to clean things up. 2) When autovacuum *does* start, that can be a source of slowdowns itself. I suspect that some level of table cleanup issue is here. I would also bet that the performance seen initially is inflated because Linux's write cache is absorbing writes at the beginning. The first few hundred megabytes or possibly more you write to the database don't wait for physical I/O at all. Once that cache fills, though, performance drops hard. Most benchmarks like this will start out really fast, then drop off dramatically once the write cache is full, and real-world disk performance limits progress. In those cases, the slower performance after things have been running a while is actually the real sustainable speed of the server. The much faster ones may only be possible when the write cache is relatively empty, which makes them representative more of burst performance. A look at the "Dirty:" line in /proc/meminfo as the test runs will give you an idea if write cache filling is actually an issue here. If that number just keeps going up and speeds keep on dropping, that's at least one cause here. This could easily be both that and an autovacuum related too though. -- Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
Hi, We have investigated further and have observed the following: We have another host with postgres installed in another IP. Called host 190. The host we have reported to have the issue is host174 We have observed that if we launch the java program from host 190 towards host 174 through the network this is: jdbc:postgresql://host174:50008/sessions Performance is stable, whereas if we launch the same java code from host174 itself to it's own database, performance is an exponential decrease function. Both databases are updated to version 9.1.3, and also have checked with the same driver in both hosts. In conclusion the odd behaviour just happens in host174, when java is launched from localhost. If java program is launched from 190 to 190 it also presents stable performance results. Autovacuum is configured. Any way here is the config of the problematic database (in host 174) which is the same as the one in 190. name | current_setting --------------------------+-------------------------------------------------------------------------------------------- ------------------ version | PostgreSQL 9.1.3 on x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red Hat 4.4.6-3), 64-bit archive_mode | off client_encoding | UTF8 fsync | on lc_collate | en_US.UTF-8 lc_ctype | en_US.UTF-8 listen_addresses | * log_directory | pg_log log_filename | postgresql-%a.log log_rotation_age | 1d log_rotation_size | 0 log_truncate_on_rotation | on logging_collector | on max_connections | 100 max_stack_depth | 2MB port | 50008 server_encoding | UTF8 shared_buffers | 32MB synchronous_commit | on TimeZone | Europe/Madrid wal_buffers | 64kB wal_sync_method | fsync (22 rows) We have enclosed a doc file including excel graphics to illustrate the tests done. We don't understand why the postgres database in host174 just presents this behaviour when java is launched locally. Please can you help us? Have dirty pages results some influence in this? Thanks and regards, John 2012/9/6 Greg Smith <greg@2ndquadrant.com>: > On 09/03/2012 01:27 PM, Jeff Janes wrote: >> >> In any case, the behavior you report is exactly would would be >> expected if autovacuum is not running. The config file you posted >> shows autovac is turned on, but I suspect that is not the config file >> actually being used by the running server. > > > It's also important to note that: > > 1) autovacuum doesn't kick in until a moderate number of changes have been > made. Having it turned on doesn't mean it runs continuously. The table can > accumulate a lot of dead junk before autovacuum decides to clean things up. > > 2) When autovacuum *does* start, that can be a source of slowdowns itself. > > I suspect that some level of table cleanup issue is here. I would also bet > that the performance seen initially is inflated because Linux's write cache > is absorbing writes at the beginning. The first few hundred megabytes or > possibly more you write to the database don't wait for physical I/O at all. > Once that cache fills, though, performance drops hard. Most benchmarks like > this will start out really fast, then drop off dramatically once the write > cache is full, and real-world disk performance limits progress. > > In those cases, the slower performance after things have been running a > while is actually the real sustainable speed of the server. The much faster > ones may only be possible when the write cache is relatively empty, which > makes them representative more of burst performance. > > A look at the "Dirty:" line in /proc/meminfo as the test runs will give you > an idea if write cache filling is actually an issue here. If that number > just keeps going up and speeds keep on dropping, that's at least one cause > here. This could easily be both that and an autovacuum related too though. > > -- > Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD > PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance
Sorry I forgot to attach the mentioned file with performance results. Please find it enclosed now. Regards 2012/9/7 John Nash <postgres.dba.needs.help@gmail.com>: > Hi, > > We have investigated further and have observed the following: > > We have another host with postgres installed in another IP. Called host 190. > > The host we have reported to have the issue is host174 > > We have observed that if we launch the java program from host 190 > towards host 174 through the network this is: > > jdbc:postgresql://host174:50008/sessions > > Performance is stable, whereas if we launch the same java code from > host174 itself to it's own database, performance is an exponential > decrease function. > > Both databases are updated to version 9.1.3, and also have checked > with the same driver in both hosts. > > In conclusion the odd behaviour just happens in host174, when java is > launched from localhost. > > If java program is launched from 190 to 190 it also presents stable > performance results. > > Autovacuum is configured. Any way here is the config of the > problematic database (in host 174) which is the same as the one in > 190. > > name | > current_setting > > --------------------------+-------------------------------------------------------------------------------------------- > ------------------ > version | PostgreSQL 9.1.3 on > x86_64-unknown-linux-gnu, compiled by gcc (GCC) 4.4.6 20110731 (Red > Hat > 4.4.6-3), 64-bit > archive_mode | off > client_encoding | UTF8 > fsync | on > lc_collate | en_US.UTF-8 > lc_ctype | en_US.UTF-8 > listen_addresses | * > log_directory | pg_log > log_filename | postgresql-%a.log > log_rotation_age | 1d > log_rotation_size | 0 > log_truncate_on_rotation | on > logging_collector | on > max_connections | 100 > max_stack_depth | 2MB > port | 50008 > server_encoding | UTF8 > shared_buffers | 32MB > synchronous_commit | on > TimeZone | Europe/Madrid > wal_buffers | 64kB > wal_sync_method | fsync > (22 rows) > > We have enclosed a doc file including excel graphics to illustrate the > tests done. > > We don't understand why the postgres database in host174 just presents > this behaviour when java is launched locally. Please can you help us? > Have dirty pages results some influence in this? > > Thanks and regards, > > John > > 2012/9/6 Greg Smith <greg@2ndquadrant.com>: >> On 09/03/2012 01:27 PM, Jeff Janes wrote: >>> >>> In any case, the behavior you report is exactly would would be >>> expected if autovacuum is not running. The config file you posted >>> shows autovac is turned on, but I suspect that is not the config file >>> actually being used by the running server. >> >> >> It's also important to note that: >> >> 1) autovacuum doesn't kick in until a moderate number of changes have been >> made. Having it turned on doesn't mean it runs continuously. The table can >> accumulate a lot of dead junk before autovacuum decides to clean things up. >> >> 2) When autovacuum *does* start, that can be a source of slowdowns itself. >> >> I suspect that some level of table cleanup issue is here. I would also bet >> that the performance seen initially is inflated because Linux's write cache >> is absorbing writes at the beginning. The first few hundred megabytes or >> possibly more you write to the database don't wait for physical I/O at all. >> Once that cache fills, though, performance drops hard. Most benchmarks like >> this will start out really fast, then drop off dramatically once the write >> cache is full, and real-world disk performance limits progress. >> >> In those cases, the slower performance after things have been running a >> while is actually the real sustainable speed of the server. The much faster >> ones may only be possible when the write cache is relatively empty, which >> makes them representative more of burst performance. >> >> A look at the "Dirty:" line in /proc/meminfo as the test runs will give you >> an idea if write cache filling is actually an issue here. If that number >> just keeps going up and speeds keep on dropping, that's at least one cause >> here. This could easily be both that and an autovacuum related too though. >> >> -- >> Greg Smith 2ndQuadrant US greg@2ndQuadrant.com Baltimore, MD >> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com >> >> >> -- >> Sent via pgsql-performance mailing list (pgsql-performance@postgresql.org) >> To make changes to your subscription: >> http://www.postgresql.org/mailpref/pgsql-performance