Creating or altering tables to use DSE In-Memory

Use CQL directives to create and alter tables to use DSE In-Memory.

Use CQL directives to create and alter tables to use DSE In-Memory and dse.yaml to limit the size of tables.

Creating a table to use DSE In-Memory

To create a table that uses DSE In-Memory, add a CQL directive to the CREATE TABLE statement. Use the compaction directive in the statement to specify the MemoryOnlyStrategy class, disable compression, and disable the key and row caches.

CREATE TABLE customers (
  uid text,
  fname text,
  lname text,
  PRIMARY KEY (uid) 
) WITH compaction= { 'class': 'MemoryOnlyStrategy' }
     AND compression = {'sstable_compression' : ''}
     AND caching = {'keys':'NONE', 'rows_per_partition':'NONE'};

Altering an existing table to use DSE In-Memory

Use the ALTER TABLE statement to change a traditional table to use in-memory, or to change an in-memory table to a traditional table. For example, use the DESCRIBE command for a table named employee. Verify that employee is a traditional table because the output of the DESCRIBE command does not include a line that looks something like:

compaction={'class': 'MemoryOnlyStrategy'} >

Alter the employee table to use DSE In-Memory and, as a best practice, disable caching:

ALTER TABLE employee WITH compaction= { 'class': 'MemoryOnlyStrategy' }
  AND compression = {'sstable_compression' : ''}
  AND caching = {'keys':'NONE', 'rows_per_partition':'NONE'};
After you alter the table, rewrite existing SSTables:
$ nodetool upgradesstables -a <keyspacename> <tablename>

Use the --jobs option to set the number of SStables that upgrade simultaneously. The default setting is 2, which minimizes impact on the cluster. Set to 0 to use all available compaction threads.

This output shows that the table uses DSE In-Memory:
CREATE TABLE employee (
  uid text PRIMARY KEY,
  fname text,
  lname text
) WITH bloom_filter_fp_chance = 0.01
  AND caching = '{"keys":"NONE", "rows_per_partition":"NONE"}'
  AND comment = ''
  AND compaction = {'min_threshold': '4', 'class': 'org.apache.cassandra.db.compaction.MemoryOnlyStrategy', 
'max_threshold': '32'}
  AND compression = {}
  AND dclocal_read_repair_chance = 0.1
  AND default_time_to_live = 0
  AND gc_grace_seconds = 864000
  AND max_index_interval = 2048
  AND memtable_flush_period_in_ms = 0
  AND min_index_interval = 128
  AND read_repair_chance = 0.0
  AND speculative_retry = '99.0PERCENTILE';
When memtable_flush_period_in_ms=0, the memtable will flush when:
  • the flush threshold is met
  • on shutdown
  • on nodetool flush
  • when commitlogs get full

Limiting the size of tables

Use the max_memory_to_lock_fraction or max_memory_to_lock_mb configuration option in the dse.yaml file to specify how much system memory to use for all in-memory tables.
max_memory_to_lock_fraction Specify a fraction of the system memory. The default value of 0.20 specifies to use up to 20% of system memory.
max_memory_to_lock_mb Specify a maximum amount of memory in MB.
The location of the dse.yaml file depends on the type of installation:
Installer-Services /etc/dse/dse.yaml
Package installations /etc/dse/dse.yaml
Installer-No Services install_location/resources/dse/conf/dse.yaml
Tarball installations install_location/resources/dse/conf/dse.yaml

Disabling caching on tables

DataStax recommends disabling caching on tables that use the DSE In-Memory option. If caching is not disabled, a warning is logged. Set the table caching property to disable both types of caching:
ALTER TABLE customers WITH caching = {'keys':'NONE', 
  'rows_per_partition':'NONE'};