100K range updates

0: explanations

These tests update every row in the 10million row table "message_list"

The updates are done via FKs, and the benchmarks show the latency differences as the rows matching an FK vary from 100 to 100K.

All tests were done on an Intel(R) Core(TM)2 Duo CPU     T9600  @ 2.80GHz

For these tests, the server should be run on core 0 via "taskset -c 0 ./redisql-server"

1: large-update-test.sh

C=200
REQ=10000000

for MOD in 100 200 400 1000 2000 5000 10000 100000; do
  $CLI DROP TABLE message_list
  init_message_list_table
  echo "INSERT $REQ rows"
  time taskset -c 1 ./gen-benchmark -q -c $C -n $REQ -s -m $MOD -A OK -Q INSERT INTO message_list VALUES "(000000000001,000000000001,4)"
  echo "MOD: $MOD FK-RANGE: $[${REQ}/${MOD}]"
  taskset -c 1 ./gen-benchmark -c 1 -n $MOD -s -A INT -Q UPDATE message_list SET "count=$MOD" WHERE "fk=000000000001"
done

A: 100 range updates

MOD: 100000 FK-RANGE: 100
  100000 requests completed in 47.65 seconds
  1 parallel clients

53.28% <= 0 milliseconds
99.83% <= 1 milliseconds
99.91% <= 2 milliseconds
99.93% <= 3 milliseconds
99.94% <= 4 milliseconds
99.95% <= 5 milliseconds
99.96% <= 6 milliseconds
99.97% <= 7 milliseconds
99.98% <= 8 milliseconds
99.98% <= 9 milliseconds
99.98% <= 10 milliseconds
99.99% <= 11 milliseconds
99.99% <= 12 milliseconds
99.99% <= 13 milliseconds
99.99% <= 16 milliseconds
99.99% <= 17 milliseconds
100.00% <= 18 milliseconds
100.00% <= 20 milliseconds
100.00% <= 23 milliseconds
2098.86 requests per second

B: 1K and 2K range updates

 MOD: 10000 FK-RANGE: 1000
  10000 requests completed in 53.86 seconds
  1 parallel clients

6.40% <= 4 milliseconds
73.61% <= 5 milliseconds
92.78% <= 6 milliseconds
96.71% <= 7 milliseconds
98.15% <= 8 milliseconds
98.92% <= 9 milliseconds
99.31% <= 10 milliseconds
99.53% <= 11 milliseconds
99.61% <= 12 milliseconds
99.66% <= 13 milliseconds
99.71% <= 14 milliseconds
99.72% <= 15 milliseconds
99.75% <= 16 milliseconds
99.77% <= 17 milliseconds
99.80% <= 18 milliseconds
99.84% <= 19 milliseconds
99.89% <= 20 milliseconds
99.94% <= 21 milliseconds
99.95% <= 22 milliseconds
99.96% <= 23 milliseconds
99.97% <= 24 milliseconds
99.98% <= 59 milliseconds
99.99% <= 60 milliseconds
100.00% <= 75 milliseconds
185.65 requests per second

MOD: 5000 FK-RANGE: 2000
  5000 requests completed in 87.40 seconds
  1 parallel clients

2.58% <= 16 milliseconds
69.34% <= 17 milliseconds
95.04% <= 18 milliseconds
98.58% <= 19 milliseconds
99.20% <= 20 milliseconds
99.28% <= 21 milliseconds
99.44% <= 22 milliseconds
99.54% <= 23 milliseconds
99.58% <= 26 milliseconds
99.60% <= 27 milliseconds
99.62% <= 28 milliseconds
99.68% <= 29 milliseconds
99.70% <= 30 milliseconds
99.80% <= 31 milliseconds
99.84% <= 32 milliseconds
99.86% <= 34 milliseconds
99.88% <= 36 milliseconds
99.90% <= 38 milliseconds
99.92% <= 39 milliseconds
99.94% <= 116 milliseconds
99.96% <= 117 milliseconds
99.98% <= 119 milliseconds
100.00% <= 122 milliseconds
57.21 requests per second

