SQLite Forum


8 forum posts by user phpstatic

20:35 Reply: Writer Concurrency - server-process-edition (artifact: c894495764 user: phpstatic)

The snapshot isolation based based begin-concurrent solution sound good.

Is there an optimal or recommend total number of connections limit for writer ? (incase add more connection will decrease the throughput, assuming that there are no page conflicts).

Will multi writer get more throughput compare to all transaction commit by one memory database in single thread mode with same schema and data (assuming no page conflicts)?

02:45 Post: config SQLITE_CONFIG_MALLOC for connection instance (artifact: bcaca35602 user: phpstatic)

I like to know if there is a way to set sqlite3_mem_methods for each sqlite3 connection instance.

The current methods setup a global sqlite3_mem_methods for process, in multithread app set sqlite3_mem_methods for each connection could reduce race condition.

02:13 Reply: Status of wal2 branch (artifact: 9d22802948 user: phpstatic)

You are most welcome.

A small warning:

warning: assigning to 'char *' from 'const char *' discards qualifiers
  pRet->zWalName2 = &zWalName[sqlite3Strlen30(zWalName)+1];

If I create a new database, the sqlite will try access journal first, then create journal file. After I set journal_mode = WAL2, sqlite delete + journal and create 2 WAL file.

Is there a way to let sqlite skip create journal file when I intend to use WAL ? (a flags pass to sqlite3_open_v2 to avoid call "pargama journal_mode = WAL" late)

The is VFS only SQLITE_OPEN_WAL, maybe reuse it to do the work(only for create new database).

15:11 Reply: Writer Concurrency - server-process-edition (artifact: d3cebaea37 user: phpstatic)

I think that's fair to say. That branch increases write concurrency compared to the begin-concurrent branch (which is embedded in server database Bedrockdb), but not by as much as we had hoped.

On this test: https://sqlite.org/src/artifact/0c6bc6f55191b690

With 2rw 1ro, server-mode write TPS increase 140% compare to begin-concurrent. 160% read/write TPS. I think this is huge improvement.

Is the recent begin-concurrent branch get extra 40% TPS compare to 2017-07-31 version ?

02:52 Reply: Native support for pluggable backends in SQLite (artifact: 8b9938ea1c user: phpstatic)

I try run lumosql benchmarks, but get some error like this:

./tool/get-lumo-sources /opt/osx/lumosql/sources test 3.33.0
Cannot load VCS(git): Can't locate Git.pm in @INC (you may need to install the Git module) (@INC contains: /usr/local/bin/../lib /Library/Perl/5.18/darwin-thread-multi-2level /Library/Perl/5.18 /Network/Library/Perl/5.18/darwin-thread-multi-2level /Network/Library/Perl/5.18 /Library/Perl/Updates/5.18.4/darwin-thread-multi-2level /Library/Perl/Updates/5.18.4 /System/Library/Perl/5.18/darwin-thread-multi-2level /System/Library/Perl/5.18 /System/Library/Perl/Extras/5.18/darwin-thread-multi-2level /System/Library/Perl/Extras/5.18 .) at /Library/Perl/5.18/NotFork/VCS/Git.pm line 13, <$fh> line 10.
BEGIN failed--compilation aborted at /Library/Perl/5.18/NotFork/VCS/Git.pm line 13, <$fh> line 10.
Compilation failed in require at (eval 6) line 2, <$fh> line 10.
make: *** [build/3.33.0] Error 1

I already run this:

perl -MCPAN -e shell
install Git::Repository

No information about the logical location of this error, eg what row(s) it affects. The application knows nothing about how rows map to pages. All the application knows is that SQLITE_IOERR_DATA was returned during a read operation. That's a lot better than silent corruption, but also not as helpful as it could be.

This is very true. I try get the page information for each transition, to know if they are conflict, to use with the Raft pipelining optimistic(still a work at early stage).

Brittle implementation due to requirements of the file format. The "bytes of reserved space on each page" value at offset 20 in the SQLite database header must be exactly 8. What if we want a better or different checksum?

I consider checksum as a reference implementation, I try build a VFS 64 byte reserved for encryption.

15:51 Reply: WAL journal file-size keeps on growing... (artifact: 1952fedde3 user: phpstatic)

This is cloud solve your problem: https://www.sqlite.org/cgi/src/doc/wal2/doc/wal2.md

15:33 Reply: Native support for pluggable backends in SQLite (artifact: da1dbc4abd user: phpstatic)

I check your benchmark report from this link: https://lumosql.org/benchmarking/

The number seems not good(100TPS for sqlite, 300 TPS for sqlite+LMDB). Is the test enable MMAP + WAL ?

On my test for https://sqlite.org/src/doc/754ad35c/README-server-edition.html with WAL2 + MMAP:

synchronous=OFF, 26425 TPS.

synchronous=NORMAL, 13000 TPS.

synchronous=FULL, 9082 TPS.

with memory 81920 TPS.

Each TPS include 6 INSERT.

The read TPS is 210000 for total 65536 records.

I test on Intel 4.5G + NVME SSD, WAL2 branch.

Enabling an SQLite codec requires the compile-time definition of SQLITE_HAS_CODEC, which is not present on standard, unmodified SQLite builds.

This is not the case any more, use a VFS shim can handle the encryption and corruption detection. Use VFS no need call internal functions.

SQLCipher modifies supporting functions to introduce special pragmas, built in functions, etc (e.g. "PRAGMA cipher_*"). Injecting this functionality in a plugin architecture wouldn't be possible.

I am not sure this could be used for the task: https://github.com/sqlite/sqlite/blob/master/ext/misc/cksumvfs.c#L725

13:27 Reply: Status of wal2 branch (artifact: ebe69ce85a user: phpstatic)

Hi Dan,

I like to report a bug find about wal2.

call sqlite3_database_file_object from xOpen get this:

* thread #1, queue = 'com.apple.main-thread', stop reason = EXC_BAD_ACCESS (code=1, address=0x48)
    frame #0: 0x0000000100056459 tests_db_bench.exe`sqlite3_database_file_object(zName="/private/tmp/test.db-wal2") at sqlite3c.h:56776:18
   56773	    zName--;
   56774	  }
   56775	  pPager = *(Pager**)(zName - 4 - sizeof(Pager*));
-> 56776	  return pPager->fd;
   56777	}
Target 0: (tests_db_bench.exe) stopped.