Setting up IMDB
Setting up an IMDB is remarkably similar to setting up any
other database, with the additional step of predefining the cache.
1) Define
the cache in which the database will exist
2) Create
the devices in which the database will reside
3) Create
the database
Defining the database cache
The sp_cacheconfig stored procedure has been modified to
accept an additional parameter, in order to define the cache as an IMDB cache
(note there’s no buffer replacement strategy here, as there is no FIFO queue in
this database).
Syntax:
sp_cacheconfig ‘Cache_Name’, ‘Cache Size’, ‘inmemory_storage’
Example:
sp_cacheconfig ‘Lookup DB Cache’, ‘1G’, ‘inmemory_storage’
Creating the device
The disk init syntax has an additional type, to specify that
it’s an in-memory device.
Example:
disk init name= ‘LookUpDBDevice’, physname = ‘Lookup DB Cache’,
size = ‘1G’, type = ‘inmemory’
It is just like creating any other device; you’re just
pointing the server towards a different resource from which to allocate pages.
Creating the database
Database creation is similar, but there are a few additional
options. Since the IMDB is recreated (in memory) every time the server starts,
it has to come from a template. The default template is the model
database (just like every other database). You can change this default to
another template, as we’re going to do below. Also as with model, any
database options follow the database. Note further that, as with other ASE
objects, once a database becomes dependant on the template, the template can’t
be dropped until the dependant database is dropped.
Example:
create inmemory database LookUpDB use ReferenceDB as template on LookUpDBDevice = ‘1G’,
log on LookUpLogDevice = ‘100M’ with durability = no_recovery
“inmemory” specifies that this database is not disk resident
“Durability = no_recovery” specifies that the database will
be recreated at startup.
“use ReferenceDB as template” specifies that the contents
of the database will be loaded from ReferenceDB instead of from model.
This is a great way to preload a database into cache. We’re
creating an in-memory database called LookUpDB. At startup, it’s going to
create a 1G database (plus the log) in the cache specified by the
LookupDBDevice, and preload it with the contents of ReferenceDB, which may
contain tables, stored procedures, data, and anything else that makes sense. It
will be released at server shutdown.
Setting up RDDB
There are two types of RDDB; ephemeral and persistent. The
ephemeral database will be dropped and recreated at shutdown/startup regardless
of the content. The persistent database, with its minimal logging, will only
persist if the server is shut down cleanly (i.e. a “shutdown with nowait” will
cause it to be marked as suspect at startup).
Example:
Create database MyRddb on rddbdev = ‘2G’ with durability = no_recovery
Or
Create database MyRddb2 on rddbdev2 = ‘2G’ with durability = at_shutdown
Minimally logged DML operations -- an important step
With ephemeral databases, it is important to ensure that the
server knows that logging is not a priority. Please note these commands ONLY
work in an IMDB and RDDB database, they will be ignored in an DRDB database.
Database-level DML logging
You can (and usually will) enable minimal-logging at the
database level. This dramatically reduces log IO. There is no need to change
anything at the application level.
You can turn this "on" at create database
time, with the “dml_logging = minimal” option, or later with the alter table
option or off with the “dml_logging = full” option.
Table-level DML logging
This can also be turned off at the table-level with the
create table or alter table statement:
Create table min_log (a int) with dml_logging = minimal
Session-level DML logging
Session-level set options can be used if you want this to
take effect only for your process:
Syntax:
set dml_logging {minimal | default }
Maintaining IMDB / RDDB
Full syntax is available in the reference manuals; these
databases may be altered as needed to extend their size dynamically.
Dump / Load
IMDB and RDDB databases can be dumped and loaded just like
any other database, which offers additional possibilities for data population.
Tempdb
Tempdb databases can be an IMDB, RDDB, or DRDB. Moving
forward, the type will most likely depend more on money than on anything else;
this will make tempdb fly.
Summary: IMDB vs. RDDB vs. DRDB
IMDB databases are ephemeral; they need to be loaded at
server startup, and they disappear at server shutdown. They are amazingly fast,
because 100% of all activity is in memory.
DRDB databases (Sybase ASE’s behavior for the past 20+
years) guarantees persistence of any data inserted into the database.
If the primary concern is performance, without regard to
data persistence, use an IMDB. If data loss will not be tolerated due to server
shutdown, use DRDB.
In between, there may be a situation where;
1) The
IMDB database will not fit into cache, or
2) You
have a persistent database with an IO bottleneck, and you are willing to
potentially sacrifice the tail end of the database
IMDB has no durability. RDDB has optional durability, which
is configurable at database definition time. DRDB is fully durable.
IMDB was just released, so we can’t make formal
recommendations based upon experience, but based on preliminary testing, I’ve
got lots of plans:
1) Tempdb,
where memory is available, is going into memory. I’m not even going to think
hard about this one. In a high-performance environment, this will eliminate
tempdb as a bottleneck (it always did for SSD, and this is a lot less
expensive).
2) Lookup
tables and other information that would typically go into its own cache are
going into databases, with underlying databases as templates; this will “prime”
cache, which we haven’t been able to conveniently do before.
3) All
data migration / load efforts are going to be performed in an IMDB if it fits
in memory and an RDDB if it doesn’t.
4) I’m
going to examine opportunities for IMDB, and look at high-availability options
(Replication Server, etc.) for continually pushing IMDB into a persistent
database with acceptable latencies.
The use cases outlined above, and many more that spring to
my mind (and yours), are getting due consideration for this easy-to-use answer
to a lot of performance questions.
A 20-year veteran of
Sybase ASE database administration, design, performance, and scaling, Jeff
Garbus has written over a dozen books, many dozens of magazine articles, and
has spoken at dozens of user’s groups on the subject over the years. He is CEO
of Soaring Eagle Consulting, and can be reached at jeff@soaringeagle.biz.
» 
IE 7
Firefox 2.0
Comment and Contribute
