Received: from localhost (unknown [200.46.204.191]) by postgresql.org (Postfix) with ESMTP id C3FF59FA56B for ; Mon, 15 Oct 2007 17:46:35 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.191]) (amavisd-maia, port 10024) with ESMTP id 56149-04 for ; Mon, 15 Oct 2007 17:46:32 -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.170]) by postgresql.org (Postfix) with ESMTP id B10459FA54E for ; Mon, 15 Oct 2007 17:46:31 -0300 (ADT) Received: by ug-out-1314.google.com with SMTP id j3so959719ugf for ; Mon, 15 Oct 2007 13:46:30 -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=ccS0aDqRkBvOXirK/Fwif0ll1tBXc/Sc4yYong1zLvI=; b=hSvgIQwLvpCsLy+kmS4roQlGAVDgggEU3LQ3PeQCmzsNwbcVy+vUV8ykBTRFV8AlGJBGp6IVCr1CBw/3+dAUUzwOjZVsDjBdLYUvos0oqNO+vdvVWpd0BOd1ideFbFs3ZOj6WAxn2Xxhr4HdmE5X9HNrzzsc0xryTmOO+r6ORdc= 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=ZhV/XbqB6Nm3ih3Z6gRPY+v/4C5ZWyR5/IRthnmFF3Pcvwc3Il9LySC7ErwwX3nKqcglpcAR1pBO3hyab/VIwTQfzOLB2fImEbivauWlt4Tf+l5PAoHXAf85YNl2ISMnnRevQQogwzEbBapmk4Od5IdbtdP0zl3A8gEh/V1fqPo= Received: by 10.67.19.11 with SMTP id w11mr8269161ugi.1192481189695; Mon, 15 Oct 2007 13:46:29 -0700 (PDT) Received: by 10.67.29.6 with HTTP; Mon, 15 Oct 2007 13:46:29 -0700 (PDT) Message-ID: Date: Mon, 15 Oct 2007 15:46:29 -0500 From: "Scott Marlowe" To: "Darren Reed" Subject: Re: Is my database now too big? Cc: "Tom Lane" , "Joshua D. Drake" , pgsql-admin@postgresql.org In-Reply-To: <4713CA8C.5020303@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> <4708A9DC.5060306@fastmail.net> <47097EA7.902@fastmail.net> <4712D97E.7010004@fastmail.net> <4712E15B.8000405@fastmail.net> <17093.1192464190@sss.pgh.pa.us> <4713CA8C.5020303@fastmail.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200710/129 X-Sequence-Number: 27043 On 10/15/07, Darren Reed wrote: > Tom Lane wrote: > > Darren Reed 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.