eviction.bash

0 better implementation

CLI=./redisql-cli

$CLI CONFIG ADD LUA test/alchemy.lua
$CLI LUA "function cap_per_fk(max, tbl, fkname, fkval, pkname)
              wc    = fkname .. '=' .. fkval;
              cnt_s = select('COUNT(*)', tbl, wc);
              cnt   = tonumber(string.sub(cnt_s, 2));
              if (cnt > max) then
                  dnum = cnt - max;
                  wcob = wc .. ' ORDER BY ' .. pkname .. ' LIMIT ' .. dnum;
                  delete(tbl, wcob);
              end
              return '+OK';
          end
          return 'cap_per_fk() added';"
$CLI CREATE TABLE thread "(id INT, page_no INT, msg TEXT)"
$CLI CREATE INDEX ind_t_p ON thread "(page_no)"
$CLI CREATE INDEX int_t_nri ON thread "LUA return cap_per_fk(100,'thread','page_no',\$page_no,'id');"

time taskset -c 1 ./gen-benchmark -c 200 -n 1000000 -s -A OK -Q INSERT INTO thread VALUES "(000000000001,1,'pagename_000000000001')"

limiting table dimensions in AlchemyDB

#!/bin/bash

CLI=./redisql-cli
$CLI CONFIG ADD LUA test/alchemy.lua                                        

$CLI LUA "function max_per_fk(max, tbl, ind_prfx, fk, pk)
    iname = ind_prfx .. fk;
    len_s = llen(iname);
    len   = string.sub(len_s, 2);
    if (tonumber(len) >= max) then
        del_pk = rpop(iname);
        delete(tbl, 'id = ' .. del_pk[1]);
    end
    lpush(iname, pk);
    return '+OK';
end"

$CLI CREATE TABLE thread "(id INT, page_no INT, msg TEXT)"
$CLI CREATE INDEX ind_t_p ON thread "(page_no)"
$CLI CREATE INDEX int_t_nri ON thread "LUA return max_per_fk(100,'thread','t_',\$page_no,\$id);"

time taskset -c 1 ./gen-benchmark -c 200 -n 500000 -s -A OK -Q INSERT INTO thread VALUES "(000000000001,1,'pagename_000000000001')"

echo "TABLE"
$CLI DUMP thread;

echo "INDEX"
$CLI LRANGE t_1 0 -1

results

  500011 requests completed in 45.18 seconds
  200 parallel clients

0.00% <= 0 milliseconds
0.01% <= 1 milliseconds
0.07% <= 2 milliseconds
0.13% <= 3 milliseconds
0.17% <= 4 milliseconds
0.26% <= 5 milliseconds
0.35% <= 6 milliseconds
0.39% <= 7 milliseconds
0.46% <= 8 milliseconds
0.55% <= 9 milliseconds
0.72% <= 10 milliseconds
3.59% <= 11 milliseconds
6.85% <= 12 milliseconds
7.19% <= 13 milliseconds
7.73% <= 14 milliseconds
8.49% <= 15 milliseconds
9.96% <= 16 milliseconds
36.59% <= 17 milliseconds
91.13% <= 18 milliseconds
93.72% <= 19 milliseconds
95.01% <= 20 milliseconds
95.42% <= 21 milliseconds
95.75% <= 22 milliseconds
96.64% <= 23 milliseconds
97.37% <= 24 milliseconds
97.86% <= 25 milliseconds
98.47% <= 26 milliseconds
99.01% <= 27 milliseconds
99.21% <= 28 milliseconds
99.40% <= 29 milliseconds
99.54% <= 30 milliseconds
99.59% <= 31 milliseconds
99.62% <= 32 milliseconds
99.68% <= 33 milliseconds
99.72% <= 34 milliseconds
99.75% <= 35 milliseconds
99.78% <= 36 milliseconds
99.80% <= 37 milliseconds
99.85% <= 38 milliseconds
99.90% <= 39 milliseconds
99.95% <= 40 milliseconds
99.97% <= 41 milliseconds
99.97% <= 42 milliseconds
99.97% <= 43 milliseconds
99.97% <= 46 milliseconds
99.97% <= 47 milliseconds
99.97% <= 49 milliseconds
99.97% <= 51 milliseconds
99.97% <= 53 milliseconds
99.97% <= 55 milliseconds
99.97% <= 57 milliseconds
99.97% <= 59 milliseconds
99.97% <= 60 milliseconds
99.97% <= 62 milliseconds
99.97% <= 64 milliseconds
99.97% <= 65 milliseconds
99.97% <= 66 milliseconds
99.97% <= 68 milliseconds
99.97% <= 71 milliseconds
99.97% <= 72 milliseconds
99.97% <= 73 milliseconds
99.97% <= 76 milliseconds
99.97% <= 78 milliseconds
99.97% <= 81 milliseconds
99.97% <= 83 milliseconds
99.97% <= 84 milliseconds
99.97% <= 86 milliseconds
99.97% <= 89 milliseconds
99.97% <= 90 milliseconds
99.97% <= 92 milliseconds
99.97% <= 94 milliseconds
99.97% <= 97 milliseconds
99.97% <= 98 milliseconds
99.97% <= 100 milliseconds
99.97% <= 103 milliseconds
99.98% <= 106 milliseconds
99.98% <= 110 milliseconds
99.98% <= 111 milliseconds
99.98% <= 116 milliseconds
99.98% <= 124 milliseconds
99.99% <= 126 milliseconds
99.99% <= 127 milliseconds
99.99% <= 130 milliseconds
99.99% <= 138 milliseconds
99.99% <= 144 milliseconds
99.99% <= 152 milliseconds
99.99% <= 158 milliseconds
99.99% <= 166 milliseconds
99.99% <= 173 milliseconds
99.99% <= 181 milliseconds
99.99% <= 187 milliseconds
99.99% <= 195 milliseconds
99.99% <= 202 milliseconds
99.99% <= 210 milliseconds
99.99% <= 217 milliseconds
99.99% <= 225 milliseconds
99.99% <= 232 milliseconds
99.99% <= 234 milliseconds
99.99% <= 238 milliseconds
99.99% <= 241 milliseconds
99.99% <= 247 milliseconds
99.99% <= 256 milliseconds
99.99% <= 263 milliseconds
99.99% <= 271 milliseconds
99.99% <= 278 milliseconds
99.99% <= 287 milliseconds
99.99% <= 294 milliseconds
99.99% <= 303 milliseconds
99.99% <= 310 milliseconds
99.99% <= 319 milliseconds
100.00% <= 326 milliseconds
100.00% <= 335 milliseconds
100.00% <= 342 milliseconds
100.00% <= 352 milliseconds
100.00% <= 359 milliseconds
100.00% <= 368 milliseconds
100.00% <= 376 milliseconds
100.00% <= 385 milliseconds
100.00% <= 393 milliseconds
100.00% <= 402 milliseconds
100.00% <= 410 milliseconds
100.00% <= 420 milliseconds
100.00% <= 428 milliseconds
100.00% <= 437 milliseconds
100.00% <= 446 milliseconds
100.00% <= 455 milliseconds
100.00% <= 464 milliseconds
100.00% <= 474 milliseconds
100.00% <= 482 milliseconds
100.00% <= 492 milliseconds
100.00% <= 500 milliseconds
100.00% <= 510 milliseconds
100.00% <= 518 milliseconds
100.00% <= 529 milliseconds
100.00% <= 537 milliseconds
100.00% <= 547 milliseconds
11068.07 requests per second

setup

Tests on dual core 2.8GHz cpu

# run server
taskset -c 0 ./redisql-server