On Mon, Aug 14, 2017 at 12:40 AM, Simon Slavin <slav...@bigfraud.org> wrote:
> > > On 14 Aug 2017, at 8:33am, J Decker <d3c...@gmail.com> wrote: > > > I'd have to wrap each one in a transaction. > > Each is really an atomic operation from a higher level program... and > each > > must complete before returning to the parent. > > There may be no point in doing that. If you do not declare a transaction > yourself using "BEGIN" then SQLite automatically wraps each command in its > own transaction. The point Clemens was making is that it is not the INSERT > which is taking the time, it is finishing the transaction. > > Are other processes trying to access the database while the parent thinks > up the next INSERT ? If you have just one process accessing the database > then it is safe to leave a transaction open for any amount of time while > you supply more commands. You can then COMMIT an hour or a day later. > > On the other hand if, in real life, you have one process supplying new > INSERT commands and another doing SELECT at the same time, you should not > do this. > > The process is linear, single threaded, and the log of commands is in sequence. (auto commit/auto wal_checkpoint would be a separate thread more later) (to catch several messages...) 1) the create table and indexes are created before any select/insert... as is shown from the attached clip of the script. (Oh I see, there is a select on sqlite_master to see if the table already exists, and what the definition of that is in case I need to update the definition) 2) since the beginning is auto transacted, not much point in the create table/indexes being in a transaction... besides in reality there is at least half second (and sometimes 9 or 10 seconds) or so between init/create and the first command. I do have the ability to auto-transact; so if the connection is idle; on the first command, generate a begin, and after X time of idle (each new command bumps the tick to its time), generate a commit. This generally works but 1 in 10000 the process ends up doing a new command at the same time I'm trying to do a commit; and they are one separate threads.... and yes criticalsections/semaphores separte the threads, but still there's a collision I'd love to avoid. 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. I see now I actually have to do something like PRAGMA mmap_size=16777216 (doesn't help) and the other developer had issued this... PRAGMA synchronous = 0 , seeing this now in conjunction with the ProcMon output I see why. (this helps a LOT) This is curious... locking at 1G of filespace? the db after 375k records is only 24M. 1:21:26.5793095 AM node.exe 1204 LockFile \Device\HarddiskVolume17\javascript\gun.db\gun.db SUCCESS Exclusive: True, Offset: 1,073,741,824, Length: 1, Fail Immediately: True but now I also see why it's so much faster in my vfs even just using DELETE journal method (PERSIST is better though) Guess I should not do a journal_mode=WAL by default anymore... curious though, now that I never say it should be WAL (so the databsae isn't marked as WAL) it tries to delete -WAL 1:45:09.9147387 AM node.exe 13796 QueryOpen C:\general\work\javascript\gun.db\gun.db-wal FAST IO DISALLOWED 1:45:09.9147710 AM node.exe 13796 CreateFile C:\general\work\javascript\gun.db\gun.db-wal REPARSE Desired Access: Read Attributes, Disposition: Open, Options: Open Reparse Point, Attributes: n/a, ShareMode: Read, Write, Delete, AllocationSize: n/a, OpenResult: <unknown> 1:45:09.9148148 AM node.exe 13796 QueryOpen \Device\HarddiskVolume17\javascript\gun.db\GUN.DB-WAL NAME NOT FOUND --------------- 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. There's a simpler driver option which is just a dump of JSON into a file; but any change requires dumping the whole graph. https://www.sqlite.org/lockingv3.html opt.client.do( "PRAGMA mmap_size=16777216" ); opt.client.do( "PRAGMA journal_mode=OFF" ); opt.client.do("PRAGMA synchronous = 0"); On the filesystem that gets me 0.4k inserts/sec (and this is file accesses, not memory mapped, still not sure how to actually get memmory mapping to work) I guess WAL and non synchronous (and no transaction begin) gets me 1.0k inserts/sec. On my VFS the best case is about 1.5k inserts/sec. (kinda the limit of the application err no, I guess that's 2.7k ) (another note, the database never in WAL mode, with journal_mode=off, continually attempts to delete -journal and -wal... I know... cleanup; but the documentation clearly states that a WAL database will continue to be WAL without specifying a mode, and implies that setting any other mode will not stop it from being WAL... so if it's NOT WAL, there should be no reason to delete -WAL) (I didn't want to have to push the extra vfs option) > Simon. > _______________________________________________ > 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
