SQLite Forum

Timeline
Login

8 forum posts by user tyke007

2021-08-17
06:56 Reply: cksumvfs producing invalid checksums (artifact: 7253fa2f5b user: tyke007)

Today I clone your repo and test on zfs, get the same error.

2021-08-11
05:55 Reply: SQLite Binary Log support! (artifact: a82c99a0d9 user: tyke007)

Thanks for the tips, and yes I has study the BedrockDB and RQLite.

Correct me if I am wrong, I think RQLite is not suit for the job. It is not a library solution and have poor performance, and you must use it with RESTful API. And it is hard to dispatch Stored Procedure as one log entity like BedrockDB plug dose.

My idea is come from BedrockDB great design. But BedrockDB is too heavy and not a library solution like DQLite. And it limit into linux X86 platform.

BedrockDB is CPU/Memory hungry and rely on NVME SSD raid with Memory-mapped file, and the design reply on lock cross multi thread.

With Binary log entity you can use memory DB handle, and late you can switch to on-disk mode with exclusive lock if your DateBase is too big. If you are in single thread mode like NodeJS, you can disable lock and dispatch huge read query into slave node.

Binary log entity also can be used for audit, time travel.

The idea is to provide a function can be used for multi goals.

05:35 Reply: SQLite Binary Log support! (artifact: e846c87e7d user: tyke007)

With a master/slave setup, I can dispatch read request into diff slave server by SQL, so the cache is more efficient.

And yes I plan to use it with high speed write system, and I think memory database with reliable persistent log will be a good solution.

2021-08-10
05:08 Reply: SQLite Binary Log support! (artifact: 2eeb866c6d user: tyke007)

Sorry for me bad expression ability, I will try again.

Sqlite has nice performance without lock, on my test it handle 100K write transaction per second with memory db. This kind performance it can be used on a lot new area.

With EXCLUSIVE lock the performance reduce a lot, but you get the concurrent read/write transaction with multi thread. in this mode the write thread will blocked to wait checkpoint. On my test around 10K write transaction per second .

That is why I want binary log, for each write transaction generate a log entity, replay it in order you will get the same result on a diff db handle. In this case you don't have to wait on LOCK for checkpoint. and without LOCK you get much higher performance. And if you don't loss log then you also get persistence storage.

Use it with Raft will allow you have a system able to provide service even one of server crashed.

The log entity should throw error if the SQL include RANDOM(), DATE() function. (so then don't get diff result when replay). It also should exclude some SQL like PRAGMA journal_mode=DELETE.

The session extension is not able to handle scheme change, so it is not suit for the task. The binary log should generate for each transaction, session extension is design for log transaction in batch, and it only work with primary key.

https://github.com/benbjohnson/litestream do what I need, it replicate SQLite change into other server. But it use cross process lock do do the job. which result in poor performance compare to in memory DB or LOCK-FREE mode(At least 1000 times fast).

I hope I explain myself better.

2021-08-09
16:00 Post: SQLite Binary Log support! (artifact: b90cb35d9c user: tyke007)

We have session/changeset/patchset for sqlite, but they are quit limited compare to Mysql Binary Log.

If there is a function generate a binary log for each write transition, then late able to replay the log on a slave instance and get same result, then this can be done with it:

a) save binary log into kafka or other message queue, then you get a quit scalable master/slave cluster.

b) Use Raft leader replicate binary log instead WAL frame, the raft entity size will be much small compare to https://github.com/canonical/dqlite.

c) You can setup a memory database with binary log store into kafka, use it with Raft you get a high performance and high availability cluster.

Here is a simple planning to add binary log:

  1. when start a write transaction create a binary log entity object
  2. for each sqlite_step call from user code, save the SQL and bind value into binary log entity object. (check duplicate SQL and value, save as ref)
  3. when commit write transaction, save serialized binary log entity object into memory block.
  4. maybe add changed page hash into the log object, to prevent misuse. (and a hash chain in order for each binary log entity)

To replay it to a database handle, first compare hash, then exec sqlite3_step in order. Before each step do the SQL statement init if not yet, and bind value, compare changed page hash.

Is this plan feasible?

07:46 Reply: WAL2 branch build error (artifact: 8c4b66e013 user: tyke007)

patch to fix it:

diff --git a/src/delete.c b/src/delete.c
index 459e932b5..0debce3cb 100644
--- a/src/delete.c
+++ b/src/delete.c
@@ -188,13 +188,13 @@ Expr *sqlite3LimitWhere(
   }else{
     Index *pPk = sqlite3PrimaryKeyIndex(pTab);
     if( pPk->nKeyCol==1 ){
-      const char *zName = pTab->aCol[pPk->aiColumn[0]].zName;
+      const char *zName = pTab->aCol[pPk->aiColumn[0]].zCnName;
       pLhs = sqlite3Expr(db, TK_ID, zName);
       pEList = sqlite3ExprListAppend(pParse, 0, sqlite3Expr(db, TK_ID, zName));
     }else{
       int i;
       for(i=0; i<pPk->nKeyCol; i++){
-        Expr *p = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[i]].zName);
+        Expr *p = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[i]].zCnName);
         pEList = sqlite3ExprListAppend(pParse, pEList, p);
       }
       pLhs = sqlite3PExpr(pParse, TK_VECTOR, 0, 0);
06:10 Reply: Status of wal2 branch (artifact: 6a0270c84f user: tyke007)

I like to know is there any plan to provide a roadmap for wal2 branch?

From the code I see, it is a runtime option to enable wal2.

Merge it into main branch and a compile time option to enable it(default disabled), will made more people use it, and more people to provide patch to improve it.

05:17 Post: WAL2 branch build error (artifact: 892e76713f user: tyke007)
/usr/lib/llvm-12/bin/clang -DNDEBUG -fvisibility=hidden -ffunction-sections -fdata-sections -D_GNU_SOURCE -fstack-protector-strong -pipe -DSQLITE_OS_UNIX=1 -I. -D_HAVE_SQLITE_CONFIG_H -DBUILD_sqlite -DSQLITE_THREADSAFE=1 -DSQLITE_OMIT_LOAD_EXTENSION=1 -DSQLITE_ENABLE_MATH_FUNCTIONS -DSQLITE_ENABLE_FTS5 -DSQLITE_ENABLE_JSON1 -DSQLITE_ENABLE_UPDATE_DELETE_LIMIT -DSQLITE_ENABLE_SESSION -DSQLITE_ENABLE_PREUPDATE_HOOK -DSQLITE_HAVE_ZLIB=1 -DSQLITE_TEMP_STORE=2 -c sqlite3.c -o sqlite3.o 

sqlite3.c:119983:56: error: no member named 'zName' in 'struct Column'; did you mean 'hName'?
      const char *zName = pTab->aCol[pPk->aiColumn[0]].zName;
                                                       ^~~~~
                                                       hName
sqlite3.c:16913:6: note: 'hName' declared here
  u8 hName;        /* Column name hash for faster lookup */
     ^
sqlite3.c:119983:19: warning: incompatible integer to pointer conversion initializing 'const char *' with an expression of type 'u8' (aka 'unsigned char') [-Wint-conversion]
      const char *zName = pTab->aCol[pPk->aiColumn[0]].zName;
                  ^       ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
sqlite3.c:119989:71: error: no member named 'zName' in 'struct Column'
        Expr *p = sqlite3Expr(db, TK_ID, pTab->aCol[pPk->aiColumn[i]].zName);
                                         ~~~~~~~~~~~~~~~~~~~~~~~~~~~~ ^
1 warning and 2 errors generated.