From: maplabs@light42.com
To: pgsql-performance@postgresql.org
Cc: Garlynn Woodsong <Garlynn@calthorpe.com>
Cc: nick@calthorpe.com
Cc: rush@winkey.org
Subject: Shared memory for large PostGIS operations
Date: Wed, 14 Mar 2012 21:29:24 -0700
Message-ID: <20120314212924.h7sjvzeoococwso0@webmail.light42.com> (raw)
(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 otherwise 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, 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)
darkblue_b I finally got the kinks worked out.. so I ran it twice.. 4.5 hours on our hardware.. once with shared_buffers set to 2400M and the second time with shared_buffers set to 18000M
darkblue_b work_mem was unchanged at 640M and.. the run times were within seconds 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 ?
davidfetter_vmw darkblue_b, um, it's a story that hasn't really gotten started until you've gotten some feedback from -performance darkblue_b ok - true...
darkblue_b pg 9.1 PostGIS 1.5.3 Ubuntu Server Oneiric 64bit Dual Xeons
one Western Digital black label for pg_default; one 3-disk RAID 5 for the database tablespace
==
Brian Hamlin
GeoCal
OSGeo California Chapter
415-717-4462 cell
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-performance@postgresql.org
Cc: maplabs@light42.com, Garlynn@calthorpe.com, nick@calthorpe.com, rush@winkey.org
Subject: Re: Shared memory for large PostGIS operations
In-Reply-To: <20120314212924.h7sjvzeoococwso0@webmail.light42.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