[sqlite] New word to replace "serverless"

server in a separate thread. In fact, that is how most >> embedded RDBMSes other than SQLite work, if I am not much mistaken. Wait, really? AFAICS embedded means in-process, no IPC required to operate. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun h

Re: [sqlite] FW: Questions about your "Performance Matters" talk re SQLite

r on a 64-threaded CPU vs 64 single-threaded processes. In practice, the single process with 64 threads ought to be slightly faster, due to less context switch overhead between threads, but if nothing else in the system is contending for CPU then context switching shouldn't even be an issu

Re: [sqlite] FTS5 Transaction Leads to OOB Read

YES. AFL with ASAN. ___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users

[sqlite] FTS5 Transaction Leads to OOB Read

The code: ``` CREATE VIRTUAL TABLE t1 USING fts5(content); BEGIN; INSERT INTO t1 (content) VALUES(''); SELECT * FROM t1('*'); END; ``` As you can see, it creates a virtual table w

[sqlite] FTS5 Transaction Leads to NULL Pointer

The code: ``` CREATE VIRTUAL TABLE t1 USING fts5(content); INSERT INTO t1 VALUES(''); BEGIN ; DELETE FROM t1 WHERE rowid = 1; SELECT * FROM t1 WHERE content MATCH ''; INSERT INTO t1 VALUES(''); SELECT * FROM t1 WHERE con

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

Jens Alfke wrote: On May 14, 2018, at 11:25 PM, Howard Chu wrote: Do you have a link to the currently updated version of this? Google gives me projects that haven't been updated since 2015. That's the most recent version. So far I haven't seen any compelling new feature

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

I contributed to the last SQLitening update. No one has reported any issues that need fixing or updating since that update. It seems to be working quite well/stable. > On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" wrote: >

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

Peter Da Silva wrote: On 5/13/18, 6:48 AM, "sqlite-users on behalf of Howard Chu" wrote: SQLightning Do you have a link to the currently updated version of this? Google gives me projects that haven't been updated since 2015. That's the most recent version. So fa

Re: [sqlite] Multi threaded readers on memory sqlite cannot scale

looser synchronous and journal_mode settings), and rely on the OS file cache. Or just use SQLightning, which has no scalability limits for readers. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect

Re: [sqlite] Enabling MMAP in Android

J Decker wrote: On Tue, Dec 12, 2017 at 4:35 PM, Howard Chu wrote: Martin Raiber wrote: On 12.12.2017 19:47 Simon Slavin wrote: On 12 Dec 2017, at 6:27pm, Jens Alfke wrote: On Dec 12, 2017, at 5:46 AM, Simon Slavin wrote: Before you answer that question, you should know that both

Re: [sqlite] Enabling MMAP in Android

mmap bug in the Android 7.0 fuse layer: https://www.mail-archive.com/openldap-its@openldap.org/msg10970.html There is definitely a bug in Android 7 fuse/mmap. The bug is definitely not present when bypassing fuse, but only rooted devices can bypass... -- -- Howard Chu CTO, Symas Corp.

Re: [sqlite] Enabling MMAP in Android

use on MacOS, no issues have been reported. On iOS there's this nagging problem that the virtual address space is still limited to 4GB, even on 64bit systems. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/

Re: [sqlite] Read-only access which does not block writers

block readers, but readers block writers. That's the best you're going to get without custom lock protocols like LMDB uses natively. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP h

Re: [sqlite] Read-only access which does not block writers

Florian Weimer wrote: On 11/24/2016 10:41 PM, Howard Chu wrote: As a compromise you could use SQLightning, which replaces SQLite's Btree layer with LMDB. Since LMDB *does* allow readers that don't block writers. How does it do that? Does LMDB perform lock-free optimistic

Re: [sqlite] Read-only access which does not block writers

on't block writers. But it would be more efficient to just use LMDB directly, and not incur the overhead of the SQL translation layer. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect,

Re: [sqlite] Parallel access to read only in memory database

cking the code if someone could give me pointers on what needs to be modified to get this working. Certainly if there is an extra flag or URI I need to use to get concurrent in memory read access that would be great, but I'm willing to try and modify the source code and sharing with the commu

Re: [sqlite] Parallel access to read only in memory database

Jens Alfke wrote: On Oct 9, 2016, at 10:41 AM, Howard Chu wrote: As for code freshness, I've seen no compelling new features from 3.8.x onward that would improve performance so there's been no reason to update further. Perhaps, but there’s important new functionality in newe

Re: [sqlite] Parallel access to read only in memory database

Jens Alfke wrote: On Oct 9, 2016, at 8:15 AM, Howard Chu wrote: Use SQLightning, it's designed specifically for write once read many workloads. "SQLite3 ported to use LMDB instead of its original Btree code” — sounds great, and the performance figures quoted in the readme are

Re: [sqlite] Parallel access to read only in memory database

once read many workloads. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users mailing list sqlite-

Re: [sqlite] Why so much I/O ? Can sqlite be improved ?

___ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun

Re: [sqlite] Sync journal's directory per transaction? or until database closed? in PERSIST mode

in RAM. One common mistake of new Unix admins is to delete large log files from /var/spool when they're low on disk space; if they do this without telling syslog to close the files first, the space remains in use and can't be freed until syslog is killed and restarted. -- -- Howard Chu

[sqlite] Make mmap_size dynamic?

r DB is larger than RAM - every time you touch a page to write to it, if it's not already memory-resident, the OS faults it in from disk. It's a wasted page fault if you were simply going to overwrite the entire page (which e.g. LMDB does). If you update pages in-place, instead of doing

[sqlite] Changing the default page_size in 3.12.0

Richard Hipp >> drh at sqlite.org >> ___ >> sqlite-users mailing list >> sqlite-users at mailinglists.sqlite.org >> http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list >

[sqlite] Fwd: Re: SQLite and Thunderbird

pen a channel of communication regarding these bugs. >>> >>> While I believe thunderbird uses a format of call that is depreciated in >>> the newer SQLite packages, it is not ideal to statically compile >>> thunderbird against sqlite to make it work (which I beli

[sqlite] IS a SQLite db of small size as good as reliable cache?

nds to the hardware, so setting PRAGMA > synchronous ON/OFF makes a big difference in write performance on Windows > systems. > > Howard Chu schrieb am 30.01.2016 um 23:23: >> dpb wrote: >>> Dear Community, >>> >>> Assuming that SQLite would arrange th

[sqlite] Bug: Successfully committed transaction rolled back after power failure

tom line - if your OS reboots successfully, there's no excuse for your database to not also come up successfully, fully intact. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/

[sqlite] IS a SQLite db of small size as good as reliable cache?

James K. Lowden wrote: > On Sat, 30 Jan 2016 22:23:24 + > Howard Chu wrote: > >> Note that the cache eviction runs quite frequently - once every 5 >> seconds or so, and evicts pages regardless of whether there's any >> memory pressure in the system. It's

[sqlite] IS a SQLite db of small size as good as reliable cache?

seconds or so, and evicts pages regardless of whether there's any memory pressure in the system. It's quite possibly the stupidest cache manager ever written. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/

[sqlite] Bug: Successfully committed transaction rolled back after power failure

ment in Linux, at least. It is also independent of whether or not the filesystem uses journaling. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/

[sqlite] Bug: Successfully committed transaction rolled back after power failure

Richard Hipp wrote: > On 1/25/16, Howard Chu wrote: >> >> This is actually quite an unusual requirement; on older Unix systems you >> couldn't even *open* a directory, let alone obtain write access to it or >> fsync it. > > Yeah. When the SQLITE_DISABLE_

[sqlite] Bug: Successfully committed transaction rolled back after power failure

which* fsync is needed? The issue here is that file creation/deletion/rename ops require an fsync *on the containing directory*. This is actually quite an unusual requirement; on older Unix systems you couldn't even *open* a directory, let alone obtain write access to it or fsync it. --

[sqlite] POSIX and MMU (was Re: out of the void: xDlSym)

zed in 1988 I'm just curious as to > whether or not an MMU is a requirement or just really nice to have. ST-Minix ran on MC68000 - no MMU. POSIX API only defines a programming model, it doesn't mandate how it gets implemented under the covers. An MMU *can* make some things e

[sqlite] SQLITE vs. OSX mmap == inevitable catalog corruption?

st way to use mmap, but keep in mind that this requires a unified buffer cache and systems like OpenBSD still don't have that, so this approach will cause corruptions on systems like that. > >http://sqlite.org/src/info/67c5d3c646c8198c > > It would be interesting to know if t

[sqlite] MMAP performance with databases over 2GB

ning has no such limit... https://github.com/LMDB/sqlightning there's nothing "heavy" about making maximum use of mmap. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/

[sqlite] Mozilla wiki 'avoid SQLite'

peration. It's often considered to be a mistake on the part of the LDAP designers. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/

[sqlite] Enabling mmap on OpenBSD: Anything really in the way, and how do?

Mikael wrote: > You who know SQLite all the way through, would either 1) or 2) or something > else still make a problem on OpenBSD? > > > If not, how do I use SQLite with all-file memory mapping on OpenBSD: I just Use SQLightning. https://github.com/LMDB/sqlightning -- -- Ho

[sqlite] How do non-SQLite DBMS communicate?

/> > 435 Moreland Road > Hauppauge, NY 11788 > 631-272-5947 / William.Drago at L-3COM.com<mailto:William.Drago at L-3COM.com> -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/

[sqlite] sqlite3 (or sqlite4) performance on NFS

ithout any of its convenience. Seriously - there are many other distributed filesystems out there, all designed because users keep running into the same deficiencies of NFS, over and over again. Please, can we stop reinventing this wheel now? -- -- Howard Chu CTO, Symas Corp.

Re: [sqlite] presentation about ordering and atomicity of filesystems

Scott Robison wrote: On Sat, Sep 13, 2014 at 2:24 PM, Howard Chu wrote: Scott Robison wrote: A couple of academic thoughts. 1. If one wanted to embed the journal within the database, would it be adequate to reserve a specific page as the "root" page of the journal, then al

Re: [sqlite] presentation about ordering and atomicity of filesystems

. A custom VFS might be able to provide a proof of concept... hmm. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ _

Re: [sqlite] presentation about ordering and atomicity of filesystems

o succeed without just getting garbage on the other end. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___

Re: [sqlite] presentation about ordering and atomicity of filesystems

x27;s no paragon of efficiency, but it doesn't need to be particularly performant in the first place, and it requires zero setup. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://

Re: [sqlite] presentation about ordering and atomicity of filesystems

Simon Slavin wrote: That's not what I meant. That's the file as a database. What I want is the entire volume as a database. That's exactly what I pointed you to before. The thesis is pretty enlightening too. http://www.fsl.cs.sunysb.edu/docs/kbdbfs-msthesis/ -- -- H

Re: [sqlite] presentation about ordering and atomicity of filesystems

there. You need device-level ordering support too. - which prompted my suggestion here http://www.spinics.net/lists/linux-fsdevel/msg70047.html -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect,

Re: [sqlite] presentation about ordering and atomicity of filesystems

lesystem like EXT4. Actually LMDB comes out on top with zero vulnerabilities. I spoke to the UWisc folks to find out what was the one Atomicity vulnerability they reported in LMDB and we confirmed that it was not in fact a valid vulnerability. -- -- Howard Chu CTO, Symas Corp. http:/

Re: [sqlite] presentation about ordering and atomicity of filesystems

e/2007/05/kernel-berkeley-db-file-system-kbdbfs.html https://github.com/dmeister/kbdb/tree/master/kbdbfs-1.0 I've got a project underway to retrace their steps, using LMDB instead of BerkeleyDB. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun

Re: [sqlite] SQlite on flash filesystem

e sector in parallel. (In fact it is an efficiency loss.) Multi-channel SSDs parallelize across multiple Flash chips, sending whole sectors to each separate channel/chip. A good question. By which I mean it's one people should worry about, not one which we're not sure about. --

Re: [sqlite] sqlite & multi process usage

related BSDs still don't implement these though, so you have to use semaphores on them instead. (Either SysV style or POSIX named semaphores.) -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, Ope

Re: [sqlite] SQLite scalability

t I am working on that may reach limits like these. The only definive SQLite limits are documentet in the relevant manual page. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Ar

Re: [sqlite] Row Level Locking as in InnoDB

the most happiest person here :) You seem to enjoy asking a lot of others, without regard to cost. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http:/

Re: [sqlite] Row Level Locking as in InnoDB

slower than SQLite. Whatever other differences there may be, there is no performance benefit to using BDB as a backend for SQLite. In most cases there is a performance loss. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc

Re: [sqlite] Row Level Locking as in InnoDB

lmdb. http://sphia.org/benchmarks.html Quite off-topic for this list, but those results are garbage. https://github.com/pmwkaa/sophia_benchmark/issues/2#issuecomment-27740082 -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hy

Re: [sqlite] Row Level Locking as in InnoDB

oblivious data structure, for example LSM tree or create new, like in sophia (sphia.org) key value DB? I just looked, sophia is nothing special. See these microbench results. http://pastebin.com/cFK1JsCN LMDB's codebase is still smaller and faster. Nothing else touches LMDB's read speed

Re: [sqlite] Row Level Locking as in InnoDB

saying BDB is better and faster than SQLite ? Oracle claims that. From what I can see, Oracle is wrong. On Sun, Nov 3, 2013 at 8:28 PM, Howard Chu wrote: Aris Setyawan wrote: SQLightning replaces the SQLite backend with Symas' LMDB, which also uses MVCC and thus supports high concurre

Re: [sqlite] Row Level Locking as in InnoDB

was important to us when I created LMDB. That's like asking why a hammer isn't a screwdriver. On 11/3/13, Howard Chu wrote: Aris Setyawan wrote: SQLite do not use row level locking, but db level locking, so it was the right behavior the second thread was blocked. For innodb like

Re: [sqlite] Row Level Locking as in InnoDB

selects and inserts in my application and hence a row level locking is suitable vs table or database level locking. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http:/

Re: [sqlite] logical to use sqlite to replace Berkeley DB ?

ter than BDB. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users mailing list sqlite-users@sqlite.org

Re: [sqlite] Minimizing Internal Function Calls in SQLite

really so complex? Are your queries? Do you need to do JOINs across multiple tables? -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.o

Re: [sqlite] Large Database Windows vs Linux

best way to handle the Hardware Destroyer (Power Management) in Windows. Disable it completely. The best thing to do with Windows is format the drive and install Unix or FreeBSD or Linux. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

Dan Kennedy wrote: On 04/04/2013 08:44 PM, Howard Chu wrote: Richard Hipp wrote: The memory-mapped I/O is only enabled for windows, linux, mac OS-X, and solaris. We have found that it does not work on OpenBSD, for reasons we have not yet been able to uncove; but as a precaution, memory mapped

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

er or not it even works for you, is very important to us. Thanks for giving the new code a try. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/pr

Re: [sqlite] SQLite 3.7.17 preview - 2x faster?

y the bug becomes obvious and easy to fix. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users

Re: [sqlite] experimental (better?) usage based sqlite cache

Simon Slavin wrote: On 25 Feb 2013, at 11:33am, Howard Chu wrote: Gabriel Corneanu wrote: Following a few other discussions, I had the feeling that sqlite should benefit from a cache which discards cached pages in a least frequently used order. Just offhand, classical LRU is quite poor in

Re: [sqlite] experimental (better?) usage based sqlite cache

, because no reorganizing of LRU lists is needed during page references. And of course, having gone thru all of these exercises of fancy application-level cache algorithms already, it's still obvious that the best approach is to leave it to the kernel. -- -- Howard Chu CTO, Symas

Re: [sqlite] Concurrent read performance

nt of programmer time invested in proper coding saves eons of user time. Putting programmer effort into making correct code fast is always The Right Thing to Do. Software that delivers the correct answer, late, is still wrong. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Dir

Re: [sqlite] Concurrent read performance

software, every moment of programmer time invested in proper coding saves eons of user time. Putting programmer effort into making correct code fast is always The Right Thing to Do. Software that delivers the correct answer, late, is still wrong. -- -- Howard Chu CTO, Symas Corp. htt

Re: [sqlite] Please help. Read only process being blocked by writer process.

buggy process could hang while having a prepared statement open and cause the wal file to grow forever, then causing errors in other processes. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, O

Re: [sqlite] Please help. Read only process being blocked by writer process.

eset() when you are done with a statement so that its implied read transaction will close. Thank you for your quick answer. On Wed, Nov 28, 2012 at 9:52 AM, Richard Hipp wrote: PRAGMA journal_mode=WAL -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director,

Re: [sqlite] light weight write barriers

Ric Wheeler wrote: On 11/16/2012 10:06 AM, Howard Chu wrote: David Lang wrote: barriers keep getting mentioned because they are a easy concept to understand. "do this set of stuff before doing any of this other set of stuff, but I don't care when any of this gets done" and the

Re: [sqlite] light weight write barriers

it. And in most cases it would be a zero-cost fix for them. Once you have drives that are actually trustworthy, actually reliable (which doesn't mean they never fail, it only means they tell the truth about successes or failures), most of these other issues disappear. Most of the need

Re: [sqlite] Shared in-memory SQLite database in shared memory

emory databases. Nothing else is anywhere close to as efficient as MDB for reads. Read more here http://highlandsun.com/hyc/mdb/ The port of SQLite using MDB as its backend is available on gitorious https://gitorious.org/mdb/ -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Dir

Re: [sqlite] light weight write barriers

ut ever slowing down for a fsync. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users mailing list sqli

[sqlite] light weight write barriers

built on MDB is progressing as well. -- -- Howard Chu CTO, Symas Corp. http://www.symas.com Director, Highland Sun http://highlandsun.com/hyc/ Chief Architect, OpenLDAP http://www.openldap.org/project/ ___ sqlite-users mailing li