cargo build returns error and perf improvement

[xin-e-liu]

➜ rediSQL git:(master) cargo build --release
error: failed to load source for a dependency on engine_pro

Caused by:
Unable to update file:///home/tetter/redis/rediSQL/engine_pro

Caused by:
failed to read /home/tetter/redis/rediSQL/engine_pro/Cargo.toml

Caused by:
No such file or directory (os error 2)

Looks like the Cargo.toml file is missing in engine_pro?

Thanks,
Xin

[siscia]

Thanks for your report.

I will look into it first thing tomorrow!

[siscia]

We hit a known issue in cargo: rust-lang/cargo#4544

At the moment it seems like there is no way around the problem but removing the dependency.

The last commit 0b65cb2 should make it compile also on your machine.

Can you confirm?

Cheers,

[xin-e-liu]

It works now! Thanks! on centos libclang version > 3.9 is needed. So had to get that there as the default version is 3.4

I'm trying to run the performance test:

➜ performance git:(master) ✗ python rediSQL_bench.py
Starting performance test
Creating tables
Traceback (most recent call last):
File "rediSQL_bench.py", line 162, in
prepare_tables(redis_pool)
File "rediSQL_bench.py", line 31, in prepare_tables
r.execute_command("REDISQL.EXEC", t)
File "/usr/lib/python2.7/site-packages/redis/client.py", line 668, in execute_command
return self.parse_response(connection, command_name, **options)
File "/usr/lib/python2.7/site-packages/redis/client.py", line 680, in parse_response
response = connection.read_response()
File "/usr/lib/python2.7/site-packages/redis/connection.py", line 629, in read_response
raise response
redis.exceptions.ResponseError: Wrong number of arguments, it accepts 3, you provide 2

The python redis installed is: https://pypi.org/project/redis/ 2.10.6 Looks like there is some protocol/version mismatch. Could you pls also shed some light on this?

Thanks,
Xin

[siscia]

Hi Xin,

actually it is my fault. The performance test should not be there.

It is quite hard to get out sensible measurement from python script like I tried to do.

If you want to stress test RediSQL the best thing to do is to run redis-benchmark, one or multiple instances.

(I wrote the scripts that follow by heart, there may be some minor mistake)

What I usually do is something like:

Inside redis-cli we create the environment.

> REDISQL.CREATE_DB DB
> REDISQL.EXEC DB "CREATE TABLE test (a INT, b INT);"
> REDISQL.CREATE_STATEMENT DB insert "INSERT INTO test VALUES(?1, ?2)"

Then we stress test it with

redis-benchmark -c $n_clients -n $total_times REDISQL.EXEC_STATEMENT DB insert 1 2

With this test you are inser $total_time (100_000 by default) times the value 1 and 2 into the table using $n_client (50 by default).

What you are really testing here is the number of transaction you are executing. I expect it to be well over 10k/sec but it definitely depends on your machine.

On my machines setting $n_client to ~200 gives the best results.

Again, you are testing the number of write transaction not the number of insert, doing something like this:

> REDISQL.CREATE_STATEMENT DB insert "INSERT INTO test VALUES(?1, ?2), (?2+1, ?1+5), (?1-?2, ?2*?1)"

Will insert 3 times as much data in the same number of transaction so it will be pretty much similar in throughput and latency.

Similarly you could test the reads, but be aware that if you are reading a lot of data you need to consider the time to transfer the data and the time to find the value in the database.

The write transaction is definitely the simplest thing to test.

If you need you could also test multiple databases:

> REDISQL.CREATE_DB DB1
> REDISQL.CREATE_DB DB2
> REDISQL.EXEC DB1 "CREATE TABLE test (a INT, b INT);"
> REDISQL.EXEC DB2 "CREATE TABLE test (a INT, b INT);"
> REDISQL.CREATE_STATEMENT DB1 insert "INSERT INTO test VALUES(?1, ?2)"
> REDISQL.CREATE_STATEMENT DB2 insert "INSERT INTO test VALUES(?1, ?2)"

And then in multiple instances of redis-benchmark

redis-benchmark -c $n_clients -n $total_times REDISQL.EXEC_STATEMENT DB1 insert 1 2
redis-benchmark -c $n_clients -n $total_times REDISQL.EXEC_STATEMENT DB2 insert 1 2

I am expecting this to scale sublinearly to the number of database.

I would love if you could share the result of your benchmarks.

[xin-e-liu]

Thanks for the detailed response. I'm evaluating redisql for a project, with a lot of writes, and reasonable read. I'll try more and let you know. Thanks!

[siscia]

I figure out that I should provide at least some form of benchmarking so I get some data.

These measurements are from an AWS EC2 c4.8xlarge which is a beast machine and the module leaves it pretty idle.

After all redis is single thread and SQLite is single thread on writing so getting 180% of cpu was already quite good IMHO.

