The stack trace you've shown is a lock for allocating new page in page cache. It's probably the lock that helps SQLite ensure that total amount of consumed memory is not greater than the limit configured. But if you work only with in-memory databases then that check is moot and with next step SQLite goes into malloc to allocate new page. Most probably the standard malloc locks its mutex on entrance and so you'll get heavy contention on that lock anyway. So to resolve this problem first you should implement your own page cache, exclude inter-thread contention from there. And then if my guess above is correct then you'll need to implement your own memory allocator and exclude (or better to say lower as much as acceptable in your application) inter-thread contention from there. Page cache would be a relatively easy task (especially if you learn SQLite's own implementation first). Memory allocator -- not so much.
Pavel On Sunday, February 19, 2012, Chad Dombrova <chad...@gmail.com> wrote: > Hi all, > I need to insert a lot of data (a million+ rows) very quickly and it must occur on multiple threads. I do not have control over the creation and management of the threads, because I am using sqlite within the context of a plugin: I provide callback functions and the controlling process provides my functions with thread id's and other relevant data. > > I decided (perhaps naively) that the safest bet to avoid thread lock would be to have each thread insert into its own database (with its own db handle) during the heavy multi-threaded writing phase, then merge all the data into a single database file at the end. I am using the special ":memory:" database as that seemed ideal for my purposes: fast, private, and temporary. Everything works, but I am getting a lot of thread locking, so much so that running on 8 threads it is about 5 times slower than when it runs on a single thread. > > So my question is: if each thread is writing to its own database with its own connection, why are the threads locking? > > I've profiled it with Shark and the problem is definitely the mutex lock. Here is the callstack: > > sqlite3_step > sqlite3VdbeExec > sqlite3BtreeInsert > sqlite3BtreeMovetoUnpacked > getAndInitPage > sqlite3PcacheFetch > pcache3Fetch > pthreadMutexEnter > pthread_mutex_lock > > > All of the inserts for a thread are wrapped in a single BEGIN.. END. > > I'm setting: > > PRAGMA synchronous=OFF > PRAGMA journal_mode=OFF > > One peculiar thing is that I see no difference in behavior, stability, or performance when changing the sqlite3_open_v2() flags to include SQLITE_OPEN_NOMUTEX or SQLITE_OPEN_FULLMUTEX. > > The next step is to compile debug versions of everything and step through to find out what is causing the lock, but before I go down that road, I'd like to know if there isn't something else I should try first. > > I'm using the sqlite that comes with osx 10.6, version 3.6.12. > > thanks, > chad > > > _______________________________________________ > 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