D: 5K Range Requests

MOD: 2000 FK-RANGE: 5000
  2000 requests completed in 81.29 seconds
  1 parallel clients

0.95% <= 38 milliseconds
22.60% <= 39 milliseconds
69.15% <= 40 milliseconds
86.60% <= 41 milliseconds
89.95% <= 42 milliseconds
93.50% <= 43 milliseconds
97.20% <= 44 milliseconds
99.45% <= 45 milliseconds
99.60% <= 46 milliseconds
99.65% <= 48 milliseconds
99.75% <= 49 milliseconds
99.85% <= 55 milliseconds
99.90% <= 61 milliseconds
99.95% <= 259 milliseconds
100.00% <= 263 milliseconds
24.60 requests per second

E: 10K range updates

MOD: 1000 FK-RANGE: 10000
  1000 requests completed in 69.42 seconds
  1 parallel clients

2.70% <= 65 milliseconds
12.60% <= 66 milliseconds
46.90% <= 67 milliseconds
66.20% <= 68 milliseconds
72.90% <= 69 milliseconds
76.70% <= 70 milliseconds
79.10% <= 71 milliseconds
81.40% <= 72 milliseconds
83.60% <= 73 milliseconds
87.60% <= 74 milliseconds
92.60% <= 75 milliseconds
97.00% <= 76 milliseconds
99.00% <= 77 milliseconds
99.50% <= 78 milliseconds
99.60% <= 79 milliseconds
99.70% <= 80 milliseconds
99.80% <= 86 milliseconds
99.90% <= 87 milliseconds
100.00% <= 447 milliseconds
14.41 requests per second

F: 25K range updates

MOD: 400 FK-RANGE: 25000
  400 requests completed in 69.88 seconds
  1 parallel clients

0.50% <= 160 milliseconds
1.75% <= 162 milliseconds
3.25% <= 163 milliseconds
7.25% <= 164 milliseconds
9.75% <= 165 milliseconds
13.25% <= 166 milliseconds
18.00% <= 167 milliseconds
22.50% <= 168 milliseconds
27.75% <= 169 milliseconds
33.50% <= 170 milliseconds
37.00% <= 171 milliseconds
42.50% <= 172 milliseconds
46.75% <= 173 milliseconds
51.75% <= 174 milliseconds
55.25% <= 175 milliseconds
59.75% <= 176 milliseconds
63.00% <= 177 milliseconds
67.75% <= 178 milliseconds
72.00% <= 179 milliseconds
76.00% <= 180 milliseconds
79.75% <= 181 milliseconds
83.00% <= 182 milliseconds
87.25% <= 183 milliseconds
90.25% <= 184 milliseconds
93.50% <= 185 milliseconds
96.50% <= 186 milliseconds
97.75% <= 187 milliseconds
99.50% <= 188 milliseconds
99.75% <= 189 milliseconds
100.00% <= 216 milliseconds
5.72 requests per second

G: 50K range updates

MOD: 200 FK-RANGE: 50000
  200 requests completed in 62.94 seconds
  1 parallel clients

