X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by postgresql.org (Postfix) with ESMTP id 1CAF49DC9A5 for ; Mon, 27 Feb 2006 14:34:26 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 72166-02 for ; Mon, 27 Feb 2006 14:34:24 -0400 (AST) X-Greylist: domain auto-whitelisted by SQLgrey- Received: from wproxy.gmail.com (wproxy.gmail.com [64.233.184.201]) by postgresql.org (Postfix) with ESMTP id D23029DC9A6 for ; Mon, 27 Feb 2006 14:34:22 -0400 (AST) Received: by wproxy.gmail.com with SMTP id i23so906175wra for ; Mon, 27 Feb 2006 10:34:23 -0800 (PST) DomainKey-Signature: a=rsa-sha1; q=dns; c=nofws; s=beta; d=gmail.com; h=received:message-id:date:from:subject:cc:in-reply-to:mime-version:content-type:references; b=f/jtmXIUcwVMSvmHWdRly9kUpHoN0uBIKtH0jgqdg0O4cxZoXMRE6WkMvQJYqL7uAS6lQJI3NAUxM8dZGMjwRAvglyBCkUbqJMej5HQESrI9QrTDtf1l48eDtDB1TFOWjSByAFbzKIfwS6LmL1Mpj1B4bGmNUQw8Q2fyBLwIv1k= Received: by 10.64.179.19 with SMTP id b19mr3627317qbf; Mon, 27 Feb 2006 10:34:20 -0800 (PST) Received: by 10.64.232.2 with HTTP; Mon, 27 Feb 2006 10:34:12 -0800 (PST) Message-ID: Date: Mon, 27 Feb 2006 18:34:12 +0000 From: "Peter Childs" Subject: Re: Large Table With Only a Few Rows Cc: pgsql-performance@postgresql.org In-Reply-To: <60slq4zpbt.fsf@dba2.int.libertyrms.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_8744_10163819.1141065252724" References: <1141051682.534005.194130@v46g2000cwv.googlegroups.com> <60slq4zpbt.fsf@dba2.int.libertyrms.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.647 required=5 tests=[AWL=0.457, HTML_MESSAGE=0.001, MISSING_HEADERS=0.189] X-Spam-Score: 0.647 X-Spam-Level: X-Archive-Number: 200602/422 X-Sequence-Number: 17408 ------=_Part_8744_10163819.1141065252724 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline On 27/02/06, Chris Browne wrote: > > "Nik" writes: > > I have a table that has only a few records in it at the time, and they > > get deleted every few seconds and new records are inserted. Table never > > has more than 5-10 records in it. > > > > However, I noticed a deteriorating performance in deletes and inserts > > on it. So I performed vacuum analyze on it three times (twice in a row, > > and once two days later). In the statistics it says that the table size > > is 863Mb, toast table size is 246Mb, and indexes size is 134Mb, even > > though the table has only 5-10 rows in it it. I was wondering how can I > > reclaim all this space and improve the performance? > > You need to run VACUUM ANALYZE on this table very frequently. > > Based on what you describe, "very frequently" should be on the order > of at least once per minute. > > Schedule a cron job specifically to vacuum this table, with a cron > entry like the following: > > * * * * * /usr/local/bin/vacuumdb -z -t my_table -p 5432 my_database > > Of course, you need to bring it back down to size, first. > > You could run CLUSTER on the table to bring it back down to size; > that's probably the fastest way... > > cluster my_table_pk on my_table; > > VACUUM FULL would also do the job, but probably not as quickly. > -- > (reverse (concatenate 'string "gro.gultn" "@" "enworbbc")) > http://cbbrowne.com/info/sgml.html > "Now they can put you in jail if they *THINK* you're gonna commit a > crime. Let me say that again, because it sounds vaguely important" > --george carlin > > ---------------------------(end of broadcast)--------------------------- > TIP 9: In versions below 8.0, the planner will ignore your desire to > choose an index scan if your joining column's datatypes do not > match > You probably want to do one or two other things. 1> Switch on autovacuum. 2> improve the setting of max_fsm_pages in your postgresql.conf a restart will be required. if you do a "vacuum verbose;" the last couple of lines should tell you how much free space is about against how much free space the database can actuall remember to use. INFO: free space map contains 5464 pages in 303 relations DETAIL: A total of 9760 page slots are in use (including overhead). 9760 page slots are required to track all free space. Current limits are: 40000 page slots, 1000 relations, using 299 KB. if the required page slots (9760 in my case) goes above the current limit (40000 in my case) you will need to do a vacuum full to reclaim the free space. (cluster of the relevent tables may work. If you run Vacuum Verbose regullally you can check you are vacuuming often enough and that your free space map is big enough to hold your free space. Peter Childs ------=_Part_8744_10163819.1141065252724 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Content-Disposition: inline

On 27/02/06, Chris Browne <cbbrowne@= acm.org> wrote:
"Nik" <XLPizza@gmail.com<= /a>> writes:
> I have a table that has only a few records in it at= the time, and they
> get deleted every few seconds and new records a= re inserted. Table never
> has more than 5-10 records in it.
>
> However, I notic= ed a deteriorating performance in deletes and inserts
> on it. So I p= erformed vacuum analyze on it three times (twice in a row,
> and once= two days later). In the statistics it says that the table size
> is 863Mb, toast table size is 246Mb, and indexes size is 134Mb, ev= en
> though the table has only 5-10 rows in it it. I was wondering ho= w can I
> reclaim all this space and improve the performance?

You need to run VACUUM ANALYZE on this table very frequently.

Ba= sed on what you describe, "very frequently" should be on the orde= r
of at least once per minute.

Schedule a cron job specifically t= o vacuum this table, with a cron
entry like the following:

* * * * * /usr/local/bin/vacuumdb -z -= t my_table -p 5432 my_database

Of course, you need to bring it back = down to size, first.

You could run CLUSTER on the table to bring it = back down to size;
that's probably the fastest way...

   cluster my_table= _pk on my_table;

VACUUM FULL would also do the job, but probably not= as quickly.
--
(reverse (concatenate 'string "gro.gultn" &= quot;@" "enworbbc"))
http://cbbrowne.com/info= /sgml.html
"Now they can put you in jail if they *THINK* you're= gonna commit a
crime.  Let me say that again, because it soun= ds vaguely important"
--george carlin

---------------------------(end of broadcast)---= ------------------------
TIP 9: In versions below 8.0, the planner will = ignore your desire to
       choose an ind= ex scan if your joining column's datatypes do not
       match

You probably want to do one or two other things.

1> Switch on autovacuum.

2> improve the setting of max_fsm_pages in your postgresql.conf a restar= t will be required.

if you do a "vacuum verbose;" the last couple of lines should tel= l you how much free space is about against how much free space the database can actuall remember to use.

INFO:  free space map contains 5464 pages in 303 relations
DETAIL:  A total of 9760 page slots are in use (including overhead). 9760 page slots are required to track all free space.
Current limits are:  40000 page slots, 1000 relations, using 299 KB.
if the required page slots (9760 in my case) goes above the current limit (40000 in my case) you will need to do a vacuum full to reclaim the free space. (cluster of the relevent tables may work.

If you run Vacuum Verbose regullally you can check you are vacuuming often enough and that your free space map is big enough to hold your free space.

Peter Childs

------=_Part_8744_10163819.1141065252724--