ClickHouse

The ClickHouse Integration allows you to effortlessly ingest and monitor your column-oriented data. This integration is useful for identifying system-wide usage patterns, performance bottlenecks in your ClickHouse clusters, and analyzing high-volume SQL query performance in real-time.

This integration works by directing your ClickHouse data to a Prometheus endpoint that asynchronously ingests and redirects ClickHouse metrics and events to Middleware.

Prerequisites

Step 1: Update ClickHouse Configuration

Add the following lines to your ClickHouse configuration (/etc/clickhouse-server/config.xml) to expose metrics on port 9126

Step 2: Navigate to ClickHouse Integration

Login to your Middleware account, navigate to the ClickHouse integration, and connect ClickHouse

Step 3: Connect ClickHouse Instance

To access ClickHouse metrics, choose a host to connect to your ClickHouse instance

Step 4: Configure ClickHouse Instance

Configure your host to connect to the IP address and port that ClickHouse stores its metrics

The default port for your ClickHouse instance is 9126

Visualize Your Data

Default ClickHouse Dashboard

Quickly access your ClickHouse data with Middleware’s default ClickHouse dashboard. Navigate to the Dashboard Builder and select the ClickHouse - Metrics (Runtime) Dashboard

Create ClickHouse Widgets

Create your own widget from scratch. Navigate to the Dashboard Builder and select the dashboard you would like to create a widget in. Select Add New Widget and choose the ClickHouse data source.

Learn more about creating your own widgets and dashboards in the Dashboard Builder section.

Metrics Collected

