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@xxxxxxxxxxxxxxx>: > 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@xxxxxxxxxxxxxxx Baltimore, MD > PostgreSQL Training, Services, and 24x7 Support www.2ndQuadrant.com > > > -- > Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) > To make changes to your subscription: > http://www.postgresql.org/mailpref/pgsql-performance -- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
