Re: [sqlite] search time is non-determinate in multi-thread enviroment
On Jul 28, 2009, at 8:09 PM, pierr wrote: > http://www.sqlite.org/threadsafe.html > "Multi-thread. In this mode, SQLite can be safely used by multiple > threads > provided that no single database connection is used simulataneously > in two > or more threads." > > If I have one(different) connection for each thread , it seems no > need to > synchronize these threads' accessing to the database because they are > through different connection. Did I read it wrongly? I believe you are correct. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search time is non-determinate in multi-thread enviroment
Jay A. Kreibich-2 wrote: > > > THREADSAFE=1 is "Serialize mode" and THREADSAFE=2 is "Multithread" > mode. THREADSAFE=2 (basic Multithread) actually offers less protection, > requiring the application to provide its own locks to prevent multiple > threads from accessing the DB at the same time, while THREADSAFE=1 > (Serialize) handles all that for you. Generally, unless you're doing > a lot of thread and lock management yourself, you want to use =1. > http://www.sqlite.org/threadsafe.html "Multi-thread. In this mode, SQLite can be safely used by multiple threads provided that no single database connection is used simulataneously in two or more threads." If I have one(different) connection for each thread , it seems no need to synchronize these threads' accessing to the database because they are through different connection. Did I read it wrongly? -- View this message in context: http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24711210.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search time is non-determinate in multi-thread enviroment
Igor Tandetnik wrote: > > pierr wrote: >> Hi, >> My sqlite is configure as serialized (THREADSAFE=1). My application >> has only one connection but it is shared by two thread. One thread >> will do insert/update/delete in the background , another thread will >> do the select upon request of the user from gui. >> Typically, we will call following function 12 times (with different >> parameters ,of course)before showing the pages to user. In the >> standalone test (single thread), a call to following function will >> take less than 40ms, so 12 times will take less than 500 ms and it is >> acceptable. However, in the real application, sometimes this function >> took 1000ms to return the result which make the gui slow. > > When the second thread makes SQLite calls, it takes a lock on the > connection. If the UI thread runs a SELECT at this moment, it also tries > to take that lock, and so has to wait until the second thread releases > it. > 1. I expected the second thread would impact the SELECT thread but not that much. The serach time increase as much as 6 times on average. But in my experiment, when i add a usleep(1) (10ms) in the INSERT thread, the search time will back to normal.I am not sure if this is the POINT. I would try this in my real application to see what happened. 2. I also suspect the increase of search time is caused by the SELECT thread can not be scheduled timely to run because of INTERRUPT that happend very frequently in my system , or because of other threads cost to much CPU time like the other_thead in my expriment ,which has nothing but while(1). (Actually ,I don't quite understand why this would impact the SELECT thread (that much). Although other_thread has a while(1), I thought kernel (linux 2.6) should use time_sharing scheduler policy as both other_thread and select_thread are nomal thread , i.e, not real time thread and no priority set explicitly. So priority base preemptive should not be applied here. Any idea? ) 3.I think the way I use the sqlite (one thread for write , another for read) is not uncommon. Do we have any best practice for the question I am asking here? Igor Tandetnik wrote: > > Further, you cannot open two connections to the same in-memory database. > This would be the thing that prevent me from going THREADSAFE=2. -- View this message in context: http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24711093.html Sent from the SQLite mailing list archive at Nabble.com. ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search time is non-determinate in multi-thread enviroment
On Tue, Jul 28, 2009 at 07:59:23AM -0400, Igor Tandetnik scratched on the wall: > pierr wrote: > > Any insight? Should I go with THREADSAFE=2 and two connections: one > > for read ,another for write? Thanks. > > THREADSAFE is not a number of allowed concurrent threads. It's a boolean > switch - 0 means non-threadsafe, non-zero means thread-safe. > THREADSAFE=2 won't make any difference. THREADSAFE=1 is "Serialize mode" and THREADSAFE=2 is "Multithread" mode. THREADSAFE=2 (basic Multithread) actually offers less protection, requiring the application to provide its own locks to prevent multiple threads from accessing the DB at the same time, while THREADSAFE=1 (Serialize) handles all that for you. Generally, unless you're doing a lot of thread and lock management yourself, you want to use =1. http://sqlite.org/compile.html#threadsafe http://sqlite.org/threadsafe.html -j -- Jay A. Kreibich < J A Y @ K R E I B I.C H > "Our opponent is an alien starship packed with atomic bombs. We have a protractor." "I'll go home and see if I can scrounge up a ruler and a piece of string." --from Anathem by Neal Stephenson ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search time is non-determinate in multi-thread enviroment
pierr wrote: > Hi, > My sqlite is configure as serialized (THREADSAFE=1). My application > has only one connection but it is shared by two thread. One thread > will do insert/update/delete in the background , another thread will > do the select upon request of the user from gui. > Typically, we will call following function 12 times (with different > parameters ,of course)before showing the pages to user. In the > standalone test (single thread), a call to following function will > take less than 40ms, so 12 times will take less than 500 ms and it is > acceptable. However, in the real application, sometimes this function > took 1000ms to return the result which make the gui slow. When the second thread makes SQLite calls, it takes a lock on the connection. If the UI thread runs a SELECT at this moment, it also tries to take that lock, and so has to wait until the second thread releases it. > Any insight? Should I go with THREADSAFE=2 and two connections: one > for read ,another for write? Thanks. THREADSAFE is not a number of allowed concurrent threads. It's a boolean switch - 0 means non-threadsafe, non-zero means thread-safe. THREADSAFE=2 won't make any difference. Further, you cannot open two connections to the same in-memory database. Igor Tandetnik ___ sqlite-users mailing list sqlite-users@sqlite.org http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
Re: [sqlite] search time is non-determinate in multi-thread enviroment
pierr wrote:
>
> Hi,
>My sqlite is configure as serialized (THREADSAFE=1). My application has
> only one connection but it is shared by two thread. One thread will do
> insert/update/delete in the background , another thread will do the select
> upon request of the user from gui.
> Typically, we will call following function 12 times (with different
> parameters ,of course)before showing the pages to user. In the standalone
> test (single thread), a call to following function will take less than
> 40ms, so 12 times will take less than 500 ms and it is acceptable.
> However, in the real application, sometimes this function took 1000ms to
> return the result which make the gui slow.
>
> int search_eit_event_in_time_inteval(...)
> 86 {
> 93
> 94 sql = (char *)" SELECT *,rowid FROM tblEvent_basic "
> 95 " WHERE "
> 96 " service_id = ? AND "
> 97 " start_time < ? AND end_time > ? "
> 98 " ORDER by start_time ASC";
> }
>
> I did some experiment try to understand why this happend? Here is the
> code I used :
> http://pastebin.ca/1509723
>
> I found :
> 1. When i create a write_thread
> a. with usleep(1) in while(1)
> search time always < 50ms
> b. without usleep(1) in while(1)
> 17.7 % search time > 50ms , usually 200ms
>
> 2. When i create another_thread (no database access at all)
> a. with usleep(1) in while(1)
> search time always < 50ms
> b. without usleep(1) in while(1)
> 21.7 % search time > 50ms , usually 200ms.
>
>Any insight? Should I go with THREADSAFE=2 and two connections: one for
> read ,another for write? Thanks.
>
I was using memory database. This should be impormant background information
:)
--
View this message in context:
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24693647.html
Sent from the SQLite mailing list archive at Nabble.com.
___
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
