Re: Shared memory for large PostGIS operations

Hi Kevin, List, others...

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

> Andy Colson <andy@squeakycode.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