test_large_update_cursor

analysis

It looks like batch-sizes of 1000-5000 is the sweet spot

batch-size of 1000 has a 18% insert-speed overhead
batch-size of 1000 is 8X faster than batch-size of 100

batch-size of 5000 has a 140% insert-speed overhead
batch-size of 5000 is 24X faster than batch-size of 100

So, depending on the priority of the update in relation to general performance, choosing batch-sizes between 1000-5000 seems reasonable.

initiaiisation

CLI="./redisql-cli"

C=200
REQ=10000000

function init_ten_mill_modTEN_fk() {
  $CLI DROP TABLE ten_mill_modTEN_fk
  $CLI CREATE TABLE ten_mill_modTEN_fk "(id int primary key, fk int, count INT)"
  $CLI CREATE INDEX ten_mill_modTEN_fk:fk:index ON ten_mill_modTEN_fk \(fk\)
}

function insert_ten_mill_modTEN_fk() {
  MOD=10
  time T=$(taskset -c 1 ./gen-benchmark -q -c $C -n $REQ -s -m $MOD -A OK -Q INSERT INTO ten_mill_modTEN_fk VALUES "(000000000001,000000000001,4)")
  GHZ_2_8_SPEED=50000
  luajit validate.lua SPEED "$GHZ_2_8_SPEED" "$T"
  validate_size ten_mill_modTEN_fk 934273046
}

function test_large_update() {
  if [ -z "$1" ]; then
    echo "Usage: test_large_update table"
    return;
  fi
  TBL="$1"
  time $CLI UPDATE "${TBL}" SET count=99 WHERE fk=1
}
# test_large_update ten_mill_modTEN_fk

function test_large_update_cursor() {
  if [ -z "$1" ]; then
    echo "Usage: test_large_update_cursor table [count] [incr]"
    return;
  fi
  TBL="$1"
  CNT=100000
  if [ -n "$2" ]; then
    CNT=$2
  fi
  INCR=1000
  if [ -n "$3" ]; then
    INCR=$3
  fi
  time (
    I=0;
    while [ $I -le $CNT ]; do
      $CLI UPDATE "${TBL}" SET count=55 WHERE fk=1 ORDER BY fk LIMIT $INCR OFFSET $I >/dev/null
      sleep 0.01;
      I=$[${I}+${INCR}];
    done;
  )
}

insert speed during updates

C=200
REQ=300000
while true; do taskset -c 1 ./gen-benchmark -q -c $C -n $REQ -s -A LINE -Q SELECT \* FROM ten_mill_modTEN_fk WHERE "id=000000000001"; done

Following are speeds while running in parallel w/ batch updates of varying sizes.
SIZE=0 (no updates running -> baseline)
  51804.87 requests per second
  50515.07 requests per second
  52076.03 requests per second

BATCH-SIZE=100
  48712.13 requests per second
  48484.00 requests per second
  47202.64 requests per second

BATCH-SIZE=500
  44054.48 requests per second
  42955.32 requests per second
  41858.94 requests per second

BATCH-SIZE=1000
  39089.64 requests per second
  37012.34 requests per second
  35846.81 requests per second

BATCH-SIZE=5000
  21715.67 requests per second
  21606.05 requests per second
  20798.28 requests per second

BATCH-SIZE=10000
  13600.41 requests per second
  13174.02 requests per second
  13967.55 requests per second

BATCH-SIZE=50000
  4111.97 requests per second
  4042.09 requests per second
  4072.14 requests per second

BATCH-SIZE=100000
  2331.69 requests per second
  2248.18 requests per second
  2319.08 requests per second

large update cursor benchmarks

test_large_update_cursor ten_mill_modTEN_fk 1000000 100;
test_large_update_cursor ten_mill_modTEN_fk 1000000 500;
test_large_update_cursor ten_mill_modTEN_fk 1000000 1000;
test_large_update_cursor ten_mill_modTEN_fk 1000000 5000; 
test_large_update_cursor ten_mill_modTEN_fk 1000000 10000;
test_large_update_cursor ten_mill_modTEN_fk 1000000 50000
test_large_update_cursor ten_mill_modTEN_fk 1000000 100000

real	3m52.128s
user	0m30.520s
sys	0m57.530s

real	0m58.049s
user	0m6.180s
sys	0m10.000s

real	0m33.651s
user	0m2.310s
sys	0m4.160s

real	0m11.555s
user	0m0.220s
sys	0m0.310s

real	0m8.737s
user	0m0.180s
sys	0m0.450s

real	0m5.686s
user	0m0.010s
sys	0m0.040s

real	0m5.371s
user	0m0.010s
sys	0m0.050s