Re: postgresql performace degrading after a while

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