We have a table in Apache Cassandra cluster with TWCS strategy that stores data which is real time in nature and the table structure like below.
CREATE TABLE "updates".update_table (
symbol text,
datasource int,
sumupduration smallint,
starttime timestamp,
col3 date,
col4 timestamp,
col5 decimal,
col6 decimal,
col7 decimal,
col8 decimal,
...
col14 int,
PRIMARY KEY ((symbol, datasource, sumupduration), starttime)
) WITH CLUSTERING ORDER BY (starttime DESC)
AND bloom_filter_fp_chance = 0.01
AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND comment = ''
AND compaction = {'class': 'org.apache.cassandra.db.compaction.TimeWindowCompactionStrategy', 'compaction_window_size': '1', 'compaction_window_unit': 'DAYS', 'max_threshold': '32', 'min_threshold': '4'}
AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
AND crc_check_chance = 1.0
AND dclocal_read_repair_chance = 0.1
AND default_time_to_live = 2592000
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 = '99PERCENTILE';
We are seeing continuous performance issue with this table and too many pending compactions on the node at any given time. Also we are seeing too many tombstone warning messages like shown below.
WARN [ReadStage-2] 2021-04-14 02:56:04,117 ReadCommand.java:522 - Read 425 live rows and 100001 tombstone cells for query SELECT * FROM updates.update_table WHERE symbol, datasource, sumupduration = A:BCDEF, 1234, 60 AND starttime > 2021-03-15 10:01-0400 AND starttime <= 2021-03-10 01:00-0500 LIMIT 1000 (see tombstone_warn_threshold)
We are thinking that this particular table is not best fit for TWCS as we see there are many tombstones getting generated which means the data is getting updated/deleted from application side. Is there a best way to confirm this?
Can you please help choose a right compaction strategy for this table. Also do we need frequent repairs for a TWCS table?. Repairs on this table taking longer time (few days some times).
Our cluster size is 8 nodes( 32cpus and 64GB memory on each node) and 2 data centers(4nodes in each DC).
Thanks,
Lavaraja.
