X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id D4F72D1B753 for ; Fri, 18 Jun 2004 05:16:54 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 03449-08 for ; Fri, 18 Jun 2004 08:16:48 +0000 (GMT) Received: from lamb.mcmillan.net.nz (218-101-13-11.paradise.net.nz [218.101.13.11]) by svr1.postgresql.org (Postfix) with ESMTP id 136DFD1B745 for ; Fri, 18 Jun 2004 05:16:44 -0300 (ADT) Received: from lamb.mcmillan.net.nz (lamb.mcmillan.net.nz [127.0.0.1]) by lamb.mcmillan.net.nz (Postfix) with ESMTP id 06EE2AD98593; Fri, 18 Jun 2004 20:15:45 +1200 (NZST) Subject: Re: Slow vacuum performance From: Andrew McMillan To: Patrick Hatcher Cc: pgsql-performance@postgresql.org In-Reply-To: References: Content-Type: multipart/signed; micalg=pgp-sha1; protocol="application/pgp-signature"; boundary="=-kDBQE+zc4SvUPN38qC0O" Date: Fri, 18 Jun 2004 20:15:44 +1200 Message-Id: <1087546544.4942.50.camel@lamb.mcmillan.net.nz> Mime-Version: 1.0 X-Mailer: Evolution 1.5.9.1 X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200406/177 X-Sequence-Number: 7235 --=-kDBQE+zc4SvUPN38qC0O Content-Type: text/plain Content-Transfer-Encoding: quoted-printable On Thu, 2004-06-17 at 13:09 -0700, Patrick Hatcher wrote: >=20 >=20 >=20 > Pg: 7.4.2 > RedHat 7.3 > Ram: 8gig >=20 > I have 6 million row table that I vacuum full analyze each night. The ti= me > seems to be streching out further and further as I add more rows. I read > the archives and Josh's annotated pg.conf guide that setting the FSM high= er > might help. Currently, my memory settings are set as such. Does this se= em > low? >=20 > Last reading from vaccum verbose: > INFO: analyzing "cdm.cdm_ddw_customer" > INFO: "cdm_ddw_customer": 209106 pages, 3000 rows sampled, 6041742 > estimated total rows > >>I think I should now set my max FSM to at least 210000 but wanted to ma= ke > sure Yes, that's my interpretation of those numbers too. I would set max_fsm_pages to 300000 (or more) in that case. If you have 8G of RAM in the machine your shared_buffers seems very low too. Depending on how it is used I would increase that to at least the recommended maximum (10000 - 80M). You don't quote your setting for effective_cache_size, but you should probably look at what "/usr/bin/free" reports as "cached", divide that by 10, and set it to that as a quick rule of thumb... Regards, Andrew McMillan > shared_buffers =3D 2000 # min 16, at least max_connections*2, 8= KB > each > sort_mem =3D 12288 # min 64, size in KB >=20 > # - Free Space Map - >=20 > max_fsm_pages =3D 100000 # min max_fsm_relations*16, 6 bytes each > #max_fsm_relations =3D 1000 # min 100, ~50 bytes each >=20 ------------------------------------------------------------------------- Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 Make things as simple as possible, but no simpler -- Einstein ------------------------------------------------------------------------- --=-kDBQE+zc4SvUPN38qC0O Content-Type: application/pgp-signature; name=signature.asc Content-Description: This is a digitally signed message part -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.2.4 (GNU/Linux) iD8DBQBA0qSwjJA0f48GgBIRAuR5AKC1ejXqwYqWE9nJ1rtpauuLesl/SgCfSSke 49klbZPZjmi226vi/dXJT/k= =V/w3 -----END PGP SIGNATURE----- --=-kDBQE+zc4SvUPN38qC0O--