Re: exponential performance decrease in ISD transaction

Sorry I forgot to attach the mentioned file with performance results.
Please find it enclosed now.

Regards

2012/9/7 John Nash <postgres(dot)dba(dot)needs(dot)help(at)gmail(dot)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(at)2ndquadrant(dot)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(at)2ndQuadrant(dot)com Baltimore, MD
>> PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com
>>
>>
>> --
>> Sent via pgsql-performance mailing list (pgsql-performance(at)postgresql(dot)org)
>> To make changes to your subscription:
>> http://www.postgresql.org/mailpref/pgsql-performance