Anyway here are the raw result: https://gist.github.com/siscia/b8a960a1af68cf3c6d27a2513cd44046

I will write an article in the next few days about them.

Spoiler, I got north of 50k write transaction per second.

I don't see many ways to get orders of magnitude better than this, maybe we can shave something off but nothing too big.

Cheers,

[xin-e-liu]

Thanks for the results.

Have you tried with bigger text columns? I tried an experiment with like 20 columns, 10 int, 10 text. Each text column like 10k size (yes it's big). So one row around 100k.

Benchmarking shows around 2k requests/s.

With pure redis SET, 100k value size, around 10k request/s. Of course pure set is easy and redisql does a lot of indexing stuff background. This is with redis-benchmark, do you think the bulk input pipe would help? I guess not given redis-benchmark has pipeline. So looks like to get even higher throughput, one has to do sharding...

[siscia]

Wow, you are really stressing it!

When you talk about 10k you mean 10k bytes?

Do you mind to share your test? I am talking about the table structure and the redis-benchmark command you used.

I haven't thought about such extreme use case, but let's see if we can improve it.

[xin-e-liu]

Sure.

I'm just doing something like this:
redis-benchmark -c 200 -n 100000 $(cat cmd_short.txt) -P 1000 -q

where cmd_short.txt is like:
REDISQL.EXEC_STATEMENT DB insert 1 2 3 4 5 6 7 8 9 ASDF... ASDF... ASDF... ASDF... ASDF... ASDF... ASDF... ASDF... ASDF... ASDF... ASDF...

Each ASDF... is a big string with size around 10k.

This gives me about 2k requests/s.

I also tried pure SET:
redis-benchmark -t set -n 1000000 -c 200 -d 114000 -P 100
This gives me about 10k requests/s.

So there is about 5 times difference, and I'm assuming this is due to pure SET is really simply and redisql does a lot more stuff.

[siscia]

Ahahaha, alright was simpler than I though.

Thank you so much!

There are some places where we are doing some allocation and data movement that we should be able to avoid. I guess this is the reason for the huge difference in performance.

I will try some experiment and I will come back to you as soon as possible (it may take some days.)

Or if you know rust and want to take a shot at this I can provide guidance, beware it could become quite complex quite fast since there are going to be quite a bit of lifetime to manage.

[xin-e-liu]

I don't know rust... but if you could provide a few pointers of where those allocs happen, I can probably take a stab and see how far I can go. Thanks!

[siscia]

It would be quite complex in rust especially if you don't know the language.

If you are interested in learning it and you want to try I would start looking here: https://github.com/RedBeardLab/rediSQL/blob/master/src/lib.rs#L154 and see if we can remove that .to_vec() then also here we are moving memory: https://github.com/RedBeardLab/rediSQL/blob/master/redisql_lib/src/redis.rs#L428 (we are creating a new vector and we are filling it with strings).

I believe it is not gonna be easy and I am quite sure it is not the most friendly way to learn rust.

Anyway, what I would suggest first is being sure that SQLite can provide better performance.

I run this TCL script

package require sqlite3
sqlite3 db :memory:
db eval { CREATE TABLE test (a INT, b INT, c INT, d INT, e INT, f INT, g INT, h INT, i INT, l INT, aa TEXT, bb TEXT, cc TEXT, dd TEXT, ee TEXT, ff TEXT, gg TEXT, hh TEXT, ii TEXT, ll TEXT); }

set bigstring oTaazFODW6YuU9fvTN6Asz4LQ2Mz3RQsNgvGK7uiTwY1h9fbrdp8tj2q54l2knkM2f2gFs4xz2SAKTlbW0OBfwpavRicWFhycxjuMGmG930TC6Kay90heHBC7HKOTAPw62kPaAiPVRHHBUeC6ZkFdSolT6u58POPPArQTrH65NhSjGRt3pfpfT0HXFjKMbsY5AvrYY67FaCuUnlSYT0X1bLfpX4sh3MV0CkoaUBmOQVYz2ixF1sjD8g493Yk97PszKIvx8AOSxnQ6zDsaZ8DdrSu4PxdJIAoZlWsKKaizbHEFQbzqM6xNd6BmS7vhVE9Ofg7b7hVtmAlQLAlpvjwOdDSr0owflCJipkVIOhJXEDgX9d36Gj1TMUzfwCQ9I0zh6QYzLRTxQFJx3jbJELOfq4sCSiEkNY0XbPRtTPn69v7n9IDjuGEC2J5no8vpkPV7XpL2JxcjdgdUIul3fGuve7t3gPQoCv6JAbalAtjyaHuI6SeJCl4mNikgurUTOPJsm0576W9EGzjhP7QScNPYdCyeu8WY6P4DRqfiecY8Rc35PXnlwwHcvq9qbaR7PqMLJ0WChllVwnydwFT5z1ebH1ByJDBEupjVOnuaO3stUXyWQIvdTHrbHL3dBNUZ5DZh6rWoY1l4lE1DQohfqJ4EByRe1tDl4jxkWZTUqmzDodOO0WKjtYBGOUCD7idFBUMOUu1ldZ77FjLxJhLVVFjxMsnhrBhtzczxupxaPn88yfT6MFgmbEyZOAd9lsKO53WTTNKFgGKScABAlfewTnEgV5FGRKt47ergZWqSKfN6yRwvYU3DLnA1YE0AndgS5MMdtxdwAzVDjzCpCh1pbYX7jiw6W5XIGFCoYZYxFueICyujSzExRdTihWfOGVoJQM8xzwGE1rl3WSW2l2iuxx5z3gyy4oCTWJ2vV1iW8CYh1tRHAUoWwCLqcQqkgNAXqOUZ7M2xUik5sIHNx9JCAdopA5XfnGSTTp5llnP89v9K2vVT2jl4MwHTPolWX2uUJTu5cCaH163tgPhsUHDHIh5g2EfjVTblfzEMtRbzIrcBe9RWi53GmfiwCULJYtnxrHPq1V2mKK62Q7KDZGgH2KHvNveMxK0hSU3CyGg9MqKU0Ojda7q95XNGKdfG1jMFp4a9eDeirA5yimiRtTpwNiW1sthHLQ23cZAS9WcmvPsrMucVF1rUz5ihsaIh8KRa1iHzhu3cSULXS927I60t46SgM6WcuZcgfTy1AHIOyhKwUMJ3WuPCB0DiRdLYNx5vMvUKTWY2alUHXsjjDRfkQQW5FmfLPYscv3Sle6nJ7XxoFZnyvIqP9sRfjbelX907g7uUXO66fQylV44VjACszpkADn0QhBSuvf6BO3lqFebGbsmSrT8VnmJEpVHtt4jyfPCB9UGCW5So0QH4id2itOu9LJ6Sc5RbHW3nnuy3H60RyDNns0WNwMvWsdcCXJMYFN0170CSvUMluusO4u3pu0Jfwdnw0bpvE5OpleLYvdR3yU6XFPBr0cs1bYk7ALKNb7g2PnA6iCgdXrAFD5CMucUu5wsKvXu6jIuFHiiPRtTjU2tFEj8pp2DeCnUUffgYOTCfaTRLZUP6V3oZ9RJCwLgJm4u96EbtXm87Al57fWceWZv2dJqwg1s4FSaYRFUc8aqb6KVkGOoxYmLGtpbSi2DYTmGvAAta6Yo1gDZlSt1MGZRiP8bMCPmrBvRTFjNn0XoqTXB20ScGjMKrWAievXzLREw4uchPhbzieU9puBARWsra9PfRG7SMrk3cQMLE0IJ4VWwdiF4w2VjlcRukBW0U6k6DGrBGvRCKEC4dQlFElbgJu2y5US3p7cMwhh8uhL4XPMmho4CZw7Y52vKvcPRAZXsBtuVaqyeRSdEgc7QxgOv292rOjkwiXyY7NYwgeilAAl629lmyFxBF6uACUZelo2GgfKRRGKhhk1LxSVInS2ZmCjzueDvuD6399qjs4eaPn4YI8AwErO2p2UoDPL2ViDDJ4TZ7pBW9ni4tkv0JfHgtrIeo375gRxaMIpHavR90PgiSXGeVpQGS0rH4qNoT3NAGnwZz25O0xFzUwnCVBO7gvKLVPZyKUkVFHmW82PXglyyBIdpJ9Ttoo0i9twnCCyC0sAoc2lqOBp48Q25Vi6LbC9aZUBh4YBLVnAMZIpSs1XT3IF9k4qtrCMtb7krbJUeanRa23nPrbzqPohv5pjUvIXqFqFiB7O002tmzlLyeRyDvEIXeLtykQdsf9KTAsfnnZQGX9acUA9v5xu8zbnSwJWBRt6TdJr2zaKwalU7m3XJQqqnThMWup3seo1UmjSoUpIVZR3bHFngSnLy19CuMnUXbKdSERIEEgc6YVkuG2LCqPXHAHWEucziM77Fe8tKC2ec1vdccRvVYUuIWVPKumknTwfFdDyvlutKJpU6Rejnsmd6jZ4iQGGahB19Pu9b2ggabB3RDftWxwqMIeFv1FL1VrztUwJk0hHXUMoLka0vd0DEapr601rbpVjWV5T3ovu5hrTxsBK96g82Kqb5WOinyQh9raHcEgS3piLZ2cESBN9h14fIYkPBWdSjOU9cfvC8ETYnW8sWnKIa6q5Oczw8tXthO7qa3hO8qOEqdk0DQ7ShU5LXhwgPCVoC4Ozi9MSrkdZR211zAxiwh3hsJ4MIfVbGeMvDShST82dS1Ju3RoeOXM2VcUrTcH9Q5YcA0fnOMOMGXLCU3bScVMnxda8YDKc4F5bVf7pul145bg5y86JfEVLWyGElBqfchyQrF1lpqeIWXqOXCmcxkzRDkBroRBVvanEapBwDE5SrWh6KUPYPtTy03txEaeXIqZU3uMmdA6HrV0skI99LedZ2fmEpfuaPOxmX4CUVa9aWGpR4BItMBxw5XmNIofnDC6FxOcRc4pXpL4eXIM0jZWScLZqbev71EeHdKBBNFo3LOPDWNDMEqJbdTnSsext0nnJSebUKPL6ewKhyx3ugjBAt2Idy7u7TvXyX58SnfBrGSJrGInhRHv4ziCJl8OQSpTVo86QhVdq2jNgcqdVzqkK1xb9zV14cse7CF6clx04zTxWpmNNHYhSRIlQEI8qezVudDcbpJL09EuBS4kI3ufqLs8x6clWbTF0EfoQJXQJIdv22zRv2Kskq6t611Iu9mWj0Q2VMyoQyp0kWVDpg9sCKHfsZLtHxrmdbPORPEer23nOp11LZVTBRth9BbU33efBrGdIVwKxutKrNwCK3cQc9hd4OthBikT7CNkka0qOeopEuNoQtEe5I1cT8F9f3r6KlnLceaTt3jobqVOFOJmbcuX8Fxm0hdGtZI9u4N9GAr9uSGwuFbOicgGxnYlZa6Gz587LsYVISpcaOOOF1nq9hI0UhVlXfspBeHkyD9s7gcBvKfgekUcx30UJbEYoYE6AYaLoDgDfkJD8yKvAEiz6Y24Em8coC1vW6Zy9pBpiHkGbzaBuyCAQcIC7ywC3ucH6fgDDAyZ6WDjpQC4tjztBQm6HyZ4CW1BwFn81E0zccGDRSyhOKH1xKjuK2lJURrsQo0XlpVw7BNfl6BbpyvWCm7yXQA7Q0wj0jAD4wDekp8ySHohe9nfb29DSsWkFrHDGT0gs3Cv7CFPu158ohIPpjXGwNF3huPGR1q5oqmBPfA76WTqLCVrnZyzbTmnJre9nG9XvZDutCLg6jGfR5fKZojWBuX9bQDBX1V7CxuiXGNWYLTTwCInfQa5m62PvjQujGiX883koV4TbBWZFhwrhCR74Exc8Y01JtaPv2gcIj0TXDcGGqnKIwoZcl9HUb25L1MhfrnIkUtKt8MDfLkJEVg6tgIDbaBGOTH0Bk1jN9gNq2f6qfEkyr4UkF9RfmnW8bzF2aKw1FPHsQWP51PerKIxCnNKEIu1BiRsmVsnLIWMg88D2yOt3iaBffTATCaaEe56agHdJqvIXergOigqK0mep0tvgwWko1TjBZO0bDttqHo6RwkqagzYqWOJX9vZ2dms6LbY2ZaBCbvb7r4WhSzAhJek4nQWaH8KdEkLN8ZJVVluMcRoVBCd5sXqGe11qO4LBGQfQ6biPHxIMWjPXOgkzROyC8EP8ztZEgBLriovr7rp9HHr59QBUABg3hfuQgk5PiFmEoYVU3IhZiw7fxa4XbumahmdHxavQMaQgUgn2HmL9oUxc8kvw8WIbFJFucIt7LCgHK4UZMxrJsILHoENMnzKIRKwqd9peQen6qaUfSOiBxx08XcLSAKUs1GuyNf1sHb2jxoZgwCM9AzZaJzG4e9tNPubCC44gW6B1ZwtEDDapig0VEtjaoPb1FKOOfR7fWhQxRH1eVTofud77nLSRXMcBkVhCoODB78qPNLqIBMlZeROfgPEe1AE1Ldb4Ok9592Tntuu618ZevRjq9AwATw94wJ19EwWR8npBjAGaUfH82t2mJ4T3XesL9mITUjNS6CEqjUTDPcCeHnFn3XqUwSoSgsmqTJheNOeBYxaqXMpNVqwMEdtc5zIcnk2AkZprClsUrSQ2YGskJ6zs3Bq1hlast7V3dgUaNg7z7W6op7NQlXqF1X9pFMbTPxvh6ObWqGi987lAlCnLsLwqAooFbdxtnmZNK3qyRitfbs2bDgYavb8NAIic4kAQbkCxQQE88RqquiN6WQIK1Btl0s4V0wTAtbRBy7BnitEEgsEdecZkkOYJrp8kbHbL517Y6sClQcCWOCoDZ1IPHtFbFB5tABPiw2k0tgQLqzHEf0KpdYInH5sgg7lAJ1MHzJE0rs0ulilIqnb1QgHaciIHhfYQbhtyWqaF2STYqjjPXJmMf7LOgC70aCBR54lNPq08PsngpD0sxgi8tfWnJRP2hWj9O6tzFPt4TkrkHqTfUDPzd1n4ACTMR9Bl138EXgs4gqxF5ZFJwcYUyouGQZpNucBq6E7JbBlNgwSkUqbdISmvUtzmkFy76XTqtkulYpVDj6YhgzHmVobuR7bny45OetjyiO0XPtODjp71ZEJhtYrl92jQT7HQRjdUWV615KiJXIePAuCIJ1GfARzZnLYH3kJ6rurji5illUasKydbE6UnTHHpEiDhvhoTLtUIbBr4STDFEkvwIBxniByWai9Apq5lt5xQ5kFxv0YsIIy7wzgj4XQKqAWzhbeiBlE8L2lDXsVPEOmIrNB6uSHtCmv4v8k0ElHIx6NAwjBEj5JdyvlxPIfD1MdHcIg3dbaSZL1Z88IePqfSbo7ZKyE5O1MOTjglx7koylCs268A6Za8efoo7SH18Mxv9By9VksEFxmcunj8RX25FIdGxA5hFMSxgnzCRZEzzSqx9rCLo9awhMdRxN7phnnXEukK72BMJgZCu9wGNGWXKh4HseQKUjS9uPICdDSQeP7Nwj2g3cwKfg2Q8qyDZnhY4aw0VfhNKsbf7aMU8l9jwY7jNbFmuKhW0RJaUwFrhyYDzvWr9IqbLcKpsfT7Y5ddiPm78rwhLMHtal7YLOe1Sk9RwcgtoVAIFNMpLwcuiImQ27sWcq07Sxozm8UjZD8ThQgqdHvmdympDAqviTBr8f2ZStfETjuLLffYdrpPiSKZ7BV9D1s0BXuzMzAycpy3S2lG97xcSM2LM6tMUFYOGhsfWJxJBoEGnZ4238ESD2vkJczRpKlJ3KAqVFqDEINP26hQJ5Fhrp2xO0ElDAsisV2b2Tt6UhzmeOhEFE4Cy1Y3cHyS6MJb3thOxmPtzMyFcG5MIOTRtd8OMf9fdt1IY3bFYzqjwhI1phwRiX5ZdMr1565GhGngk3b7DuXIiIBJdIYLenW6Zibt1PK5GFbOql6jebqdmCm7dyGcoi13OaaUGkXtbEn3x8s9UC6DmAswreFuwpTuS9lm9fxLUu2wmZIc3JdiIOWgadfypYbwpZvhT7oFb8LIWUT1zQaLu8PqCfTZKjCoR91eCBuaLPVJpn6cuI6d6KzMmlMvKle8BoyyYx5izSTnSSRd6BcMVfdRMROrxOxyPCMU472T6Bh2DVu4WeIFOyR8MuGw3iNSMA1Mn7iYNsSE7DhdljwlkmBWNPOqb82F3jImNDHXCXKbUjxOeNyGX8iy6FDFyVSaZGD8J6kWoJ51AuDE6rUxcdc6hJJvcvq2CiV18gxb4neOtH8VSPWvOvF4k1zctA0BbaJS52ndUcF7GPzhPD85cbCPqWn7sstQvigvLr7gtkcPDyKgDxiuKYAAidTp9L5lfKVROi7IWs1V3NhItE4sDCPaS8mI3BrY3ieateXERaPR9Qk9HmSfCHoOnCrhUSFFrLi0uUObBM9pmNxOLwi9cSbExRtj39y3KjMjbTvt9NAkaqxoQ7khIrxFC5ppE0oZ85gQM36Gmjo63HSL0FM9NkQ6QaKgebZExa2WEUWUaKlP1V0cnCZhPS6DOYquA4jz6WurI6PzfsiKawkqDxT6L8q5DPfVllOwHAxdndKdC1nuYZaE6Qfy3EVCfzw5O3HOwRJgO4EfIqG6TcOrbekKc9Uxs8WtyvmE0OSrq0DnX49eavuZ6L3d6CxUhdVck4PCPuHRUfOLcGSv9zUS8oYSvh1fqLdkcVwZeqreIySupgdrB5F0TYcTIAesjsG48awyMw2f0fktqzu8b3mQuvlOFtAo0WjN0A5uoJVeWkn6fXdFsAUZlxoQGNO2996fhzrMFcQTC3aGGeVtBIpC9bSgv8UKHWWU084n9kCWKDEBcQOjmadDHkf0fev2DtEcccuBEIPy64tAp9YKVONDwMuYNMSXDMrChZD5Nz7OElDsuWb3yuMQq8YsD26EmlUGhwMrvQGdpPWrTbSfErwMaYlWzhHy8zZxV3WHXee9L7AlBv5PFCaZDk4CNkCxZV9H1XxS91GCnlRBUsuhFvDJwDRGv63PS2horzp0Ze17uHuJ1R9Prv2f1zP4f5hXVUUpW2CbTeznKGsrJ0DlTrPWedeFITTHPqw1j94AaJm7qkpPLvlYw9FzDNWmQxZ5FjBm6zLIq5CigBjjNyYirZZ3QxgNj8oCwAltSHLfXqb1Vs5bHugq6twERWDMkPf6MVezong6MwMHdm4wZi2iEHtRAodaCKLvC822IyNNPmQeTcucf1Zf0biz2FS06XZFkGM0TtEBOGBmzvwANr5jUtFJRDo4O2QdqDDHMwLjMOL0vzfWcyBmb5Iw9E2jUYjWLRZdnZbtLezGX019vx9xY9pGa69CVIWP2W2HUZfSSRGZXxfOpjr295Y02FBiIYRsDBJqkUUZsfITb75iLse4pzRM6zOVaVmhJFJ7Y0hHh1idoEHFNoiWWcYRs6aq64JuJXcMmS23Zza2pQwi74nSNy2VIMekbbVR0lRpEZORadtM0zMaBvrAU3HZQ6oQVXAdwKf5ae1mAqa09Z8wFcpKFT7artIC8tc5fSjegt758iLnMErWIHCpP1ff51IQqQo9q6VP38coK89FOUjpovkg8tPkXlWu1vruUZ6VAtQhGobIpB5bg1Hv8Vzbeyvgo9TSYrvnOtSiifnJnIcXNy4ltWa3xpmYrDOFeaNOMBUhoqhmoHLtwWRD0htlEgWkkOBbzFdS5v2JUvLIRUvgVFcFd0lQqQvVEooqsVnCGkGT5rnEEWpcSXA8znzb6fjZ1J3TiSUipe4UuDRsKHMypgq3CMDq27nJJkebRhw3ZIZ368zrOJqAUtRjnL1U036WMmEkMtUijF07FyjCs9iIBdlZSZ2xndnZ6cVUhEIC5eohcYh6BzWDlLynmD2OVZx9OiDD7r3gnpBw1TIPEQRsTj9LrnkqpGwZlGlcDzWV0N7cyouDvVP8XhDAMPLUQ9hGNM62B7RTFVXSQMZIbBhsDXFGqAwFhtI0Ae18F5UZFLGZ1y272LXZjvirMMGeHw5KTMETtyRyiFInYmVKCVW9ISoORaV7GTtNpArRFuThrN6HENKJsisfpRBcfxS4eB5jPmipP7wDySJx2P3mdi1Nb4i6IijqtI3c8d2UAuLOGTAip8oPpwq4J6cf15G8SHjjcgWWwzHBme6lymHgiu3IC577yDPqOq00DsWPYewbrEFRdZ2uIOcqljQm1Dwirn3ycm3CBBXexdk6hlFnVQZJb9c2M1is7oJVxFpoy5ppYamtdSmLK8nfi6Xe0NdXRY8WJpMksDy4NDnugImhBPeEyI33pKtcXOfL9FKPNLwdLp4GG4Nk0SdrwM2nwpjOcbw7Sk7VHhl4GKERb6xXzC6xp4wlmG7Kg8m0dThtuB60yCUuB0P5m9YyXu9iYZO4dLpY6qAqtWzK8KxH6jP8mpmKOxvEfFAUD2bdtHDKBB07nUa6vYCVPxuRbwZJHMmXv4Rv96kqDaLUdZUGv6qIFm51yAjGtI9CPgEy6rpN1m3q36LoEaaIYdk4crFdGCMaUdwbtdMNSIW4IGGGCkihxOoBDUFLp1vMbQbKFYeaxHKidAYgZInIBcYR6g2IXqfXgId6eu7i6sVauqOPxkXkTFfFHiz0c8UgefKZWahrEphEqn2hrPhy5ydrrD5pDXQHtBwB2n11ZJFGSMn3zUbKWJ7wlovzr51m1mb7U8Dmzt13gsaUzqpEyN1ge9ZBN96JdWLy3MLsIYp8001DHVqKGdZ65tXrVJ0ExROd5yS6tcKgtU4Mz0swGsP5eh6KO5nskAaVzVEeQE7RV9tZcbeJqXai24Zo0X5HB1r2coDtg3vnTmo7qpoWSc9YsOj3kXVqxUx1iRaHhK65i45tUhvF9fXxXmT8OP98yxlaPqJP75Tl6rIAtQddBcwUIoGDnaAj8ItXDVXUsCEhFbXEvSbCY6EQkM9fz571i8hFI4IRZMgn9gpfmiJkLJbzt5rHJGXnbaQ0MITGcUDaqim9LUZyeE3HuinfGMwJPop868YD6wzEFCprwGz0MipC4uWHWMBuPLWd8RtutPuO7TZIJKnsennEkdd8i4KnZvxU1COJ8Juctyia0PwspiTbAcp7VKWPifrYS9UQxEEcLZxcnvUJ4XCU5TKARUXfZp6ToDNHjZFuFd0O9XdwU03O6eTwo2o46BKtnJQFjBP2XSf7rQQ17gJEGyS4mokaPeqjXtDXFYcJ3yLenFBDiP15P0fn2V1YjBAZ7XJfS2TG0ISQ7O20ZBUISbd3GJ1hhTPHSp77ltvUfc6ysEagVaGLabZrHoPsKEPvolO9VC5vlGCA688yxMzFQYYgkeQKaiJWINm3nheIWcAmbryrLOvEl9bVkXrC70dysTGOvXKzI3RfK9gX0ziXngC4YX8RmkTdpaww1eFkeBQDzUfQIlB6J9it1bdH2e6AOGYo0gbWxOfM1lX1jpJmC3TeLbiUA1kpYATGwBtSl7GG6tvhU5bXWWrgsmVxEeFA1RubRwc5SP7LxO5XvaocjRrdv1mlS6NU6g1Hll6m5qMAccwfZwEsx3ezHpYZETT9cQDSwbZLmrkqUIAYC6uFibVVmmQPfulAtazI0NWWPTGrj2ZOdiNQRn8DOgvI2OluQKEyOyerzF3lSM44gUN5iqjfCENXGmIanAYfEzRRNSGwXPWlT6Da19HZWmvAJaixcwa01lWXgn7d6PEhlD0KFd1OMCw5cBwRK7ridW4pimKKZBIISCzfJGDk9alM2o7qwXcpan3IraNtROOVK1nkqw9r5CcTa8rvl1QwKrnQDlcySbpqTZ0KdY2ONFE8ATPcSFLYbLUtn1gkCvF7NvWpBhPWNYadxG7flExNJLzmzHkyw8GfVzvQUieuewlGRHGEZYltdJN7UNrpljiX77xbYubDupWlrW05Xhvq4JMHnodhjXAA49sEUriI2Q7mqgvYogBvHgloZJUEOioxpGFLK6cripxFzWYG7xeoLfDheVc00YutdrNdzDtg0AdOzoWpVuapVdLFzNyt2gEfrvvlXUKq27JAqavYVvIN5zTsbhYqXS2lm6x7XwKbcYOVqot6ghYE5iWzXggnnftV5aKEbiDQlGMrcn3dlbYQi90KYDaGpVRvBdgLvjiJI2LLPKbFk18H1iLRQU4htxlcWrir38UecJrFfzcl3UhGZ4lzH 

proc insert_n_rows {n} {
  for {set i 0} {$i<$n} {incr i} {
    db eval { INSERT INTO test VALUES(1, 2, 3, 4, 5, 6, 7, 8, 9, 0, $bigstring, $bigstring, $bigstring, $bigstring, $bigstring, $bigstring, $bigstring, $bigstring, $bigstring, $bigstring) }
  }
}

set nStep 100000

for {set i 0} {$i < 100} {incr i} {
  set us [lindex [time { insert_n_rows $nStep }] 0]
  puts "[expr $i*$nStep] [format %.2f [expr (1000000.0 * $nStep) / $us]]/sec"
}

And it seems to have quite reasonable performances, if you could replicate it and confirm that this is what we want it would be great!

[xin-e-liu]

This is from the same host where I tried redis:

➜ redis tclsh sqlite_test.tcl
0 335889.45/sec
100000 292093.61/sec
200000 331140.08/sec
300000 299299.04/sec
400000 285272.52/sec
500000 314314.18/sec
600000 326883.91/sec
700000 328050.62/sec
800000 282876.63/sec
900000 283617.14/sec
1000000 326392.06/sec
1100000 331548.50/sec
1200000 314786.13/sec
1300000 332517.56/sec
1400000 323021.41/sec
1500000 325151.44/sec
1600000 329544.67/sec
1700000 291952.90/sec
1800000 306515.29/sec
1900000 314255.90/sec
2000000 326737.59/sec
2100000 326872.16/sec
2200000 328044.17/sec
2300000 327582.08/sec
2400000 332852.92/sec
2500000 328659.79/sec
2600000 323537.69/sec
2700000 273305.44/sec
2800000 271109.24/sec
2900000 310736.57/sec
3000000 308788.75/sec
3100000 290409.80/sec
3200000 298979.58/sec
3300000 310722.09/sec
3400000 327442.64/sec
3500000 327104.18/sec
3600000 320485.09/sec
3700000 313140.96/sec
3800000 332447.91/sec
3900000 298129.24/sec

Looks pretty good. Is it 300k rows per second? I see 10m rows added in 30s :--) And each row is like 100k size

