Created
October 22, 2025 14:02
-
-
Save REASY/cfa46aca4181885a99b6e0476d5176a4 to your computer and use it in GitHub Desktop.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| 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; |
Author
Author
"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.
Author
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": {
}
}
]
Author
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
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": { } }