Rotation policy

[KleinenberG]

Hello. Please, can i get more infogmation about rotation log?
I setup both variables

      LABELS_DAYS: 7,
      SAMPLES_DAYS: 7,

cloki :) SELECT * FROM settings
┌──────────fingerprint─┬─type───┬─name────────────────┬─value──────┬───────────────────inserted_at─┐
│            990984054 │ rotate │ v3_samples_days     │ 7          │ 2022-10-18 06:23:40.000000000 │
│           4103757074 │ rotate │ v3_time_series_days │ 7          │ 2022-10-18 06:23:40.000000000 │
│ 14553193486094442270 │ update │ v3_1                │ 1666074220 │ 2022-10-18 06:23:40.000000000 │
└──────────────────────┴────────┴─────────────────────┴────────────┴───────────────────────────────┘

as I understand it, the rotation settings are applied

but I see, in file system, files older than 7 days

drwxr-x--- 2 systemd-resolve systemd-journal 4096 Dec 14 12:46 202212_657575_657819_57
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Dec 14 12:16 202212_656880_657574_159
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Dec 14 10:49 202212_655946_656879_166
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Dec 14 09:01 202212_599395_655945_168
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Dec  9 12:17 202212_540183_599394_173
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Dec  5 17:29 202211_500841_503460_99
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Dec  4 08:07 202212_503465_540182_167
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Dec  1 03:15 202211_503461_503464_1
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Nov 30 21:32 202211_497919_500840_162
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Nov 30 15:33 202211_482366_497918_162
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Nov 29 17:04 202210_1_158056_206
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Nov 29 08:58 202211_420098_482365_177
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Nov 27 10:22 202211_158057_331915_211
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Nov 23 23:33 202211_331916_420097_192
drwxr-x--- 2 systemd-resolve systemd-journal 4096 Oct 18 09:34 detached
-rw-r----- 1 systemd-resolve systemd-journal    1 Oct 18 09:34 format_version.txt

What query should be executed to check that the old data is really in the database?
Or i dont understand how work this rotation policy!?
Thanks.

[R-omk]

for debug purposes exec

select * from system.parts where table = 'samples_v3' and active = 1

look at the fields delete_ttl_info_min delete_ttl_info_max

also look at SHOW CREATE TABLE qryn.samples_v3

there should be a TTL section

[KleinenberG]

select delete_ttl_info_min delete_ttl_info_max from system.parts where table = 'samples_v3' and active = 1