[siscia]

How you can see single thread performance are not the issues.

Unfortunately we will never get to 300k/s since Redis top to 10k/s something more realistic would be 5k/s but also that is not easy...

I will try my best and keep you updated :)

[xin-e-liu]

Thanks! I have a few quick questions regarding redisql and hope you could help:

1. look like the combination of redis and sql in redisql context, redis is just a client facing interface, all actual data storage is done by sqlite. There is no redis internal storage/data structure being used.
2. For a table created, is each column by default indexed in sqlite, or we will have to issue the index sql statement to get that column indexed?

[siscia]

Sure!

1. Yes Redis is used only as a layer for connectivity here. It is actually possible to have the data you write in SQLite appear also in Redis but it is not yet possible the other way around (I am sawing some development in this direction in redis, so maybe the next minor release 4.0.10 will provide us with this capability)

2. Assuming that with index you mean those data structures that makes find a row faster inside a database. No, RediSQL does not create any index for you, they make writes a little slower and are technical choice that the user should do. However SQLite automatically indexes the primary key in your tables.

https://sqlite.org/queryplanner.html
https://sqlite.org/optoverview.html

[siscia]

Hi Xin,

would you mind to run again your performance tests?

I would like you to compare the result of the master branch with the result from the branch type_refactor

In my test the performance for very heavy insert doubled from one to the other. I would like some indipendent confirmation also from you.

