Tuning PostgreSQL for bulk imports

[Kirk Strauser]

On Sunday 07 January 2007 17:15, Ivan Voras wrote:

> What are your current IO rates? Since you have only two drives you might be
> restricted by available disk bandwidth... 

So says gstat.  According to top, the import only takes about 40% of one CPU, 
but both of the drives are saturated.

I'm contemplating mounting that filesystem async and letting PostgreSQL decide 
when to sync it.  Although I'm sort of allergic to the idea, this seems like 
it might be relatively safe since no other process touches that data.  Any 
thoughts?

The biggest benefit I'm getting from SMP right now is that I can run the 
Foxpro->PostgreSQL conversion on one processor while postmaster grinds away 
on the other.

> I've just remembered - do you run VACUUM ANALYZE after your big imports?
> You should (to get performance on SELECTs afterwards, but it won't help
> the imports themselves).

Actually, I've been letting the vacuum daemon make that decision for the last 
few months with no perceptible ill effects.

> Ok. Be careful not to use SELECT ... LIMIT ALL ;)

LOL!  Duly noted.
-- 
Kirk Strauser
-------------- next part --------------
A non-text attachment was scrubbed...
Name: not available
Type: application/pgp-signature
Size: 187 bytes
Desc: not available
Url : http://lists.freebsd.org/pipermail/freebsd-questions/attachments/20070108/582ffc1b/attachment.pgp