Creating or altering tables to use DSE In-Memory

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

dse.yaml

The location of the dse.yaml file depends on the type of installation:
Package installations /etc/dse/dse.yaml
Tarball installations installation_location/resources/dse/conf/dse.yaml

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 statement. Use the compaction directive in the statement to specify the MemoryOnlyStrategy class and disable the key and row caches.

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

Altering an existing table to use DSE In-Memory

Use the 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:

ALTER TABLE employee WITH compaction= { 'class': 'MemoryOnlyStrategy' }
  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.

In cqlsh, use the command to view table properties:

DESCRIBE TABLE employee;
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 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 nodesync = {'enabled' : 'true'}
  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 megabytes (MB).

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'};