As always you should be able to compile with cargo build --release

Thanks,

Simone

[siscia]

Hi Xin,

would you mind to run again your performance tests?

I would like you to compare the result of the master branch with the result from the branch type_refactor

In my test the performance for very heavy insert doubled from one to the other. I would like some indipendent confirmation also from you.

As always you should be able to compile with cargo build --release

Thanks,

Simone

[xin-e-liu]

Sure. I just pulled it and got a compilation error. Is there something not comitted?

error[E0583]: file not found for module redis_type
--> redisql_lib/src/lib.rs:4:9
|
4 | pub mod redis_type;
| ^^^^^^^^^^
|
= help: name the file either redis_type.rs or redis_type/mod.rs inside the directory "redisql_lib/src"

error: aborting due to previous error

The following warnings were emitted during compilation:

warning: src/CDeps/SQLite/sqlite3.c: In function ‘exprAnalyze’:
warning: src/CDeps/SQLite/sqlite3.c:131527:39: warning: ‘pLeft’ may be used uninitialized in this function [-Wmaybe-uninitialized]
warning: pNewTerm->u.leftColumn = pLeft->iColumn;
warning: ^
warning: src/CDeps/SQLite/sqlite3.c:93128:39: warning: ‘pRight’ may be used uninitialized in this function [-Wmaybe-uninitialized]
warning: return p ? exprDup(db, p, flags, 0) : 0;
warning: ^
warning: src/CDeps/SQLite/sqlite3.c:131506:11: note: ‘pRight’ was declared here
warning: Expr *pRight, *pLeft;
warning: ^
warning: src/CDeps/SQLite/sqlite3.c:131529:28: warning: ‘eOp2’ may be used uninitialized in this function [-Wmaybe-uninitialized]
warning: pNewTerm->eMatchOp = eOp2;
warning: ^

