Rural Hunter <ruralhunter@xxxxxxxxx> wrote: > 2011/8/29 23:18, Kevin Grittner: >> I also recommend a VACUUM FREEZE ANALYZE on the database unless >> most of these rows will be deleted or updated before you run a >> billion database transactions. Otherwise you will get a painful >> "anti-wraparound" autovacuum on everything, probably at a time >> of heavy usage. > hmm....I will try to turn autovacuum off though I didn't see any > resource intension caused by it. Well, turning off autovacuum during a bulk load is probably a net gain if it's insert-only (i.e., no need to query just-loaded data to decide what to do with new rows); but that's not what I was getting at. Bulk loading 200 GB of data which is not going to be deleted or updated heavily is setting a performance time bomb without a VACUUM FREEZE. At some point, perhaps months later, it will be necessary to freeze the tuples to prevent data loss, and since this occurs based on a threshold of how many transaction IDs have been consumed, it is most likely to happen at peak OLTP loads, when it will be the biggest problem. A VACUUM FREEZE (and you might as well throw in ANALYZE while you're at it) will take care of that up front. As a side benefit it will keep SELECT statements from generating heavy *write* loads on the first access to tuples, and will perform other maintenance which will improve database performance. I just count the time for VACUUM FREEZE ANALYZE as part of the bulk load time required before letting in users. >> Network latency? > No, I do the import locally on the db server so the network > letency can be excluded. Hmm... I don't remember the details, but there was a problem at some point where Linux pipe connections could introduce significant latency, and you could get much better performance on a TCP connection through localhost. It might be worth a try. (Maybe someone else will remember the details.) -Kevin -- Sent via pgsql-admin mailing list (pgsql-admin@xxxxxxxxxxxxxx) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-admin
