[postgis-users] RE: Slow vacuuming

[Paragon]

Greg,
 
Below are my settings.  I was fiddling with the vacuuming settings because I
noticed it was getting in the way so I had turned it off for the time being
until I had all the data loaded.  Thanks for forcing me to look again - I
think my work_mem might be the culprit.  I really thought I had increased
this and then was shocked to see it at its default settings.
 
Below is the other stuff.
shared_buffers = 1536MB 
temp_buffers = 128MB 
maintenance_work_mem = 256MB  (this I had upped to 512MB before but didnt
seem to make a difference)
work_mem = 1MB   (this I thought I had upped a lot.  I guess I was dreaming
so this very well could be my problem)
max_fsm_relations = 1000
 
max_fsm_pages = 204800
max_fsm_relations = 1000 (never quite understood how these fsm things work)
 
vmstat shows

procs -----------memory---------- ---swap-- -----io---- --system--
-----cpu------
 r  b   swpd   free   buff  cache   si   so    bi    bo   in   cs us sy id
wa st
 0  0    280 123328   4320 2762896    0    0    31    74   62   33  1  0 98
1  0
 
cat /proc/cpuinfo key elements show 8 of these

model name      : Intel(R) Xeon(R) CPU           E5410  @ 2.33GHz
stepping        : 6
cpu MHz         : 2333.644
cache size      : 6144 KB

uname -a shows: 2.6.18-53.1.4.el5 #1 SMP Wed Nov 14 10:37:33 EST 2007 i686
i686 i386 GNU/Linux

 
I'm going to up my work_mem once my current state load is done or at least
at a safe breaking point.
 
Thanks,
Regina
 
 


  _____  

From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of Gregory
Williamson
Sent: Thursday, April 10, 2008 10:17 PM
To: PostGIS Users Discussion; postgis-users at postgis.refractions.net
Subject: RE: [postgis-users] RE: Slow vacuuming



Perhaps a problem with autovacuum settings ? Could you post your config
file, esp. stuff having to do with fsm, WAL (never know), autovacuum and
checkpoint settings, that might help, along with OS and RAM.

Do you have output from vmstat or some other utility that might show if you
are CPU or IO bound ?

No real ideas, just hunting for clues ...

Greg W.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net on behalf of Paragon
Sent: Thu 4/10/2008 7:11 PM
To: postgis-users at postgis.refractions.net
Subject: [postgis-users] RE: Slow vacuuming

Forgot to add I'm running the following

POSTGIS="1.3.3SVN" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.5.0, 22 Oct 2006"
USE_STATS

(the SVN is probably from about a week ago.  I haven't reloaded the latest
SVN yet)
Thanks,
Regina

-----Original Message-----
From: Paragon [mailto:lr at pcorp.us]
Sent: Thursday, April 10, 2008 10:00 PM
To: 'postgis-users at postgis.refractions.net'
Subject: Slow vacuuming

Has anyone noticed an issue in 8.3 with vacuuming or clustering on
geometries.  I remember there was an issue with running PostgreSQL on Xeon
processors - is that still a problem?

I've been loading tiger data and for large states like Florida and
California, my process seems to be hanging a lot vacuuming and some on
clustering of the geometries.  I'm beginning to point the finger at my
clustered geometries (but I'm performing experiments now to see if I can
rule it out).

At first I blamed the box, but now they beefed it up to a 2 processor Quad
Core and added 4 gig and I upped some settings.  I'm not sure.  This seemed
to be faster on the old server I was on (which was considerably slower and
running 8.2) , but then too many variables have changed.  E.g. the old was
regular non-xeon processors. 

Anyrate too many variables have changed between  the 2  -e.g. before I was
running with Tiger 2006SE loading via Ogr and now I'm loading shape via
shp2pgsql Tiger2007FE and they've change the whole structure anyway.   All I
know is that for some reason my

Vacuum analyze on my florida edges table

never seems to complete.

This may not be an issue but could be just something stupid with my
configuration - like my box happens to be 32-bit instead of 64-bit which
frustrates me.  RedHat Linux EL 5 is a new experience to me anyway.

Thanks,
Regina



_______________________________________________
postgis-users mailing list
postgis-users at postgis.refractions.net
http://postgis.refractions.net/mailman/listinfo/postgis-users



-------------- next part --------------
An HTML attachment was scrubbed...
URL: <http://lists.osgeo.org/pipermail/postgis-users/attachments/20080410/af8f5e2c/attachment.html>