JakSprats

module('Redis', package.seeall)

local socket = require('socket')
local uri    = require('socket.url')

local commands = {}
local network, request, response = {}, {}, {}

local defaults = { host = '127.0.0.1', port = 6379, tcp_nodelay = false }
local protocol = {

JakSprats

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.

JakSprats

CLI=./redisql-server
$CLI CREATE TABLE data "(id INT, val TEXT)"
$CLI INSERT INTO data VALUES "(1,ONE)"
OK
$CLI INSERT INTO data VALUES "(2,TWO)"
OK
$CLI INSERT INTO data VALUES "(3,THREE)"
OK
$CLI INSERT INTO data VALUES "(4,FOUR)"
OK

JakSprats

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"

JakSprats

$CLI -h one DROP TABLE messages
$CLI -h one CREATE TABLE messages "(id int primary key, cat INT, text TEXT)"; 
$CLI -h one CREATE INDEX nrl:messages:index ON messages "PUBLISH MSG:\$cat message=\$text"
NUM=1000000; 
taskset -c 1 ./redisql-benchmark -h one  -n $NUM -r $NUM -c 200 -SRM 4 -MSG

JakSprats

#!/bin/bash

./redisql-cli DROP TABLE test
./redisql-cli CREATE TABLE test "("id INT, value TEST")"
./redisql-cli DESC test
./redisql-cli CREATE INDEX test_ind ON test "(value)"
./redisql-cli DESC test

./redisql-cli INSERT INTO test VALUES "(1,ONE)"
./redisql-cli INSERT INTO test VALUES "(2,TWO)"

JakSprats

====== SET ======
  1000006 requests completed in 11.14 seconds
  200 parallel clients
  3 bytes payload
  keep alive: 1

6.03% <= 1 milliseconds
74.08% <= 2 milliseconds
98.22% <= 3 milliseconds
99.67% <= 4 milliseconds

JakSprats

-- MYSQL INTEGRATION
require "luasql.mysql" 
mysql_db   = "backupdb";
mysql_user = "root";
mysql_pass = "";
mysql_host = "localhost";

function connect_mysql()
  env  = luasql.mysql();
  conn = env:connect(mysql_db, mysql_user, mysql_pass, mysql_host);

JakSprats

#!/bin/bash

CLI=./redisql-cli

$CLI CREATE TABLE test "(obj_id int primary key, score int)"
$CLI CREATE INDEX test_score_idx ON test \(score\)

I=0; 
while [ $I -lt 10 ]; do
    N=$[${I}*10000];

JakSprats

var Client  = require('mysql').Client;
var redisql = require("redisql");

var mclient = new Client();
var client  = redisql.createClient();

// PURPOSE:
//  this script will backup ALL (except string) redis data objects into
//    timestamped tables in the mysql DB "backupdb"
//