Metrics NameDescription
AggregatorThreadsNumber of threads in the Aggregator thread pool.
AggregatorThreadsActiveNumber of threads in the Aggregator thread pool running a task.
TablesLoaderForegroundThreadsNumber of threads in the async loader foreground thread pool.
TablesLoaderForegroundThreadsActiveNumber of threads in the async loader foreground thread pool running a task.
TablesLoaderBackgroundThreadsNumber of threads in the async loader background thread pool.
TablesLoaderBackgroundThreadsActiveNumber of threads in the async loader background thread pool running a task.
AsyncInsertCacheSizeNumber of async insert hash id in cache
AsynchronousInsertThreadsNumber of threads in the AsynchronousInsert thread pool.
AsynchronousInsertThreadsActiveNumber of threads in the AsynchronousInsert thread pool running a task.
AsynchronousReadWaitNumber of threads waiting for asynchronous read.
BackgroundBufferFlushSchedulePoolSizeLimit on number of tasks in BackgroundBufferFlushSchedulePool
BackgroundBufferFlushSchedulePoolTaskNumber of active tasks in BackgroundBufferFlushSchedulePool. This pool is used for periodic Buffer flushes
BackgroundCommonPoolSizeLimit on number of tasks in an associated background pool
BackgroundCommonPoolTaskNumber of active tasks in an associated background pool
BackgroundDistributedSchedulePoolSizeLimit on number of tasks in BackgroundDistributedSchedulePool
BackgroundDistributedSchedulePoolTaskNumber of active tasks in BackgroundDistributedSchedulePool. This pool is used for distributed sends that is done in background.
BackgroundFetchesPoolSizeLimit on number of simultaneous fetches in an associated background pool
BackgroundFetchesPoolTaskNumber of active fetches in an associated background pool
BackgroundMergesAndMutationsPoolSizeLimit on number of active merges and mutations in an associated background pool
BackgroundMergesAndMutationsPoolTaskNumber of active merges and mutations in an associated background pool
BackgroundMessageBrokerSchedulePoolSizeLimit on number of tasks in BackgroundProcessingPool for message streaming
BackgroundMessageBrokerSchedulePoolTaskNumber of active tasks in BackgroundProcessingPool for message streaming
BackgroundMovePoolSizeLimit on number of tasks in BackgroundProcessingPool for moves
BackgroundMovePoolTaskNumber of active tasks in BackgroundProcessingPool for moves
BackgroundSchedulePoolSizeLimit on number of tasks in BackgroundSchedulePool. This pool is used for periodic ReplicatedMergeTree tasks, like cleaning old data parts, altering data parts, replica re-initialization, etc.
BackgroundSchedulePoolTaskNumber of active tasks in BackgroundSchedulePool. This pool is used for periodic ReplicatedMergeTree tasks, like cleaning old data parts, altering data parts, replica re-initialization, etc.
BackupsIOThreadsNumber of threads in the BackupsIO thread pool.
BackupsIOThreadsActiveNumber of threads in the BackupsIO thread pool running a task.
BackupsThreadsNumber of threads in the thread pool for BACKUP.
BackupsThreadsActiveNumber of threads in thread pool for BACKUP running a task.
BrokenDistributedFilesToInsertNumber of files for asynchronous insertion into Distributed tables that has been marked as broken. This metric will starts from 0 on start. Number of files for every shard is summed.
CacheDetachedFileSegmentsNumber of existing detached cache file segments
CacheDictionaryThreadsNumber of threads in the CacheDictionary thread pool.
CacheDictionaryThreadsActiveNumber of threads in the CacheDictionary thread pool running a task.
CacheDictionaryUpdateQueueBatchesNumber of 'batches' (a set of keys) in update queue in CacheDictionaries.
CacheDictionaryUpdateQueueKeysExact number of keys in update queue in CacheDictionaries.
CacheFileSegmentsNumber of existing cache file segments
ContextLockWaitNumber of threads waiting for lock in Context. This is global lock.
DDLWorkerThreadsNumber of threads in the DDLWorker thread pool for ON CLUSTER queries.
DDLWorkerThreadsActiveNumber of threads in the DDLWORKER thread pool for ON CLUSTER queries running a task.
DatabaseCatalogThreadsNumber of threads in the DatabaseCatalog thread pool.
DatabaseCatalogThreadsActiveNumber of threads in the DatabaseCatalog thread pool running a task.
DatabaseOnDiskThreadsNumber of threads in the DatabaseOnDisk thread pool.
DatabaseOnDiskThreadsActiveNumber of threads in the DatabaseOnDisk thread pool running a task.
DelayedInsertsNumber of INSERT queries that are throttled due to high number of active data parts for partition in a MergeTree table.
DestroyAggregatesThreadsNumber of threads in the thread pool for destroy aggregate states.
DestroyAggregatesThreadsActiveNumber of threads in the thread pool for destroy aggregate states running a task.
DictCacheRequestsNumber of requests in fly to data sources of dictionaries of cache type.
DiskObjectStorageAsyncThreadsNumber of threads in the async thread pool for DiskObjectStorage.
DiskObjectStorageAsyncThreadsActiveNumber of threads in the async thread pool for DiskObjectStorage running a task.
DiskSpaceReservedForMergeDisk space reserved for currently running background merges. It is slightly more than the total size of currently merging parts.
DistributedFilesToInsertNumber of pending files to process for asynchronous insertion into Distributed tables. Number of files for every shard is summed.
DistributedSendNumber of connections to remote servers sending data that was INSERTed into Distributed tables. Both synchronous and asynchronous mode.
EphemeralNodeNumber of ephemeral nodes hold in ZooKeeper.
FilesystemCacheElementsFilesystem cache elements (file segments)
FilesystemCacheReadBuffersNumber of active cache buffers
FilesystemCacheSizeFilesystem cache size in bytes
GlobalThreadNumber of threads in global thread pool.
GlobalThreadActiveNumber of threads in global thread pool running a task.
HTTPConnectionNumber of connections to HTTP server
HashedDictionaryThreadsNumber of threads in the HashedDictionary thread pool.
HashedDictionaryThreadsActiveNumber of threads in the HashedDictionary thread pool running a task.
IOPrefetchThreadsNumber of threads in the IO prefertch thread pool.
IOPrefetchThreadsActiveNumber of threads in the IO prefetch thread pool running a task.
IOThreadsNumber of threads in the IO thread pool.
IOThreadsActiveNumber of threads in the IO thread pool running a task.
IOUringInFlightEventsNumber of io_uring SQEs in flight
IOUringPendingEventsNumber of io_uring SQEs waiting to be submitted
IOWriterThreadsNumber of threads in the IO writer thread pool.
IOWriterThreadsActiveNumber of threads in the IO writer thread pool running a task.
InterserverConnectionNumber of connections from other replicas to fetch parts
KafkaAssignedPartitionsNumber of partitions Kafka tables currently assigned to
KafkaBackgroundReadsNumber of background reads currently working (populating materialized views from Kafka)
KafkaConsumersNumber of active Kafka consumers
KafkaConsumersInUseNumber of consumers which are currently used by direct or background reads
KafkaConsumersWithAssignmentNumber of active Kafka consumers which have some partitions assigned.
KafkaLibrdkafkaThreadsNumber of active librdkafka threads
KafkaProducersNumber of active Kafka producer created
KafkaWritesNumber of currently running inserts to Kafka
KeeperAliveConnectionsNumber of alive connections
KeeperOutstandingRequetsNumber of outstanding requests
LocalThreadNumber of threads in local thread pools. The threads in local thread pools are taken from the global thread pool.
LocalThreadActiveNumber of threads in local thread pools running a task.
MMappedAllocBytesSum bytes of mmapped allocations
MMappedAllocsTotal number of mmapped allocations
MMappedFileBytesSum size of mmapped file regions.
MMappedFilesTotal number of mmapped files.
MarksLoaderThreadsNumber of threads in thread pool for loading marks.
MarksLoaderThreadsActiveNumber of threads in the thread pool for loading marks running a task.
MaxDDLEntryIDMax processed DDL entry of DDLWorker.
MaxPushedDDLEntryIDMax DDL entry of DDLWorker that pushed to zookeeper.
MemoryTrackingTotal amount of memory (bytes) allocated by the server.
MergeNumber of executing background merges
MergeTreeAllRangesAnnouncementsSentThe current number of announcement being sent in flight from the remote server to the initiator server about the set of data parts (for MergeTree tables). Measured on the remote server side.
MergeTreeBackgroundExecutorThreadsNumber of threads in the MergeTreeBackgroundExecutor thread pool.
MergeTreeBackgroundExecutorThreadsActiveNumber of threads in the MergeTreeBackgroundExecutor thread pool running a task.
MergeTreeDataSelectExecutorThreadsNumber of threads in the MergeTreeDataSelectExecutor thread pool.
MergeTreeDataSelectExecutorThreadsActiveNumber of threads in the MergeTreeDataSelectExecutor thread pool running a task.
MergeTreePartsCleanerThreadsNumber of threads in the MergeTree parts cleaner thread pool.
MergeTreePartsCleanerThreadsActiveNumber of threads in the MergeTree parts cleaner thread pool running a task.
MergeTreePartsLoaderThreadsNumber of threads in the MergeTree parts loader thread pool.
MergeTreePartsLoaderThreadsActiveNumber of threads in the MergeTree parts loader thread pool running a task.
MergeTreeReadTaskRequestsSentThe current number of callback requests in flight from the remote server back to the initiator server to choose the read task (for MergeTree tables). Measured on the remote server side.
MoveNumber of currently executing moves
MySQLConnectionNumber of client connections using MySQL protocol
NetworkReceiveNumber of threads receiving data from network. Only ClickHouse-related network interaction is included, not by 3rd party libraries.
NetworkSendNumber of threads sending data to network. Only ClickHouse-related network interaction is included, not by 3rd party libraries.
OpenFileForReadNumber of files open for reading
OpenFileForWriteNumber of files open for writing
ParallelFormattingOutputFormatThreadsNumber of threads in the ParallelFormattingOutputFormatThreads thread pool.
ParallelFormattingOutputFormatThreadsActiveNumber of threads in the ParallelFormattingOutputFormatThreads thread pool running a task.
ParallelParsingInputFormatThreadsNumber of threads in the ParallelParsingInputFormat thread pool.
ParallelParsingInputFormatThreadsActiveNumber of threads in the ParallelParsingInputFormat thread pool running a task.
PartMutationNumber of mutations (ALTER DELETE/UPDATE)
PartsActiveActive data part, used by current and upcoming SELECTs.
PartsCommittedDeprecated. See PartsActive.
PartsCompactCompact parts.
PartsDeleteOnDestroyPart was moved to another disk and should be deleted in own destructor.
PartsDeletingNot active data part with identity refcounter, it is deleting right now by a cleaner.
PartsInMemoryIn-memory parts.
PartsOutdatedNot active data part, but could be used by only current SELECTs, could be deleted after SELECTs finishes.
PartsPreActiveThe part is in data_parts, but not used for SELECTs.
PartsPreCommittedDeprecated. See PartsPreActive.
PartsTemporaryThe part is generating now, it is not in data_parts list.
PartsWideWide parts.
PendingAsyncInsertNumber of asynchronous inserts that are waiting for flush.
PostgreSQLConnectionNumber of client connections using PostgreSQL protocol
QueryNumber of executing queries
QueryPreemptedNumber of queries that are stopped and waiting due to 'priority' setting.
QueryThreadNumber of query processing threads
RWLockActiveReadersNumber of threads holding read lock in a table RWLock.
RWLockActiveWritersNumber of threads holding write lock in a table RWLock.
RWLockWaitingReadersNumber of threads waiting for read on a table RWLock.
RWLockWaitingWritersNumber of threads waiting for write on a table RWLock.
ReadNumber of read (read, pread, io_getevents, etc.) syscalls in fly
ReadTaskRequestsSentThe current number of callback requests in flight from the remote server back to the initiator server to choose the read task (for s3Cluster table function and similar). Measured on the remote server side.
ReadonlyReplicaNumber of Replicated tables that are currently in readonly state due to re-initialization after ZooKeeper session loss or due to startup without ZooKeeper configured.
RemoteReadNumber of read with remote reader in fly
ReplicatedChecksNumber of data parts checking for consistency
ReplicatedFetchNumber of data parts being fetched from replica
ReplicatedSendNumber of data parts being sent to replicas
RestartReplicaThreadsNumber of threads in the RESTART REPLICA thread pool.
RestartReplicaThreadsActiveNumber of threads in the RESTART REPLICA thread pool running a task.
RestoreThreadsNumber of threads in the thread pool for RESTORE.
RestoreThreadsActiveNumber of threads in the thread pool for RESTORE running a task.
RevisionRevision of the server. It is a number incremented for every release or release candidate except patch releases.
S3RequestsS3 requests
SendExternalTablesNumber of connections that are sending data for external tables to remote servers. External tables are used to implement GLOBAL IN and GLOBAL JOIN operators with distributed subqueries.
SendScalarsNumber of connections that are sending data for scalars to remote servers.
StorageBufferBytesNumber of bytes in buffers of Buffer tables
StorageBufferRowsNumber of rows in buffers of Buffer tables
StorageDistributedThreadsNumber of threads in the StorageDistributed thread pool.
StorageDistributedThreadsActiveNumber of threads in the StorageDistributed thread pool running a task.
StorageHiveThreadsNumber of threads in the StorageHive thread pool.
StorageHiveThreadsActiveNumber of threads in the StorageHive thread pool running a task.
StorageS3ThreadsNumber of threads in the StorageS3 thread pool.
StorageS3ThreadsActiveNumber of threads in the StorageS3 thread pool running a task.
SystemReplicasThreadsNumber of threads in the system.replicas thread pool.
SystemReplicasThreadsActiveNumber of threads in the system.replicas thread pool running a task.
TCPConnectionNumber of connections to TCP server (clients with native interface), also included server-server distributed query connections
TablesToDropQueueSizeNumber of dropped tables, that are waiting for background data removal.
TemporaryFilesForAggregationNumber of temporary files created for external aggregation
TemporaryFilesForJoinNumber of temporary files created for JOIN
TemporaryFilesForSortNumber of temporary files created for external sorting
TemporaryFilesUnknownNumber of temporary files created without known purpose
ThreadPoolFSReaderThreadsNumber of threads in the thread pool for local_filesystem_read_method=threadpool.
ThreadPoolFSReaderThreadsActiveNumber of threads in the thread pool for local_filesystem_read_method=threadpool running a task.
ThreadPoolRemoteFSReaderThreadsNumber of threads in the thread pool for remote_filesystem_read_method=threadpool.
ThreadPoolRemoteFSReaderThreadsActiveNumber of threads in the thread pool for remote_filesystem_read_method=threadpool running a task.
ThreadsInOvercommitTrackerNumber of waiting threads inside of OvercommitTracker
TotalTemporaryFilesNumber of temporary files created
VersionIntegerVersion of the server in a single integer number in base-1000. For example, version 11.22.33 is translated to 11022033.
WriteNumber of write (write, pwrite, io_getevents, etc.) syscalls in fly
ZooKeeperRequestNumber of requests to ZooKeeper in fly.
ZooKeeperSessionNumber of sessions (connections) to ZooKeeper. Should be no more than one, because using more than one connection to ZooKeeper may lead to bugs due to lack of linearizability (stale reads) that ZooKeeper consistency model allows.
ZooKeeperWatchNumber of watches (event subscriptions) in ZooKeeper.

Troubleshooting

Missing Integrations Menu

If you do not see the Integrations Menu in Middlware, that means your account has not been granted Installation permissions. Contact your system administrator to add the Installation permission to your user role in Settings.

Next Steps

Need assistance or want to learn more about Middleware? Contact our support team in Slack.