Re: exponential performance decrease in ISD transaction - Mailing list 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