Skip site navigation (1) Skip section navigation (2)

Re: Shared memory for large PostGIS operations

From: Andy Colson <andy(at)squeakycode(dot)net>
To: maplabs(at)light42(dot)com
Cc: pgsql-performance(at)postgresql(dot)org, Garlynn Woodsong <Garlynn(at)calthorpe(dot)com>, nick(at)calthorpe(dot)com, rush(at)winkey(dot)org
Subject: Re: Shared memory for large PostGIS operations
Date: 2012-03-16 13:55:39
Message-ID: 4F63465B.1020508@squeakycode.net (view raw or flat)
Thread:
Lists: pgsql-performance

So let me clean that up for you:

 > On 3/14/2012 11:29 PM, maplabs(at)light42(dot)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

In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2012-03-16 14:17:31
Subject: Re: Shared memory for large PostGIS operations
Previous:From: Ants AasmaDate: 2012-03-15 11:44:31
Subject: Re: index choosing problem

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group