Re: exponential performance decrease in ISD transaction - Mailing list pgsql-performance

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