keeper-interactions.sql

-- https://clickhouse.com/codebrowser/ClickHouse/src/Common/ProfileEvents.cpp.html
WITH
    'default' AS db_name,
    'wikistat' AS table_name,
    (SELECT uuid FROM system.tables WHERE database = db_name and name = table_name) AS table_id,
    (SELECT min(event_time) FROM system.part_log WHERE table_uuid = table_id) AS start_time,
    (
        SELECT event_time
        FROM clusterAllReplicas(default, system.query_log)
        WHERE
            has(tables, db_name || '.' || table_name)
            AND length(tables) = 1
            AND is_initial_query
            AND query_kind = 'Insert'
            AND type = 'QueryFinish'
        ORDER BY event_time_microseconds DESC
        LIMIT 1
    ) AS insert_end_time
SELECT
-----------------------------------------------------------------------------------------------------------------
-- Number of ZooKeeper operations, which include both read and write operations as well as multi-transactions.
-----------------------------------------------------------------------------------------------------------------
    sum(ProfileEvent_ZooKeeperTransactions) AS total_requests,

-----------------------------------------------------------------------------------------------------------------
-- Number of WRITE 'multi' requests to ZooKeeper (compound transactions). Only write subrequests
-----------------------------------------------------------------------------------------------------------------
    sum(ProfileEvent_ZooKeeperMulti) AS multi_write_requests,

-----------------------------------------------------------------------------------------------------------------
-- Number of READ 'multi' requests to ZooKeeper (compound transactions). Only read subrequests
-----------------------------------------------------------------------------------------------------------------
    sum(ProfileEvent_KeeperMultiReadRequest) AS multi_read_requests,

-----------------------------------------------------------------------------------------------------------------
-- Number of 'list' (getChildren) requests to ZooKeeper.
-----------------------------------------------------------------------------------------------------------------
    sum(ProfileEvent_ZooKeeperList) AS list_requests,

-----------------------------------------------------------------------------------------------------------------
-- Number of 'create' requests to ZooKeeper.
-----------------------------------------------------------------------------------------------------------------
    sum(ProfileEvent_ZooKeeperCreate) AS create_requests,

-----------------------------------------------------------------------------------------------------------------
-- Number of 'set' requests to ZooKeeper.
-----------------------------------------------------------------------------------------------------------------
    sum(ProfileEvent_ZooKeeperSet) AS set_requests,

-----------------------------------------------------------------------------------------------------------------
-- Number of 'remove' requests to ZooKeeper.
-----------------------------------------------------------------------------------------------------------------
    sum(ProfileEvent_ZooKeeperRemove) AS remove_requests,

-----------------------------------------------------------------------------------------------------------------
-- Number of 'exists' requests to ZooKeeper
-----------------------------------------------------------------------------------------------------------------
    sum(ProfileEvent_ZooKeeperExists) AS exists_requests,

-----------------------------------------------------------------------------------------------------------------
-- Number of times watch notification has been received from ZooKeeper.
-----------------------------------------------------------------------------------------------------------------
    sum(ProfileEvent_ZooKeeperWatchResponse) AS watch_notifications,

-----------------------------------------------------------------------------------------------------------------
-- Number of bytes send over network while communicating with ZooKeeper.
-----------------------------------------------------------------------------------------------------------------
   formatReadableSize(sum(ProfileEvent_ZooKeeperBytesSent)) AS data_sent,

-----------------------------------------------------------------------------------------------------------------
-- Number of bytes received over network while communicating with ZooKeeper.
-----------------------------------------------------------------------------------------------------------------
   formatReadableSize(sum(ProfileEvent_ZooKeeperBytesReceived)) AS data_received
FROM clusterAllReplicas(default, system.metric_log)
WHERE event_time >= start_time AND event_time <= insert_end_time
SETTINGS skip_unavailable_shards = 1
FORMAT Vertical;