┌─delete_ttl_info_min─┬─delete_ttl_info_max─┐
│ 2022-12-11 00:00:00 │ 2022-12-11 18:14:52 │
│ 2022-12-11 18:14:49 │ 2022-12-11 21:12:20 │
│ 2022-12-11 21:11:22 │ 2022-12-11 22:02:14 │
│ 2022-12-11 22:02:13 │ 2022-12-11 22:51:42 │
│ 2022-12-11 22:51:41 │ 2022-12-11 23:58:54 │
│ 2022-12-11 23:59:59 │ 2022-12-11 23:59:59 │
│ 2022-12-12 00:00:00 │ 2022-12-12 18:02:14 │
│ 2022-12-12 18:02:13 │ 2022-12-12 21:38:28 │
│ 2022-12-12 21:38:27 │ 2022-12-12 22:59:43 │
│ 2022-12-12 22:59:42 │ 2022-12-12 23:23:34 │
│ 2022-12-12 23:23:33 │ 2022-12-12 23:44:31 │
│ 2022-12-12 23:44:30 │ 2022-12-12 23:59:00 │
│ 2022-12-12 23:59:00 │ 2022-12-12 23:59:59 │
│ 2022-12-12 23:59:59 │ 2022-12-12 23:59:59 │
│ 2022-12-13 00:00:00 │ 2022-12-13 17:28:57 │
│ 2022-12-13 17:28:56 │ 2022-12-13 20:42:49 │
│ 2022-12-13 20:42:47 │ 2022-12-13 21:23:49 │
│ 2022-12-13 21:23:47 │ 2022-12-13 22:13:53 │
│ 2022-12-13 22:13:52 │ 2022-12-13 23:15:38 │
│ 2022-12-13 23:15:37 │ 2022-12-13 23:32:48 │
│ 2022-12-13 23:32:47 │ 2022-12-13 23:42:21 │
│ 2022-12-13 23:42:19 │ 2022-12-13 23:59:36 │
│ 2022-12-13 23:59:35 │ 2022-12-13 23:59:59 │
│ 2022-12-13 23:59:58 │ 2022-12-13 23:59:59 │
│ 2022-12-14 00:00:00 │ 2022-12-14 17:18:45 │
│ 2022-12-14 17:18:43 │ 2022-12-14 19:25:46 │
│ 2022-12-14 19:25:45 │ 2022-12-14 20:23:12 │
│ 2022-12-14 20:23:11 │ 2022-12-14 20:58:32 │
│ 2022-12-14 20:58:31 │ 2022-12-14 22:02:00 │
│ 2022-12-14 22:01:59 │ 2022-12-14 23:11:25 │
│ 2022-12-14 23:11:24 │ 2022-12-14 23:58:59 │
│ 2022-12-14 23:58:58 │ 2022-12-14 23:59:59 │
│ 2022-12-14 23:59:59 │ 2022-12-14 23:59:59 │
│ 2022-12-15 00:00:00 │ 2022-12-15 19:50:07 │
│ 2022-12-15 19:50:06 │ 2022-12-15 23:34:30 │
│ 2022-12-15 23:34:29 │ 2022-12-15 23:55:16 │
│ 2022-12-15 23:55:15 │ 2022-12-15 23:58:43 │
│ 2022-12-15 23:58:42 │ 2022-12-15 23:59:57 │
│ 2022-12-15 23:59:56 │ 2022-12-15 23:59:59 │
│ 2022-12-16 00:00:00 │ 2022-12-16 07:33:01 │
│ 2022-12-16 07:33:00 │ 2022-12-16 22:09:16 │
│ 2022-12-16 22:09:15 │ 2022-12-16 23:05:30 │
│ 2022-12-16 23:05:28 │ 2022-12-16 23:19:32 │
│ 2022-12-16 23:19:31 │ 2022-12-16 23:50:23 │
│ 2022-12-16 23:50:16 │ 2022-12-16 23:54:05 │
│ 2022-12-16 23:54:04 │ 2022-12-16 23:57:32 │
│ 2022-12-16 23:57:31 │ 2022-12-16 23:59:04 │
│ 2022-12-16 23:59:03 │ 2022-12-16 23:59:48 │
│ 2022-12-16 23:59:47 │ 2022-12-16 23:59:59 │
│ 2022-12-16 23:59:59 │ 2022-12-16 23:59:59 │
│ 2022-12-17 00:00:00 │ 2022-12-17 04:52:12 │
│ 2022-12-17 04:52:10 │ 2022-12-17 10:43:47 │
│ 2022-12-17 10:43:46 │ 2022-12-17 13:57:45 │
│ 2022-12-17 13:57:44 │ 2022-12-17 16:50:23 │
│ 2022-12-17 16:50:22 │ 2022-12-17 20:04:43 │
│ 2022-12-17 20:04:42 │ 2022-12-17 21:00:28 │
│ 2022-12-17 21:00:27 │ 2022-12-17 23:47:29 │
│ 2022-12-17 23:47:28 │ 2022-12-17 23:59:19 │
│ 2022-12-17 23:59:18 │ 2022-12-17 23:59:48 │
│ 2022-12-17 23:59:47 │ 2022-12-17 23:59:59 │
│ 2022-12-17 23:59:58 │ 2022-12-17 23:59:59 │
│ 2022-12-18 00:00:00 │ 2022-12-18 17:14:09 │
│ 2022-12-18 17:14:08 │ 2022-12-18 20:31:44 │
│ 2022-12-18 20:31:43 │ 2022-12-18 21:05:50 │
│ 2022-12-18 21:05:49 │ 2022-12-18 22:13:46 │
│ 2022-12-18 22:13:45 │ 2022-12-18 23:21:03 │
│ 2022-12-18 23:21:02 │ 2022-12-18 23:27:02 │
│ 2022-12-18 23:27:01 │ 2022-12-18 23:40:00 │
│ 2022-12-18 23:39:59 │ 2022-12-18 23:54:09 │
│ 2022-12-18 23:54:08 │ 2022-12-18 23:56:08 │
│ 2022-12-18 23:56:07 │ 2022-12-18 23:59:16 │
│ 2022-12-18 23:59:15 │ 2022-12-18 23:59:47 │
│ 2022-12-18 23:59:46 │ 2022-12-18 23:59:59 │
│ 2022-12-18 23:59:59 │ 2022-12-18 23:59:59 │
│ 2022-12-19 00:00:00 │ 2022-12-19 17:13:13 │
│ 2022-12-19 17:13:11 │ 2022-12-19 20:45:25 │
│ 2022-12-19 20:45:23 │ 2022-12-19 21:51:35 │
│ 2022-12-19 21:51:34 │ 2022-12-19 22:46:24 │
│ 2022-12-19 22:46:23 │ 2022-12-19 23:49:38 │
│ 2022-12-19 23:49:37 │ 2022-12-19 23:52:48 │
│ 2022-12-19 23:52:47 │ 2022-12-19 23:54:26 │
│ 2022-12-19 23:54:25 │ 2022-12-19 23:56:54 │
│ 2022-12-19 23:56:53 │ 2022-12-19 23:58:33 │
│ 2022-12-19 23:58:32 │ 2022-12-19 23:59:45 │
│ 2022-12-19 23:59:44 │ 2022-12-19 23:59:59 │
│ 2022-12-19 23:59:59 │ 2022-12-19 23:59:59 │
│ 2022-12-20 00:00:00 │ 2022-12-20 17:49:52 │
│ 2022-12-20 17:49:51 │ 2022-12-20 20:49:05 │
│ 2022-12-20 20:49:04 │ 2022-12-20 21:34:59 │
│ 2022-12-20 21:34:58 │ 2022-12-20 23:30:43 │
│ 2022-12-20 23:30:42 │ 2022-12-20 23:38:55 │
│ 2022-12-20 23:38:54 │ 2022-12-20 23:45:40 │
│ 2022-12-20 23:45:39 │ 2022-12-20 23:51:46 │
│ 2022-12-20 23:51:45 │ 2022-12-20 23:54:52 │
│ 2022-12-20 23:54:51 │ 2022-12-20 23:58:10 │
│ 2022-12-20 23:58:08 │ 2022-12-20 23:58:52 │
│ 2022-12-20 23:58:51 │ 2022-12-20 23:59:44 │
│ 2022-12-20 23:59:43 │ 2022-12-20 23:59:59 │
│ 2022-12-20 23:59:59 │ 2022-12-20 23:59:59 │
│ 2022-12-21 00:00:00 │ 2022-12-21 16:13:38 │
│ 2022-12-21 16:13:38 │ 2022-12-21 16:48:07 │
│ 2022-12-21 16:48:06 │ 2022-12-21 16:51:40 │
│ 2022-12-21 16:51:39 │ 2022-12-21 16:55:00 │
│ 2022-12-21 16:54:58 │ 2022-12-21 16:56:53 │
│ 2022-12-21 16:56:51 │ 2022-12-21 16:59:02 │
│ 2022-12-21 16:58:59 │ 2022-12-21 16:59:37 │
│ 2022-12-21 16:59:36 │ 2022-12-21 16:59:50 │
│ 2022-12-21 16:59:49 │ 2022-12-21 16:59:53 │
│ 2022-12-21 16:59:52 │ 2022-12-21 16:59:53 │
└─────────────────────┴─────────────────────┘

