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.