Skip to content

Instantly share code, notes, and snippets.

@REASY

REASY/waf-ch.sql

Created October 22, 2025 14:02
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save REASY/cfa46aca4181885a99b6e0476d5176a4 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save REASY/cfa46aca4181885a99b6e0476d5176a4 to your computer and use it in GitHub Desktop.
Download ZIP
Raw
waf-ch.sql
CREATE TABLE IF NOT EXISTS waf_events
(
event_time DateTime64(3),
data_date Date DEFAULT toDate(event_time),
hour_key UInt32 DEFAULT toYYYYMMDD(event_time) * 100 + toHour(event_time),
bucket_s DateTime DEFAULT toStartOfSecond(event_time),
topic LowCardinality(String),
partition Int32,
offset Int64,
queue_time DateTime64(3),
app_receive_time DateTime64(3),
app_parse_time DateTime64(3),
app_enqueue_time DateTime64(3),
app_dequeue_time DateTime64(3),
id String,
server String,
ip IPv4,
request_url Nullable(String),
full_request_url Nullable(String),
referrer Nullable(String),
user_agent Nullable(String),
session_id Nullable(String),
user_id Nullable(String),
ajax_request Bool,
known_bot Bool,
known_bot_name Nullable(String),
mitigation_action Nullable(String),
traffic_type Nullable(String),
accept_language Nullable(String),
whitelabel_id Int32,
resource Nullable(String),
platform Nullable(String),
subnet String,
is_new_user_id Bool,
excluded_from_bot_detection Bool,
excluded_from_resource_capacity Bool,
) ENGINE = MergeTree
PARTITION BY hour_key
ORDER BY (bucket_s)
SETTINGS index_granularity = 8192;
CREATE TABLE IF NOT EXISTS fs_styx_userid_resource_v2
(
-- Partitioning Key
day Date,
-- Grouping Keys
bucket_s DateTime,
feature_key String,
-- Aggregate Function States
count AggregateFunction(count),
last_seen_event_time AggregateFunction(max, DateTime64(3))
) ENGINE = AggregatingMergeTree()
PARTITION BY day
ORDER BY (bucket_s, feature_key)
TTL day + INTERVAL 7 DAY DELETE;
CREATE MATERIALIZED VIEW IF NOT EXISTS fs_styx_userid_resource_v2_mv
TO fs_styx_userid_resource_v2 AS
SELECT toDate(event_time) AS day,
bucket_s,
concat(user_id, '_', resource) AS feature_key,
countState() AS count,
maxState(event_time) AS last_seen_event_time
FROM waf_events
WHERE excluded_from_bot_detection != TRUE AND coalesce (resource, 'default') != 'default' AND is_new_user_id = FALSE
GROUP BY day, bucket_s, feature_key;
CREATE TABLE IF NOT EXISTS fs_styx_userid_resource_v2_1m
(
-- Partitioning Key
day Date,
-- Grouping Keys
bucket_m DateTime,
feature_key String,
-- Aggregate Function States
count AggregateFunction(count),
last_seen_event_time AggregateFunction(max, DateTime64(3))
) ENGINE = AggregatingMergeTree()
PARTITION BY day
ORDER BY (bucket_m, feature_key)
TTL day + INTERVAL 7 DAY DELETE;
CREATE MATERIALIZED VIEW IF NOT EXISTS fs_styx_userid_resource_v2_1m_mv
TO fs_styx_userid_resource_v2_1m AS
SELECT toDate(bucket_s) AS day,
toStartOfMinute(bucket_s) AS bucket_m,
feature_key,
countMergeState(count) AS count,
maxMergeState(last_seen_event_time) AS last_seen_event_time
FROM fs_styx_userid_resource_v2
WHERE bucket_s >= toTimeZone(parseDateTime64BestEffort('2025-10-22 03:11:56', 3, 'Asia/Singapore'), 'UTC')
GROUP BY day, bucket_m, feature_key;
@REASY
Copy link
Copy Markdown
Author

REASY commented Oct 22, 2025 •
edited
Loading

Query to fetch in 900 second window:

WITH
    now() AS E,
    (E - toIntervalSecond(900)) AS S,
    toStartOfMinute(S) AS Sm,
    toStartOfMinute(E) AS Em,
    (Sm + INTERVAL 1 MINUTE) AS Sm_next
SELECT
    feature_key,
    countMerge(count) AS count,
    maxMerge(last_seen_event_time) AS last_seen_event_time
FROM
    (
        -- 1) Head partial seconds: S .. min(E, Sm_next-1s)
        SELECT
            feature_key,
            countMergeState(count) AS count,
            maxMergeState(last_seen_event_time) AS last_seen_event_time
        FROM fs_styx_userid_resource_v2
        WHERE bucket_s >= S
          AND bucket_s < Sm_next
          AND bucket_s <= E
        GROUP BY feature_key

        UNION ALL

        -- 2) Full minutes strictly inside (S, E)
        SELECT
            feature_key,
            countMergeState(count) AS count,
            maxMergeState(last_seen_event_time) AS last_seen_event_time
        FROM fs_styx_userid_resource_v2_1m
        WHERE bucket_m >= Sm_next
          AND bucket_m < Em
        GROUP BY feature_key

        UNION ALL

        -- 3) Tail partial seconds: Em .. E
        SELECT
            feature_key,
            countMergeState(count) AS count,
            maxMergeState(last_seen_event_time) AS last_seen_event_time
        FROM fs_styx_userid_resource_v2
        WHERE bucket_s >= Em
          AND bucket_s <= E
        GROUP BY feature_key
        )
GROUP BY feature_key;

@REASY
Copy link
Copy Markdown
Author

REASY commented Oct 22, 2025

system.query_log for a slow query

