Slow, design approach

[siscia]

Overall I find the whole module + sqlite quite slow, especially when it writes on disk.

On my machine I get roughly 8 inserts per second on disk using the most naive method, independently of the concurrency used (which makes sense since at least for now it is everything single threaded).

simo@simo:~/redis-4.0-rc1/src$ ./redis-benchmark -c 1 -r 10000 -n 100 REDISQL.EXEC DB 'INSERT INTO test VALUES(__rand_int__, __rand_int__, __rand_int__);'
====== REDISQL.EXEC DB INSERT INTO test VALUES(__rand_int__, __rand_int__, __rand_int__); ======
  100 requests completed in 12.30 seconds
  1 parallel clients
  3 bytes payload
  keep alive: 1

1.00% <= 110 milliseconds
2.00% <= 111 milliseconds
40.00% <= 112 milliseconds
43.00% <= 113 milliseconds
46.00% <= 116 milliseconds
47.00% <= 122 milliseconds
67.00% <= 123 milliseconds
72.00% <= 124 milliseconds
74.00% <= 133 milliseconds
84.00% <= 134 milliseconds
91.00% <= 135 milliseconds
92.00% <= 144 milliseconds
94.00% <= 145 milliseconds
97.00% <= 156 milliseconds
99.00% <= 167 milliseconds
100.00% <= 201 milliseconds
8.13 requests per second

clearly this is not enough.

However the slow performance are expected, if we hit the disk for each insert, we will be slow no matter what.

The obvious solution is to don't hit the disk for every and each insert, but batch multiple insert and execute all together inside a single transaction.

simo@simo:~/redis-4.0-rc1/src$ ./redis-benchmark -n 1 REDISQL.EXEC DB 'BEGIN TRANSACTION'; ./redis-benchmark -c 1 -r 10000 REDISQL.EXEC DB 'INSERT INTO test VALUES(__rand_int__, __rand_int__, __rand_int__);'; ./redis-benchmark -n 1 REDISQL.EXEC DB 'COMMIT TRANSACTION'
====== REDISQL.EXEC DB BEGIN TRANSACTION ======
  1 requests completed in 0.00 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
1000.00 requests per second

====== REDISQL.EXEC DB INSERT INTO test VALUES(__rand_int__, __rand_int__, __rand_int__); ======
  100000 requests completed in 10.62 seconds
  1 parallel clients
  3 bytes payload
  keep alive: 1

99.98% <= 1 milliseconds
100.00% <= 2 milliseconds
100.00% <= 3 milliseconds
9411.76 requests per second

====== REDISQL.EXEC DB COMMIT TRANSACTION ======
  1 requests completed in 0.19 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 188 milliseconds
5.32 requests per second

This other approach provide a very higher throughput (2 order of magnitude) with very small effort.

Now, before to go blindly ahead and create a pool of statements and provide a new command FLUSH, to force a transaction, I am going to explore more deeply what PRAGMA option SQLite can offer.

Usually there is a trade off between velocity and safeness, however, living inside redis I can use the guarantees of redis to make up some of the safeness lost and see if I can reach good enough performance.

[1]: Statement, something that modify the internal status of the database, it is in contraposition with query that simply read the database (INSERT, UPDATE, DELETE vs SELECT)

[siscia]

The test using the PRAGMA setting are good but not extremely good:

simo@simo:~/redis-4.0-rc1/src$ ./redis-benchmark -n 1 REDISQL.EXEC DB 'PRAGMA journal_mode = MEMORY; PRAGMA synchronous=OFF; PRAGMA count_changes=OFF; PRAGMA temp_store=MEMORY';./redis-benchmark -c 1 -n 10000 -r 10000 REDISQL.EXEC DB 'INSERT INTO test VALUES(__rand_int__, __rand_int__, __rand_int__);'
====== REDISQL.EXEC DB PRAGMA journal_mode = MEMORY; PRAGMA synchronous=OFF; PRAGMA count_changes=OFF; PRAGMA temp_store=MEMORY ======
  1 requests completed in 0.00 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

100.00% <= 0 milliseconds
1000.00 requests per second

====== REDISQL.EXEC DB INSERT INTO test VALUES(__rand_int__, __rand_int__, __rand_int__); ======
  10000 requests completed in 2.07 seconds
  1 parallel clients
  3 bytes payload
  keep alive: 1

99.96% <= 1 milliseconds
100.00% <= 1 milliseconds
4826.25 requests per second

A little less than 5k transaction for second, which is roughly half of what I get using bulk insert.

[siscia]

I believe I am making too many problems for the whole persistence deal... By default redis is not too persistence aware.

Of course SQLite is born whit different use cases and it defaults make it clear.

[siscia]

I decided that I would be better off using a pool and a FLUSH command.

This keep the invariant of the persistence on the SQLite side, after a FLUSH you are always sure that everything is been written on disk.

Before the FLUSH you are on the persistence security of Redis, which is more than enough in most occasion.

[siscia]

I don't like the idea of batching in memory and then flush.
Mainly because I don't have a simple and clear way to communicate errors.

Another interesting idea is to don't care too much about persistency, store everything in memory, and implement the rdb_save and rdb_load callback (to write the whole database inside the RDB file of redis), possible implement also another command REDISQL.SAVE that write an in memory database on a file passed as input (use the backup API).

I tried a simple benchmark to understand how fast is the in memory database, it is fast enough for small dataset, however it get slower and slower after every insert.

# Records	Insertion per sec
100000	23798
200000	15708
300000	11774
400000	9256
500000	7542
600000	5964
700000	5383
800000	4589
900000	3583
1000000	3539
1100000	3604
1200000	4004
1300000	3643

4k insert for second is not that bad, but it is not even extremely good, considering that we are keeping everything in memory.

