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
whichdeletes 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
scriptalways 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
runat 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
hungresuming 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.