SHOW CREATE TABLE qryn.samples_v3

Received exception from server (version 22.1.3):
Code: 81. DB::Exception: Received from localhost:9000. DB::Exception: Database qryn doesn't exist. (UNKNOWN_DATABASE)

[lmangani]

Which version of ClickHouse are you using?

[KleinenberG]

Which version of ClickHouse are you using?

ClickHouse server version 22.1.3.7 (official build).

[KleinenberG]

data table settings

┌─statement──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
(
    `fingerprint` UInt64,
    `timestamp_ns` Int64 CODEC(DoubleDelta),
    `value` Float64 CODEC(Gorilla),
    `string` String
)
ENGINE = MergeTree
PARTITION BY toStartOfDay(toDateTime(timestamp_ns / 1000000000))
ORDER BY timestamp_ns
TTL toDateTime(timestamp_ns / 1000000000) + toIntervalDay(7)
SETTINGS index_granularity = 8192, ttl_only_drop_parts = 1, merge_with_ttl_timeout = 3600 │
└────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘

[akvlad]

Sometimes clickhouse just doesn't drop by TTL.
@KleinenberG Can you please do another request?
select distinct partition from system.parts where table = 'samples_v3' and database ='<YOUR_DATABASE_HERE> and active = 1';
Let's see the minimum day you have stored.

[KleinenberG]

@akvlad This fresh output

┌─partition───────────┐
│ 2022-12-09 00:00:00 │
│ 2022-12-10 00:00:00 │
│ 2022-12-11 00:00:00 │
│ 2022-12-12 00:00:00 │
│ 2022-12-13 00:00:00 │
│ 2022-12-14 00:00:00 │
│ 2022-12-15 00:00:00 │
│ 2022-12-16 00:00:00 │
│ 2022-12-17 00:00:00 │
└─────────────────────┘

the first line should have been deleted yesterday

┌─partition───────────┬─name─────────────────────────────┬─delete_ttl_info_min─┬─delete_ttl_info_max─┐
│ 2022-12-09 00:00:00 │ 1670544000_39623624_40438585_104 │ 2022-12-16 14:27:18 │ 2022-12-16 23:59:59 │

but deleted only today after a manual start optimize table samples_v3 FINAL;

also i see in log file many errors
<Error> executeQuery: Code: 117. DB::ParsingException: Size of JSON object is extremely large. Expected not greater than 10485760 bytes, but current is 104857606 bytes per row. Increase the value setting 'min_chunk_bytes_for_parallel_parsing' or check your data manually, most likely JSON is malformed: While executing ParallelParsingBlockInputFormat. (INCORRECT_DATA) (version 22.1.3.7 (official build)) (from 127.0.0.1:49648) (in query: INSERT INTO cloki.samples_v3(fingerprint, timestamp_ns, value, string) FORMAT JSONEachRow ), Stack trace (when copying this message, always include the lines below):

could this be the reason?
Thanks a lot for your help