Re: high transaction rate

On 12/7/2016 2:23 PM, Rob Sargent wrote:
> How does your reply change, if at all, if:
> - Fields not index
> - 5000 hot records per 100K records (millions of records total)
> - A dozen machines writing 1 update per 10 seconds (one machine
> writing every 2 mins)
> - - each to a different "5000"
> or (two modes of operation)
> - - each to same "5000"
>
> My guess this would be slow enough even in the second mode? Or at
> this rate and style should I care?
> Sorry for taking this off from OP's point

thats 1 update of 5000 records every 2 minutes per each of 12 client
hosts? thats still a fair amount of tuples/second and in a table with
millions of records, the vacuum will have a lot more to go through.

9.6 has some potentially significant enhancements in how autovacuum
operates with respect to incrementally freezing blocks.

if you think your update patterns can take advantage of HOT, its a good
idea to set the FILL_FACTOR of the table prior to populating it, maybe
to 50% ? this will make the initial table twice as large, but provide
freespace in every block for these in-block HOT operations.

for a table that large, you'll definitely need to crank up the
aggressiveness of autovacuum if you hope to keep up with that number of
stale tuples distributed among millions of records.

--
john r pierce, recycling bits in santa cruz