[postgis-users] Large Databases

[Robert Burgholzer]

I just went through an upgrade of server, and was initially quite
disappointed that my 2.0 Ghz with 1 G RAM was taking forever on a
"vacuum full" of a table with about 50 million records. I abandoned the
analyze after 6 hours, and I then read the Pg manual, and changed
maintenance_work_mem in postgresql.conf from the default value as
follows:

#maintenance_work_mem = 16384           # min 1024, size in KB
maintenance_work_mem = 163840

I then re-ran the vacuum and it processed in under 5 minutes.

Just food for thought that those settings can really help you out,
although I have found it difficult to get good solid advice on which
settings to change and what to change them to.

r.b.

-----Original Message-----
From: postgis-users-bounces at postgis.refractions.net
[mailto:postgis-users-bounces at postgis.refractions.net] On Behalf Of
Martin Daly
Sent: Tuesday, June 13, 2006 4:24 AM
To: PostGIS Users Discussion
Subject: RE: [postgis-users] Large Databases

Jim,

> > We did this with a Big Dumb Upload(tm) into a single table, on a
> > standard (i.e. with no config tuning at all) PostgreSQL 
> 8.0.4/PostGIS
> > 1.0.4 installation on Windows, and are getting excellent 
> results.  The
> > machine spec is as follows:
>  
> Wow, you should try tuning it and see what happens. In particular,
> increasing shared_buffers, effective_cache_size, and possibly 
> work_mem.
> 
> You're also likely to see a noticable gain by going to 8.1.4 
> (you should
> at least get the latest 8.0 release; there's data loss bugs that have
> been fixed).

Luckily, this is just a test database, not a production one.  Also, we
were primarily interested in the upload performance, to see if it scaled
linearly up to national coverage, which it did very nicely.

That said, the server is mine to play with, so I will rerun the upload
some time soon with the latest PostgreSQL/PostGIS, plus some of your
dark arts tuning.

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