0.50% <= 279 milliseconds
1.00% <= 285 milliseconds
1.50% <= 287 milliseconds
2.00% <= 289 milliseconds
2.50% <= 291 milliseconds
3.50% <= 292 milliseconds
4.00% <= 299 milliseconds
4.50% <= 300 milliseconds
5.50% <= 301 milliseconds
6.00% <= 303 milliseconds
7.50% <= 304 milliseconds
10.50% <= 305 milliseconds
12.50% <= 306 milliseconds
17.50% <= 307 milliseconds
22.00% <= 308 milliseconds
28.00% <= 309 milliseconds
33.00% <= 310 milliseconds
40.00% <= 311 milliseconds
46.00% <= 312 milliseconds
50.50% <= 313 milliseconds
56.50% <= 314 milliseconds
59.00% <= 315 milliseconds
64.00% <= 316 milliseconds
67.50% <= 317 milliseconds
74.00% <= 318 milliseconds
83.00% <= 319 milliseconds
87.50% <= 320 milliseconds
91.50% <= 321 milliseconds
93.00% <= 322 milliseconds
94.00% <= 323 milliseconds
95.00% <= 324 milliseconds
95.50% <= 326 milliseconds
96.00% <= 329 milliseconds
96.50% <= 338 milliseconds
97.00% <= 339 milliseconds
97.50% <= 347 milliseconds
98.00% <= 351 milliseconds
98.50% <= 356 milliseconds
99.00% <= 391 milliseconds
99.50% <= 393 milliseconds
100.00% <= 408 milliseconds
3.18 requests per second

H: 100K Range Update

MOD: 100 FK-RANGE: 100000
  100 requests completed in 71.25 seconds
  1 parallel clients

1.00% <= 678 milliseconds
2.00% <= 680 milliseconds
3.00% <= 685 milliseconds
4.00% <= 686 milliseconds
5.00% <= 687 milliseconds
6.00% <= 692 milliseconds
7.00% <= 695 milliseconds
8.00% <= 697 milliseconds
10.00% <= 698 milliseconds
13.00% <= 700 milliseconds
18.00% <= 701 milliseconds
21.00% <= 702 milliseconds
30.00% <= 703 milliseconds
31.00% <= 704 milliseconds
37.00% <= 705 milliseconds
38.00% <= 706 milliseconds
40.00% <= 707 milliseconds
42.00% <= 708 milliseconds
48.00% <= 709 milliseconds
57.00% <= 710 milliseconds
61.00% <= 711 milliseconds
66.00% <= 712 milliseconds
71.00% <= 713 milliseconds
72.00% <= 714 milliseconds
75.00% <= 715 milliseconds
76.00% <= 725 milliseconds
77.00% <= 726 milliseconds
78.00% <= 727 milliseconds
81.00% <= 729 milliseconds
84.00% <= 731 milliseconds
85.00% <= 732 milliseconds
87.00% <= 733 milliseconds
88.00% <= 735 milliseconds
91.00% <= 737 milliseconds
93.00% <= 738 milliseconds
95.00% <= 739 milliseconds
96.00% <= 740 milliseconds
97.00% <= 741 milliseconds
98.00% <= 745 milliseconds
99.00% <= 747 milliseconds
100.00% <= 750 milliseconds
1.40 requests per second

new results

Results from 3.0 GHz CPU and IN-PLACE-UPDATEs

2-3X speedup :)

INSERT 10000000 rows
73299.88 requests per second


real	2m16.432s
user	0m21.150s
sys	1m28.110s
MOD: 100 FK-RANGE: 100000
  100 requests completed in 24.37 seconds
  1 parallel clients

1.00% <= 237 milliseconds
2.00% <= 239 milliseconds
5.00% <= 240 milliseconds
20.00% <= 242 milliseconds
45.00% <= 243 milliseconds
67.00% <= 244 milliseconds
89.00% <= 245 milliseconds
97.00% <= 246 milliseconds
100.00% <= 247 milliseconds
4.10 requests per second


(integer) 3
OK
OK
INSERT 10000000 rows
70636.44 requests per second


real	2m21.576s
user	0m24.140s
sys	1m40.110s
MOD: 200 FK-RANGE: 50000
  200 requests completed in 26.81 seconds
  1 parallel clients

1.50% <= 130 milliseconds
6.00% <= 131 milliseconds
13.50% <= 132 milliseconds
28.00% <= 133 milliseconds
62.50% <= 134 milliseconds
92.00% <= 135 milliseconds
97.50% <= 136 milliseconds
98.00% <= 137 milliseconds
99.00% <= 138 milliseconds
99.50% <= 139 milliseconds
100.00% <= 145 milliseconds
7.46 requests per second


