<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>RE: [postgis-users] RE: Slow vacuuming</TITLE>
<META http-equiv=Content-Type content="text/html; charset=us-ascii">
<META content="MSHTML 6.00.6000.16608" name=GENERATOR></HEAD>
<BODY>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>Greg,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>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.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>Below is the other stuff.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>shared_buffers = 1536MB </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>temp_buffers = 128MB </SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>maintenance_work_mem = 256MB (this I had upped to
512MB before but didnt seem to make a difference)</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>work_mem = 1MB (this I thought I had upped
a lot. I guess I was dreaming so this very well could be my
problem)</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>max_fsm_relations = 1000</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>max_fsm_pages = 204800</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>max_fsm_relations = 1000 (never quite understood how
these fsm things work)</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>vmstat shows</SPAN></FONT></DIV><FONT><SPAN
class=835303202-11042008>
<DIV dir=ltr align=left><BR><FONT face=Arial color=#0000ff size=2>procs
-----------memory---------- ---swap-- -----io---- --system--
-----cpu------<BR> r b swpd free
buff cache si so
bi bo in cs us sy id wa
st<BR> 0 0 280 123328 4320
2762896 0 0
31 74 62 33 1 0 98
1 0</FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff
size=2></FONT> </DIV>
<DIV dir=ltr align=left><SPAN class=835303202-11042008></SPAN><FONT face=Arial
color=#0000ff size=2><SPAN class=835303202-11042008>cat /proc/cpuinfo key
elements show 8 of these</SPAN></FONT></DIV><FONT><SPAN
class=835303202-11042008>
<DIV dir=ltr align=left><BR><FONT face=Arial color=#0000ff size=2>model
name : Intel(R) Xeon(R)
CPU E5410 @
2.33GHz<BR>stepping : 6<BR>cpu
MHz : 2333.644<BR>cache
size : 6144 KB<BR></FONT></DIV>
<DIV dir=ltr align=left><SPAN class=835303202-11042008><FONT face=Arial
color=#0000ff size=2>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<BR></FONT></SPAN></SPAN></FONT><FONT
face=Arial color=#0000ff size=2></DIV></FONT></SPAN></FONT>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>I'm going to up my work_mem once my current state load
is done or at least at a safe breaking point.</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>Thanks,</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008>Regina</SPAN></FONT></DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV>
<DIV dir=ltr align=left><FONT face=Arial color=#0000ff size=2><SPAN
class=835303202-11042008></SPAN></FONT> </DIV><FONT face=Arial
color=#0000ff size=2></FONT><FONT face=Arial color=#0000ff size=2></FONT><BR>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left>
<HR tabIndex=-1>
<FONT face=Tahoma size=2><B>From:</B>
postgis-users-bounces@postgis.refractions.net
[mailto:postgis-users-bounces@postgis.refractions.net] <B>On Behalf Of
</B>Gregory Williamson<BR><B>Sent:</B> Thursday, April 10, 2008 10:17
PM<BR><B>To:</B> PostGIS Users Discussion;
postgis-users@postgis.refractions.net<BR><B>Subject:</B> RE: [postgis-users] RE:
Slow vacuuming<BR></FONT><BR></DIV>
<DIV></DIV><!-- Converted from text/plain format -->
<P><FONT size=2>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.<BR><BR>Do you have
output from vmstat or some other utility that might show if you are CPU or IO
bound ?<BR><BR>No real ideas, just hunting for clues ...<BR><BR>Greg
W.<BR><BR>-----Original Message-----<BR>From:
postgis-users-bounces@postgis.refractions.net on behalf of Paragon<BR>Sent: Thu
4/10/2008 7:11 PM<BR>To: postgis-users@postgis.refractions.net<BR>Subject:
[postgis-users] RE: Slow vacuuming<BR><BR>Forgot to add I'm running the
following<BR><BR>POSTGIS="1.3.3SVN" GEOS="3.0.0-CAPI-1.4.1" PROJ="Rel. 4.5.0, 22
Oct 2006"<BR>USE_STATS<BR><BR>(the SVN is probably from about a week ago.
I haven't reloaded the latest<BR>SVN
yet)<BR>Thanks,<BR>Regina<BR><BR>-----Original Message-----<BR>From: Paragon [<A
href="mailto:lr@pcorp.us">mailto:lr@pcorp.us</A>]<BR>Sent: Thursday, April 10,
2008 10:00 PM<BR>To: 'postgis-users@postgis.refractions.net'<BR>Subject: Slow
vacuuming<BR><BR>Has anyone noticed an issue in 8.3 with vacuuming or clustering
on<BR>geometries. I remember there was an issue with running PostgreSQL on
Xeon<BR>processors - is that still a problem?<BR><BR>I've been loading tiger
data and for large states like Florida and<BR>California, my process seems to be
hanging a lot vacuuming and some on<BR>clustering of the geometries. I'm
beginning to point the finger at my<BR>clustered geometries (but I'm performing
experiments now to see if I can<BR>rule it out).<BR><BR>At first I blamed the
box, but now they beefed it up to a 2 processor Quad<BR>Core and added 4 gig and
I upped some settings. I'm not sure. This seemed<BR>to be faster on
the old server I was on (which was considerably slower and<BR>running 8.2) , but
then too many variables have changed. E.g. the old was<BR>regular non-xeon
processors. <BR><BR>Anyrate too many variables have changed between
the 2 -e.g. before I was<BR>running with Tiger 2006SE loading via Ogr and
now I'm loading shape via<BR>shp2pgsql Tiger2007FE and they've change the whole
structure anyway. All I<BR>know is that for some reason
my<BR><BR>Vacuum analyze on my florida edges table<BR><BR>never seems to
complete.<BR><BR>This may not be an issue but could be just something stupid
with my<BR>configuration - like my box happens to be 32-bit instead of 64-bit
which<BR>frustrates me. RedHat Linux EL 5 is a new experience to me
anyway.<BR><BR>Thanks,<BR>Regina<BR><BR><BR><BR>_______________________________________________<BR>postgis-users
mailing list<BR>postgis-users@postgis.refractions.net<BR><A
href="http://postgis.refractions.net/mailman/listinfo/postgis-users">http://postgis.refractions.net/mailman/listinfo/postgis-users</A><BR><BR></FONT></P></BODY></HTML>