On Mon, Oct 21, 2019 at 5:00 PM Richard Hipp <d...@sqlite.org> wrote: > > On 10/21/19, Jonathan Brandmeyer <jbrandme...@planetiq.com> wrote: > > On Mon, Oct 21, 2019 at 10:31 AM Richard Hipp <d...@sqlite.org> wrote: > >> > >> On 10/21/19, Jonathan Brandmeyer <jbrandme...@planetiq.com> wrote: > >> > Or, how many times is each page written by SQLite for an insert-heavy > >> > test? The answer appears to be "4", but I can only account for two of > >> > those four. > >> > >> Are you measuring writes at the OS-interface layer, or writes at the > >> hardware layer? SQLite should issue no more than 2x writes in WAL > >> mode, and less if the same page is modified more than once. But who > >> knows how the OS might be multiplying this to accomplish its own > >> filesystem consistency. > >> > >> Consider compiling the > >> https://www.sqlite.org/src/file/ext/misc/vfslog.c extension into > >> SQLite on a workstation and running your code there, to get more > >> details about everything that SQLite is doing with storage. > > > > Thanks for the pointer. I didn't do this exactly, but instead > > instrumented SQLite's unixWrite() and my lowest-level NAND driver's > > block write function on the target hardware to capture summary > > counters of both events. The filesystem's own consistency overhead > > adds up to about 2% additional writes - not nearly enough to account > > for the missing 2x. The throughput implied by the low-level counters > > is consistent with the benchmark results. > > > > What happens if you set "PRAGMA synchronous=OFF".
No significant change. The target filesystem only caches non-aligned writes, so there usually isn't anything for it to do on fsync anyway. > (I'm not suggesting > that you do this in production - it is just an experiment to try to > help figure out what is going on.) Understood. If it helps, we are doing a couple of other things that are dissimilar to a typical workstation configuration. We are using the builtin "unix-none" VFS, the -DSQLITE_DEFAULT_WAL_SYNCHRONOUS=1 and -DSQLITE_DEFAULT_LOCKING_MODE=1 compile-time options, along with a design rules that there are zero or one database connection per file, and only one thread accesses that connection at a time. The target lacks mmap(), posix file locks, and multiple address spaces, so this seemed like the right settings to use. Thanks, -- Jonathan Brandmeyer PlanetiQ _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
