Received: from makus.postgresql.org (makus.postgresql.org [98.129.198.125]) by mail.postgresql.org (Postfix) with ESMTP id 607D6951F38 for ; Thu, 15 Mar 2012 01:29:41 -0300 (ADT) Received: from mail41c40.carrierzone.com ([209.235.156.181]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1S82Jn-0008Ff-0j for pgsql-performance@postgresql.org; Thu, 15 Mar 2012 04:29:40 +0000 Received: from mail41c40.carrierzone.com (localhost [127.0.0.1]) by mail41c40.carrierzone.com (8.13.6/8.13.1) with ESMTP id q2F4TOOv010810 (version=TLSv1/SSLv3 cipher=DHE-RSA-AES256-SHA bits=256 verify=NO); Thu, 15 Mar 2012 04:29:24 +0000 Received: (from webmail@localhost) by mail41c40.carrierzone.com (8.13.6/8.12.2/Submit) id q2F4TOXT010809; Thu, 15 Mar 2012 00:29:24 -0400 Received: from c-24-7-112-232.hsd1.ca.comcast.net (c-24-7-112-232.hsd1.ca.comcast.net [24.7.112.232]) by webmail.light42.com (Webmail 5.0 V.V.I.) with HTTP for ; Wed, 14 Mar 2012 21:29:24 -0700 Message-ID: <20120314212924.h7sjvzeoococwso0@webmail.light42.com> From: maplabs@light42.com To: pgsql-performance@postgresql.org Cc: Garlynn Woodsong , nick@calthorpe.com, rush@winkey.org Subject: Shared memory for large PostGIS operations Date: Wed, 14 Mar 2012 21:29:24 -0700 X-Priority: 3 MIME-Version: 1.0 Content-Type: text/plain; charset=UTF-8 Content-Disposition: inline Content-Transfer-Encoding: quoted-printable User-Agent: Webmail 6.0 X-CSC: 0 X-CHA: v=1.1 cv=yp/PH20Ib1uoriyxRDiuvA2JNV3wxwhL3DjZM6a8u34= c=1 sm=1 a=Q5QLeeme7KwA:10 a=Fd4tY2JZFW4A:10 a=YBgeoPI6DXAA:10 a=IkcTkHD0fZMA:10 a=0bi1xQVcAobyObW1H+I8wQ==:17 a=wGf-kfSVW913AT5ESRcA:9 a=YE7sRoLhou4XptD7r1cA:7 a=QEXdDO2ut3YA:10 a=BIl60XqcmF8A:10 a=WkljmVdYkabdwxfqvArNOQ==:117 X-Pg-Spam-Score: 0.8 (/) X-Archive-Number: 201203/108 X-Sequence-Number: 46558 (from #postgresql IRC on freenode) darkblue_b I did an interesting experiment the other day davidfetter_vmw ..= davidfetter_vmw do tell darkblue_b well you know I do these huge monolithic postGIS queries on an o= therwise idle linux machine.. and there was a persistant thought in my head= that Postgresql+PostGIS did not make good use of memory allocation >2G darkblue_b so I had this long, python driven analysis.. 15 steps.. some, un= usual for me, are multiple queries running at once on the same data ... and= others are just one labor intensive thing then the next =C2=A0 =C2=A0 =C2=A0 (one result table is 1.8M rows for 745M on disk, others are s= maller) darkblue_b I finally got the kinks worked out.. so I ran it twice.. 4.5 hou= rs on our hardware.. once with shared_buffers set to 2400M and the second t= ime with shared_buffers set to 18000M darkblue_b work_mem was unchanged at 640M and.. the run times were within s= econds of each other.. no improvement, no penalty darkblue_b I have been wondering about this for the last two years davidfetter_vmw darkblue_b, have you gone over any of this on -performance = or -hackers? darkblue_b no - though I think I should start a blog .. I have= a couple of things like this now darkblue_b good story though eh ? =C2=A0davidfetter_vmw darkblue_b, um, it's a story that hasn't really gotte= n started until you've gotten some feedback from -performance darkblue_b ok= - true... darkblue_b =C2=A0 =C2=A0pg 9.1 =C2=A0PostGIS 1.5.3 =C2=A0 =C2=A0Ubuntu Serv= er Oneiric 64bit =C2=A0Dual Xeons =C2=A0 one Western Digital black label for pg_default; one 3-disk RAID 5 for the d= atabase tablespace =3D=3D Brian Hamlin GeoCal OSGeo California Chapter 415-717-4462 cell