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

Re: Shared memory for large PostGIS operations

From: Brian Hamlin <maplabs(at)light42(dot)com>
To: Kevin Grittner <Kevin(dot)Grittner(at)wicourts(dot)gov>
Cc: "Andy Colson" <andy(at)squeakycode(dot)net>, "Garlynn Woodsong" <Garlynn(at)calthorpe(dot)com>, <nick(at)calthorpe(dot)com>, <pgsql-performance(at)postgresql(dot)org>, <rush(at)winkey(dot)org>
Subject: Re: Shared memory for large PostGIS operations
Date: 2012-03-16 21:00:11
Message-ID: 35AEFCF6-B6A7-42AD-A7B3-C73B6E9ABA09@light42.com (view raw or flat)
Thread:
Lists: pgsql-performance
Hi Kevin, List, others...

On Mar 16, 2012, at 7:17 AM, Kevin Grittner wrote:

> Andy Colson <andy(at)squeakycode(dot)net> wrote:
>
>> I tried shared_buffers at both 2400M and 18000M, and it took 4.5
>> hours both times.  ... (weak attempts at humor omitted) ....
>
> All else being the same, adjusting shared_buffers affects how much
> of your cache is managed by PostgreSQL and how much of your cache is
> managed by the OS; it doesn't exactly change how much you have
> cached or necessarily affect disk waits.  (There's a lot more that
> can be said about the fine points of this, but you don't seem to
> have sorted out the big picture yet.)

   Linux caching is aggressive already.. so I think this example  
points out that
Postgres caching is not contributing here..  thats why I posted this  
short
example to this list.. I thought ti was a useful data point..  that  
it might be
useful to others... and to the PostgreSQL project devs...

>
>> I heard of this program called vmstat that I'll read up on and
>> post some results for.  -----ignore- I dont take advice with  
>> vinegar well...
>
> That's a good way to get a handle on whether your bottleneck is
> currently CPU or disk access.
>
>> (attempted insults omitted)
>
> If you're looking to make things faster (a fact not yet exactly in
> evidence), you might want to start with the query which runs the
> longest, or perhaps the one which most surprises you with its run
> time, and get the EXPLAIN ANALYZE output for that query.  There is
> other information you should include; this page should help:
>
> http://wiki.postgresql.org/wiki/SlowQueryQuestions


   some of the queries have been gone over fairly well, other not..
Its a complex sequence and we are in production mode here,
so I dont get a chance to do everything I might do with regard to
one particular query...


>
>> I just learned about http://explain.depesz.com/ and figure it
>> might help me.
>
> It is a nice way to present EXPLAIN ANALYZE output from complex
> queries.


   explain.depesz.com  definitely a good reference, thank you for that..


==
Brian Hamlin
GeoCal
OSGeo California Chapter
415-717-4462 cell




In response to

Responses

pgsql-performance by date

Next:From: Kevin GrittnerDate: 2012-03-16 22:30:06
Subject: Re: Shared memory for large PostGIS operations
Previous:From: Karl DenningerDate: 2012-03-16 15:38:32
Subject: Re: Obtaining resource usage statistics from execution? (v 9.1)

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