On Jan 30, 2006, at 12:28 AM, Ron Marom wrote:
First of all thanks for you quick and efficient response. Indeed I forgot to mention that I AM vacuuming the database using a daemon every few hours; however this seems not to be the issue this time, as when the CPU consumptions went up I tried to vacuum manually and this seemed to take no affect.
This does not necessarily mean that your vacuuming is all being taken care of properly. I would look at the size of the files for those tables and associated indexes and toast tables. The table could be growing much larger than it needs to be and becoming much slower than it needs to be. If you fsm settings are too low then even after vacuuming there will still be tons of dead tuples causing your tables to get bloated. Even if your settings are high enough you probably need to vacuum more often. If you are doing it every two hours and they are being updated every 30 seconds and there are 67 records then by the time vacuum gets around to cleaning up you already have space allocated for 16,080 tuples for a table that only has 67. Now doing anything on that table is going to take way longer than it should. You could try doing a vacuum full on it sometime (this will block access to the table until it is done so be careful) to see if that speeds it up. If that works then the problem is that your table is simply becoming too bloated. If you get the shrunk down through either a reload of the table or a vacuum full then a vacuum on that specific table should be near instantaneous if you've only got 67 records. I would try setting up a vacuum on that one table about every 10 minutes after you've got it shrunk down and see if that helps. Also once again make sure your fsm settings are high enough or all the vacuuming in the world won't do any good.
It's possible that your indexes are also getting bloated. Try reindexing the table and see if that helps.
I am no expert here so please anyone correct me if I am wrong but I have been dealing with very similar issues lately that is how I fixed it.
Rick
