From: Scott Marlowe <scott.marlowe@gmail.com>
To: Darren Reed <darrenr@fastmail.net>
Cc: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Joshua D. Drake <jd@commandprompt.com>
Cc: pgsql-admin@postgresql.org
Subject: Re: Is my database now too big?
Date: Mon, 15 Oct 2007 15:46:29 -0500
Message-ID: <dcc563d10710151346w430da8a2s265bd8e129ee7d@mail.gmail.com> (raw)
In-Reply-To: <4713CA8C.5020303@fastmail.net>
References: <1191730382.18880.1214536197@webmail.messagingengine.com>
<dcc563d10710070114m35df7741tcaa01700b21baec@mail.gmail.com>
<4708A9DC.5060306@fastmail.net>
<dcc563d10710070713q6aa1fbfdqe8f07e5356d39d41@mail.gmail.com>
<47097EA7.902@fastmail.net>
<dcc563d10710071803ia92a046neef1296999173d13@mail.gmail.com>
<4712D97E.7010004@fastmail.net>
<4712E15B.8000405@fastmail.net>
<17093.1192464190@sss.pgh.pa.us>
<4713CA8C.5020303@fastmail.net>
On 10/15/07, Darren Reed <darrenr@fastmail.net> wrote:
> Tom Lane wrote:
> > Darren Reed <darrenr@fastmail.net> writes:
> > > # /usr/pkg/bin/psql -U postgres template1
> > > psql: FATAL: out of memory
> > > DETAIL: Failed on request of size 20.
> >
> > I'm starting to think there is something very broken about your machine :-(.
> > Have you run any hardware diagnostics on it lately? The level of
> > flakiness you're seeing starts to suggest bad RAM to me.
> >
>
> No, I haven't run any diagnostics.
>
> But I'm not convinced the hardware is a problem because the flakiness
> has only really
> been a problem when I started doing more than just inserts and updates.
> The table that
> has shown the most problems (ifl) is a table of work to do, so I'm
> inserting records,
> doing a select of random items out of it and also deleting records (once
> the work is
> complete.) Multiple processes can be trying to simultaneously be doing
> all of these,
> which should not be anything out of the ordinary. Or maybe this
> workload is just making
> the database stress the hardware more?
So, I'm guessing you're doing something like:
select * from ifl order by random()
in several different threads? that means that the table has to be
materialized twice in memory, and then most of the result thrown away.
Generally, the preferred way to do a random select of a large table is
to assign a random number to each row and then select a range based on
that number. So, you have 100,000 rows, you assign the numbers 1
through 100,000 to each row at random, then you select them using
something like a sequence to make sure that each process isn't bumping
into each other.
So, if you're going to process 10 records at a time, you create a
sequence with an increment of 10 and use a select from it to get your
"random" row to operate on
Again, I'm kinda shooting in the dark here as you reveal more and more
what you are doing a little at a time. A test case that can invoke
this failure would be most useful.
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-admin@postgresql.org
Cc: scott.marlowe@gmail.com, darrenr@fastmail.net, tgl@sss.pgh.pa.us, jd@commandprompt.com
Subject: Re: Is my database now too big?
In-Reply-To: <dcc563d10710151346w430da8a2s265bd8e129ee7d@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