[postgis-users] postgis tuning parameters
Dylan Beaudette
dylan.beaudette at gmail.com
Wed Sep 26 11:22:02 PDT 2007
On Wednesday 26 September 2007, Michael Smedberg wrote:
> I don't THINK you have to recompile- I think Mark was on the right
> track.
>
> You can reset shmmax on Linux using the instructions in the "Linux"
> section of
> http://www.postgresql.org/docs/8.2/interactive/kernel-resources.html,
> and then you can bump up the Postgres shared_buffers setting.
>
> Basically, the OS isn't allowing Postgres to start because Postgres is
> asking for too much memory. You need to tell the OS to allow processes
> to grab lots of memory, then tell Postgres to grab it.
>
> I did this recently on a CentOS machine. It was pretty easy and
> improved performance greatly.
>
> Good luck!
>
> -----Original Message-----
> From: postgis-users-bounces at postgis.refractions.net
> [mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Webb
> Sprague
> Sent: Wednesday, September 26, 2007 9:32 AM
> To: PostGIS Users Discussion
> Subject: Re: [postgis-users] postgis tuning parameters
>
> > > When performing complex queries, with thousands of records and
> > > geometries I am noticing that the postmaster process never exceeds
>
> 1%
>
> > > of my system's memory (2Gb). I have tried setting the shared_buffers
> > > parameter to values > 2000 ... but this only leads to an error when
>
> the
>
> > > postgres server is restarted: cannot allocate this much mem, check
>
> the
>
> > > SHMMAX kernel parameter.
>
> It sounds to me like you need to recompile Postgres with new values
> for SHMAX (and maybe other parameters too - I would ask on the
> Postgres General list). Building postgres is trivial, and if it is
> for a production machine it might be quite worthwhile -- there might
> be options for stripping debug symbols etc .
>
> -W
>
> > > Perhaps some answers on are this page:
>
> http://www.redhat.com/docs/manuals/database/RHDB-2.1-Manual/admin_user/
>
> > > kernel-resources.html
> > >
> > > or in the minds of people on the list. The machine in question is
> > > mostly used for such queries and I would like postgis to be able to
> > > better utilize the resources available.
> > >
> > > Cheers,
> > >
> > > Dylan
> >
> > Hi Dylan,
> >
> > Indeed, everything you need to know should be on that page - here is
>
> the
>
> > most recent version:
> > http://www.postgresql.org/docs/8.2/interactive/kernel-resources.html.
> > Experiment with different settings, but most people tend to recommend
> > about 25% of your available RAM (~60,000 buffers for 2Gb) for a
> > dedicated database server.
> >
> >
> > HTH,
> >
> > Mark.
> >
Thanks for all the tips. I reset the kernel's limts on memory allocation, and
now the postmoster process starts as expected.
Are these reasonable limits?
kernel.shmmax = 268435456
shared_buffers = 16000
work_mem = 1024
If there were no one else using this machine I would have set them higher. I
am still not sure about the work_mem setting...
For most of what I am doing the conservative defaults provided good enough
response time. It was a query involving GeomUnion() that sparked my interest
in improving resource usage.
Attached is a query plan for the large query that sent me on this romp through
postgres tuning land. I do not see any 'seq scan' lines in there, so I am
assuming (wrongly?) that there is proper utilization of the indexes.
Cheers,
Dylan
--
Dylan Beaudette
Soil Resource Laboratory
http://casoilresource.lawr.ucdavis.edu/
University of California at Davis
530.754.7341
-------------- next part --------------
HashAggregate (cost=5576.88..5576.92 rows=1 width=4460)
InitPlan
-> Aggregate (cost=34.03..34.04 rows=1 width=50314)
-> Bitmap Heap Scan on mapunit_bound_poly (cost=12.02..34.01 rows=7 width=50314)
Recheck Cond: (((areasymbol)::text = 'ca654'::text) OR ((areasymbol)::text = 'ca651'::text) OR ((areasymbol)::text = 'ca649'::text) OR ((areasymbol)::text = 'ca644'::text) OR ((areasymbol)::text = 'ca648'::text) OR ((areasymbol)::text = 'ca077'::text))
-> BitmapOr (cost=12.02..12.02 rows=7 width=0)
-> Bitmap Index Scan on mapunit_bound_poly_areasymbol_id (cost=0.00..2.00 rows=1 width=0)
Index Cond: ((areasymbol)::text = 'ca654'::text)
-> Bitmap Index Scan on mapunit_bound_poly_areasymbol_id (cost=0.00..2.00 rows=1 width=0)
Index Cond: ((areasymbol)::text = 'ca651'::text)
-> Bitmap Index Scan on mapunit_bound_poly_areasymbol_id (cost=0.00..2.00 rows=1 width=0)
Index Cond: ((areasymbol)::text = 'ca649'::text)
-> Bitmap Index Scan on mapunit_bound_poly_areasymbol_id (cost=0.00..2.00 rows=1 width=0)
Index Cond: ((areasymbol)::text = 'ca644'::text)
-> Bitmap Index Scan on mapunit_bound_poly_areasymbol_id (cost=0.00..2.00 rows=1 width=0)
Index Cond: ((areasymbol)::text = 'ca648'::text)
-> Bitmap Index Scan on mapunit_bound_poly_areasymbol_id (cost=0.00..2.00 rows=1 width=0)
Index Cond: ((areasymbol)::text = 'ca077'::text)
-> Nested Loop (cost=5538.72..5542.83 rows=1 width=4460)
-> Unique (cost=5532.10..5532.13 rows=1 width=27)
-> Sort (cost=5532.10..5532.11 rows=5 width=27)
Sort Key: component.mukey, component.comppct_r
-> Bitmap Heap Scan on component (cost=40.18..5532.04 rows=5 width=27)
Recheck Cond: (((areasymbol)::text = 'ca654'::text) OR ((areasymbol)::text = 'ca651'::text) OR ((areasymbol)::text = 'ca649'::text) OR ((areasymbol)::text = 'ca644'::text) OR ((areasymbol)::text = 'ca648'::text) OR ((areasymbol)::text = 'ca077'::text))
Filter: ((majcompflag = 'Yes'::bpchar) AND (((compname)::text ~~* '%keyes%'::text) OR ((compname)::text ~~* '%Redding%'::text) OR ((compname)::text ~~* '%corning%'::text) OR ((compname)::text ~~* '%whitney%'::text) OR ((compname)::text ~~* '%montpellier%'::text) OR ((compname)::text ~~* '%rocklin%'::text) OR ((compname)::text ~~* '%exiter%'::text) OR ((compname)::text ~~* '%san joaquin%'::text) OR ((compname)::text ~~* '%madera%'::text) OR ((compname)::text ~~* '%snelling%'::text) OR ((compname)::text ~~* '%hanford%'::text) OR ((compname)::text ~~* '%grangeville%'::text)))
-> BitmapOr (cost=40.18..40.18 rows=4623 width=0)
-> Bitmap Index Scan on areasymbol_component_idx (cost=0.00..10.72 rows=1349 width=0)
Index Cond: ((areasymbol)::text = 'ca654'::text)
-> Bitmap Index Scan on areasymbol_component_idx (cost=0.00..4.66 rows=473 width=0)
Index Cond: ((areasymbol)::text = 'ca651'::text)
-> Bitmap Index Scan on areasymbol_component_idx (cost=0.00..4.66 rows=473 width=0)
Index Cond: ((areasymbol)::text = 'ca649'::text)
-> Bitmap Index Scan on areasymbol_component_idx (cost=0.00..4.66 rows=473 width=0)
Index Cond: ((areasymbol)::text = 'ca644'::text)
-> Bitmap Index Scan on areasymbol_component_idx (cost=0.00..10.83 rows=1381 width=0)
Index Cond: ((areasymbol)::text = 'ca648'::text)
-> Bitmap Index Scan on areasymbol_component_idx (cost=0.00..4.66 rows=473 width=0)
Index Cond: ((areasymbol)::text = 'ca077'::text)
-> Bitmap Heap Scan on mapunit_poly (cost=6.62..10.65 rows=1 width=4421)
Recheck Cond: ((mapunit_poly.mukey)::text = ("outer".mukey)::text)
Filter: ((((areasymbol)::text = 'ca654'::text) OR ((areasymbol)::text = 'ca651'::text) OR ((areasymbol)::text = 'ca649'::text) OR ((areasymbol)::text = 'ca644'::text) OR ((areasymbol)::text = 'ca648'::text) OR ((areasymbol)::text = 'ca077'::text)) AND (wkb_geometry && $0) AND isvalid(wkb_geometry))
-> BitmapAnd (cost=6.62..6.62 rows=1 width=0)
-> Bitmap Index Scan on mapunit_spatial_idx (cost=0.00..2.04 rows=13 width=0)
Index Cond: (wkb_geometry && $0)
-> Bitmap Index Scan on mapunit_poly_mukey_id (cost=0.00..4.33 rows=379 width=0)
Index Cond: ((mapunit_poly.mukey)::text = ("outer".mukey)::text)
More information about the postgis-users
mailing list