multiple queries, one thread

classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

multiple queries, one thread

Tim Larson
Since multi-threading issues are notoriously troublesome,
usually deciding to wait to appear until the software is
deployed and under heavy load...

Is there a way to have multiple (perhaps only "select"?)
queries running at the same time from a single thread?

This would allow the OS to schedule the single-threaded
application's multiple eventual reads more efficiently
(elevator scheduling and all that) and allow the fast cpu
to keep working while waiting for the much slower disk to
return data.

Reading about the busy handler I found out that sqlite is
reentrant, and there is a progress handler that lets you
do other work every N virtual machine opcodes, but I did
not find anything that lets us do work specifically during
the time spans when sqlite is waiting on the disk drive
(unless that is what happens between v machine opcodes?)

Is this currently possible with sqlite, or are the only
option available to user multiple processes or threads to
efficiently interleave multiple queries and while possibly
also doing other cpu-bound work while the disk is busy?

Perhaps there is some way using sqlite3_progress_handler
with sqlite3_enable_shared_cache set, using one connection
per simultaneous query, all in the same thread?

--Tim Larson
Reply | Threaded
Open this post in threaded view
|

Re: multiple queries, one thread

D. Richard Hipp
Tim Larson <[hidden email]> wrote:
> I did not find anything that lets us do work specifically
> during the time spans when sqlite is waiting on the disk drive

Most of the disk drive wait time is spent inside the fsync()
or FlushFileBuffers() system calls.  There is no way to have
the same thread be doing other work while these system calls
are running.
--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: multiple queries, one thread

Tim Larson
On Sun, Mar 19, 2006 at 06:40:51AM -0500, [hidden email] wrote:

> Tim Larson <[hidden email]> wrote:
> > I did not find anything that lets us do work specifically
> > during the time spans when sqlite is waiting on the disk drive
>
> Most of the disk drive wait time is spent inside the fsync()
> or FlushFileBuffers() system calls.  There is no way to have
> the same thread be doing other work while these system calls
> are running.
> --
> D. Richard Hipp   <[hidden email]>

That makes sense for writes, due to the sqlite and OS caches,
but are those also the main disk drive wait times for reads?

I was under the impression that for reads that using either
a multi-process and/or a multi-threaded design could give a
performance enhancement, due to the way this allows the OS
to schedule reads.

What I am trying to find out is if this is the case and if
there is a way with sqlite to get this benefit (increased
performance by having more than one "select" being processed
at a time) without having to leave the single threaded,
single process design.  For context, I am thinking about
using sqlite in combination the pseudo-threaded software at
http://www.annexia.org/freeware/rws

--Tim Larson