Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 9BBC0131F6D2 for ; Fri, 16 Mar 2012 10:55:50 -0300 (ADT) Received: from li339-205.members.linode.com ([96.126.114.205] helo=squeakycode.net) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S8XdF-0007C5-G9 for pgsql-performance@postgresql.org; Fri, 16 Mar 2012 13:55:50 +0000 Received: from [192.168.10.84] (97-64-237-59.client.mchsi.com [97.64.237.59]) (using TLSv1 with cipher DHE-RSA-AES256-SHA (256/256 bits)) (No client certificate requested) (Authenticated sender: andy) by squeakycode.net (Postfix) with ESMTPSA id E9EB617DE6; Fri, 16 Mar 2012 09:55:35 -0400 (EDT) Message-ID: <4F63465B.1020508@squeakycode.net> Date: Fri, 16 Mar 2012 08:55:39 -0500 From: Andy Colson User-Agent: Mozilla/5.0 (Windows NT 6.1; WOW64; rv:10.0.2) Gecko/20120216 Thunderbird/10.0.2 MIME-Version: 1.0 To: maplabs@light42.com CC: pgsql-performance@postgresql.org, Garlynn Woodsong , nick@calthorpe.com, rush@winkey.org Subject: Re: Shared memory for large PostGIS operations References: <20120314212924.h7sjvzeoococwso0@webmail.light42.com> In-Reply-To: <20120314212924.h7sjvzeoococwso0@webmail.light42.com> Content-Type: text/plain; charset=UTF-8; format=flowed Content-Transfer-Encoding: 7bit X-Pg-Spam-Score: -1.9 (-) X-Archive-Number: 201203/109 X-Sequence-Number: 46559 So let me clean that up for you: > On 3/14/2012 11:29 PM, maplabs@light42.com wrote: Hello list, my name is Brian Hamlin, but I prefer to go by darkblue, its mysterious and dangerous! I run PG 9.1, PostGIS 1.5.3, Linux 64 on Dual Xeons, OS on a single drive, and db is on 3-disk raid 5. I'm the only user. work_mem = 640M I do these huge monolithic postGIS queries on an otherwise idle linux machine. python driven analysis.. 15 steps.. some, unusual for me, are multiple queries running at once on the same data ... and others are just one labor intensive thing then the next (one result table is 1.8M rows for 745M on disk, others are smaller) I tried shared_buffers at both 2400M and 18000M, and it took 4.5 hours both times. I dont know if I am CPU bound or IO bound, but since giving PG more ram didnt help much, I'll assume I'm CPU bound. I heard of this program called vmstat that I'll read up on and post some results for. I don't know how much memory my box has, and I've never run explain analyze, but I'll try it out and post some. I just learned about http://explain.depesz.com/ and figure it might help me. This is the best list ever! Thanks all! (especially that poetic Dave Fetter, and that somewhat mean, but helpful, Andy Colson) Shout outs to my friends Garlynn, Nick and Rush (best band ever!). Party, my house, next week! > == > (Virtually) Brian Hamlin > GeoCal > OSGeo California Chapter > 415-717-4462 cell -Andy