From aa@devshock.com Tue Sep 20 04:28:55 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id 346EDB5DC01 for ; Tue, 20 Sep 2011 01:28:55 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 27415-05 for ; Tue, 20 Sep 2011 04:28:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-yx0-f174.google.com (mail-yx0-f174.google.com [209.85.213.174]) by mail.postgresql.org (Postfix) with ESMTP id A7982B5DC00 for ; Tue, 20 Sep 2011 01:28:48 -0300 (ADT) Received: by yxl31 with SMTP id 31so78100yxl.19 for ; Mon, 19 Sep 2011 21:28:48 -0700 (PDT) Received: by 10.236.177.66 with SMTP id c42mr1594520yhm.14.1316492928025; Mon, 19 Sep 2011 21:28:48 -0700 (PDT) Received: from AnibalVAIO ([200.85.32.68]) by mx.google.com with ESMTPS id z23sm479325yhl.1.2011.09.19.21.28.44 (version=TLSv1/SSLv3 cipher=OTHER); Mon, 19 Sep 2011 21:28:46 -0700 (PDT) From: "Anibal David Acosta" To: Subject: where is max_fsm_pages in PG9.0? Date: Tue, 20 Sep 2011 00:28:43 -0400 Message-ID: <000301cc774d$c9ec3770$5dc4a650$@devshock.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_0004_01CC772C.42DBA8E0" X-Mailer: Microsoft Outlook 14.0 Thread-Index: Acx3TR1h857fSAY2TiuFMMmbj+v/yA== Content-Language: es-py X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 201109/284 X-Sequence-Number: 45060 This is a multipart message in MIME format. ------=_NextPart_000_0004_01CC772C.42DBA8E0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit I have a lot of wasted bytes in some tables. Somewhere I read that maybe auto-vacuum can't release space due to a low max_fsm_pages setting. I want to increase it, but I don't found the param in the postgres.conf. This param exists? If not? How can I deal with bloated tables? I have many tables with lot of insert/updates/delete (aprox. 5,000,000 per day) Thanks! ------=_NextPart_000_0004_01CC772C.42DBA8E0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

I have a = lot of wasted bytes in some tables.

Somewhere I read that maybe auto-vacuum can’t = release space due to a low max_fsm_pages setting.

I want to = increase it, but I don’t found the param in the = postgres.conf.

This param = exists? If not? How can I deal with bloated tables?

I have many = tables with lot of insert/updates/delete (aprox. 5,000,000 per = day)

Thanks!

------=_NextPart_000_0004_01CC772C.42DBA8E0-- From scott.marlowe@gmail.com Tue Sep 20 04:49:57 2011 Received: from maia.hub.org (maia-5.hub.org [200.46.204.29]) by mail.postgresql.org (Postfix) with ESMTP id EBE17B5DC01 for ; Tue, 20 Sep 2011 01:49:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.29]) (amavisd-maia, port 10024) with ESMTP id 50114-07 for ; Tue, 20 Sep 2011 04:49:51 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.8.0-rc2 Received: from mail-bw0-f46.google.com (mail-bw0-f46.google.com [209.85.214.46]) by mail.postgresql.org (Postfix) with ESMTP id 7A7B2B5DC00 for ; Tue, 20 Sep 2011 01:49:51 -0300 (ADT) Received: by bkbzt4 with SMTP id zt4so109172bkb.19 for ; Mon, 19 Sep 2011 21:49:50 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type:content-transfer-encoding; bh=5APYW2QHnLwUxZ/yENBC1D7PwwRtyef6rz5Ynv8bd4o=; b=a7ZFjsmHxYr0X+WtZtPIU/gysEtu+0mjEQqzNsQza11SLmQtKmKQxnlLNLeTDD+683 ze8tF5SmTEyvI0viUfOVOS9nmTv5IqYv4UGxzV16958BieuVomc7JkypW+KNutnetjAN K65zHY6nLcXt5VrXwovp3cPpXcX0JJo6sOvi0= MIME-Version: 1.0 Received: by 10.204.156.129 with SMTP id x1mr168894bkw.254.1316494190335; Mon, 19 Sep 2011 21:49:50 -0700 (PDT) Received: by 10.205.82.195 with HTTP; Mon, 19 Sep 2011 21:49:50 -0700 (PDT) In-Reply-To: <000301cc774d$c9ec3770$5dc4a650$@devshock.com> References: <000301cc774d$c9ec3770$5dc4a650$@devshock.com> Date: Mon, 19 Sep 2011 22:49:50 -0600 Message-ID: Subject: Re: where is max_fsm_pages in PG9.0? From: Scott Marlowe To: Anibal David Acosta Cc: pgsql-performance@postgresql.org Content-Type: text/plain; charset=windows-1252 Content-Transfer-Encoding: quoted-printable X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-1.899 tagged_above=-10 required=5 tests=BAYES_00=-1.9, FREEMAIL_FROM=0.001 X-Spam-Level: X-Archive-Number: 201109/285 X-Sequence-Number: 45061 On Mon, Sep 19, 2011 at 10:28 PM, Anibal David Acosta wro= te: > I have a lot of wasted bytes in some tables. > > Somewhere I read that maybe auto-vacuum can=92t release space due to a lo= w > max_fsm_pages setting. It's no longer there, as fsm was moved from memory (which was limited by max fsm pages) to disk, which is limited by the size of your disks. Most likely you aren't vacuuming aggresively enough. Lower autovacuum_vacuum_cost_delay and raise autovacuum_vacuum_cost_limit, and possibly raise max threads for vacuum works (forget the name)