Simple ATTACH/memory database question

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

Simple ATTACH/memory database question

Kervin L. Pierre
Hello,

We are getting ready to start using in-
memory database to cache sqlite reads/writes
in effort to improve speed.  For background,
the application is an email client.

The way we envision the caching working is
that we have a mirror copy of any table in
use in memory.  The tables are small and not
many are in use at any time, so we are ok with
space.  Since we read much more than we
write, reading from memory should improve.
Also, this should simplify our transaction
model for transacted objects.

Has anyone done this?  Comments?

Best Regards,
Kervin



Reply | Threaded
Open this post in threaded view
|

Re: Simple ATTACH/memory database question

Andrew Piskorski
On Tue, Feb 14, 2006 at 10:16:14AM -0500, Kervin L. Pierre wrote:

> We are getting ready to start using in- memory database to cache
> sqlite reads/writes in effort to improve speed.  For background,

> Since we read much more than we write, reading from memory should
> improve.

Shouldn't SQLite's built-in cache for disk-backed databases already
accomplished that for reads?  Have you tested the actual performance?

--
Andrew Piskorski <[hidden email]>
http://www.piskorski.com/
Reply | Threaded
Open this post in threaded view
|

Re: Simple ATTACH/memory database question

Jay Sprenkle
In reply to this post by Kervin L. Pierre
> We are getting ready to start using in-
> memory database to cache sqlite reads/writes
> in effort to improve speed.  For background,
> the application is an email client.
>
> The way we envision the caching working is
> that we have a mirror copy of any table in
> use in memory.  The tables are small and not
> many are in use at any time, so we are ok with
> space.  Since we read much more than we
> write, reading from memory should improve.
> Also, this should simplify our transaction
> model for transacted objects.
>
> Has anyone done this?  Comments?

It sounds like overkill to me too.

In memory databases are unique per connection. If you have
multiple processes they won't be able to share the same database.
You could share the same database amoung threads or a
database server process.

Using the 90/10 rule this last 10% of performance is going to cost you
90% of the effort.
Reply | Threaded
Open this post in threaded view
|

Re: Simple ATTACH/memory database question

D. Richard Hipp
In reply to this post by Andrew Piskorski
Andrew Piskorski <[hidden email]> wrote:

> On Tue, Feb 14, 2006 at 10:16:14AM -0500, Kervin L. Pierre wrote:
>
> > We are getting ready to start using in- memory database to cache
> > sqlite reads/writes in effort to improve speed.  For background,
>
> > Since we read much more than we write, reading from memory should
> > improve.
>
> Shouldn't SQLite's built-in cache for disk-backed databases already
> accomplished that for reads?  Have you tested the actual performance?
>

I'm reading and replying to this message using an SQLite-backed
email client  See

  http://www.sqlite.org/cvstrac/wiki?p=ExperimentalMailUserAgent.

All incoming and archival emails are stored as BLOBs in a table.
The full text of messages is indexed.  It is all very fast and
I haven't had to do anything special to make it so.

Of course, all this assumes a modern workstation.  Perhaps your MUA
is designed to work on a handheld with cheapest (read: slowest)
flash memory available and a 50 MHz ARM processor or something?

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Simple ATTACH/memory database question

Kervin L. Pierre
[hidden email] wrote:
> I'm reading and replying to this message using an SQLite-backed
> email client  See
>

That's very interesting...

> All incoming and archival emails are stored as BLOBs in a table.
> The full text of messages is indexed.  It is all very fast and
> I haven't had to do anything special to make it so.
>

How did you implement full text indexing?
I though sqlite did not support full text
index, am I wrong?

> Of course, all this assumes a modern workstation.  Perhaps your MUA
> is designed to work on a handheld with cheapest (read: slowest)
> flash memory available and a 50 MHz ARM processor or something?
>

Lol, I wish. It's the direct opposite.  The
project is essentially an alternative to PST
in Microsoft Outlook :)
http://openconnector.org/

We are stuck with Outlook's internal API.
Our design has an SQLite table for each
message.  Message objects are transacted.
Hence the plan is to mirror the message
SQLite table in an in memory database until
the SaveChanges() API is called, which would
copy the changed rows to disk.

Currently message saves and retieval is
very slow because Outlook makes about
250 calls to our 'sqlite-backed' generic
property retrival function when it opens a
single message.  So that SQLite-based
function, GetProps(), needs to be very,
very, fast.  Any ideas?

Best Regards,
Kervin

Reply | Threaded
Open this post in threaded view
|

Re: Simple ATTACH/memory database question

D. Richard Hipp
"Kervin L. Pierre" <[hidden email]> wrote:
>
> How did you implement full text indexing?
> I though sqlite did not support full text
> index, am I wrong?

SQLite does not have *automatic* full-text indexing.
But it is easy enough to index the full text of an
email message yourself in application code.  I just
counted 32 lines of code in my email reader used to
implement full-text indexing.  It is not hard.

> Our design has an SQLite table for each
> message.

Is this right?  You are doing a separate
CREATE TABLE for each message?  That's going
to be the source of your problem.  I think
you have much better success if you put
each message in its own row of a single
table.

--
D. Richard Hipp   <[hidden email]>

Reply | Threaded
Open this post in threaded view
|

Re: Simple ATTACH/memory database question

Kervin L. Pierre
[hidden email] wrote:
> Is this right?  You are doing a separate
> CREATE TABLE for each message?  That's going
> to be the source of your problem.  I think

Yes we are.  I tried a message/row design
early on but I could not count on it being
behaving well.  The problem was fitting
Outlook's internal API ( MAPI ) on a SQL
database.  Every message has a one-to-many
relationship with properties, which have
a one-to-many with values.  Combined with
some of the reporting requirements of the
API, I thought that a simple message/table
would work, at least for version 1.

I was hoping that the CREATE hit would not
be significant since it is only occured
when a message is created.

But there doesn't seem to be much we can do
about the reads.

PS. One approach seems to be to replace the
default 'sqlite3OsMalloc' with a pool based
malloc for speed.  It would be nice if this
was a function pointer instead of a define.
that way we could replace the function with-
out having to modify SQLite source.

Best Regards,
Kervin