{
    "formatReadableSize(result_bytes)": "348.05 MiB",
    "percent_of_used_rows": 74.1902922604228,
    "hostname": "3855f1d48dd4",
    "type": "QueryFinish",
    "event_date": "2025-10-22",
    "event_time": "2025-10-22T15:08:28Z[UTC]",
    "event_time_microseconds": "2025-10-22T15:08:28.749151Z[UTC]",
    "query_start_time": "2025-10-22T15:08:18Z[UTC]",
    "query_start_time_microseconds": "2025-10-22T15:08:18.440938Z[UTC]",
    "query_duration_ms": 10315,
    "read_rows": 5415102,
    "read_bytes": 600515147,
    "written_rows": 0,
    "written_bytes": 0,
    "result_rows": 4017480,
    "result_bytes": 364959424,
    "memory_usage": 2166696133,
    "current_database": "default",
    "query": "\n            WITH\n                now() AS E,\n                (E - toIntervalSecond(900)) AS S,\n                toStartOfMinute(S) AS Sm,\n                toStartOfMinute(E) AS Em,\n                (Sm + INTERVAL 1 MINUTE) AS Sm_next\n            SELECT\n                feature_key,\n                countMerge(count) AS count,\n                maxMerge(last_seen_event_time) AS last_seen_event_time\n            FROM\n                (\n                    -- 1) Head partial seconds: S .. min(E, Sm_next-1s)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= S\n                      AND bucket_s < Sm_next\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 2) Full minutes strictly inside (S, E)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2_1m\n                    WHERE bucket_m >= Sm_next\n                      AND bucket_m < Em\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 3) Tail partial seconds: Em .. E\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= Em\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n                    )\n            GROUP BY feature_key;\n            \n",
    "formatted_query": "",
    "normalized_query_hash": "<failed to load>\njava.sql.SQLException: Method: getLong(\"normalized_query_hash\") encountered an exception.\n\tat com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:74)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:320)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:175)\n\tin CHouseJdbcHelper.tryGetObject(CHouseJdbcHelper.java:156)\nCaused by: java.lang.ArithmeticException: integer overflow: 16443151356930779613 cannot be presented as long\n\tat com.clickhouse.client.api.data_formats.internal.NumberConverter.toLong(NumberConverter.java:87)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.readNumberValue(AbstractBinaryFormatReader.java:378)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.getLong(AbstractBinaryFormatReader.java:402)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:314)\n\t... 22 more\n",
    "query_kind": "Select",
    "databases": ["default"],
    "tables": ["default.fs_styx_userid_resource_v2", "default.fs_styx_userid_resource_v2_1m"],
    "columns": ["default.fs_styx_userid_resource_v2.bucket_s", "default.fs_styx_userid_resource_v2.count", "default.fs_styx_userid_resource_v2.feature_key", "default.fs_styx_userid_resource_v2.last_seen_event_time", "default.fs_styx_userid_resource_v2_1m.bucket_m", "default.fs_styx_userid_resource_v2_1m.count", "default.fs_styx_userid_resource_v2_1m.feature_key", "default.fs_styx_userid_resource_v2_1m.last_seen_event_time"],
    "partitions": ["default.fs_styx_userid_resource_v2.20251022", "default.fs_styx_userid_resource_v2_1m.20251022"],
    "projections": [],
    "views": [],
    "exception_code": 0,
    "exception": "",
    "stack_trace": "",
    "is_initial_query": 1,
    "user": "default",
    "query_id": "2a4b68c8-1eaf-4dc3-b4c4-5493fe077de6",
    "address": "/172.18.0.3",
    "port": 51156,
    "initial_user": "default",
    "initial_query_id": "2a4b68c8-1eaf-4dc3-b4c4-5493fe077de6",
    "initial_address": "/172.18.0.3",
    "initial_port": 51156,
    "initial_query_start_time": "2025-10-22T15:08:18Z[UTC]",
    "initial_query_start_time_microseconds": "2025-10-22T15:08:18.440938Z[UTC]",
    "interface": 2,
    "is_secure": 0,
    "os_user": "",
    "client_hostname": "",
    "client_name": "",
    "client_revision": 0,
    "client_version_major": 0,
    "client_version_minor": 0,
    "client_version_patch": 0,
    "script_query_number": 0,
    "script_line_number": 0,
    "http_method": 1,
    "http_user_agent": "clickhouse-rs/0.14.0 (lv:rust/1.89.0, os:linux)",
    "http_referer": "",
    "forwarded_for": "",
    "quota_key": "",
    "distributed_depth": 0,
    "revision": 54510,
    "log_comment": "",
    "thread_ids": [910388, 911467, 911545, 911577, 911202, 911533, 911503, 911597, 910046, 909871, 911205, 907041, 910625, 909051, 908222, 909225, 909619, 911493, 908538, 905564, 910098, 908719, 908176, 910737, 908373, 910828, 910703, 910720, 910195, 908308, 909096, 907109, 910547, 910376, 910111, 910863, 908321, 911199, 910805, 910864, 910922, 908658, 910825, 908625, 909019, 908355, 907370, 910925, 906788, 910050, 907883, 909452, 907609, 910904, 909250, 908265, 911520, 907974, 909882, 911064, 910909, 908348, 906801, 905540, 910819, 910769, 909981, 910854, 910030, 908060, 909888, 910873, 909829, 910898, 910219, 911046, 907865, 909999, 910091, 910100, 909197, 909993, 910090, 910077, 910453, 911627, 910839, 910051, 910924, 909954, 910806, 910115, 910794, 909574, 911029, 910640, 910452, 910058, 911014, 910226, 910717, 910323, 907930, 910840, 910699, 910763, 910860, 910851, 910463, 909260, 910639, 910930, 910833, 910830, 910927, 911024, 910918, 910871, 910812, 910911, 910910, 910716, 909317, 911063, 904144, 911039, 911604, 908452, 910816, 869397],
    "peak_threads_usage": 87,
    "ProfileEvents": {
      "Query": 1,
      "SelectQuery": 1,
      "InitialQuery": 1,
      "QueriesWithSubqueries": 4,
      "SelectQueriesWithSubqueries": 4,
      "SelectQueriesWithPrimaryKeyUsage": 3,
      "FileOpen": 15,
      "ReadBufferFromFileDescriptorReadBytes": 136869636,
      "ReadCompressedBytes": 124512119,
      "CompressedReadBufferBlocks": 1832,
      "CompressedReadBufferBytes": 403149070,
      "OpenedFileCacheHits": 213,
      "OpenedFileCacheMisses": 15,
      "OpenedFileCacheMicroseconds": 356,
      "IOBufferAllocs": 524,
      "IOBufferAllocBytes": 220503914,
      "ArenaAllocChunks": 2785,
      "ArenaAllocBytes": 1326977024,
      "FunctionExecute": 2870,
      "MarkCacheHits": 228,
      "QueryConditionCacheHits": 5,
      "QueryConditionCacheMisses": 17,
      "CreatedReadBufferOrdinary": 228,
      "DiskReadElapsedMicroseconds": 120141,
      "NetworkSendElapsedMicroseconds": 4174702,
      "NetworkSendBytes": 201664449,
      "GlobalThreadPoolLockWaitMicroseconds": 56468,
      "GlobalThreadPoolJobs": 136,
      "LocalThreadPoolExpansions": 135,
      "LocalThreadPoolShrinks": 110,
      "LocalThreadPoolThreadCreationMicroseconds": 205930,
      "LocalThreadPoolLockWaitMicroseconds": 3965,
      "LocalThreadPoolJobs": 369,
      "AnalyzePatchRangesMicroseconds": 8,
      "SelectedParts": 11,
      "SelectedPartsTotal": 46,
      "SelectedRanges": 11,
      "SelectedMarks": 666,
      "SelectedMarksTotal": 53021,
      "SelectedRows": 5415102,
      "SelectedBytes": 600515147,
      "RowsReadByMainReader": 5409873,
      "RowsReadByPrewhereReaders": 5415102,
      "FilteringMarksWithPrimaryKeyMicroseconds": 1316,
      "WaitMarksLoadMicroseconds": 45457,
      "ContextLock": 217,
      "ContextLockWaitMicroseconds": 1,
      "RWLockAcquiredReadLocks": 3,
      "PartsLockHoldMicroseconds": 18,
      "PartsLockWaitMicroseconds": 47,
      "RealTimeMicroseconds": 675733199,
      "UserTimeMicroseconds": 10642519,
      "SystemTimeMicroseconds": 5401969,
      "SoftPageFaults": 532944,
      "OSCPUWaitMicroseconds": 28775762,
      "OSCPUVirtualTimeMicroseconds": 16043601,
      "OSReadChars": 137489530,
      "OSWriteChars": 350536,
      "QueryProfilerRuns": 703,
      "ThreadPoolReaderPageCacheHit": 1131,
      "ThreadPoolReaderPageCacheHitBytes": 136869636,
      "ThreadPoolReaderPageCacheHitElapsedMicroseconds": 120141,
      "SynchronousReadWaitMicroseconds": 121271,
      "AggregationHashTablesInitializedAsTwoLevel": 53,
      "InterfaceHTTPSendBytes": 201664449,
      "ConcurrencyControlSlotsGranted": 1,
      "ConcurrencyControlSlotsAcquired": 63,
      "ConcurrencyControlSlotsAcquiredNonCompeting": 1,
      "IndexBinarySearchAlgorithm": 46
    },
    "Settings": {
      "use_uncompressed_cache": "0",
      "load_balancing": "in_order",
      "log_queries": "1",
      "readonly": "2",
      "max_memory_usage": "21474836480",
      "parallel_replicas_for_cluster_engines": "0"
    },
    "used_aggregate_functions": ["count", "max", "min"],
    "used_aggregate_function_combinators": ["State", "Merge"],
    "used_database_engines": [],
    "used_data_type_families": [],
    "used_dictionaries": [],
    "used_formats": ["RowBinaryWithNamesAndTypes"],
    "used_functions": ["subtractSeconds", "now", "greaterOrEquals", "minus", "and", "toIntervalMinute", "toStartOfMinute", "plus", "toIntervalSecond", "notIn", "lessOrEquals", "less", "addMinutes"],
    "used_storages": [],
    "used_table_functions": [],
    "used_executable_user_defined_functions": [],
    "used_sql_user_defined_functions": [],
    "used_row_policies": [],
    "used_privileges": ["SELECT(feature_key, count, last_seen_event_time, bucket_m) ON default.fs_styx_userid_resource_v2_1m", "SELECT(feature_key, count, last_seen_event_time, bucket_s) ON default.fs_styx_userid_resource_v2"],
    "missing_privileges": [],
    "transaction_id": [0, 0,
      "00000000-0000-0000-0000-000000000000"
    ],
    "query_cache_usage": "None",
    "asynchronous_read_counters": {
    }
  }

