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
[sqlite] search time is non-determinate in multi-thread enviroment
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.
--
View this message in context:
http://www.nabble.com/search-time-is-non-determinate-in-multi-thread-enviroment-tp24693604p24693604.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
