Monday, March 31, 2008

Who wants "in-memory" mode?

If you sign up for the Panorama beta, you can find the documentation for the new "in-memory mode" feature in the Help:

   SQL Anywhere Server
Database Administration
Starting and Connecting to Your Database
The database server
Database server options
-im server option
What's the point of in-memory mode? Speed, at the cost of durability.

This isn't a "database file mode", it's a "server operation mode" which means you create and load a database file in "SQL Anywhere Classic" mode and then run it in one of the two in-memory modes: Never Write or Checkpoint Only.

And before anyone gets their knickers in a knot, Panorama isn't introducing any kind of cheesy non-transactional data store like MySQL.

SQL Anywhere is still a fully-relational database management system, fully transactional, almost fully ACId even when running in-memory mode.
ACId means Atomic, Consistent, Isolated and durable, the Four Horsemen Of The Transaction. Durability means a COMMIT is permanent. A little "d" is used here because the Checkpoint Only flavor of in-memory mode preserves all changes up to the last checkpoint, but doesn't allow forward recovery beyond that point. And no recovery at all for the Never Write flavor... the name kinda gives that away. More on this later...
Oh, yeah, it's the same engine, not some bogus Lite version with all sorts of syntactical and semantic differences. It is, however, a "separately licensed component" like high availability.

Who wants it?

What I'd like to know is, who wants "in-memory" mode? I think I do, for some things. Maybe for Foxhound which is a classic "high throughput" application doing huge numbers of inserts and deletes.

The Panorama Help talks about some of the reasons you might want to use in-memory mode, I won't repeat them here. What I will say is this, "Never Write" doesn't mean "Read Only", it just means if you want to save your changes to the disk you're gonna have to find another way; UNLOAD TABLE is one suggestion.

Here's how I see the in-memory modes stacking up against one another, and against Classic mode:
                   -im nw  -im c
Never Checkpoint
Write Only Classic
====== ========== =======
Transactional yes yes yes
.db file updated no yes yes
Transaction log no no yes
Temporary file no no yes
Checkpoint log no yes yes
Checkpoints no yes yes
Dirty page flushes no yes(1) yes
Unlimited growth no yes(2) yes
Recoverable no yes(3) yes
(1) Dirty pages are flushed only on checkpoint.

(2) Without a temporary file, checkpoints are the only way to keep the cache from being exhausted.

(3) After a crash, a restart will perform the usual automatic recovery to the last good checkpoint, but without a transaction log, it is not possible to run a forward recovery from the last good checkpoint to the last committed transaction.
What's missing from the table above is a column for "UltraLite"... will in-memory mode give UltraLite some competition?

1 comment:

Breck Carter said...

Question, posted on the Panorama newsgroup: Do idle checkpoints happen in Checkpoint Only mode? The docs say "dirty pages are flushed to disk only on checkpoint".

Answer: Yes, "idle" checkpoints happen in checkpoint-only mode. That is, if the server thinks it needs to checkpoint based on its normal rules, it will. You will also get a checkpoint if you *explicitly* ask for one.

No other "idle" I/O is performed in checkpoint only mode.