On Sat, Apr 17, 2010 at 05:27:35PM -0700, andrew fabbro scratched on the wall:
> If a DB is entirely in-memory (i.e., opened with :memory:), which pragmas
> can be used to improve performance?
>
> (1) I assume synchronous = OFF is desirable
N/A. There is no such thing as synchronizing memory writes.
> (2) I'm guessing journal_mode = MEMORY is already set.
No need to guess. Docs:
"Note that the journal_mode for an in-memory database is either
MEMORY or OFF and can not be changed to a different value."
> Is journal_mode = OFF another possible speed gain? Of course, then
> one loses the ability to do transactions.
If it is, it is likely to be very minor.
> (3) If journaling is set to MEMORY, what is the best setting then for
> journal_size_limit?
N/A. Only applies to on-disk journals.
> I guess it depends on how much memory one is willing to
> use overall, but in this case, it's not there for crash protection but
> rather to support transactions. Is there a sizing guide?
JOURNAL_SIZE_LIMIT doesn't limit the size of an active journal, only
left-over journals.
Docs:
"This pragma may be used to limit the size of journal files left in
the file-system **after** transactions are committed..." [emp. added]
> (4) How about locking_mode? I imagine it would be OK and a small gain to
> set to NORMAL in a single-threaded application, but obviously not a good
> idea for multi-threaded.
Docs:
"The "temp" database (in which TEMP tables and indices are stored)
and in-memory databases always uses exclusive locking mode."
Multi-threaded has nothing to do with it. Even with multi-thread you
still have to sync your use of the database connection.
> (5) If referential integrity can be sacrificed (the Oracle DBA in my
> whimpers a little), foreign_keys = false, but that's true whether on-disk or
> in-memory.
Yes. But if you don't have FKs, it is unlikely to make much difference.
> (6) Is there any advantage to playing with:
> - page_size?
Doubt it. Unless you're storing a lot of TEXT/BLOB values that are
just slightly larger than a page, and getting a lot of fragmentation,
there isn't likely to be much difference.
If you're memory bound a slightly larger page is likely to help, but
that's a big balancing act that depends a lot on the data and layout
of the database.
> - default_cache_size?
N/A. This only comes into play when a database is re-opened. You
can't do that with an instance of an in-memory DB.
> - read_uncommitted? I assume there's a different answer for single- vs.
> multi-threaded (or rather, depending on how readers/writers interact)
N/A. This only applies to shared-cache mode. Shared cache mode only
applies when you have multiple connections to the same database. You
can't have multiple connections to an in-memory instance.
-j
--
Jay A. Kreibich < J A Y @ K R E I B I.C H >
"Our opponent is an alien starship packed with atomic bombs. We have
a protractor." "I'll go home and see if I can scrounge up a ruler
and a piece of string." --from Anathem by Neal Stephenson
_______________________________________________
sqlite-users mailing list
sqlite-users@sqlite.org
http://sqlite.org:8080/cgi-bin/mailman/listinfo/sqlite-users
