On Mon, Aug 14, 2017 at 2:34 AM, Rowan Worth <row...@dug.com> wrote: > On 14 August 2017 at 17:11, J Decker <d3c...@gmail.com> wrote: > > > I monitored the process with ProcMon (I'm on windows 7 BTW) > > > > 12:55:34.7416316 AM node.exe 9012 WriteFile > > \Device\HarddiskVolume17\javascript\gun.db\gun.db-wal SUCCESS Offset: > > 188,416, Length: 20,480, I/O Flags: Non-cached, Paging I/O, Synchronous > > Paging I/O, Priority: Normal > > > > this is the last in a block of writes that goes to -wal (which I thought > > was memory mapped anyway and shouldn't even see writes to the file). > It's > > got the extra flags of non-caches, paging, synchrous etc... and takes the > > significant portion of the time. > > > > AFAIK sqlite only ever uses mmap for read paths, never for writing. > Anyway, durability is not free. The synchronous write you are seeing is > part of sqlite's protocol to ensure robustness even across a machine crash > or power loss event. You can play with pragmas etc. but turning off > synchronous is a surefire way to end up with a corrupted DB if one of those > occurs. > > The balanced strategy is to minimise the number of synchronous writes, by > minimising the number of transactions as you've been advised. Where you > draw the line depends on how much data you're willing to lose. > > > > This is curious... locking at 1G of filespace? the db after 375k records > > is only 24M. > > > > That's allowed, and completely normal for sqlite. > > > > There doesn't appear to be a way to disable locking? In this usage, the > > database is used by Javascript from Node.exe which is single threaded. > > There are no other processes that will access the database at any time > > while the process is running, it's(sqlite) just a persistance layer that > > provides indexing of values for partial updates of simple fields. > > > "PRAGMA locking_mode = EXCLUSIVE" will eliminate most locks (there'll just > be a few at the start and end). >
https://sqlite.org/pragma.html#pragma_locking_mode Well that does make it go nice and fast, but it's not exactly the same as never locking. A second database connection to the same file is not allowed. Even though the process will always be single threaded access even if there are multiple connections, it would be more helpful to have a locking_mode=NONE. Performance wise I go from 1.4-1.5k per second transactions to 0.9-1.05k transactions per second. approximately ( .714ms to 1.11ms per transaction which is a difference of .397ms per transaction... which is -50% of the speed) And the lock is a logical lock within sqlite. Against my VFS, which supports no locking, it still locks a second connection. I did do some more reading on https://sqlite.org/compile.html which has SQLITE_ENABLE_LOCKING_STYLE; which after looking at the source to see how it's used, I guess I should be able to set SQLITE_ENABLE_LOCKING_STYLE=0 at compile time. But that doesn't help. EXCLUSIVE still locks other connections; and non exclusive is 33% slower (takes 50% longer) And it still generates locks.... (very strange... went looking to see if it was actually built with the flag... it's set in the project, enabled preprocess to a file, and "ENABLE_LOCKING_STYLE=" CTIMEOPT_VAL(SQLITE_ENABLE_LOCKING_STYLE), is not in the output so it must be off) these are on though... #line 17519 "..\\src\\sqlite3.c" "DEFAULT_SYNCHRONOUS=" "2", #line 17522 "..\\src\\sqlite3.c" "DEFAULT_WAL_SYNCHRONOUS=" "2", the windows vfs doesn't have options to disable locking, so it always calls WinLock. here. static int pagerLockDb(Pager *pPager, int eLock){ rc = pPager->noLock ? SQLITE_OK : sqlite3OsLock(pPager->fd, eLock); (And then I looked at that in more depth) Ahh... there's a noLock option there, maybe.... Okay: opening with file:gun.db?nolock=1&mode=rwc ( rwc might not be required, I didn't have that option on the open initially it was just open_readwrite but no open_create, not having open_create prevented rwc from working though, and now that I have that as part of the open I don't have to specify it...) ) but there's still locks not on the journal file... There's opens of -wal (which is not and was not enabled; again that status is recorded in the database according to docs) QueryStandardInformationFile \Device\HarddiskVolume17\javascript\gun-db\gun.db QueryOpen C:\general\work\javascript\gun-db\gun.db-wal FAST IO DISALLOWED CreateFile C:\general\work\javascript\gun-db\gun.db-wal REPARSE QueryOpen \Device\HarddiskVolume17\javascript\gun-db\GUN.DB-WAL NAME NOT FOUND there's the occasional lock on gun.db... followed immediately by an unlock (looks pretty no-op to me ) LockFile \Device\HarddiskVolume17\javascript\gun-db\gun.db SUCCESS UnlockFileSingle \Device\HarddiskVolume17\javascript\gun-db\gun.db SUCCESS With exclusive enbled, neither of the prior operations happen..... -------------------- So, to summarize. SQLITE_ENABLE_LOCKING_STYLE=0 does nothing. (no improvement) file:gun.db?nolock=1 does nothing(no improvement) PRAGMA locking_mode = EXCLUSIVE gives me good speed, at the cost of only 1 connection ever. (not usable) *sigh* so much work making URI work; testing that locking styule actually compiled in... resulting in a whole lot of nothing. > -Rowan > _______________________________________________ > sqlite-users mailing list > sqlite-users@mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users > _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
