SYBASE ASE - IN MEMORY
DATABASE -
An In Memory Database(IMDB) is a memory resident database
introduced in Sybase ASE 15.5. It provides much better performance by
eliminating physical IO over Durability. This database is created on memory
resident devices which are created on a named cache. A named cache created for IMDB can be used by
only one IMDB and it cannot be used by any other IMDB or cache as its
structurally different from a normal cache.
Steps to Create IMDB
1. Create a named cache for IMDB using sp_cacheconfig with
"inmemory_storage" parameter as shown below
sp_cacheconfig
imdb_dba_cache, '50M', inmemory_storage
Memory
allocated for the imdb_dba_cache cachelet 1: 51200 Kb
The
change is completed. The option is dynamic and ASE need not be rebooted for
the
change to take effect.
(return
status = 0)
Note that the above command will fail if free memory
available is less than 2GB
2. Ensure that this is refelected in configuration file -
cat
$SYBASE/$SYBASE_ASE/ASE1.cfg
[Named
Cache:imdb_dba_cache]
cache size = 50M
cache status = in-memory storage cache
cache replacement policy = none
local cache partition number = DEFAULT
3. Create device on this cache using disk init. phys_name
would be name of cache created above.
1> disk init name = 'imdb_dba_data_dev',physname =
'imdb_dba_cache',size = '10M', type = "inmemory"
2>
go
00:00:00000:00012:2013/05/29
13:10:23.27 server Initializing the
Virtual Cache Device 'imdb_dba_data_dev' from Cache 'imdb_dba_cache'
00:00:00000:00012:2013/05/29
13:10:23.27 kernel Virtual device 10
started using cache device i/o.
Virtual
cache device 'imdb_dba_data_dev' of size = 10 MB has been created on
cache
'imdb_dba_cache'.
4. Create an inmemory database on the devices created above
using create inmemory database command -
1> create inmemory database dba_imdb1
on imdb_dba_data_dev = '6M'
log on imdb_dba_data_dev = '2M'
with override, durability =
no_recovery2> 3> 4>
5>
go
00:00:00000:00012:2013/05/29
13:21:57.94 server Timestamp for
database 'dba_imdb1' is (0x0000, 0x0000162d).
CREATE
DATABASE: allocating 3072 logical pages (6.0 megabytes) on disk
'imdb_dba_data_dev'
(3072 logical pages requested).
CREATE
DATABASE: allocating 1024 logical pages (2.0 megabytes) on disk
'imdb_dba_data_dev'
(1024 logical pages requested).
Warning:
Database 'dba_imdb1' has a durability level of NO_RECOVERY; changes to
it
will be lost when you restart Adaptive Server.
Database
'dba_imdb1' is now online.
5. Your inmemory database is created now. Run sp_helpdb
dba_imdb1 and compare how it is different from that of a disk resident database
-
1> sp_helpdb dba_imdb1
2> go
name db_size owner dbid
created durability status
------------------ --------------------------
---------- --------
------------------------ ---------------------- ------------------
dba_imdb1 8.0 MB sa 8
May 29, 2013 no_recovery in-memory
(1 row affected)
name attribute_class attribute
int_value char_value comments
------------------
------------------------------ --------------------------
------------------
---------------------------- ----------------
dba_imdb1 buffer manager cache binding
1 imdb_dba_cache NULL
device_fragments
size usage
created
free kbytes
------------------------------------------------------------
--------------------------
----------------------------------------
--------------------------------------------------
--------------------------------
imdb_dba_data_dev
6.0 MB data only
May 29 2013 1:21PM
4416
imdb_dba_data_dev
2.0 MB log only
May 29 2013 1:21PM
not applicable
----------------------------------------------------------------------------------------------------------------------------
log only free kbytes = 2026
(return status = 0)
1>
No comments:
Post a Comment