You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@cassandra.apache.org by Eric Wong <Er...@solvians.com> on 2021/10/15 04:51:53 UTC
TWCS not cleaning up data as fast as expected
Hi all:
I am using Cassandra 4.0 and a table called 'minute' is configured to use TWCS.
Since it is TWCS, I do not have cassandra-reaper to perform any repair of the table. There is also no manual compaction performed on the table.
When we insert data into the db, we set a TTL of one day from Monday to Thursday. Then a custom TTL on records inserted on Friday for 3 days.
This is our table definition:
CREATE TABLE test_db.minute (
market smallint,
sin bigint,
field smallint,
slot timestamp,
close frozen<pricerecord>,
high frozen<pricerecord>,
low frozen<pricerecord>,
open frozen<pricerecord>,
PRIMARY KEY ((market, sin, field), slot)
) WITH CLUSTERING ORDER BY (slot ASC)
AND additional_write_policy = '99p'
AND bloom_filter_fp_chance = 0.01
AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
AND cdc = false
AND comment = ''
AND compaction = {'class': 'org.apache.cassandra.db.compaction.TimeWindowCompactionStrategy', 'compaction_window_size': '2', 'compaction_window_unit': 'HOURS', 'max_threshold': '32', 'min_threshold': '4', 'unsafe_aggressive_sstable_expiration': 'true'}
AND compression = {'chunk_length_in_kb': '64', 'class': 'org.apache.cassandra.io.compress.LZ4Compressor'}
AND crc_check_chance = 1.0
AND default_time_to_live = 86400
AND extensions = {}
AND gc_grace_seconds = 86400
AND max_index_interval = 2048
AND memtable_flush_period_in_ms = 0
AND min_index_interval = 128
AND read_repair = 'BLOCKING'
AND speculative_retry = '99p';
But on Oct 15th (Friday), I still see data files created on Monday still exist in the system.
# ls -ltrh *Data.db
-rw-r--r-- 1 cassandra cassandra 702M Oct 11 06:02 nb-158077-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.8G Oct 11 08:13 nb-158282-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.3G Oct 11 10:08 nb-158490-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.1G Oct 11 12:15 nb-158717-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.3G Oct 11 14:08 nb-158921-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.3G Oct 11 16:08 nb-159136-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.7G Oct 11 18:13 nb-159349-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.4G Oct 11 20:05 nb-159522-big-Data.db
-rw-r--r-- 1 cassandra cassandra 347M Oct 11 22:41 nb-159539-big-Data.db
-rw-r--r-- 1 cassandra cassandra 646M Oct 12 02:04 nb-159580-big-Data.db
-rw-r--r-- 1 cassandra cassandra 606M Oct 12 03:53 nb-159600-big-Data.db
-rw-r--r-- 1 cassandra cassandra 761M Oct 12 06:03 nb-159629-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.7G Oct 12 08:07 nb-159818-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.3G Oct 12 10:08 nb-160034-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.0G Oct 12 12:08 nb-160244-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.4G Oct 12 14:10 nb-160466-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.6G Oct 12 16:09 nb-160691-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.6G Oct 12 18:06 nb-160885-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.3G Oct 12 20:05 nb-161068-big-Data.db
-rw-r--r-- 1 cassandra cassandra 379M Oct 12 22:38 nb-161086-big-Data.db
-rw-r--r-- 1 cassandra cassandra 357M Oct 13 00:04 nb-161102-big-Data.db
-rw-r--r-- 1 cassandra cassandra 610M Oct 13 02:03 nb-161125-big-Data.db
-rw-r--r-- 1 cassandra cassandra 658M Oct 13 04:07 nb-161148-big-Data.db
-rw-r--r-- 1 cassandra cassandra 737M Oct 13 06:02 nb-161176-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.7G Oct 13 08:07 nb-161360-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.4G Oct 13 10:08 nb-161582-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.9G Oct 13 12:15 nb-161802-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.5G Oct 13 14:09 nb-162014-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.6G Oct 13 16:08 nb-162238-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.5G Oct 13 18:08 nb-162426-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.3G Oct 13 20:06 nb-162600-big-Data.db
-rw-r--r-- 1 cassandra cassandra 354M Oct 13 22:41 nb-162616-big-Data.db
-rw-r--r-- 1 cassandra cassandra 393M Oct 14 00:07 nb-162635-big-Data.db
-rw-r--r-- 1 cassandra cassandra 632M Oct 14 02:07 nb-162658-big-Data.db
-rw-r--r-- 1 cassandra cassandra 598M Oct 14 03:56 nb-162678-big-Data.db
-rw-r--r-- 1 cassandra cassandra 763M Oct 14 06:02 nb-162708-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.7G Oct 14 08:07 nb-162902-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.1G Oct 14 10:08 nb-163112-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.7G Oct 14 12:07 nb-163319-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.3G Oct 14 14:09 nb-163538-big-Data.db
-rw-r--r-- 1 cassandra cassandra 4.3G Oct 14 16:08 nb-163755-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.2G Oct 14 18:06 nb-163937-big-Data.db
-rw-r--r-- 1 cassandra cassandra 3.1G Oct 14 20:06 nb-164103-big-Data.db
-rw-r--r-- 1 cassandra cassandra 365M Oct 14 22:39 nb-164121-big-Data.db
-rw-r--r-- 1 cassandra cassandra 412M Oct 15 00:01 nb-164141-big-Data.db
-rw-r--r-- 1 cassandra cassandra 628M Oct 15 01:58 nb-164163-big-Data.db
-rw-r--r-- 1 cassandra cassandra 27M Oct 15 04:01 nb-164187-big-Data.db
-rw-r--r-- 1 cassandra cassandra 623M Oct 15 04:02 nb-164188-big-Data.db
I was expecting data from Monday to Wednesday already expired and deleted. From what I can see, the clean up of data only happened on Sunday. DB performance is ok from Monday to Wednesday. On Thursday & Friday, we have many slow query alerts. Then after Cassandra clean up the expired data from 'minute' on Sunday, performance of the db improves. Then on Thursday & Friday performance drop again.
I can see in debug.log "DEBUG [CompactionExecutor:11] 2021-10-15 04:42:41,089 TimeWindowCompactionStrategy.java:124 - TWCS skipping check for fully expired SSTables". So Cassandra sort of know some of the tables already expired. But the expired table files are not being deleted. I ran sstableexpiredblockers against the table, there is nothing blocking the clean up.
When I looked at the data file with sstablemetadata:
# sstablemetadata nb-158282-big-Data.db
SSTable: /var/lib/cassandra/data/test_db/minute-d7955270f31d11ea88fabb8dcc37b800/nb-158282-big
Partitioner: org.apache.cassandra.dht.Murmur3Partitioner
Bloom Filter FP chance: 0.01
Minimum timestamp: 1499178300000000 (07/04/2017 14:25:00)
Maximum timestamp: 1633939179628350 (10/11/2021 07:59:39)
SSTable min local deletion time: 1634018406 (10/12/2021 06:00:06)
SSTable max local deletion time: 1634198378 (10/14/2021 07:59:38)
Compressor: org.apache.cassandra.io.compress.LZ4Compressor
Compression ratio: 0.2617497307180066
TTL min: 86400 (1 day)
TTL max: 259200 (3 days)
First token: -9156529758809369559 (322:20632605:6)
Last token: 9211448821245734928 (3870:24273549:7)
minClusteringValues: [2017-07-04T14:25:00.000Z]
maxClusteringValues: [2021-10-11T07:58:00.000Z]
Estimated droppable tombstones: 1.1312506927559127
Therefore, I want to see if you guys can help me to identify why the expired tables are not being cleaned up based on the TTL.
Thanks, Eric
Re: TWCS not cleaning up data as fast as expected
Posted by Bowen Song <bo...@bso.ng>.
I noticed the table default TTL is 1 day, but the SSTable's max TTL is 3
days. Any idea why would this happen? Does any INSERT/UPDATE statement
have a TTL longer than the table's default? The min timestamp is also
very odd, it's in 2017. Do you insert data using very old timestamps?
On 15/10/2021 05:51, Eric Wong wrote:
>
> Hi all:
>
> I am using Cassandra 4.0 and a table called ‘minute’ is configured to
> use TWCS.
>
> Since it is TWCS, I do not have cassandra-reaper to perform any repair
> of the table. There is also no manual compaction performed on the table.
>
> When we insert data into the db, we set a TTL of one day from Monday
> to Thursday. Then a custom TTL on records inserted on Friday for 3 days.
>
> This is our table definition:
>
> CREATE TABLE test_db.minute (
>
> market smallint,
>
> sin bigint,
>
> field smallint,
>
> slot timestamp,
>
> close frozen<pricerecord>,
>
> high frozen<pricerecord>,
>
> low frozen<pricerecord>,
>
> open frozen<pricerecord>,
>
> PRIMARY KEY ((market, sin, field), slot)
>
> ) WITH CLUSTERING ORDER BY (slot ASC)
>
> AND additional_write_policy = '99p'
>
> AND bloom_filter_fp_chance = 0.01
>
> AND caching = {'keys': 'ALL', 'rows_per_partition': 'NONE'}
>
> AND cdc = false
>
> AND comment = ''
>
> AND compaction = {'class':
> 'org.apache.cassandra.db.compaction.TimeWindowCompactionStrategy',
> 'compaction_window_size': '2', 'compaction_window_unit': 'HOURS',
> 'max_threshold': '32', 'min_threshold': '4',
> 'unsafe_aggressive_sstable_expiration': 'true'}
>
> AND compression = {'chunk_length_in_kb': '64', 'class':
> 'org.apache.cassandra.io.compress.LZ4Compressor'}
>
> AND crc_check_chance = 1.0
>
> AND default_time_to_live = 86400
>
> AND extensions = {}
>
> AND gc_grace_seconds = 86400
>
> AND max_index_interval = 2048
>
> AND memtable_flush_period_in_ms = 0
>
> AND min_index_interval = 128
>
> AND read_repair = 'BLOCKING'
>
> AND speculative_retry = '99p';
>
> But on Oct 15^th (Friday), I still see data files created on Monday
> still exist in the system.
>
> # ls -ltrh *Data.db
>
> -rw-r--r-- 1 cassandra cassandra 702M Oct 11 06:02 nb-158077-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.8G Oct 11 08:13 nb-158282-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.3G Oct 11 10:08 nb-158490-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.1G Oct 11 12:15 nb-158717-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.3G Oct 11 14:08 nb-158921-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.3G Oct 11 16:08 nb-159136-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.7G Oct 11 18:13 nb-159349-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.4G Oct 11 20:05 nb-159522-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 347M Oct 11 22:41 nb-159539-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 646M Oct 12 02:04 nb-159580-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 606M Oct 12 03:53 nb-159600-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 761M Oct 12 06:03 nb-159629-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.7G Oct 12 08:07 nb-159818-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.3G Oct 12 10:08 nb-160034-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.0G Oct 12 12:08 nb-160244-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.4G Oct 12 14:10 nb-160466-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.6G Oct 12 16:09 nb-160691-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.6G Oct 12 18:06 nb-160885-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.3G Oct 12 20:05 nb-161068-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 379M Oct 12 22:38 nb-161086-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 357M Oct 13 00:04 nb-161102-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 610M Oct 13 02:03 nb-161125-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 658M Oct 13 04:07 nb-161148-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 737M Oct 13 06:02 nb-161176-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.7G Oct 13 08:07 nb-161360-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.4G Oct 13 10:08 nb-161582-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.9G Oct 13 12:15 nb-161802-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.5G Oct 13 14:09 nb-162014-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.6G Oct 13 16:08 nb-162238-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.5G Oct 13 18:08 nb-162426-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.3G Oct 13 20:06 nb-162600-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 354M Oct 13 22:41 nb-162616-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 393M Oct 14 00:07 nb-162635-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 632M Oct 14 02:07 nb-162658-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 598M Oct 14 03:56 nb-162678-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 763M Oct 14 06:02 nb-162708-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.7G Oct 14 08:07 nb-162902-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.1G Oct 14 10:08 nb-163112-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.7G Oct 14 12:07 nb-163319-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.3G Oct 14 14:09 nb-163538-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 4.3G Oct 14 16:08 nb-163755-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.2G Oct 14 18:06 nb-163937-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 3.1G Oct 14 20:06 nb-164103-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 365M Oct 14 22:39 nb-164121-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 412M Oct 15 00:01 nb-164141-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 628M Oct 15 01:58 nb-164163-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 27M Oct 15 04:01 nb-164187-big-Data.db
>
> -rw-r--r-- 1 cassandra cassandra 623M Oct 15 04:02 nb-164188-big-Data.db
>
> I was expecting data from Monday to Wednesday already expired and
> deleted. From what I can see, the clean up of data only happened on
> Sunday. DB performance is ok from Monday to Wednesday. On Thursday &
> Friday, we have many slow query alerts. Then after Cassandra clean up
> the expired data from ‘minute’ on Sunday, performance of the db
> improves. Then on Thursday & Friday performance drop again.
>
> I can see in debug.log “DEBUG [CompactionExecutor:11] 2021-10-15
> 04:42:41,089 TimeWindowCompactionStrategy.java:124 - TWCS skipping
> check for fully expired SSTables”. So Cassandra sort of know some of
> the tables already expired. But the expired table files are not being
> deleted. I ran sstableexpiredblockers against the table, there is
> nothing blocking the clean up.
>
> When I looked at the data file with sstablemetadata:
>
> # sstablemetadata nb-158282-big-Data.db
>
> SSTable:
> /var/lib/cassandra/data/test_db/minute-d7955270f31d11ea88fabb8dcc37b800/nb-158282-big
>
> Partitioner: org.apache.cassandra.dht.Murmur3Partitioner
>
> Bloom Filter FP chance: 0.01
>
> Minimum timestamp: 1499178300000000 (07/04/2017 14:25:00)
>
> Maximum timestamp: 1633939179628350 (10/11/2021 07:59:39)
>
> SSTable min local deletion time: 1634018406 (10/12/2021 06:00:06)
>
> SSTable max local deletion time: 1634198378 (10/14/2021 07:59:38)
>
> Compressor: org.apache.cassandra.io.compress.LZ4Compressor
>
> Compression ratio: 0.2617497307180066
>
> TTL min: 86400 (1 day)
>
> TTL max: 259200 (3 days)
>
> First token: -9156529758809369559 (322:20632605:6)
>
> Last token: 9211448821245734928 (3870:24273549:7)
>
> minClusteringValues: [2017-07-04T14:25:00.000Z]
>
> maxClusteringValues: [2021-10-11T07:58:00.000Z]
>
> Estimated droppable tombstones: 1.1312506927559127
>
> Therefore, I want to see if you guys can help me to identify why the
> expired tables are not being cleaned up based on the TTL.
>
> Thanks, Eric
>