On 03/21/2012 09:34 AM, Jim Green wrote:
> On 21 March 2012 11:01, Steve Crawford<scrawford@pinpointresearch.com> wrote:
>> Something sounds wrong, here. XID wraps around somewhere around 4-billion
>> transactions which is a substantial multiple of the entire number of records
>> you are trying to insert. Do you have any unusual vacuum settings?
> I have autvacumm=off in my config and I still get autovacuum process..
> but yes, I don't have 4 billion inserts till now.. I have attached my
> config used to do bulk loading at the end..
I should have noted, that autovacuum_freeze_max_age defaults to
200-million transactions which you are sure to hit in less than a year's
worth of data if you are doing individual inserts (each insert is a
transaction).
>> As mentioned by others and myself, anything that batches lots of inserts
>> together into a single transaction instead of each record being its own
>> transaction will help whether this is COPY or BEGIN, INSERT......,
> I thought I already turned synchronous commit off in config, but
> probably it is not the same thing as you said.
It is not.
Let's look at inserting one day's data into a single table as a COPY vs.
individual INSERTs. A COPY is *one* transaction. Individual INSERTs is
1,200,000 transactions which moves your transaction ID ahead an
unnecessary 1,199,999 times. There is plenty of other overhead consumed
in each transaction as well.
Turning synchronous_commit off will help mask some of the inefficiency
of doing individual transactions but it will not cure it. Note that even
when turned off, the max delay before writing WAL is three times
wal_writer_delay which is typically 200ms so you are only looking at 0.6
seconds before the WAL is written.
Cheers,
Steve