Hi,
I am developing a multi-threaded sqlite database application on an Ubuntu
virtual machine that has 4 processors allocated to it. I am using sqlite
version 3.7.13. I created a test to verify that multiple threads/connections
can read from the database at the same time.
I have two executables. The first executable simply creates a database,
creates 1 table in that database, inserts 50 items into that table, and then
closes the database. This does not involve any multi-threading at all and
is simply meant to provide a database with entries in it.
The second executable creates multiple threads to read from the database and
waits for them to finish and records the time that it took for all of the
threads to complete. Each thread does the following:
-create a database connection using sqlite_open_v2() so that
each thread has its own individual connection to the database created from
the first executable
-perform 100000 SELECTS on the one database table (each
select queries for one row in the table)
-close the database connection
When I ran this test with SQLITE_OPEN_READWRITE specified as the flags for
sqlite_open_v2 in each thread, I get the following results for total time to
perform all queries:
1 Thread - 0.65 seconds
2 Threads - 0.70 seconds
3 Threads - 0.76 seconds
4 Threads - 0.91 seconds
5 Threads - 1.10 seconds
6 Threads - 1.28 seconds
7 Threads - 1.57 seconds
8 Threads - 1.78 seconds
These results were as expected as the times increase just a little (probably
from context switching between threads and other causes) as I add threads,
meaning that reads are basically being done in parallel.
However, when I ran this same test with SQLITE_OPEN_READWRITE |
SQLITE_OPEN_SHAREDCACHE for the flags, I get the following results:
1 Thread - 0.67 seconds
2 Threads - 2.43 seconds
3 Threads - 4.81 seconds
4 Threads - 6.60 seconds
5 Threads - 8.03 seconds
6 Threads - 9.41 seconds
7 Threads - 11.17 seconds
8 Threads - 12.79 seconds
>From these results, it appears that something in shared cache mode is
preventing multiple reads from happening in the database at the same time.
I have verified that indeed different the threads are running in parallel
(thread 4 reads, thread 8 reads, thread 2 reads, etc. rather than thread 1
performs all its reads, thread 2 performs all its reads, thread 3 performs
all its reads, etc.). However, it appears that the reads for each
individual transaction are being done in serial, or something else is
slowing down the database in shared cache.
Why am I seeing such a high increase in times as I add threads in shared
cache mode as opposed to without it? Is there a way to fix this and still
use shared cache mode?
Thanks for any help. It is much appreciated.
--
View this message in context:
http://sqlite.1065341.n5.nabble.com/Multi-Thread-Reads-to-SQLite-Database-tp63697.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