---

To reproduce the results of my simulation is sufficient to start redis with rediSQL and then:

./redis-benchmark -e -n 1 REDISQL.CREATE_DB DB; 
./redis-benchmark -e -n 1 REDISQL.EXEC DB "CREATE TABLE test (a INT, b INT, c INT);"; 
./redis-benchmark -e -n 100000 -r 10000 REDISQL.EXEC DB 'INSERT INTO test VALUES(__rand_int__, __rand_int__, __rand_int__);'

to create the database connection, the table and runs the first 100.000 insert.

Then

./redis-benchmark -e -n 100000 -r 10000 REDISQL.EXEC DB 'INSERT INTO test VALUES(__rand_int__, __rand_int__, __rand_int__);'

to insert other 100.000 tuple.

[siscia]

I wronte on the SQLite mailist to try to get a better insight on the whole issue.

An extremely helpful user wrote a simple TCL benchmark script that let me benchmark SQLite by itself, the script is at the bottom of the message.

It seems like the script is doing exactly the same thing than my implementation but the performance are extremely different, and I don't see a degradation of performance on the SQLite / TCL script

Overall all the data are in the following table:

# Records	RediSQL (Inserts per second)	SQLite (Inserts per second)	RediSQL (Log)	SQLite (Log)
100000	23798	246073	4.37654046	5.391063964
200000	15708	246679	4.196120893	5.392132179
300000	11774	245979	4.070924031	5.390898032
400000	9256	242408	3.966423346	5.384546948
500000	7542	239960	3.877486528	5.380138853
600000	5964	244596	3.775537635	5.388449351
700000	5383	244054	3.73102438	5.38748593
800000	4589	241277	3.661718058	5.382515924
900000	3583	242812	3.554246808	5.385270146
1000000	3539	244418	3.548880563	5.388133186
1100000	3604	243074	3.556784782	5.385738508
1200000	4004	243118	3.602494069	5.385817114
1300000	3643	244126	3.561459171	5.387614035

The graphs below show even clearer the difference in performance and the non-degradation of performance in using vanilla SQLite

---

    package require sqlite3

    sqlite3 db :memory:
    db eval { CREATE TABLE test (a INT, b INT, c INT); }

    proc insert_n_rows {n} {
      for {set i 0} {$i<$n} {incr i} {
        db eval { INSERT INTO test VALUES(random(), random(), random()) }
      }
    }

    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"
    }

[siscia]

Let me establish a simple baseline, on my machine a simple PING, which is the simplest and fastest command you may do, perform at little less than 80k requests per second.

====== PING_INLINE ======
  100000 requests completed in 1.27 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

99.90% <= 1 milliseconds
99.98% <= 2 milliseconds
100.00% <= 2 milliseconds
78492.93 requests per second

====== PING_BULK ======
  100000 requests completed in 1.33 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

99.97% <= 1 milliseconds
100.00% <= 1 milliseconds
75187.97 requests per second

Opening a key and increment its value takes the same time:

====== INCR ======
  100000 requests completed in 1.28 seconds
  50 parallel clients
  3 bytes payload
  keep alive: 1

99.64% <= 1 milliseconds
99.94% <= 2 milliseconds
100.00% <= 2 milliseconds
78308.54 requests per second

Overall I don't think I can go much faster than this numbers anyway...

[siscia]

Finally some updates.

I decide to run perf on redis running rediSQL and on the script TCL.

Since the software are doing similar things the intuition lead me to think that the hostest path where the same.

I was actually very wrong.

I started a TCL section, get the pid digiting pid, run in a separate terminal the command sudo perf top -F 399 -p PID and I got the result in the image below.

There is not so much about SQLite itself and even the hostes function is not so hot.

Then I did a similar work for rediSQL.

Start redis and load the module, get the pid from the log and stat perf as above, then run the benchmark.

The result are in the image below.

Here we see that most of the time is spent in sqlite3PcacheTruncate and in sqlite3PcacheClearWritable

Also, we see that, even if the second benchmark ran for a lot of more times we have less samples, ~600 against ~14k

[siscia]

First performace improvement

Change SQLite after mail list help.

SQLite had a bug in version 3.15 and this caused the slowing down behaviour.

Performance do not decrease anymore and are stable at around 34k inserts per second, after optimization we get to ~44k inserts per second.

However Redis can keep up at ~90K operations per second while SQLite can write more than 200K tuples per second.

We are leaving on the table around half of the potetial performance.

Potential probelm in context switch, query are loadad into a queue that another thread consume and write on SQLite, processor switch very often and I don't see a stable 100% in any processor.

Writing only on SQLite give me stable 100% in one processor.

Disprove the hypothesis of the context switch problem.

Write two small C program, one that write directly on SQLite and another that use a thread pool just like rediSQL.

Two interesting fact.

1. The program that write directly on SQLite is still slower than the TCL scritp used above to test, which makes me wonder how that script can go so fast.

2. No noticebla difference in the direct write and the "pooled" write, the "pooled" is harded to measure with precision but I rough estimate tell us that the performance are quite similar => no context switch problem.

The performance of both scripts are yes, slower than the TCL script but still higher than Redis, so we still have ground to top performace to the one of redis.

Still haven't understand why we are going slower.

TCL was cheating

The TCL script was executing always the same insert which was heavely cached, it wasn't recompiled but it was simply loaded from the cache.

Considering that the compiling operation was one of the slowest, this makes a big difference.

I disable the cache and now the TCL script is down at roughly 50K inserts per second.

Different Approach to message passing

I would like to get a single CPU at 100%, I wrote a simple queue a wrap it in a mutex.

Now I am able to saturate a whole CPU and also SQLite.

Reached roughly 50K insert per second.