[postgis-users] Fast access to PostGIS from c/c++

[Mark Cave-Ayland]

On Tue, 2006-12-05 at 09:36 -0700, Chapman, Martin wrote:
> Mark,
> 
> Just out of curiosity, you mention PostgreSQL tuning at the bottom of
> your email.  How will changing that setting help performance?  What do
> you set it at when you run your database.
> 
> Thanks,
> 
> Martin Chapman
> SANZ Inc.
> Software Developer
> http://www.earthwhere.com
> W - 303.495.6326
> C  - 303.898.0397


Hi Martin,

A lot :) I did post a very basic PostgreSQL/PostGIS tuning guide to the
list a while back
(http://postgis.refractions.net/pipermail/postgis-users/2006-March/011539.html) which should contain the basics.

Setting shared_buffers sets the amount of data PostgreSQL can cache
without spilling to disk, while effective_cache_size is used to
influence the planner by giving an idea of how much caching is performed
by the kernel.

You will see the biggest difference if you are using an application that
has high concurrency, large tables (into tens/hundreds of thousands of
rows) and large joins. For example, you could try issuing a "SELECT
COUNT(*) FROM mytable" a number of times and take the average before and
after changing the settings - you should see a noticeable difference. As
a rough guide, I would see pg_bench performance increase from ~ 70tps to
just under 400tps by using 40000 shared_buffers on a 12Gb Opteron
server.

Of course, things will vary depending upon your
storage/RAM/CPU/PostgreSQL version, so please feel free to post an
update if you find any of this information is out of date.


HTH,

Mark.