[sqlite] CREATE IF NOT EXISTS and locks
When a statement of the form: CREATE TABLE xxx IF NOT EXISTS what sort of locking overhead is incurred in the case where the table already exists? Regards, Iker -- Iker Arizmendi e: [EMAIL PROTECTED] w: ikerariz.blogspot.com
[sqlite] sqlite3 connect spawns additional thread
Using pysqlite2 I noticed (using ps) that whenever I connected to a database for the first time an extra thread is spawned. At first I thought this might be the doing of pysqlite but a quick test with the sqlite3 command line tool confirmed that this occurs within sqlite3 itself: $ sqlite3 gen-us_en.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> [1]+ Stopped sqlite3 gen-us_en.db $ ps PID TTY TIME CMD 5484 pts/16 00:00:00 bash 5753 pts/16 00:00:00 sqlite3 5754 pts/16 00:00:00 sqlite3 5757 pts/16 00:00:00 ps $ cat /proc/version Linux version 2.6.11-gentoo-r4 (gcc version 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1)) #6 SMP Mon Apr 4 10:20:09 EST 2005 A look at the sqlite3 source shows that pthread_create is only called twice and is immediately followed by a matching pthread_join - where does the extra thread come from? Regards, Iker -- Iker Arizmendi e: [EMAIL PROTECTED] w: ikerariz.blogspot.com
Re: [sqlite] sqlite3 connect spawns additional thread
My version of Linux is still using LinuxThreads (as opposed to NPTL) - I guess that extra thread is the "manager" thread. Iker On 7/12/06, Iker Arizmendi <[EMAIL PROTECTED]> wrote: Using pysqlite2 I noticed (using ps) that whenever I connected to a database for the first time an extra thread is spawned. At first I thought this might be the doing of pysqlite but a quick test with the sqlite3 command line tool confirmed that this occurs within sqlite3 itself: $ sqlite3 gen-us_en.db SQLite version 3.3.6 Enter ".help" for instructions sqlite> [1]+ Stopped sqlite3 gen-us_en.db $ ps PID TTY TIME CMD 5484 pts/16 00:00:00 bash 5753 pts/16 00:00:00 sqlite3 5754 pts/16 00:00:00 sqlite3 5757 pts/16 00:00:00 ps $ cat /proc/version Linux version 2.6.11-gentoo-r4 (gcc version 3.3.5 (Gentoo Linux 3.3.5-r1, ssp-3.3.2-3, pie-8.7.7.1)) #6 SMP Mon Apr 4 10:20:09 EST 2005 A look at the sqlite3 source shows that pthread_create is only called twice and is immediately followed by a matching pthread_join - where does the extra thread come from? Regards, Iker -- Iker Arizmendi e: [EMAIL PROTECTED] w: ikerariz.blogspot.com -- Iker Arizmendi e: [EMAIL PROTECTED] e: [EMAIL PROTECTED]
[sqlite] FTS3: custom tokenizer filter over built-in tokenizer (resend)
Hello all, I'd like to create an FTS3 tokenizer that filters stop words by internally creating the simple or porter tokenizers and filtering its output as needed (ala Lucene's StandardAnalyzer). Eg, CREATE VIRTUAL TABLE test USING fts3(txt, tokenize=stopword simple w1 ... wN) However, the xCreate function doesn't take a sqlite3* argument so it appears a tokenizer can't get at other tokenizers via fts3_tokenizer as described in the FTS3 docs. I'm considering tweaking xCreate to take a sqlite3* but before I do - is this something the official implementation would be interested in adopting? Regards, Iker -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3: custom tokenizer filter over built-in tokenizer
Hello all, I'd like to create an FTS3 tokenizer that filters stop words by internally creating the simple or porter tokenizers and filtering its output as needed (ala Lucene's StandardAnalyzer). Eg, CREATE VIRTUAL TABLE test USING fts3(txt, tokenize=stopword simple w1 ... wN) However, the xCreate function doesn't take a sqlite3* argument so it appears a tokenizer can't get at other tokenizers via fts3_tokenizer as described in the FTS3 docs. I'm considering tweaking xCreate to take a sqlite3* but before I do - is this something the official implementation would be interested in adopting? Regards, Iker -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab e: i...@research.att.com w: http://research.att.com p: 973-360-8516 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS3 bug?
The function that opens a cursor for the simple tokenizer, simpleOpen, does not set the "pTokenizer" member of the returned cursor. Ie, it appears the following line is missing: c->base.pTokenizer = pTokenizer; which causes problems in simpleNext . Possible bug? Regards, Iker -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab e: i...@research.att.com w: http://research.att.com p: 973-360-8516 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS3 bug?
Dan Kennedy wrote: > On 12/08/2010 04:18 AM, Iker Arizmendi wrote: >> The function that opens a cursor for the simple tokenizer, >> simpleOpen, does not set the "pTokenizer" member of the >> returned cursor. Ie, it appears the following line is >> missing: >> >> c->base.pTokenizer = pTokenizer; >> >> which causes problems in simpleNext . Possible bug? > > How do we reproduce the problem? The problem doesn't show up with the stock tokenizers since the functions that open cursors in sqlite3.c set the pTokenizer member on return from xOpen. My custom tokenizer hit upon the issue because it wraps calls to the simple xOpen and treated the returned cursors as opaque objects. Iker ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] FTS slowdown with matchinfo
Hello all,
I'm running into what seems like an abnormally large
performance drop on on some FTS queries that use
matchinfo when compared to those that don't.
I created an FTS table using the following:
CREATE VIRTUAL TABLE test
USING FTS4(rowid, content);
and then filled it with ~2 million docs of ~10 tokens
each which resulted in a file of around 275 MB. After
running "optimize" on the table I issued a query with
3 terms like so:
SELECT length(content) FROM test
WHERE MATCH "w1 OR w2 OR w3"
which returned ~164,000 rows in 1.1 seconds. However,
if I throw in a call to matchinfo:
SELECT length(matchinfo(test, 'x')) FROM test
WHERE MATCH "w1 OR w2 OR w3"
the query takes 7.5 minutes. It seems FTS is getting
stuck calculating the 2nd and 3rd part of the "x"
matchinfo data ("hits all rows" and "docs with hits")
but it's not clear why this should take so long.
Any ideas on what might be causing the slowdown?
Thanks,
Iker
--
Iker Arizmendi
AT Labs - Research
Speech and Image Processing Lab
e: i...@research.att.com
w: http://research.att.com
p: 973-360-8516
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS slowdown with matchinfo
Dan Kennedy wrote: > > Can you make the database available for download? And > supply the exact query you are using too? I'd like to > know why this is. Thanks. > > Dan. > You can find a tarball of the DB file here: http://www.research.att.com/people/Arizmendi_Iker/geo.db.tgz This query runs in around 1.2 seconds: SELECT length(content) FROM locateme WHERE locateme MATCH 'newark OR new OR brunswick'; And this one in around 8.5 minutes: SELECT length(matchinfo(locateme, 'x')) FROM locateme WHERE locateme MATCH 'newark OR new OR brunswick'; Thanks! Iker ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] FTS slowdown with matchinfo
Dan Kennedy wrote: > On 02/17/2011 05:41 AM, Iker Arizmendi wrote: >> Dan Kennedy wrote: >>> Can you make the database available for download? And >>> supply the exact query you are using too? I'd like to >>> know why this is. Thanks. >>> >>> Dan. >>> >> You can find a tarball of the DB file here: >> >> http://www.research.att.com/people/Arizmendi_Iker/geo.db.tgz >> >> This query runs in around 1.2 seconds: >> >> SELECT length(content) >> FROM locateme >> WHERE locateme MATCH 'newark OR new OR brunswick'; >> >> And this one in around 8.5 minutes: >> >> SELECT length(matchinfo(locateme, 'x')) >> FROM locateme >> WHERE locateme MATCH 'newark OR new OR brunswick'; >> > > The database uses a custom tokenizer - "stopwords" - so I can't > run the queries directly. If I dump the data into a regular fts3 > table using the default tokenizer and then run your queries with > 3.7.5 they both run in pretty much the same amount of time. Both > much quicker than 1 second on a Linux PC. > > There was a bug causing excessive calls to realloc() fixed a > little while ago, although from memory I don't think it would > have hit this case. The symptoms are similar though, so I could > easily be wrong on that. > > Suggest upgrading to 3.7.5 to see if that clears the problem. > > If you can get this slowdown with 3.7.5 and one of the built in > tokenizers, please post so I can look again. > > Thanks, > Dan. Upgrading to 3.7.5 (from 3.7.4) did the trick. Thanks again! Iker ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] server process gives better concurrency - why?
Hello all, One oft-cited method to address SQLite's limited concurrency support is to use a client-server database. Such databases enjoy better concurrency thanks to their use of a master, coordinating process which is hard to obtain efficiently using unrelated processes like those of SQLite. What's the key to this master process's increased concurrency that is difficult to emulate using a scheme among unrelated processes? Does the primary benefit of the master process lie in its ability to efficiently detect abnormal termination of child processes and deal with their locks? Regards, Iker -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab w: http://research.att.com ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
The question is whether a client-server design is /necessary/ to efficiently implement higher concurrency. It appears to be easier to do so with a client-server model, but is such a model required? Are there functions performed by a server process that cannot be carried out at all without it? Iker Simon Slavin wrote: > If SQLite was to be > designed to handle multiple processes 'properly', it would have to be > rewritten as a client/server system. > > This would, of course, kill all the advantages of SQLite: it could no > longer be tiny, fast, and ultra-portable. So it would be a bad design > choice for SQLite (bowing, of course, to DRH's right to do whatever he > pleases with it). > > This is why I get uneasy when I see posts here that suggest spinning > off threads especially to deal with locking issues, or do other things > that solve concurrency or latency problems. Often you find that > making such a change in your program just leads to one of the threads > immediately being blocked by another, defeating the point of threading > in the first place. Software has to be designed around what is > possible with the tools you're using, not around some mythical idea of > the perfect generic SQL engine. > > Simon. -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab e: i...@research.att.com w: http://research.att.com p: 973-360-8516 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
A single server process can be used to track "global" information, but shared memory mapped by unrelated processes might do as well. For example, a common mmap'ed file might serve to track lock info for each process. Of course, such a scheme would have to support handling of crashed processes without burdening the common case. But assuming it did, is this the main obstacle? Iker Igor Tandetnik wrote: > Iker Arizmendi wrote: >> The question is whether a client-server design is /necessary/ to >> efficiently implement higher concurrency. It appears to be easier >> to do so with a client-server model, but is such a model required? >> Are there functions performed by a server process that cannot be >> carried out at all without it? > > On a high, theoretical level, the advantage of a single server process > is that it has more context. It knows intimate details about everything > going on in the system, and can manage concurrent tasks more efficiently > using this information (e.g. use fine-grained locks). On the other hand, > multiple cooperating processes share only a limited amount of > information; each process knows very little beyond what it itself is > doing. > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab e: i...@research.att.com w: http://research.att.com p: 973-360-8516 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
The benefits are the same as they would be for any application that wants to use SQLite instead of a traditional database: trivial install and configuration, simple administration, etc. Were SQLite to support higher concurrency then these benefits could be extended to a wider variety of applications. The point is try to identify the technical issues that have stood in the way thus far and distilling them to something simple that can be explored and hacked on without diving too deeply into SQLite internals. Iker Igor Tandetnik wrote: > > A better question may be - what's the benefit? What's the point of the > exercise? What's the advantage over the traditional server architecture? > > Igor Tandetnik > > > > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab e: i...@research.att.com w: http://research.att.com p: 973-360-8516 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
Proposals for techniques like MVCC and shadow paging have been made on this list before and they appeared feasible (IIUC) without a server process. The problems with the serverless approach arise once multiple writers are introduced. For example, efficiently detecting crashed writers and initiating recovery. I think this problem may be soluble, at least non-portably, with robust futexes or mutexes. But I imagine there are other issues as well - these are the issues I'd like to get a bead on. Iker Ken wrote: > The key to increased concurrency is MVCC. Without MVCC concurrency is limited > to page locking, table locking etc. > > Google MVCC... > > --- On Tue, 9/8/09, Iker Arizmendi <i...@research.att.com> wrote: > >> From: Iker Arizmendi <i...@research.att.com> >> Subject: Re: [sqlite] server process gives better concurrency - why? >> To: sqlite-users@sqlite.org >> Date: Tuesday, September 8, 2009, 9:34 PM >> The question is whether a >> client-server design is /necessary/ to >> efficiently implement higher concurrency. It appears to be >> easier >> to do so with a client-server model, but is such a model >> required? >> Are there functions performed by a server process that >> cannot be >> carried out at all without it? >> >> Iker >> >> Simon Slavin wrote: >> > If SQLite was to be >> > designed to handle multiple processes 'properly', it >> would have to be >> > rewritten as a client/server system. >> > >> > This would, of course, kill all the advantages of >> SQLite: it could no >> > longer be tiny, fast, and ultra-portable. So it >> would be a bad design >> > choice for SQLite (bowing, of course, to DRH's right >> to do whatever he >> > pleases with it). >> > >> > This is why I get uneasy when I see posts here that >> suggest spinning >> > off threads especially to deal with locking issues, >> or do other things >> > that solve concurrency or latency problems. >> Often you find that >> > making such a change in your program just leads to >> one of the threads >> > immediately being blocked by another, defeating the >> point of threading >> > in the first place. Software has to be designed >> around what is >> > possible with the tools you're using, not around some >> mythical idea of >> > the perfect generic SQL engine. >> > >> > Simon. >> >> -- >> Iker Arizmendi >> AT Labs - Research >> Speech and Image Processing Lab >> e: i...@research.att.com >> w: http://research.att.com >> p: 973-360-8516 >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab e: i...@research.att.com w: http://research.att.com p: 973-360-8516 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
I'm hopeful that it's possible to devise a scheme that will let SQLite support multiple readers and writers while completely preserving all of its current benefits (eg, serverless, efficient, zero-conf, simple API, small footprint). To that end, I'm trying to understand some of the "sub problems" that get in the way of multiple writers in order to take a stab at working them out. I can see that the overall problem is nontrivial and an acceptable solution has not been found. But this only proves that the problem is hard, not that it's impossible (*). Iker (*) Of course, if folks have actually shown that solving this problem amounts to squaring the circle then that's another story. Pavel Ivanov wrote: > > I just keep wondering: do you want to write some new database engine > based on SQLite so that it will heed all these caveats? Otherwise this > discussion is useless because all these features are not implementable > on top of SQLite and are way nontrivial to implement inside SQLite... > > Pavel -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
I'm not planning to develop a new SQL engine, nor am I proposing any changes to SQLite. I'm only looking for some pointers regarding the relevant issues in order to study them more closely. If something useful were to come as a result of doing so then I might be in a position to actually flesh out a proposal. Because SQLite is a complex piece of code (as others have pointed out) I found it helpful to focus on just the following items: 1) maintaining shared lock state 2) efficiently detecting failed database processes The assumption being that a lock facility that can handle these issues is needed by any concurrency scheme (MVCC, shadow pages, etc) and so can be thought about independently. Does the ability of a client-server DB to support multiple writers follow solely from the fact that it centralizes the lock bookkeeping and that it can easily detect death of its children? If so, then I think one could get a similar result using a mmap'ed file and futexes among unrelated processes. This is the area I thought worth looking into provided that (1) and (2) really are at the heart of the matter. If not, course corrections are appreciated :) Regards, Iker Pavel Ivanov wrote: >> I'm hopeful that it's possible to devise a scheme that will let SQLite >> support >> multiple readers and writers while completely preserving all of its current >> benefits > > So the answer to my question is yes, you want to develop a new SQL engine... > Then first of all you have to describe one thing: how will you > implement ACID properties and most probably MVCC? What kind of OS and > programming tools will you use for this? How exactly you'll make sure > that multiple writers don't write to the same place in database and > readers don't read inconsistent data while writers are writing? > > As you explain your concepts in these topics we will be happy to > discuss it with you and help you make a great SQL engine. > > BTW, you can search list archives and find what DRH said about why > ACID cannot be implemented along with fine-grained locks using just > file system locking as SQLite does now. > > Pavel > > On Wed, Sep 9, 2009 at 4:09 PM, Iker Arizmendi <i...@research.att.com> wrote: >> I'm hopeful that it's possible to devise a scheme that will let SQLite >> support >> multiple readers and writers while completely preserving all of its current >> benefits (eg, serverless, efficient, zero-conf, simple API, small footprint). >> To that end, I'm trying to understand some of the "sub problems" that get in >> the way of multiple writers in order to take a stab at working them out. I >> can >> see that the overall problem is nontrivial and an acceptable solution has not >> been found. But this only proves that the problem is hard, not that it's >> impossible (*). >> >> Iker >> >> (*) Of course, if folks have actually shown that solving this problem >> amounts to squaring the circle then that's another story. >> >> Pavel Ivanov wrote: >>> I just keep wondering: do you want to write some new database engine >>> based on SQLite so that it will heed all these caveats? Otherwise this >>> discussion is useless because all these features are not implementable >>> on top of SQLite and are way nontrivial to implement inside SQLite... >>> >>> Pavel >> -- >> Iker Arizmendi >> AT Labs - Research >> Speech and Image Processing Lab >> >> ___ >> sqlite-users mailing list >> sqlite-users@sqlite.org >> http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users >> > ___ > sqlite-users mailing list > sqlite-users@sqlite.org > http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users > -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab e: i...@research.att.com w: http://research.att.com p: 973-360-8516 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
Cory Nelson wrote: > > Right now sqlite works over a network via NFS and the like -- if you > wanted to keep it an embedded library (with no server), you'd have to > drop support for that. > > PS. please stop top posting! > > It looks like any move away from file based locks suffers from this drawback. Perhaps the locking scheme could be offered as a compile time option or left to the application as a pragma at the cost of making things a bit less "zero-conf". Iker -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] server process gives better concurrency - why?
Simon Slavin wrote: > On 10 Sep 2009, at 12:02am, Iker Arizmendi wrote: > >> The assumption being that a lock facility that can handle these issues >> is needed by any concurrency scheme (MVCC, shadow pages, etc) and so >> can >> be thought about independently. Does the ability of a client-server >> DB to >> support multiple writers follow solely from the fact that it >> centralizes >> the lock bookkeeping and that it can easily detect death of its >> children? > > 'Death of children' is an issue in some DBMS systems (like SQLite > currently is), but not others. In particular, if you are using MVCC > correctly, death of children hardly matters at all. All it means is > that the DBMS engine will be using a little extra memory (and no extra > processing power) until it decides that a child which hasn't done > anything for an hour is probably not going to come back to life. > > Read up on MVCC. Read up on how to support ACID in a multiuser > concurrent system. Neither allow a client to explicitly lock > anything. Locks are handled by low-level processes inside the engine > and last fractions of a second. > > Simon. Even with MVCC write locks need to be kept somewhere so it does not address the problems that are resolved by a persistent server process. In any event, (I think) SQLite uses write ahead logging and infers the death of a writer by the presence of the rollback journal so this is the scenario of interest. Iker -- Iker Arizmendi AT Labs - Research Speech and Image Processing Lab e: i...@research.att.com w: http://research.att.com p: 973-360-8516 ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
[sqlite] concurrent DB creation
The FAQ makes clear that calling sqlite3_open from muliple processes is safe if the database in question already exists. However, is it safe to do so even if the database file does _not_ exist? In other words, can multiple processes safely compete to create a new database file? Regards, Iker -- Iker Arizmendi e: [EMAIL PROTECTED] w: ikerariz.blogspot.com
