I want to contribute my 0.02€ to this discussion. Basically I believe your (Emerson) design is flawed. I've been working for years with multithreaded and even multi-core systems. From my experience a design using threads for specific tasks is *always* performing better, than having multiple threads execute the same things in parallel. This experience is based on the facts about lock contention, thread starvation, caching and more. These always apply unless you have a realtime operating system, which guarantees you that every thread is scheduled and the scheduling order. On most systems this ain't the case AFAIK. At least not on Windows/Linux, if there's heavy threading and lock contention going on.
I really would suggest you to test out a design, where threads are assigned single tasks, such as retrieving data from db, writing data to db and queues, which provide these threads with work items. If you're really smart, you'll create queues using atomic operations so that no thread will take locks for these operations. To summarize my points: - Create one Sqlite writer thread, one sqlite reader thread. - Create queues for all worker threads, which provide them with work items. - Do *not* take kernel locks on data structures or libraries, this *will* really hurt your performance. - Use transactions coarse grained on the Sqlite writer thread. Either decide inside the thread, when to commit and start a new one or design a specific workitem to trigger this from the outside. This depends on having a consistent state in your data structures/the database. But: Your limiting factor will always be the hard disk. Analyze your tasks to determine, what the bounding factor is: Is it the CPU? Is it the disk? Is it the network? Only then start changing something. Multithreading only makes sense if you can parallelize heavily and are not bound by disk/network, but by CPU and have multiple (unused) cores available. And: Having multiple statements executing concurrently is only possible with multiple Sqlite connections. A connection can only keep one resultset open or a statement executing (unless that has changed recently.) Mike -----Ursprüngliche Nachricht----- Von: Emerson Clarke [mailto:[EMAIL PROTECTED] Gesendet: Samstag, 30. Dezember 2006 17:08 An: sqlite-users@sqlite.org Betreff: Re: [sqlite] sqlite performance, locking & threading Richard, Are you sure we are not just getting into semantic knots here ? Do we have the same definition of "at the same time". I mean concurrently, so that both threads use the same sqlite3 * structure, within mutexes. Each query is allowed to complete before the other one starts, but each thread may have multiple statements or result sets open. When i try to do this, i get api called out of sequence errors... On 12/30/06, [EMAIL PROTECTED] <[EMAIL PROTECTED]> wrote: > "Emerson Clarke" <[EMAIL PROTECTED]> wrote: > > But why then can i not have a single transaction wrapping a single > > connection which is used within multiple threads, obvioulsy not at > > the same time. > > You can. What makes you think you can't? > -- > D. Richard Hipp <[EMAIL PROTECTED]> > > > ---------------------------------------------------------------------- > ------- To unsubscribe, send email to > [EMAIL PROTECTED] > ---------------------------------------------------------------------- > ------- > > ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] ----------------------------------------------------------------------------- ----------------------------------------------------------------------------- To unsubscribe, send email to [EMAIL PROTECTED] -----------------------------------------------------------------------------