@REASY
Copy link
Copy Markdown
Author

REASY commented Oct 23, 2025 

"query_duration_ms": 10315,
  "result_rows": 4017480,
  "result_bytes": 364959424,
"ProfileEvents": {
    "NetworkSendElapsedMicroseconds": 4174702,
    "NetworkSendBytes": 201664449,

This query spent 40.4% (4174.702 ms / 10315 ms) of time sending 192.3Mi bytes of compressed query result (original result_bytes is 348.05 Mi). I use clickhouse-rs/0.14.0 with enabled validation, will try to disable validation. Unfortunately clickhouse-rs only supports HTTP transport, no native TCP.

@REASY
Copy link
Copy Markdown
Author

REASY commented Oct 25, 2025 •
edited
Loading

Running with 128, unbound max_threads

[
  {
    "formatReadableSize(result_bytes)": "348.87 MiB",
    "percent_of_used_rows": 79.57393793688618,
    "NetworkSendElapsedMs": 4419.988,
    "NetworkSendBytes": 203281092,
    "hostname": "30efeb4c2152",
    "type": "QueryFinish",
    "event_date": "2025-10-25",
    "event_time": "2025-10-25T05:19:03Z[UTC]",
    "event_time_microseconds": "2025-10-25T05:19:03.813771Z[UTC]",
    "query_start_time": "2025-10-25T05:18:48Z[UTC]",
    "query_start_time_microseconds": "2025-10-25T05:18:48.602413Z[UTC]",
    "query_duration_ms": 15214,
    "read_rows": 5093718,
    "read_bytes": 565402698,
    "written_rows": 0,
    "written_bytes": 0,
    "result_rows": 4053272,
    "result_bytes": 365818432,
    "memory_usage": 2594066441,
    "current_database": "default",
    "query": "\n            WITH\n                now() AS E,\n                (E - toIntervalSecond(900)) AS S,\n                toStartOfMinute(S) AS Sm,\n                toStartOfMinute(E) AS Em,\n                (Sm + INTERVAL 1 MINUTE) AS Sm_next\n            SELECT\n                feature_key,\n                countMerge(count) AS count,\n                maxMerge(last_seen_event_time) AS last_seen_event_time\n            FROM\n                (\n                    -- 1) Head partial seconds: S .. min(E, Sm_next-1s)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= S\n                      AND bucket_s < Sm_next\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 2) Full minutes strictly inside (S, E)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2_1m\n                    WHERE bucket_m >= Sm_next\n                      AND bucket_m < Em\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 3) Tail partial seconds: Em .. E\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= Em\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n                    )\n            GROUP BY feature_key;\n            \n",
    "formatted_query": "",
    "normalized_query_hash": "<failed to load>\njava.sql.SQLException: Method: getLong(\"normalized_query_hash\") encountered an exception.\n\tat com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:74)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:320)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:175)\n\tin CHouseJdbcHelper.tryGetObject(CHouseJdbcHelper.java:156)\nCaused by: java.lang.ArithmeticException: integer overflow: 16443151356930779613 cannot be presented as long\n\tat com.clickhouse.client.api.data_formats.internal.NumberConverter.toLong(NumberConverter.java:87)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.readNumberValue(AbstractBinaryFormatReader.java:378)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.getLong(AbstractBinaryFormatReader.java:402)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:314)\n\t... 22 more\n",
    "query_kind": "Select",
    "databases": ["default"],
    "tables": ["default.fs_styx_userid_resource_v2", "default.fs_styx_userid_resource_v2_1m"],
    "columns": ["default.fs_styx_userid_resource_v2.bucket_s", "default.fs_styx_userid_resource_v2.count", "default.fs_styx_userid_resource_v2.feature_key", "default.fs_styx_userid_resource_v2.last_seen_event_time", "default.fs_styx_userid_resource_v2_1m.bucket_m", "default.fs_styx_userid_resource_v2_1m.count", "default.fs_styx_userid_resource_v2_1m.feature_key", "default.fs_styx_userid_resource_v2_1m.last_seen_event_time"],
    "partitions": ["default.fs_styx_userid_resource_v2.20251025", "default.fs_styx_userid_resource_v2_1m.20251025"],
    "projections": [],
    "views": [],
    "exception_code": 0,
    "exception": "",
    "stack_trace": "",
    "is_initial_query": 1,
    "user": "default",
    "query_id": "deb0fcb1-e543-44ff-bbf5-9c76430407da",
    "address": "/172.18.0.3",
    "port": 34612,
    "initial_user": "default",
    "initial_query_id": "deb0fcb1-e543-44ff-bbf5-9c76430407da",
    "initial_address": "/172.18.0.3",
    "initial_port": 34612,
    "initial_query_start_time": "2025-10-25T05:18:48Z[UTC]",
    "initial_query_start_time_microseconds": "2025-10-25T05:18:48.602413Z[UTC]",
    "interface": 2,
    "is_secure": 0,
    "os_user": "",
    "client_hostname": "",
    "client_name": "",
    "client_revision": 0,
    "client_version_major": 0,
    "client_version_minor": 0,
    "client_version_patch": 0,
    "script_query_number": 0,
    "script_line_number": 0,
    "http_method": 1,
    "http_user_agent": "clickhouse-rs/0.14.0 (lv:rust/1.89.0, os:linux)",
    "http_referer": "",
    "forwarded_for": "",
    "quota_key": "",
    "distributed_depth": 0,
    "revision": 54510,
    "log_comment": "",
    "thread_ids": [3767, 3641, 3712, 3548, 4376, 3278, 4318, 2400, 4308, 2313, 2074, 4321, 4296, 3306, 2353, 3442, 3114, 4305, 3125, 4316, 4310, 4358, 4301, 1986, 2621, 2224, 2227, 3021, 1886, 1826, 2223, 2308, 1911, 1634, 2031, 2539, 2142, 3775, 2584, 2187, 2122, 1673, 4136, 2554, 3348, 2546, 2472, 2379, 4123, 3138, 2315, 2022, 2816, 2422, 3170, 2579, 3297, 2567, 4335, 4147, 2956, 1817, 3205, 2014, 2167, 3276, 2326, 3120, 3451, 2095, 4344, 3359, 2950, 2553, 2288, 2761, 1967, 2806, 1821, 4150, 3165, 3235, 4144, 2953, 2295, 2641, 3146, 1816, 1681, 2347, 1646, 1658, 1846, 3037, 3287, 1699, 2290, 1621, 2418, 3212, 3023, 1644, 2038, 2082, 2479, 3250, 2068, 1859, 2647, 2355, 1958, 2549, 3337, 4140, 2155, 2534, 2634, 2437, 2104, 2695, 2301, 3174, 2230, 3421, 2836, 2042, 2064, 1670, 2458, 3596, 3199, 3020, 1838, 2035, 1638, 1835, 2751, 2586, 3380, 2001, 2192, 2986, 4132, 4341, 2753, 4292, 4304, 2716, 2116, 3307, 1731, 3284, 2217, 2360, 3742, 3345, 1890, 2069, 1672, 3248, 2178, 2375, 2145, 2542, 2939, 2545, 3427, 2129, 3279, 1885, 2273, 1882, 2079, 1836, 2630, 3909, 4306, 2533, 2242, 1972, 2660, 2739, 2345, 2510, 4295, 1931, 2568, 3450, 2656, 3253, 2459, 3444, 3247, 3178, 1723, 2690, 2296, 2005, 3274, 2255, 3372, 2037, 2231, 3215, 4297, 1724, 3312, 2090, 2270, 3398, 2413, 3586, 3189, 3438, 2126, 4302, 3111, 2238, 4128, 3237, 2646, 2367, 1807],
    "peak_threads_usage": 139,
    "ProfileEvents": {
      "Query": 1,
      "SelectQuery": 1,
      "InitialQuery": 1,
      "QueriesWithSubqueries": 4,
      "SelectQueriesWithSubqueries": 4,
      "SelectQueriesWithPrimaryKeyUsage": 3,
      "FileOpen": 16,
      "ReadBufferFromFileDescriptorReadBytes": 132310373,
      "ReadCompressedBytes": 117451236,
      "CompressedReadBufferBlocks": 1796,
      "CompressedReadBufferBytes": 385653201,
      "OpenedFileCacheHits": 296,
      "OpenedFileCacheMisses": 16,
      "OpenedFileCacheMicroseconds": 1058,
      "IOBufferAllocs": 724,
      "IOBufferAllocBytes": 320270412,
      "ArenaAllocChunks": 2983,
      "ArenaAllocBytes": 1300369408,
      "FunctionExecute": 2778,
      "MarkCacheHits": 312,
      "QueryConditionCacheHits": 3,
      "QueryConditionCacheMisses": 13,
      "CreatedReadBufferOrdinary": 312,
      "DiskReadElapsedMicroseconds": 110358,
      "NetworkSendElapsedMicroseconds": 4419988,
      "NetworkSendBytes": 203281092,
      "GlobalThreadPoolExpansions": 8,
      "GlobalThreadPoolThreadCreationMicroseconds": 364689,
      "GlobalThreadPoolLockWaitMicroseconds": 1941,
      "GlobalThreadPoolJobs": 229,
      "LocalThreadPoolExpansions": 228,
      "LocalThreadPoolShrinks": 185,
      "LocalThreadPoolThreadCreationMicroseconds": 400796,
      "LocalThreadPoolLockWaitMicroseconds": 968,
      "LocalThreadPoolJobs": 483,
      "AnalyzePatchRangesMicroseconds": 8,
      "SelectedParts": 8,
      "SelectedPartsTotal": 128,
      "SelectedRanges": 8,
      "SelectedMarks": 624,
      "SelectedMarksTotal": 390106,
      "SelectedRows": 5093718,
      "SelectedBytes": 565402698,
      "RowsReadByMainReader": 5093718,
      "RowsReadByPrewhereReaders": 5093718,
      "FilteringMarksWithPrimaryKeyMicroseconds": 6555,
      "WaitMarksLoadMicroseconds": 26471,
      "ContextLock": 237,
      "ContextLockWaitMicroseconds": 1,
      "RWLockAcquiredReadLocks": 3,
      "PartsLockHoldMicroseconds": 63,
      "PartsLockWaitMicroseconds": 28,
      "RealTimeMicroseconds": 1437533232,
      "UserTimeMicroseconds": 10202568,
      "SystemTimeMicroseconds": 36753872,
      "SoftPageFaults": 721004,
      "OSCPUWaitMicroseconds": 6982864,
      "OSCPUVirtualTimeMicroseconds": 46954411,
      "OSReadChars": 133016650,
      "OSWriteChars": 539752,
      "QueryProfilerRuns": 1487,
      "ThreadPoolReaderPageCacheHit": 1125,
      "ThreadPoolReaderPageCacheHitBytes": 132310373,
      "ThreadPoolReaderPageCacheHitElapsedMicroseconds": 110358,
      "SynchronousReadWaitMicroseconds": 111458,
      "AggregationHashTablesInitializedAsTwoLevel": 75,
      "InterfaceHTTPSendBytes": 203281092,
      "ConcurrencyControlSlotsGranted": 1,
      "ConcurrencyControlSlotsAcquired": 95,
      "ConcurrencyControlSlotsAcquiredNonCompeting": 1,
      "IndexBinarySearchAlgorithm": 128
    },
    "Settings": {
      "use_uncompressed_cache": "0",
      "load_balancing": "in_order",
      "log_queries": "1",
      "readonly": "2",
      "max_memory_usage": "21474836480",
      "parallel_replicas_for_cluster_engines": "0"
    },
    "used_aggregate_functions": ["count", "max", "min"],
    "used_aggregate_function_combinators": ["State", "Merge"],
    "used_database_engines": [],
    "used_data_type_families": [],
    "used_dictionaries": [],
    "used_formats": ["RowBinaryWithNamesAndTypes"],
    "used_functions": ["subtractSeconds", "now", "greaterOrEquals", "minus", "and", "toIntervalMinute", "toStartOfMinute", "plus", "toIntervalSecond", "notIn", "lessOrEquals", "less", "addMinutes"],
    "used_storages": [],
    "used_table_functions": [],
    "used_executable_user_defined_functions": [],
    "used_sql_user_defined_functions": [],
    "used_row_policies": [],
    "used_privileges": ["SELECT(feature_key, count, last_seen_event_time, bucket_m) ON default.fs_styx_userid_resource_v2_1m", "SELECT(feature_key, count, last_seen_event_time, bucket_s) ON default.fs_styx_userid_resource_v2"],
    "missing_privileges": [],
    "transaction_id": [0, 0,
      "00000000-0000-0000-0000-000000000000"
    ],
    "query_cache_usage": "None",
    "asynchronous_read_counters": {
    }
  },
  {
    "formatReadableSize(result_bytes)": "317.53 MiB",
    "percent_of_used_rows": 80.93912222177323,
    "NetworkSendElapsedMs": 4346.592,
    "NetworkSendBytes": 134488024,
    "hostname": "30efeb4c2152",
    "type": "QueryFinish",
    "event_date": "2025-10-25",
    "event_time": "2025-10-25T05:19:03Z[UTC]",
    "event_time_microseconds": "2025-10-25T05:19:03.507916Z[UTC]",
    "query_start_time": "2025-10-25T05:18:48Z[UTC]",
    "query_start_time_microseconds": "2025-10-25T05:18:48.602433Z[UTC]",
    "query_duration_ms": 14909,
    "read_rows": 3316054,
    "read_bytes": 368081994,
    "written_rows": 0,
    "written_bytes": 0,
    "result_rows": 2683985,
    "result_bytes": 332955544,
    "memory_usage": 2109740073,
    "current_database": "default",
    "query": "\n            WITH\n                now() AS E,\n                (E - toIntervalSecond(600)) AS S,\n                toStartOfMinute(S) AS Sm,\n                toStartOfMinute(E) AS Em,\n                (Sm + INTERVAL 1 MINUTE) AS Sm_next\n            SELECT\n                feature_key,\n                countMerge(count) AS count,\n                maxMerge(last_seen_event_time) AS last_seen_event_time\n            FROM\n                (\n                    -- 1) Head partial seconds: S .. min(E, Sm_next-1s)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= S\n                      AND bucket_s < Sm_next\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 2) Full minutes strictly inside (S, E)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2_1m\n                    WHERE bucket_m >= Sm_next\n                      AND bucket_m < Em\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 3) Tail partial seconds: Em .. E\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= Em\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n                    )\n            GROUP BY feature_key;\n            \n",
    "formatted_query": "",
    "normalized_query_hash": "<failed to load>\njava.sql.SQLException: Method: getLong(\"normalized_query_hash\") encountered an exception.\n\tat com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:74)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:320)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:175)\n\tin CHouseJdbcHelper.tryGetObject(CHouseJdbcHelper.java:156)\nCaused by: java.lang.ArithmeticException: integer overflow: 16443151356930779613 cannot be presented as long\n\tat com.clickhouse.client.api.data_formats.internal.NumberConverter.toLong(NumberConverter.java:87)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.readNumberValue(AbstractBinaryFormatReader.java:378)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.getLong(AbstractBinaryFormatReader.java:402)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:314)\n\t... 22 more\n",
    "query_kind": "Select",
    "databases": ["default"],
    "tables": ["default.fs_styx_userid_resource_v2", "default.fs_styx_userid_resource_v2_1m"],
    "columns": ["default.fs_styx_userid_resource_v2.bucket_s", "default.fs_styx_userid_resource_v2.count", "default.fs_styx_userid_resource_v2.feature_key", "default.fs_styx_userid_resource_v2.last_seen_event_time", "default.fs_styx_userid_resource_v2_1m.bucket_m", "default.fs_styx_userid_resource_v2_1m.count", "default.fs_styx_userid_resource_v2_1m.feature_key", "default.fs_styx_userid_resource_v2_1m.last_seen_event_time"],
    "partitions": ["default.fs_styx_userid_resource_v2.20251025", "default.fs_styx_userid_resource_v2_1m.20251025"],
    "projections": [],
    "views": [],
    "exception_code": 0,
    "exception": "",
    "stack_trace": "",
    "is_initial_query": 1,
    "user": "default",
    "query_id": "6be74f2c-f846-4056-998c-ca6d49c2b48e",
    "address": "/172.18.0.3",
    "port": 34604,
    "initial_user": "default",
    "initial_query_id": "6be74f2c-f846-4056-998c-ca6d49c2b48e",
    "initial_address": "/172.18.0.3",
    "initial_port": 34604,
    "initial_query_start_time": "2025-10-25T05:18:48Z[UTC]",
    "initial_query_start_time_microseconds": "2025-10-25T05:18:48.602433Z[UTC]",
    "interface": 2,
    "is_secure": 0,
    "os_user": "",
    "client_hostname": "",
    "client_name": "",
    "client_revision": 0,
    "client_version_major": 0,
    "client_version_minor": 0,
    "client_version_patch": 0,
    "script_query_number": 0,
    "script_line_number": 0,
    "http_method": 1,
    "http_user_agent": "clickhouse-rs/0.14.0 (lv:rust/1.89.0, os:linux)",
    "http_referer": "",
    "forwarded_for": "",
    "quota_key": "",
    "distributed_depth": 0,
    "revision": 54510,
    "log_comment": "",
    "thread_ids": [4300, 4337, 3793, 4369, 4311, 4320, 4323, 4356, 4307, 2123, 4290, 4315, 4209, 4203, 4201, 4188, 4183, 4179, 4177, 4169, 4360, 4163, 4161, 4151, 4345, 4148, 4142, 4138, 4317, 4120, 3829, 3825, 4207, 3813, 4383, 3792, 3784, 4157, 3763, 3758, 3745, 3707, 3695, 3678, 1831, 3604, 4175, 3584, 3521, 3821, 1654, 3624, 3230, 3056, 4314, 1753, 2738, 3396, 4331, 4134, 3149, 2164, 3528, 3134, 2917, 4145, 2766, 3715, 3518, 2927, 2336, 4211, 3817, 2832, 3770, 2391, 2785, 1891, 3270, 4155, 3564, 2385, 3370, 4192, 3798, 1828, 3010, 2697, 2033, 3634, 1961, 3537, 3049, 3246, 2732, 2247, 4205, 3614, 3420, 2159, 2942, 1887, 4153, 2577, 3668, 3274, 2092, 2266, 2110, 4198, 3804, 3213, 4313, 2146, 1983, 1692, 3658, 1885, 4125, 2943, 2461, 2264, 3546, 2167, 3555, 4167, 2000, 2197, 3282, 4194, 1633, 4196, 2029, 2795, 1825, 4173, 3188, 1709, 1806, 2047, 4190, 2220, 4181, 2802, 3397, 3594, 2333, 2024, 3206, 2073, 3255, 2267, 3449, 3158, 2359, 3212, 4312, 4115, 2145, 1757, 3456, 2046, 3648, 3260, 1651, 4171, 3777, 3186, 2004, 4365, 4168, 2198, 3771, 3574, 2604, 4327, 4130, 3736, 2160, 3808, 3020, 4299, 3117],
    "peak_threads_usage": 124,
    "ProfileEvents": {
      "Query": 1,
      "SelectQuery": 1,
      "InitialQuery": 1,
      "QueriesWithSubqueries": 4,
      "SelectQueriesWithSubqueries": 4,
      "SelectQueriesWithPrimaryKeyUsage": 3,
      "FileOpen": 1,
      "ReadBufferFromFileDescriptorReadBytes": 87522224,
      "ReadCompressedBytes": 77309819,
      "CompressedReadBufferBlocks": 1172,
      "CompressedReadBufferBytes": 250542485,
      "OpenedFileCacheHits": 203,
      "OpenedFileCacheMisses": 1,
      "OpenedFileCacheMicroseconds": 1377,
      "IOBufferAllocs": 508,
      "IOBufferAllocBytes": 212368395,
      "ArenaAllocChunks": 2218,
      "ArenaAllocBytes": 870883328,
      "FunctionExecute": 1801,
      "MarkCacheHits": 204,
      "QueryConditionCacheHits": 3,
      "QueryConditionCacheMisses": 13,
      "CreatedReadBufferOrdinary": 204,
      "DiskReadElapsedMicroseconds": 2308419,
      "NetworkSendElapsedMicroseconds": 4346592,
      "NetworkSendBytes": 134488024,
      "GlobalThreadPoolExpansions": 75,
      "GlobalThreadPoolThreadCreationMicroseconds": 4076847,
      "GlobalThreadPoolLockWaitMicroseconds": 1850,
      "GlobalThreadPoolJobs": 196,
      "LocalThreadPoolExpansions": 195,
      "LocalThreadPoolShrinks": 167,
      "LocalThreadPoolThreadCreationMicroseconds": 4121160,
      "LocalThreadPoolLockWaitMicroseconds": 16172,
      "LocalThreadPoolJobs": 483,
      "SelectedParts": 8,
      "SelectedPartsTotal": 128,
      "SelectedRanges": 8,
      "SelectedMarks": 407,
      "SelectedMarksTotal": 390106,
      "SelectedRows": 3316054,
      "SelectedBytes": 368081994,
      "RowsReadByMainReader": 3316054,
      "RowsReadByPrewhereReaders": 3316054,
      "FilteringMarksWithPrimaryKeyMicroseconds": 7772,
      "WaitMarksLoadMicroseconds": 4529,
      "ContextLock": 210,
      "ContextLockWaitMicroseconds": 3,
      "RWLockAcquiredReadLocks": 3,
      "PartsLockHoldMicroseconds": 48,
      "RealTimeMicroseconds": 1143494373,
      "UserTimeMicroseconds": 8061590,
      "SystemTimeMicroseconds": 27946703,
      "SoftPageFaults": 546240,
      "OSCPUWaitMicroseconds": 6007365,
      "OSCPUVirtualTimeMicroseconds": 35997263,
      "OSReadChars": 88133634,
      "OSWriteChars": 457496,
      "QueryProfilerRuns": 1180,
      "ThreadPoolReaderPageCacheHit": 745,
      "ThreadPoolReaderPageCacheHitBytes": 87522224,
      "ThreadPoolReaderPageCacheHitElapsedMicroseconds": 2308419,
      "SynchronousReadWaitMicroseconds": 2309382,
      "AggregationHashTablesInitializedAsTwoLevel": 48,
      "InterfaceHTTPSendBytes": 134488024,
      "ConcurrencyControlSlotsGranted": 1,
      "ConcurrencyControlSlotsAcquired": 95,
      "ConcurrencyControlSlotsAcquiredNonCompeting": 1,
      "IndexBinarySearchAlgorithm": 128
    },
    "Settings": {
      "use_uncompressed_cache": "0",
      "load_balancing": "in_order",
      "log_queries": "1",
      "readonly": "2",
      "max_memory_usage": "21474836480",
      "parallel_replicas_for_cluster_engines": "0"
    },
    "used_aggregate_functions": ["count", "max", "min"],
    "used_aggregate_function_combinators": ["State", "Merge"],
    "used_database_engines": [],
    "used_data_type_families": [],
    "used_dictionaries": [],
    "used_formats": ["RowBinaryWithNamesAndTypes"],
    "used_functions": ["subtractSeconds", "now", "greaterOrEquals", "minus", "and", "toIntervalMinute", "toStartOfMinute", "plus", "toIntervalSecond", "notIn", "lessOrEquals", "less", "addMinutes"],
    "used_storages": [],
    "used_table_functions": [],
    "used_executable_user_defined_functions": [],
    "used_sql_user_defined_functions": [],
    "used_row_policies": [],
    "used_privileges": ["SELECT(feature_key, count, last_seen_event_time, bucket_m) ON default.fs_styx_userid_resource_v2_1m", "SELECT(feature_key, count, last_seen_event_time, bucket_s) ON default.fs_styx_userid_resource_v2"],
    "missing_privileges": [],
    "transaction_id": [0, 0,
      "00000000-0000-0000-0000-000000000000"
    ],
    "query_cache_usage": "None",
    "asynchronous_read_counters": {
    }
  }
]

@REASY
Copy link
Copy Markdown
Author

REASY commented Oct 25, 2025

Bring back 64 CPU to ClickHouse and set <max_threads>24</max_threads>

[
  {
    "formatReadableSize(result_bytes)": "348.68 MiB",
    "percent_of_used_rows": 76.10965853548387,
    "NetworkSendElapsedMs": 4100.457,
    "NetworkSendBytes": 203357724,
    "hostname": "8edab12dd339",
    "type": "QueryFinish",
    "event_date": "2025-10-25",
    "event_time": "2025-10-25T14:11:23Z[UTC]",
    "event_time_microseconds": "2025-10-25T14:11:23.024151Z[UTC]",
    "query_start_time": "2025-10-25T14:11:16Z[UTC]",
    "query_start_time_microseconds": "2025-10-25T14:11:16.831848Z[UTC]",
    "query_duration_ms": 6193,
    "read_rows": 5314315,
    "read_bytes": 589887147,
    "written_rows": 0,
    "written_bytes": 0,
    "result_rows": 4044707,
    "result_bytes": 365612872,
    "memory_usage": 2116662222,
    "current_database": "default",
    "query": "\n            WITH\n                now() AS E,\n                (E - toIntervalSecond(900)) AS S,\n                toStartOfMinute(S) AS Sm,\n                toStartOfMinute(E) AS Em,\n                (Sm + INTERVAL 1 MINUTE) AS Sm_next\n            SELECT\n                feature_key,\n                countMerge(count) AS count,\n                maxMerge(last_seen_event_time) AS last_seen_event_time\n            FROM\n                (\n                    -- 1) Head partial seconds: S .. min(E, Sm_next-1s)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= S\n                      AND bucket_s < Sm_next\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 2) Full minutes strictly inside (S, E)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2_1m\n                    WHERE bucket_m >= Sm_next\n                      AND bucket_m < Em\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 3) Tail partial seconds: Em .. E\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= Em\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n                    )\n            GROUP BY feature_key;\n            \n",
    "formatted_query": "",
    "normalized_query_hash": "<failed to load>\njava.sql.SQLException: Method: getLong(\"normalized_query_hash\") encountered an exception.\n\tat com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:74)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:320)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:175)\n\tin CHouseJdbcHelper.tryGetObject(CHouseJdbcHelper.java:156)\nCaused by: java.lang.ArithmeticException: integer overflow: 16443151356930779613 cannot be presented as long\n\tat com.clickhouse.client.api.data_formats.internal.NumberConverter.toLong(NumberConverter.java:87)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.readNumberValue(AbstractBinaryFormatReader.java:378)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.getLong(AbstractBinaryFormatReader.java:402)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:314)\n\t... 22 more\n",
    "query_kind": "Select",
    "databases": ["default"],
    "tables": ["default.fs_styx_userid_resource_v2", "default.fs_styx_userid_resource_v2_1m"],
    "columns": ["default.fs_styx_userid_resource_v2.bucket_s", "default.fs_styx_userid_resource_v2.count", "default.fs_styx_userid_resource_v2.feature_key", "default.fs_styx_userid_resource_v2.last_seen_event_time", "default.fs_styx_userid_resource_v2_1m.bucket_m", "default.fs_styx_userid_resource_v2_1m.count", "default.fs_styx_userid_resource_v2_1m.feature_key", "default.fs_styx_userid_resource_v2_1m.last_seen_event_time"],
    "partitions": ["default.fs_styx_userid_resource_v2.20251025", "default.fs_styx_userid_resource_v2_1m.20251025"],
    "projections": [],
    "views": [],
    "exception_code": 0,
    "exception": "",
    "stack_trace": "",
    "is_initial_query": 1,
    "user": "default",
    "query_id": "0f43ce98-df4e-48a4-8bce-1449f89c1df0",
    "address": "/172.18.0.3",
    "port": 45996,
    "initial_user": "default",
    "initial_query_id": "0f43ce98-df4e-48a4-8bce-1449f89c1df0",
    "initial_address": "/172.18.0.3",
    "initial_port": 45996,
    "initial_query_start_time": "2025-10-25T14:11:16Z[UTC]",
    "initial_query_start_time_microseconds": "2025-10-25T14:11:16.831848Z[UTC]",
    "interface": 2,
    "is_secure": 0,
    "os_user": "",
    "client_hostname": "",
    "client_name": "",
    "client_revision": 0,
    "client_version_major": 0,
    "client_version_minor": 0,
    "client_version_patch": 0,
    "script_query_number": 0,
    "script_line_number": 0,
    "http_method": 1,
    "http_user_agent": "clickhouse-rs/0.14.0 (lv:rust/1.89.0, os:linux)",
    "http_referer": "",
    "forwarded_for": "",
    "quota_key": "",
    "distributed_depth": 0,
    "revision": 54510,
    "log_comment": "",
    "thread_ids": [2218, 10208, 1989, 2163, 2870, 22099, 2223, 2659, 1708, 1913, 2186, 2089, 2059, 2253, 1937, 2224, 3485, 1711, 2293, 1868, 8466, 2168, 2294, 3751, 2296, 1949, 2046, 7478, 2281, 3154, 1838, 1869, 8465, 15562, 2938, 1858, 2124, 2119, 1919, 2307, 2943, 2238, 1750, 1626, 2596, 1914, 2205, 1863, 2004, 4128, 1874, 2182, 2085, 2807, 15558, 1881, 2134, 2110, 2298, 1692],
    "peak_threads_usage": 29,
    "ProfileEvents": {
      "Query": 1,
      "SelectQuery": 1,
      "InitialQuery": 1,
      "QueriesWithSubqueries": 4,
      "SelectQueriesWithSubqueries": 4,
      "SelectQueriesWithPrimaryKeyUsage": 3,
      "FileOpen": 17,
      "ReadBufferFromFileDescriptorReadBytes": 127992579,
      "ReadCompressedBytes": 121050748,
      "CompressedReadBufferBlocks": 1759,
      "CompressedReadBufferBytes": 395281031,
      "OpenedFileCacheHits": 109,
      "OpenedFileCacheMisses": 17,
      "OpenedFileCacheMicroseconds": 162,
      "IOBufferAllocs": 280,
      "IOBufferAllocBytes": 99394733,
      "ArenaAllocChunks": 2337,
      "ArenaAllocBytes": 1455620096,
      "FunctionExecute": 2784,
      "MarkCacheHits": 126,
      "QueryConditionCacheHits": 3,
      "QueryConditionCacheMisses": 19,
      "CreatedReadBufferOrdinary": 126,
      "DiskReadElapsedMicroseconds": 200971,
      "NetworkSendElapsedMicroseconds": 4100457,
      "NetworkSendBytes": 203357724,
      "GlobalThreadPoolLockWaitMicroseconds": 13546,
      "GlobalThreadPoolJobs": 66,
      "LocalThreadPoolExpansions": 65,
      "LocalThreadPoolShrinks": 61,
      "LocalThreadPoolThreadCreationMicroseconds": 57413,
      "LocalThreadPoolLockWaitMicroseconds": 8,
      "LocalThreadPoolJobs": 424,
      "AnalyzePatchRangesMicroseconds": 3,
      "SelectedParts": 11,
      "SelectedPartsTotal": 141,
      "SelectedRanges": 11,
      "SelectedMarks": 653,
      "SelectedMarksTotal": 467501,
      "SelectedRows": 5314315,
      "SelectedBytes": 589887147,
      "RowsReadByMainReader": 5314315,
      "RowsReadByPrewhereReaders": 5314315,
      "FilteringMarksWithPrimaryKeyMicroseconds": 10099,
      "WaitMarksLoadMicroseconds": 743,
      "ContextLock": 192,
      "ContextLockWaitMicroseconds": 1,
      "RWLockAcquiredReadLocks": 3,
      "PartsLockHoldMicroseconds": 43,
      "PartsLockWaitMicroseconds": 3,
      "RealTimeMicroseconds": 158779500,
      "UserTimeMicroseconds": 9000520,
      "SystemTimeMicroseconds": 2725414,
      "SoftPageFaults": 467824,
      "OSCPUWaitMicroseconds": 22098255,
      "OSCPUVirtualTimeMicroseconds": 11725246,
      "OSReadChars": 128633002,
      "OSWriteChars": 215600,
      "QueryProfilerRuns": 174,
      "ThreadPoolReaderPageCacheHit": 1019,
      "ThreadPoolReaderPageCacheHitBytes": 127992579,
      "ThreadPoolReaderPageCacheHitElapsedMicroseconds": 200971,
      "SynchronousReadWaitMicroseconds": 202216,
      "AggregationHashTablesInitializedAsTwoLevel": 27,
      "InterfaceHTTPSendBytes": 203357724,
      "ConcurrencyControlSlotsGranted": 1,
      "ConcurrencyControlSlotsAcquired": 23,
      "ConcurrencyControlSlotsAcquiredNonCompeting": 1,
      "IndexBinarySearchAlgorithm": 141
    },
    "Settings": {
      "max_threads": "24",
      "use_uncompressed_cache": "0",
      "load_balancing": "in_order",
      "log_queries": "1",
      "readonly": "2",
      "max_memory_usage": "21474836480",
      "parallel_replicas_for_cluster_engines": "0"
    },
    "used_aggregate_functions": ["count", "max", "min"],
    "used_aggregate_function_combinators": ["State", "Merge"],
    "used_database_engines": [],
    "used_data_type_families": [],
    "used_dictionaries": [],
    "used_formats": ["RowBinaryWithNamesAndTypes"],
    "used_functions": ["subtractSeconds", "now", "greaterOrEquals", "minus", "and", "toIntervalMinute", "toStartOfMinute", "plus", "toIntervalSecond", "notIn", "lessOrEquals", "less", "addMinutes"],
    "used_storages": [],
    "used_table_functions": [],
    "used_executable_user_defined_functions": [],
    "used_sql_user_defined_functions": [],
    "used_row_policies": [],
    "used_privileges": ["SELECT(feature_key, count, last_seen_event_time, bucket_m) ON default.fs_styx_userid_resource_v2_1m", "SELECT(feature_key, count, last_seen_event_time, bucket_s) ON default.fs_styx_userid_resource_v2"],
    "missing_privileges": [],
    "transaction_id": [0, 0,
      "00000000-0000-0000-0000-000000000000"
    ],
    "query_cache_usage": "None",
    "asynchronous_read_counters": {
    }
  },
  {
    "formatReadableSize(result_bytes)": "349.27 MiB",
    "percent_of_used_rows": 76.13832306467357,
    "NetworkSendElapsedMs": 4079.524,
    "NetworkSendBytes": 204576749,
    "hostname": "8edab12dd339",
    "type": "QueryFinish",
    "event_date": "2025-10-25",
    "event_time": "2025-10-25T14:14:42Z[UTC]",
    "event_time_microseconds": "2025-10-25T14:14:42.275620Z[UTC]",
    "query_start_time": "2025-10-25T14:14:36Z[UTC]",
    "query_start_time_microseconds": "2025-10-25T14:14:36.084875Z[UTC]",
    "query_duration_ms": 6192,
    "read_rows": 5346527,
    "read_bytes": 592853083,
    "written_rows": 0,
    "written_bytes": 0,
    "result_rows": 4070756,
    "result_bytes": 366238048,
    "memory_usage": 2149646422,
    "current_database": "default",
    "query": "\n            WITH\n                now() AS E,\n                (E - toIntervalSecond(900)) AS S,\n                toStartOfMinute(S) AS Sm,\n                toStartOfMinute(E) AS Em,\n                (Sm + INTERVAL 1 MINUTE) AS Sm_next\n            SELECT\n                feature_key,\n                countMerge(count) AS count,\n                maxMerge(last_seen_event_time) AS last_seen_event_time\n            FROM\n                (\n                    -- 1) Head partial seconds: S .. min(E, Sm_next-1s)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= S\n                      AND bucket_s < Sm_next\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 2) Full minutes strictly inside (S, E)\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2_1m\n                    WHERE bucket_m >= Sm_next\n                      AND bucket_m < Em\n                    GROUP BY feature_key\n\n                    UNION ALL\n\n                    -- 3) Tail partial seconds: Em .. E\n                    SELECT\n                        feature_key,\n                        countMergeState(count) AS count,\n                        maxMergeState(last_seen_event_time) AS last_seen_event_time\n                    FROM fs_styx_userid_resource_v2\n                    WHERE bucket_s >= Em\n                      AND bucket_s <= E\n                    GROUP BY feature_key\n                    )\n            GROUP BY feature_key;\n            \n",
    "formatted_query": "",
    "normalized_query_hash": "<failed to load>\njava.sql.SQLException: Method: getLong(\"normalized_query_hash\") encountered an exception.\n\tat com.clickhouse.jdbc.internal.ExceptionUtils.toSqlState(ExceptionUtils.java:74)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:320)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:175)\n\tin CHouseJdbcHelper.tryGetObject(CHouseJdbcHelper.java:156)\nCaused by: java.lang.ArithmeticException: integer overflow: 16443151356930779613 cannot be presented as long\n\tat com.clickhouse.client.api.data_formats.internal.NumberConverter.toLong(NumberConverter.java:87)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.readNumberValue(AbstractBinaryFormatReader.java:378)\n\tat com.clickhouse.client.api.data_formats.internal.AbstractBinaryFormatReader.getLong(AbstractBinaryFormatReader.java:402)\n\tat com.clickhouse.jdbc.ResultSetImpl.getLong(ResultSetImpl.java:314)\n\t... 22 more\n",
    "query_kind": "Select",
    "databases": ["default"],
    "tables": ["default.fs_styx_userid_resource_v2", "default.fs_styx_userid_resource_v2_1m"],
    "columns": ["default.fs_styx_userid_resource_v2.bucket_s", "default.fs_styx_userid_resource_v2.count", "default.fs_styx_userid_resource_v2.feature_key", "default.fs_styx_userid_resource_v2.last_seen_event_time", "default.fs_styx_userid_resource_v2_1m.bucket_m", "default.fs_styx_userid_resource_v2_1m.count", "default.fs_styx_userid_resource_v2_1m.feature_key", "default.fs_styx_userid_resource_v2_1m.last_seen_event_time"],
    "partitions": ["default.fs_styx_userid_resource_v2.20251025", "default.fs_styx_userid_resource_v2_1m.20251025"],
    "projections": [],
    "views": [],
    "exception_code": 0,
    "exception": "",
    "stack_trace": "",
    "is_initial_query": 1,
    "user": "default",
    "query_id": "d94d1dc4-dfe6-4db4-8994-68441dfb3e1e",
    "address": "/172.18.0.3",
    "port": 45996,
    "initial_user": "default",
    "initial_query_id": "d94d1dc4-dfe6-4db4-8994-68441dfb3e1e",
    "initial_address": "/172.18.0.3",
    "initial_port": 45996,
    "initial_query_start_time": "2025-10-25T14:14:36Z[UTC]",
    "initial_query_start_time_microseconds": "2025-10-25T14:14:36.084875Z[UTC]",
    "interface": 2,
    "is_secure": 0,
    "os_user": "",
    "client_hostname": "",
    "client_name": "",
    "client_revision": 0,
    "client_version_major": 0,
    "client_version_minor": 0,
    "client_version_patch": 0,
    "script_query_number": 0,
    "script_line_number": 0,
    "http_method": 1,
    "http_user_agent": "clickhouse-rs/0.14.0 (lv:rust/1.89.0, os:linux)",
    "http_referer": "",
    "forwarded_for": "",
    "quota_key": "",
    "distributed_depth": 0,
    "revision": 54510,
    "log_comment": "",
    "thread_ids": [2079, 2202, 2154, 2263, 2186, 22101, 2804, 3480, 1607, 1922, 7479, 2281, 2239, 2280, 2264, 1636, 1915, 2109, 1867, 2255, 1760, 1909, 2254, 2157, 3149, 1694, 3060, 1605, 1969, 1709, 1971, 1750, 2138, 1862, 1765, 13696, 2271, 35833, 2197, 3749, 1930, 1924, 22100, 2118, 13408, 7103, 2171, 2074, 3748, 11217, 1838, 1644, 3487, 2062, 2256, 1635, 3478, 4070, 1645, 11219, 2098, 1904, 1710, 2195, 1855, 23389, 2049, 1705, 2093, 2230, 3486, 2128, 1968, 1774, 2593, 2302, 2075, 1881, 2228, 1646, 1722, 35836, 2662, 1746, 2037, 2084, 2266, 15555, 2238, 2247, 8465],
    "peak_threads_usage": 32,
    "ProfileEvents": {
      "Query": 1,
      "SelectQuery": 1,
      "InitialQuery": 1,
      "QueriesWithSubqueries": 4,
      "SelectQueriesWithSubqueries": 4,
      "SelectQueriesWithPrimaryKeyUsage": 3,
      "FileOpen": 12,
      "ReadBufferFromFileDescriptorReadBytes": 126913575,
      "ReadCompressedBytes": 121223715,
      "CompressedReadBufferBlocks": 1749,
      "CompressedReadBufferBytes": 397484770,
      "OpenedFileCacheHits": 104,
      "OpenedFileCacheMisses": 12,
      "OpenedFileCacheMicroseconds": 140,
      "IOBufferAllocs": 260,
      "IOBufferAllocBytes": 96643435,
      "ArenaAllocChunks": 2333,
      "ArenaAllocBytes": 1484189696,
      "FunctionExecute": 2760,
      "MarkCacheHits": 116,
      "QueryConditionCacheHits": 4,
      "QueryConditionCacheMisses": 8,
      "CreatedReadBufferOrdinary": 116,
      "DiskReadElapsedMicroseconds": 203708,
      "NetworkSendElapsedMicroseconds": 4079524,
      "NetworkSendBytes": 204576749,
      "GlobalThreadPoolLockWaitMicroseconds": 152,
      "GlobalThreadPoolJobs": 94,
      "LocalThreadPoolExpansions": 93,
      "LocalThreadPoolShrinks": 83,
      "LocalThreadPoolThreadCreationMicroseconds": 9949,
      "LocalThreadPoolLockWaitMicroseconds": 705,
      "LocalThreadPoolJobs": 416,
      "AnalyzePatchRangesMicroseconds": 2,
      "SelectedParts": 6,
      "SelectedPartsTotal": 133,
      "SelectedRanges": 6,
      "SelectedMarks": 655,
      "SelectedMarksTotal": 468025,
      "SelectedRows": 5346527,
      "SelectedBytes": 592853083,
      "RowsReadByMainReader": 5340827,
      "RowsReadByPrewhereReaders": 5346527,
      "FilteringMarksWithPrimaryKeyMicroseconds": 2780,
      "WaitMarksLoadMicroseconds": 5638,
      "ContextLock": 187,
      "ContextLockWaitMicroseconds": 2,
      "RWLockAcquiredReadLocks": 3,
      "PartsLockHoldMicroseconds": 63,
      "RealTimeMicroseconds": 161292633,
      "UserTimeMicroseconds": 8830734,
      "SystemTimeMicroseconds": 2771818,
      "SoftPageFaults": 453750,
      "OSCPUWaitMicroseconds": 21462539,
      "OSCPUVirtualTimeMicroseconds": 11601927,
      "OSReadChars": 127523142,
      "OSWriteChars": 219872,
      "QueryProfilerRuns": 178,
      "ThreadPoolReaderPageCacheHit": 1009,
      "ThreadPoolReaderPageCacheHitBytes": 126913575,
      "ThreadPoolReaderPageCacheHitElapsedMicroseconds": 203708,
      "SynchronousReadWaitMicroseconds": 204825,
      "AggregationHashTablesInitializedAsTwoLevel": 28,
      "InterfaceHTTPSendBytes": 204576749,
      "ConcurrencyControlSlotsGranted": 1,
      "ConcurrencyControlSlotsAcquired": 23,
      "ConcurrencyControlSlotsAcquiredNonCompeting": 1,
      "IndexBinarySearchAlgorithm": 133
    },
    "Settings": {
      "max_threads": "24",
      "use_uncompressed_cache": "0",
      "load_balancing": "in_order",
      "log_queries": "1",
      "readonly": "2",
      "max_memory_usage": "21474836480",
      "parallel_replicas_for_cluster_engines": "0"
    },
    "used_aggregate_functions": ["count", "max", "min"],
    "used_aggregate_function_combinators": ["State", "Merge"],
    "used_database_engines": [],
    "used_data_type_families": [],
    "used_dictionaries": [],
    "used_formats": ["RowBinaryWithNamesAndTypes"],
    "used_functions": ["subtractSeconds", "now", "greaterOrEquals", "minus", "and", "toIntervalMinute", "toStartOfMinute", "plus", "toIntervalSecond", "notIn", "lessOrEquals", "less", "addMinutes"],
    "used_storages": [],
    "used_table_functions": [],
    "used_executable_user_defined_functions": [],
    "used_sql_user_defined_functions": [],
    "used_row_policies": [],
    "used_privileges": ["SELECT(feature_key, count, last_seen_event_time, bucket_m) ON default.fs_styx_userid_resource_v2_1m", "SELECT(feature_key, count, last_seen_event_time, bucket_s) ON default.fs_styx_userid_resource_v2"],
    "missing_privileges": [],
    "transaction_id": [0, 0,
      "00000000-0000-0000-0000-000000000000"
    ],
    "query_cache_usage": "None",
    "asynchronous_read_counters": {
    }
  }
]

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment