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@xxxxxxxxx>: > 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
Attachment:
Performance results.docx
Description: application/vnd.openxmlformats-officedocument.wordprocessingml.document
-- Sent via pgsql-performance mailing list (pgsql-performance@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-performance