(integer) 3
OK
OK
INSERT 10000000 rows
71071.72 requests per second


real	2m20.708s
user	0m23.490s
sys	1m36.060s
MOD: 400 FK-RANGE: 25000
  400 requests completed in 28.22 seconds
  1 parallel clients

0.25% <= 66 milliseconds
3.00% <= 67 milliseconds
6.50% <= 68 milliseconds
10.00% <= 69 milliseconds
38.50% <= 70 milliseconds
88.75% <= 71 milliseconds
99.50% <= 72 milliseconds
100.00% <= 73 milliseconds
14.18 requests per second


(integer) 3
OK
OK
INSERT 10000000 rows
70261.23 requests per second


real	2m22.332s
user	0m24.120s
sys	1m44.350s
MOD: 1000 FK-RANGE: 10000
  1000 requests completed in 31.18 seconds
  1 parallel clients

5.20% <= 30 milliseconds
78.30% <= 31 milliseconds
99.80% <= 32 milliseconds
100.00% <= 33 milliseconds
32.08 requests per second


(integer) 3
OK
OK
INSERT 10000000 rows
69254.52 requests per second


real	2m24.402s
user	0m23.920s
sys	1m44.890s
MOD: 2000 FK-RANGE: 5000
  2000 requests completed in 33.63 seconds
  1 parallel clients

0.15% <= 15 milliseconds
21.60% <= 16 milliseconds
98.70% <= 17 milliseconds
99.75% <= 18 milliseconds
99.80% <= 19 milliseconds
99.90% <= 20 milliseconds
99.95% <= 22 milliseconds
100.00% <= 23 milliseconds
59.47 requests per second


(integer) 3
OK
OK
INSERT 10000000 rows
67215.60 requests per second


real	2m28.781s
user	0m24.270s
sys	1m45.250s
MOD: 5000 FK-RANGE: 2000
  5000 requests completed in 24.39 seconds
  1 parallel clients

19.30% <= 4 milliseconds
95.60% <= 5 milliseconds
99.54% <= 6 milliseconds
99.68% <= 7 milliseconds
99.80% <= 8 milliseconds
99.82% <= 9 milliseconds
99.84% <= 12 milliseconds
99.94% <= 13 milliseconds
99.98% <= 14 milliseconds
100.00% <= 18 milliseconds
205.00 requests per second


(integer) 3
OK
OK
INSERT 10000000 rows
67553.42 requests per second


real	2m28.037s
user	0m25.420s
sys	1m43.220s
MOD: 10000 FK-RANGE: 1000
  10000 requests completed in 25.06 seconds
  1 parallel clients

51.71% <= 2 milliseconds
99.63% <= 3 milliseconds
99.69% <= 4 milliseconds
99.70% <= 5 milliseconds
99.86% <= 6 milliseconds
99.90% <= 7 milliseconds
99.91% <= 8 milliseconds
99.92% <= 9 milliseconds
99.93% <= 10 milliseconds
99.95% <= 11 milliseconds
100.00% <= 12 milliseconds
398.98 requests per second


(integer) 3
OK
OK
INSERT 10000000 rows
68871.48 requests per second


real	2m25.203s
user	0m23.550s
sys	1m44.910s
MOD: 100000 FK-RANGE: 100
  100000 requests completed in 36.88 seconds
  1 parallel clients

63.75% <= 0 milliseconds
99.95% <= 1 milliseconds
99.97% <= 2 milliseconds
99.97% <= 4 milliseconds
99.98% <= 5 milliseconds
99.98% <= 6 milliseconds
99.99% <= 7 milliseconds
99.99% <= 8 milliseconds
99.99% <= 9 milliseconds
100.00% <= 11 milliseconds
100.00% <= 12 milliseconds
2711.86 requests per second