Received: from localhost (unknown [200.46.204.182]) by postgresql.org (Postfix) with ESMTP id 6B6609F9F6B for ; Sun, 7 Oct 2007 22:06:11 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024) with ESMTP id 46104-03-5 for ; Sun, 7 Oct 2007 22:05:52 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from ug-out-1314.google.com (ug-out-1314.google.com [66.249.92.173]) by postgresql.org (Postfix) with ESMTP id 1A2FB9F9FC2 for ; Sun, 7 Oct 2007 22:03:44 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so769824ugf for ; Sun, 07 Oct 2007 18:03:41 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; bh=bzm0nkcQs+Lau1uZ2kN4EreCtZCfkpRkoJiGHThhplI=; b=sILTlvNahARt36IdRqURF5xL9YelVbHQQ3mbFtxNt8Gi9v4fcMgX+dOFvYwqIHlBm8ZR/LbyDBHnfTt0UkHft5PEaRhwQOd0Ozpd1qhq9LLl5ba6x10ri0mrg77zwX/6lDI6ygbwKOcRFMHOAxdJ65zgWN2JB0i89C/4WPI+fzs= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:content-transfer-encoding:content-disposition:references; b=HsilBgQmAJ+shHxc/9VrN3vaAHbxUjRvt0S8i2V/rI3UYf9A2pW2ywApIgiVzHD9jkSyLeBTy5BQL6twkgo4BcVfMTcLozhUuHQj00D/S/51P1ZwGQ9rQcs/bVPyejPKXaiTYld6nzByZrKLLoOCjN5AGlLlcgICxnhTy4ziDBA= Received: by 10.66.249.8 with SMTP id w8mr6205380ugh.1191805421046; Sun, 07 Oct 2007 18:03:41 -0700 (PDT) Received: by 10.66.220.7 with HTTP; Sun, 7 Oct 2007 18:03:41 -0700 (PDT) Message-ID: Date: Sun, 7 Oct 2007 20:03:41 -0500 From: "Scott Marlowe" To: "Darren Reed" Subject: Re: Is my database now too big? Cc: "Joshua D. Drake" , pgsql-admin@postgresql.org In-Reply-To: <47097EA7.902@fastmail.net> MIME-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline References: <1191730382.18880.1214536197@webmail.messagingengine.com> <47087723.7040403@commandprompt.com> <1191744007.14918.1214548241@webmail.messagingengine.com> <4708A9DC.5060306@fastmail.net> <47097EA7.902@fastmail.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/40 X-Sequence-Number: 26954 On 10/7/07, Darren Reed wrote: > Scott Marlowe wrote: > > On 10/7/07, Darren Reed wrote: > > > Scott Marlowe wrote: > A few days ago I did: > pg_dumpall > foo > What I was doing yesterday was: > rm -rf /data/db/* > initdb -D /data/db > start > psql < foo > run for some period > stop > reboot > start > ...tables have gone but disk space is still in use. > I dont know if it was during the period of running that the > database got corrupted (interrupted insert/update/query?) > or what happened. Are you sure postgresql was starting up in the /data/db directory after reboot and not somewhere else like /var/lib/pgsql/data??? IF you're definitely hitting the right directory, then Is the database shutting down cleanly on reboot? It might be that it's getting killed during a write and you've got some kind of problem with fsync on your machine so the db is getting corrupted > > Can you be more specific on what exact query causes the problem to show up? > > > > It turned out that _any_ query on that table caused the problem to show up. > > I couldn't even do "DROP TABLE ifl;" without postgres growing until it > ran out of memory. definitely sounds like some kind of issue other just the size of the table, like some kind of corruption. > > So in the end, I wiped it clean and reloaded the data - this time > bounding all of the > work with BEGIN/COMMIT. So far things are looking better. All of the > data I've > been building the tables with is elsewhere, so I can reconstruct it. > Maybe adding > BEGIN/COMMIT makes no difference to not using them before, but I'm curious > to see if it does. Ideally I'd like to get to a place where I don't > need to use vacuum > at all. If nothing else, wrapping your load and building in begin;commit; should make it faster. > > Have you been analyzing your data before you start working on it? > > > > No. You should definitely run analyze after updating the table. It helps the query planner make the optimal choice for query plans. > > > Can we see your postgresql.conf file? > > > > Sure, I've attached it. > I've also run with the "default" .conf file without tuning it (down.) > > Darren > > Nothing odd here: > shared_buffers = 2000 # min 16 or max_connections*2, 8KB each > temp_buffers = 200 # min 100, 8KB each > max_prepared_transactions = 5 # can be 0 or more > # note: increasing max_prepared_transactions costs ~600 bytes of shared memory > # per transaction slot, plus lock space (see max_locks_per_transaction). > work_mem = 4096 # min 64, size in KB > maintenance_work_mem = 8192 # min 1024, size in KB > max_stack_depth = 400 # min 100, size in KB > > # - Free Space Map - > > max_fsm_pages = 20000 # min max_fsm_relations*16, 6 bytes each > max_fsm_relations = 200 # min 100, ~70 bytes each > This: > effective_cache_size = 1000 # typically 8KB each is a little low, but that's not a huge deal. effective cache size doesn't allocate anything, it just tells the planner about how much memory the OS is using to cache your database. And I don't see anything else in your postgresql.conf that looks suspicious. I'm leaning towards possible pilot error in shutting down or starting up the db.