Обсуждение: postgresql performace degrading after a while

On Mon, Jan 30, 2006 at 09:02:24AM +0200, Ron Marom wrote:
> I seem to have a problem with postgresql 7.4.2 running on Red Hat
> Enterprise Linux ES3.

If you can't upgrade to 8.0 or 8.1 then at least consider using the
latest version in the 7.4 branch (7.4.11).  You're missing almost
two years of bug fixes.

http://www.postgresql.org/docs/7.4/static/release.html

> I am running an application server over a database in which tables
> contains at most a few thousands of records. The problematic table,
> however, contains 67 records, with an application daemon updating
> all those records every 30 seconds.

Are you vacuuming that table frequently?  If not then it's accumulating
a lot of dead tuples, which would cause performance to degrade.

Are you vacuuming at all?  See "Routine Database Maintenance Tasks"
in the documentation for an explanation of what it is and why it's
necessary.

http://www.postgresql.org/docs/7.4/static/maintenance.html

--
Michael Fuhr

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.

As for the version, I am aware of the most recent versions and the next
version of the application will be certified with postgresql 8.1.x,
however the behavior I mentioned occurred on a live site in which making
changes is not simple. I will do it if I have no choice but I would be
happy if there is any workaround to hold the server for a while until
the ordinary upgrade occurs.

Thanks again.




-----Original Message-----
From: Michael Fuhr [mailto:mike@fuhr.org]
Sent: Monday, January 30, 2006 9:17 AM
To: Ron Marom
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] postgresql performace degrading after a while

On Mon, Jan 30, 2006 at 09:02:24AM +0200, Ron Marom wrote:
> I seem to have a problem with postgresql 7.4.2 running on Red Hat
> Enterprise Linux ES3.

If you can't upgrade to 8.0 or 8.1 then at least consider using the
latest version in the 7.4 branch (7.4.11).  You're missing almost
two years of bug fixes.

http://www.postgresql.org/docs/7.4/static/release.html

> I am running an application server over a database in which tables
> contains at most a few thousands of records. The problematic table,
> however, contains 67 records, with an application daemon updating
> all those records every 30 seconds.

Are you vacuuming that table frequently?  If not then it's accumulating
a lot of dead tuples, which would cause performance to degrade.

Are you vacuuming at all?  See "Routine Database Maintenance Tasks"
in the documentation for an explanation of what it is and why it's
necessary.

http://www.postgresql.org/docs/7.4/static/maintenance.html

--
Michael Fuhr

On Mon, Jan 30, 2006 at 09:28:14AM +0200, Ron Marom wrote:
> 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.

You may be suffering from index bloat. Newer versions mostly deal with
this but old ones don't. Check the data directory for files that keep
growing. Cross-reference with relfilenode in pg_class. Also, vacuuming
once every few hours for such a commonly updated table seems not
enough, maybe you need to do it more often. What about your FSM
settings?

If it's index bloat, simply running REINDEX over the table should fix
it. In general, VACUUM FULL + REINDEX should have pretty much the same
effect as a dump/restore..

Hope this helps,
--
Martijn van Oosterhout   <kleptog@svana.org>   http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.

"Ron Marom" <ron@bitband.com> writes:
> 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 is not nearly often enough.  You stated that the problem table
contains 67 records and all of them are updated every 30 seconds.
Therefore, after one hour the table contains 67 live records and
8040 dead ones, and a slowdown on the order of 100x is not exactly
surprising.

I would recommend vacuuming this specific table every five minutes or
less via a cron job.  Or even fix that application daemon to issue
a vacuum after each time it updates the table --- if there are never
more than 67 dead rows then vacuuming won't take long.

After you update to 8.1 you might want to consider using autovacuum
instead of a hand-tuned cron script.

            regards, tom lane

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