Обсуждение: PG 8.0.1 is getting slow in 24 hours. Only daily VACUUM FULL helps

Hi,
there is a very intensively used database. The "usage" means that there are
many-many changes in it. It is 24/7 application.

One table is used to insert 200+ rows per minute from a datacolletor driver
application. 200+ deletions are also performed on the same table. It is very
very important to complete the insertions within the one minute.

In other tables there are 56 rows inserted per minute and 224 updates are
performed.
Because of the quick gowing of the tables the old rows are rotated out from
the "production" tables into archive tables on a daily basis. Archive tables
are for select only. This rotation means ~76000 delete per day from the
"production" tables.

The critical tables are vacuum analyzed in every hour. (Not vacuum full
because of the several minutes long exclusive lock)

Earlier the system had been running for 3 months without problems, but a few
weeks ago suddenly it got slower and slower. By now I have to vacuum full
analyze the whole database almost every day to achieve the requested
performance.

The operating system is a Win2k adv. server, on a HP NetServer LC2000
(2xPIII1GHz + 2x36GB SCSI RAID1; 1GB RAM)

I know that Windows is blame... etc., but what about that 3 months perfect
runtime?

What could cause this issue?

Many thanks in advance.

Bye,
  -- Csaba Együd

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 2005.06.11.




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 2005.06.11.

=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:
> The critical tables are vacuum analyzed in every hour.

Evidently that's not often enough.  Or it could be that your
FSM settings aren't large enough?

            regards, tom lane

On Mon, Jun 13, 2005 at 22:27:31 +0200,
  Együd Csaba <csegyud@vnet.hu> wrote:
>
> The critical tables are vacuum analyzed in every hour. (Not vacuum full
> because of the several minutes long exclusive lock)
>
> Earlier the system had been running for 3 months without problems, but a few
> weeks ago suddenly it got slower and slower. By now I have to vacuum full
> analyze the whole database almost every day to achieve the requested
> performance.
>
> The operating system is a Win2k adv. server, on a HP NetServer LC2000
> (2xPIII1GHz + 2x36GB SCSI RAID1; 1GB RAM)
>
> I know that Windows is blame... etc., but what about that 3 months perfect
> runtime?
>
> What could cause this issue?

Is your FSM setting high enough?

Dear Tom, Bruno,

thank you for your reply. Since these parameters
(max_fsm_pages,max_fsm_relations) can be set at server startup, I have to
wait for at least the evening - and plus a few days letting the system going
wrong or better, staying good. :)

I'm going to inform you about the results.

Thank you very much.

-----Original Message-----
From: Tom Lane [mailto:tgl@sss.pgh.pa.us]
Sent: Monday, June 13, 2005 10:45 PM
To: Együd Csaba
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] PG 8.0.1 is getting slow in 24 hours. Only daily
VACUUM FULL helps

=?iso-8859-2?Q?Egy=FCd_Csaba?= <csegyud@vnet.hu> writes:
> The critical tables are vacuum analyzed in every hour.

Evidently that's not often enough.  Or it could be that your FSM settings
aren't large enough?

            regards, tom lane

--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 2005.06.11.




--
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.323 / Virus Database: 267.6.9 - Release Date: 2005.06.11.

"Tom Lane" <tgl@sss.pgh.pa.us> writes
>
> Evidently that's not often enough.  Or it could be that your
> FSM settings aren't large enough?
>

Setting various proper parameters of FSM is onerous for maintainence. Is
there any plan to make FSM buffer-file-able, just like what clog does now?

Regards,
Qingqing