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
Views: Raw Message | Whole Thread | Download mbox | Resend email
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

Browse pgsql-performance by date

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