error: Could not compile redisql_lib.

[siscia]

Sorry,

you should be good now.

Thanks for catching this error up, really appreciated.

[xin-e-liu]

I get around 8.2k requests/s with around 100k payload :--) Much much better!

[xin-e-liu]

btw, just tried pure redis set with a few combinations of benchmark arguments. turns out i can get to 27k/s

➜ redis redis-benchmark -t set -n 1000000 -c 1 -d 114000 -P 100
^CT: 26971.79

I'll try out redisql a few different combinations and see how it goes

[xin-e-liu]

Looks like I can get to a bit more than half of what pure set can get.

14710.21 requests per second

Mon Apr 23 07:15:56 PDT 2018
➜ redis date; redis-benchmark -c 4 -n 100000 $(cat cmd_short.txt) -P 5 -q; date

[siscia]

Wow, personally I am quite satisfied by 8k request/s (which is the same figure I was getting), if you can get that up to 14k request/s even better.

Do you need it to go even faster? It would require some significant work that I am not even sure what make it slower than pure redis.

Between today and tomorrow I will make an official release with this changes :)

Thanks so much for your feedback, it really helped.

If you are satisfied I would suggest you to close the issue 😃

[xin-e-liu]

Edited the title to reflect more of what is discussed, and closing. Thanks! I'll also try with some more indexing with redisql :--) will see. Thanks!

[siscia]

Wonderful!

Thank you so much that your feedback is been fundamental.

Also, I am looking for beta tester for the PRO version (the one with replication) if you are interested let me know (you should be able to see my email) and I will provide to you the executable.

Thanks again!