X-Original-To: pgsql-performance@postgresql.org Received: from localhost (developer.postgresql.org [64.117.224.193]) by developer.postgresql.org (Postfix) with ESMTP id 46009925BAD for ; Mon, 2 Jun 2003 01:33:56 -0400 (EDT) Received: from developer.postgresql.org ([64.117.224.193]) by localhost (developer.postgresql.org [64.117.224.193]) (amavisd-new, port 10024) with ESMTP id 88745-07 for ; Mon, 2 Jun 2003 01:33:45 -0400 (EDT) Received: from ns1.bee.lt (ns.bi.lt [213.226.131.131]) by developer.postgresql.org (Postfix) with ESMTP id 42F63924C3A for ; Mon, 2 Jun 2003 01:33:44 -0400 (EDT) Received: from B027543 (inet.bee.lt [213.226.131.30]) by ns1.bee.lt (8.11.6/8.11.6) with SMTP id h525Xgi11514 for ; Mon, 2 Jun 2003 08:33:43 +0300 Message-ID: <005a01c328c8$885b9760$f20214ac@bite.lt> From: "Mindaugas Riauba" To: Subject: Degrading performance Date: Mon, 2 Jun 2003 08:33:38 +0300 MIME-Version: 1.0 Content-Type: text/plain; charset="windows-1257" Content-Transfer-Encoding: 7bit X-Priority: 3 X-MSMail-Priority: Normal X-Mailer: Microsoft Outlook Express 6.00.2800.1158 X-MIMEOLE: Produced By Microsoft MimeOLE V6.00.2800.1165 X-Virus-Scanned: by amavisd-new X-Archive-Number: 200306/10 X-Sequence-Number: 2094 Hello, I have table with slowly degrading performance. Table is special is such way that all its rows are updated every 5 minutes (routers interfaces). vacuum does not help. vacuum full does but I'd like to avoid it. Below I added explain analyze output before and after vacuum full. How could I make that table not to grow? PostgreSQL 7.3.2 on Redhat Linux 7.1. max_fsm_pages=10000 max_fsm_relations=1000. Mindaugas router_db=# explain analyze select * from ifdata; QUERY PLAN ---------------------------------------------------------------------------- ------------------------------- Seq Scan on ifdata (cost=0.00..4894.76 rows=776 width=133) (actual time=31.65..1006.76 rows=776 loops=1) Total runtime: 1007.72 msec (2 rows) router_db=# VACUUM full verbose ifdata; INFO: --Relation public.ifdata-- INFO: Pages 4887: Changed 0, reaped 4883, Empty 0, New 0; Tup 776: Vac 46029, Keep/VTL 0/0, UnUsed 186348, MinLen 130, MaxLen 216; Re-using: Free/Avail. Space 38871060/15072128; EndEmpty/Avail. Pages 2981/1895. CPU 0.33s/0.04u sec elapsed 0.45 sec. INFO: Index ifdata_clientid_key: Pages 2825; Tuples 776: Deleted 46029. CPU 0.23s/0.32u sec elapsed 1.98 sec. INFO: Rel ifdata: Pages: 4887 --> 17; Tuple(s) moved: 776. CPU 0.30s/0.35u sec elapsed 1.65 sec. INFO: Index ifdata_clientid_key: Pages 2825; Tuples 776: Deleted 776. CPU 0.21s/0.04u sec elapsed 0.29 sec. VACUUM router_db=# explain analyze select * from ifdata; QUERY PLAN ---------------------------------------------------------------------------- ------------------------- Seq Scan on ifdata (cost=0.00..24.76 rows=776 width=133) (actual time=0.03..7.53 rows=776 loops=1) Total runtime: 8.17 msec (2 rows)