Thread: high transaction rate

No idea of that is helpful but where I would probably start

On 12/7/2016 8:47 AM, Rob Sargent wrote:
> Please tell me that in this case, updating 2 (big)integer columns does
> not generate dead tuples (i.e. does not involve a insert/delete pair).

if the fields being updated aren't indexed, and there's free tuple space
that has already been vacuumed in the same block, then the update is
done via "HOT" (or is it HEAT?) within the same block... but with 1000s
of updates per second to the same 500 rows ?  odds of autovacuum keeping
up are sketchy..    otherwise, all updates are insert/delete operations
due to the requirements of MVCC

a SQL database may not really be the best way to do what you're
doing.    an array of your 500 rows, updated in memory, with updates
logged to a transaction file and the whole array periodically written to
disk, would very likely be MUCH higher performance..


--
john r pierce, recycling bits in santa cruz

On 12/07/2016 09:58 AM, John R Pierce wrote:
> On 12/7/2016 8:47 AM, Rob Sargent wrote:
>> Please tell me that in this case, updating 2 (big)integer columns
>> does not generate dead tuples (i.e. does not involve a insert/delete
>> pair).
>
> if the fields being updated aren't indexed, and there's free tuple
> space that has already been vacuumed in the same block, then the
> update is done via "HOT" (or is it HEAT?) within the same block... but
> with 1000s of updates per second to the same 500 rows ?  odds of
> autovacuum keeping up are sketchy..    otherwise, all updates are
> insert/delete operations due to the requirements of MVCC
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

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

On 12/07/2016 03:32 PM, John R Pierce wrote:
> 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.
>
>
Much appreciated - endOfOffTopic :)