[sqlite] how to detect when a table is modified ?
I just have a question about the function : void *sqlite3_update_hook( sqlite3*, void(*)(void *,int ,char const *,char const *,sqlite3_int64), void* ); what is the last void* ? is it a pointer to a user data structure ? regards, Nicolas J.
[sqlite] how to detect when a table is modified ?
Hi Roger Binns and R. Smith, > Is the database being modified from within the same process, or from > outside? for that program I consider within the same process. > > The update hook will tell you (most of the time) about changes within > the same connection (ie if you are making the changes): > > https://sqlite.org/c3ref/update_hook.html after reading, I think this should works. I will give a try. > > The data_version pragma can tell you if something has changed, but > you'll need to poll: > > https://www.sqlite.org/pragma.html#pragma_data_version > > You can use change notification of the file system to know when the > database file is changing and then do the pragma polling to find out > when the change is complete. For example on Windows the API starts > with FindFirstChangeNotification and on Linux you'll find i/dnotify. > Do make sure to monitor the main database file, as well as any other > related filenames such as wal, journal and shm. I try to do something like this with GIO, but I don't like how the code looks like. I want (if I can) something more simpler. > Best way to do so is using the Authorizer - read more here: > > https://sqlite.org/c3ref/set_authorizer.html sound interesting too, but I think using update_hook is simpler. Anyway I keep this for some further uses. thx to both of you. Regards, Nicolas J.
[sqlite] SQLite crash
I observed the sqlite command shell version 3.8.11.1 to crash (exit to the OSwithout an error message) while running in a Cygwin shell under Windows 7 when I tried to create anindex.? The source was compiled by gcc 4.9.2. ?The same type of crashhappened when I tried the operation from a Lua script linked to the same objectlibrary. ? Here are the DDL statements entered previous to the crash: ? CREATE TABLEd200_on_passport(fn,path,size,serial,pid); CREATE INDEX d200_on_passport_serial ond200_on_passport(serial); CREATE VIEW d200 as select * fromd200_on_passport; ? And here is the statement thatcaused the crash: create index d200_on_passport_fn ond200_on_passport(fn); ? The crash didn?t happen when Itried the CREATE INDEX statement on a test database with the same ddlstatements but containing no data. The compressed version of the database that illustrates the problem is a little smaller than 2 MB. ?It contains some semi-personal information (it's part of an attempt to organize my photo library; pathnames hint at where I've been on vacation and so on, but the database contains no images) and I'd prefer not to post it to a list, but I can send it for use by those tracking down the bug. Robert Weiss
[sqlite] how to detect when a table is modified ?
Hi, I'm writing a program using sqlite3 and gtk to screen a database, there is a way to get a signal from sqlite3 when something is modified inside a database (values, add/delete row...), in the way to update what the program has to screen ? otherwise, any idea how I can do something similar ? regards, Nicolas J.
[sqlite] how to detect when a table is modified ?
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/07/2015 04:23 PM, Nicolas J?ger wrote: > I'm writing a program using sqlite3 and gtk to screen a database, > there is a way to get a signal from sqlite3 when something is > modified inside a database (values, add/delete row...), in the way > to update what the program has to screen ? Is the database being modified from within the same process, or from outside? The update hook will tell you (most of the time) about changes within the same connection (ie if you are making the changes): https://sqlite.org/c3ref/update_hook.html The data_version pragma can tell you if something has changed, but you'll need to poll: https://www.sqlite.org/pragma.html#pragma_data_version You can use change notification of the file system to know when the database file is changing and then do the pragma polling to find out when the change is complete. For example on Windows the API starts with FindFirstChangeNotification and on Linux you'll find i/dnotify. Do make sure to monitor the main database file, as well as any other related filenames such as wal, journal and shm. Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlXFTvcACgkQmOOfHg372QSjZwCfZkGNxETrd5MSdx41Wks9/wGh 670AnA7I7jQxlKMe2rMkVTraR/m7PYag =Uu+0 -END PGP SIGNATURE-
[sqlite] MMAP performance with databases over 2GB
On 08/07/2015 12:35 AM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > On 08/06/2015 09:27 AM, Dan Kennedy wrote: >>> Is it using more CPU cycles in mmap mode or just taking longer? >>> If the former, does [time] attribute them to "user" or "system"? > It is taking longer. I have 3 XML dumps which I turn into JSON > (incrementally) and then denormalise and insert into SQLite across > several tables. While all this work is going on, the code prints out > statistics about how long it is running and about how many records per > second are being processed. The final database size after commit and > vacuum is ~8GB. There are a lot of foreign keys too, although all > simple (referring to an INTEGER PRIMARY KEY column). > > I gave mmap a try - ie the *only* change was to add an extra pragma > before the beginning of the transaction: > > "pragma mmap_size="+str(2*1024*1024*1024) > > In hard numbers, without that line I was doing ~1,118 records per > second and with it it does ~300. A normal run takes about 1h20m but > the mmap one was still running 3 hours later when I aborted it. > > (BTW this is all on a tmpfs filesystem on 64 bit Linux with swap > spread across two ssds, and 32GB of ram. ie the actual storage > hardware isn't a factor. Also single threaded because XML.) When the b-tree layer requests a page reference in mmap mode, SQLite first needs to figure out whether it should use regular in-memory page (data cached in heap memory) or a mmap page (data is actually a pointer into mmap'd address space). If a write-transaction is open, it cannot use a mmap page if: 1) there is an entry for the requested page in the wal file, or 2) there is an entry (possibly a dirty one) for the requested page in the cache. If the wal file is really large, as in this case, then test (1) can be quite slow. One interesting thing is that the wal-file lookup is done before the cache lookup. Which doesn't seem quite right. Source code archeology and testing have failed to reveal why it is that way. The branch here swaps the two tests around: http://www.sqlite.org/src/info/3a82c8e6cb7227fe Does that improve performance any in your case? Thanks, Dan. > >>> How large are you letting the wal file grow between checkpoints? > Pretty much the entire database size. For the tests I was starting > with a deleted database directory (ie no pre-existing files), and then > doing these pragmas: > >"pragma page_size=4096", > # "pragma mmap_size="+str(2*1024*1024*1024), >"pragma journal_mode=wal", >"pragma wal_autocheckpoint=1", >"pragma foreign_keys=on", > > Then I start a transaction, and do the importing within that > transaction. The database file is 4kb during that process, the wal > file gets to be about 10GB. > > If I use gdb to periodically break into the running process in the > mmap case, then it was always in sqlite3WalFindFrame. > > I don't need any help fixing my importing process (eg don't need a > journal on an empty database anyway). But it is frustrating that mmap > only goes up to a few kb shy of 2GB even for 64 bit, and I have one > example (ie anecdote not data) showing that mmap hurts for inserts on >> 2GB databases. Perhaps it is worth others testing to see if this >> is > a systemic problem, or just bad luck for me :-) > > It may also be relevant that tables add columns over time. I > dynamically add them after encountering previously unseen fields in > the JSON. However I'd expect the schema to be final a few thousand > records in. Most tables have 3 to 6 million records. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1 > > iEYEARECAAYFAlXDmukACgkQmOOfHg372QSVtgCbBihGgIuZqS3Yy2JARXZ1+Q59 > GmwAoMG53XxuLNhcMIw1PV46fD/Z/5tT > =luXx > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] MMAP performance with databases over 2GB
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 08/07/2015 02:55 AM, Dan Kennedy wrote: >> http://www.sqlite.org/src/info/3a82c8e6cb7227fe > >> Does that improve performance any in your case? I tested 3a82c8e6 (fix above) against e596a6b6 (previous changeset), in both cases starting with an empty database, on tmpfs and with WAL. Three kinds of data are imported into the database, but it also means the first kind fits mostly within 2GB. I stopped the third kind import at 48 minutes in both cases. 3a82c8e6 (with mmap change) - --- 2m52s 22,821 per second 2m56s 4,823 per second 47m56s 1,157 per second 3.3 million records imported of this kind e596a6b6 (without mmap change) - -- 2m51s 22,855 per second 3m43s 3,800 per second 47m54s462 per second 1.3 million records imported of this kind Your change definitely helped once the database got above 2GB (I don't track exactly where that change happens in the import process - looks like in the second kind.) It would also be really nice if there wasn't a 2GB mmap limit on 64 bit machines. The database would fit in my RAM around 4 times, and in the address space more times than there are grains of sand! Yea I know this isn't very Lite ... Roger -BEGIN PGP SIGNATURE- Version: GnuPG v1 iEYEARECAAYFAlXFLlkACgkQmOOfHg372QQStwCfXXQhxJsyfJWUq/hOUm2KYdbs aPoAoLOHYbBn7CItwbmASG5igPeeeXpl =f1gz -END PGP SIGNATURE-
[sqlite] Determine query type
Hi Stephan Ben and all, by the way: checking the column count only applies to SQLite. Other databases (Postgres, Oracle) support the insert into returning or update ... returning syntax, resulting in statements that change the database and have columns at the same time. This is a feature I missed in SQLite occasionally, but I guess it isn't SQL standard. Martin Am 06.08.2015 um 18:57 schrieb Ben Newberg: > Excellent. This is exactly what I was looking for. > > Thanks. > > On Thu, Aug 6, 2015 at 11:50 AM, Stephan Beal > wrote: > >> On Thu, Aug 6, 2015 at 6:46 PM, Ben Newberg wrote: >> >>> Is there a good way to determine if a sql query is either attempting to >>> modify a database, or simply querying it? >>> >> Check the column count of the prepared statement: it will be >0 for a >> select or select-like pragma. Anything else is an update, delete, drop, >> create table/view, non-select-like pragma, or similar. >> >> -- >> - stephan beal >> http://wanderinghorse.net/home/stephan/ >> http://gplus.to/sgbeal >> "Freedom is sloppy. But since tyranny's the only guaranteed byproduct of >> those who insist on a perfect world, freedom will have to do." -- Bigby >> Wolf >> ___ >> 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-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] Read data from database slow the first time but then very fast thereafter. How can I improve the speed for the first time read?
On Thu, 6 Aug 2015 10:17:43 -0500, John McKown wrote: > Too bad that SQLite does not implement updatable views. Sometimes a similar construct as updatable views can be obtained with an INSTEAD OF trigger. -- Regards, Kees Nuyt
[sqlite] MMAP performance with databases over 2GB
On 08/06/2015 09:53 PM, Roger Binns wrote: > -BEGIN PGP SIGNED MESSAGE- > Hash: SHA1 > > In my testing I am finding that using SQLite's mmap support with > larger databases (final db size is ~10GB) to create the database (WAL > mode) is considerably (about three times) slower than no mmap. > > The default max mmap limit is 2GB (sadly). Has anyone else tested > mmap with >2GB databases and have results? I don't know if this is a > peculiarity of my data set, or because of how SQLite is implemented. Is it using more CPU cycles in mmap mode or just taking longer? If the former, does [time] attribute them to "user" or "system"? How large are you letting the wal file grow between checkpoints? Dan. > > Roger > -BEGIN PGP SIGNATURE- > Version: GnuPG v1 > > iEYEARECAAYFAlXDdQMACgkQmOOfHg372QRt3wCeKQpP9g2OhWS2yJg+iU7Gxvxo > TPUAn3ikDarecOaKLPIsnS3Xv+IltreU > =yUxV > -END PGP SIGNATURE- > ___ > sqlite-users mailing list > sqlite-users at mailinglists.sqlite.org > http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
