I have a fairly large Java webapp system that connects to a postgres8.4 db on rhel. This app only does inserts and reads. This has been running without issue for 2.5 years and for the last year or so I implemented a data deletion script which deletes up to 5000 parent records and the corresponding child data in 3 tables. This deletion script is called from a cron job every 5 minutes. It has been working flawlessly for over a year. This script always takes about 1-3 seconds to complete. Also called from cron is vacuum verbose analyse script which is just called once a day (a few minutes before a deletion). This also has been working flawlessly for over a year. This takes about 15 minutes to complete. Now last weekend (Saturday), the vacuum kicked off at 14:03, the deletion kicked off at 14:07 and did not complete. The next deletion kicked off at 14:12 and encountered a deadlock. The webapp at 14:14 hung. At 14:16 everything resumed as per normal and has been running fine since. Now To add more confusion, another server has an almost identical setup (standby sever) however the vacuum cron is due to run at 02:03 in the morning. When the vacuum kicked off on Sunday at 02:03, the same situation as above was encountered and at 02:14 the Java app hung resuming at 02:15. More To further confuse me: every time I go to the site I takes reading of df - this is always around 40% but after this happened, the df now reports 5% less My understanding was that VACUUM ANALYZE (not FULL) does not table lock or return space to the OS. Any idea what has gone wrong here, and what I can do to prevent further occurances. Regards. -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
