From: Scott Marlowe <scott.marlowe@gmail.com>
To: Alexy Khrabrov <deliverable@gmail.com>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: pgsql-performance@postgresql.org
Subject: Re: two memory-consuming postgres processes
Date: Fri, 2 May 2008 14:40:51 -0600
Message-ID: <dcc563d10805021340u274ff4fegcf8902f66118d7c1@mail.gmail.com> (raw)
In-Reply-To: <72E02D29-848B-467A-AE6B-401568010254@gmail.com>
References: <A728C858-76CF-473F-917B-3C9EA4AA75CF@gmail.com>
<dcc563d10805021230i5916561k3962a41e2afa894c@mail.gmail.com>
<27A4D379-2553-496D-A96C-102F2A5E62C2@gmail.com>
<dcc563d10805021253n1589baa7u559fdfc8053d23d3@mail.gmail.com>
<21403.1209759217@sss.pgh.pa.us>
<72E02D29-848B-467A-AE6B-401568010254@gmail.com>
On Fri, May 2, 2008 at 2:26 PM, Alexy Khrabrov <deliverable@gmail.com> wrote:
>
> So how should I divide say a 512 MB between shared_buffers and, um, what
> else? (new to pg tuning :)
Don't worry so much about the rest of the settings. Maybe increase
sort_mem (aka work_mem) to something like 16M or so. that's about it.
> I naively thought that if I have a 100,000,000 row table, of the form
> (integer,integer,smallint,date), and add a real coumn to it, it will scroll
> through the memory reasonably fast.
This is a database. It makes changes on disk in such a way that they
won't be lost should power be cut off. If you're just gonna be batch
processing data that it's ok to lose halfway through, then python /
perl / php etc might be a better choice.
> Yet when I had shared_buffers=128 MB,
> it was hanging there 8 hours before I killed it, and now with 1500MB is
> paging again for several hours with no end in sight.
You went from kinda small to WAY too big. 512M should be a happy medium.
> Why can't it just add
> a column to a row at a time and be done with it soon enough? :)
Adding a column is instantaneous. populating it is not.
> It takes
> inordinately long compared to a FORTRAN or even python program and there's
> no index usage for this table, a sequential scan, why all the paging?
Again, a database protects your data from getting scrambled should the
program updating it quit halfway through etc...
Have you been vacuuming between these update attempts? Each one has
created millions of dead rows and bloated your data store. vacuum
full / cluster / reindex may be needed.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: scott.marlowe@gmail.com, deliverable@gmail.com, tgl@sss.pgh.pa.us
Subject: Re: two memory-consuming postgres processes
In-Reply-To: <dcc563d10805021340u274ff4fegcf8902f66118d7c1@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox