search
数据采集 采集源配置 clickhouse 指标采集

clickhouse 指标采集

简介

采集 clickhouse 数据指标并上报到 DataFlux 中

前置条件

配置

进入 DataKit 安装目录下的 conf.d/db 目录,复制 clickhouse.conf.sample 并命名为 clickhouse.conf。示例如下:

# Read metrics from one or many ClickHouse servers
[[inputs.clickhouse]]
  ## Username for authorization on ClickHouse server
  ## example: username = "default"
  username = "default"

  ## Password for authorization on ClickHouse server
  ## example: password = "super_secret"

  ## HTTP(s) timeout while getting metrics values
  ## The timeout includes connection time, any redirects, and reading the response body.
  ##   example: timeout = 1s
  # timeout = 5s

  ## List of servers for metrics scraping
  ## metrics scrape via HTTP(s) clickhouse interface
  ## https://clickhouse.tech/docs/en/interfaces/http/
  ##    example: servers = ["http://127.0.0.1:8123","https://custom-server.mdb.yandexcloud.net"]
  servers         = ["http://127.0.0.1:8123"]

  ## If "auto_discovery"" is "true" plugin tries to connect to all servers available in the cluster
  ## with using same "user:password" described in "user" and "password" parameters
  ## and get this server hostname list from "system.clusters" table
  ## see
  ## - https://clickhouse.tech/docs/en/operations/system_tables/#system-clusters
  ## - https://clickhouse.tech/docs/en/operations/server_settings/settings/#server_settings_remote_servers
  ## - https://clickhouse.tech/docs/en/operations/table_engines/distributed/
  ## - https://clickhouse.tech/docs/en/operations/table_engines/replication/#creating-replicated-tables
  ##    example: auto_discovery = false
  # auto_discovery = true

  ## Filter cluster names in "system.clusters" when "auto_discovery" is "true"
  ## when this filter present then "WHERE cluster IN (...)" filter will apply
  ## please use only full cluster names here, regexp and glob filters is not allowed
  ## for "/etc/clickhouse-server/config.d/remote.xml"
  ## <yandex>
  ##  <remote_servers>
  ##    <my-own-cluster>
  ##        <shard>
  ##          <replica><host>clickhouse-ru-1.local</host><port>9000</port></replica>
  ##          <replica><host>clickhouse-ru-2.local</host><port>9000</port></replica>
  ##        </shard>
  ##        <shard>
  ##          <replica><host>clickhouse-eu-1.local</host><port>9000</port></replica>
  ##          <replica><host>clickhouse-eu-2.local</host><port>9000</port></replica>
  ##        </shard>
  ##    </my-onw-cluster>
  ##  </remote_servers>
  ##
  ## </yandex>
  ##
  ## example: cluster_include = ["my-own-cluster"]
  # cluster_include = []

  ## Filter cluster names in "system.clusters" when "auto_discovery" is "true"
  ## when this filter present then "WHERE cluster NOT IN (...)" filter will apply
  ##    example: cluster_exclude = ["my-internal-not-discovered-cluster"]
  # cluster_exclude = []

  ## Optional TLS Config
  # tls_ca = "/etc/telegraf/ca.pem"
  # tls_cert = "/etc/telegraf/cert.pem"
  # tls_key = "/etc/telegraf/key.pem"
  ## Use TLS but skip chain & host verification
  # insecure_skip_verify = false

注意,auto_discovery 参数的作用是,在分布式 clickhouse 中自动发现其他节点,并将其地址添加到采集列表中。

例如 servers 参数中配置了 [http://192.168.1.10:8123:],当 auto_discoverytrue 时,采集器会自动发现与 192.168.1.10:8123 在同一个集群的其他 clickhouse(比如 ip 为192.168.1.20192.168.1.30),

此时采集器会给 192.168.1.20192.168.1.30 配置端口 8123,并将这两个地址添加到采集列表中,后续会自动采集这两个 clickhouse 的指标。详细说明需查看配置文件中的官方文档。

auto_discovery 默认是开启状态,即 true。非分布式 clickhouse 请将此参数手动设置为 false

配置好后,重启 DataKit 即可生效。

采集指标

  • 指标集 clickhouse_events
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
arena_alloc_bytes fields int
arena_alloc_chunks fields int
compressed_read_buffer_blocks fields int
compressed_read_buffer_bytes fields int
context_lock fields int
created_read_buffer_ordinary fields int
created_write_buffer_ordinary fields int
disk_read_elapsed_microseconds fields int
disk_write_elapsed_microseconds fields int
file_open fields int
function_execute fields int
inserted_bytes fields int
inserted_rows fields int
io_buffer_alloc_bytes fields int
io_buffer_allocs fields int
merge fields int
merge_tree_data_writer_blocks fields int
merge_tree_data_writer_blocks_already_sorted fields int
merge_tree_data_writer_compressed_bytes fields int
merge_tree_data_writer_rows fields int
merge_tree_data_writer_uncompressed_bytes fields int
merged_rows fields int
merged_uncompressed_bytes fields int
merges_time_milliseconds fields int
os_read_chars fields int
os_write_bytes fields int
os_write_chars fields int
oscpu_virtual_time_microseconds fields int
query fields int
read_buffer_from_file_descriptor_read fields int
read_buffer_from_file_descriptor_read_bytes fields int
read_compressed_bytes fields int
real_time_microseconds fields int
rw_lock_acquired_read_locks fields int
select_query fields int
soft_page_faults fields int
system_time_microseconds fields int
user_time_microseconds fields int
write_buffer_from_file_descriptor_write fields int
write_buffer_from_file_descriptor_write_bytes fields int

详情查看 system.events

  • 指标集 clickhouse_metrics
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
background_buffer_flush_schedule_pool_task fields int
background_distributed_schedule_pool_task fields int
background_move_pool_task fields int
background_pool_task fields int
background_schedule_pool_task fields int
cache_dictionary_update_queue_batches fields int
cache_dictionary_update_queue_keys fields int
context_lock_wait fields int
delayed_inserts fields int
dict_cache_requests fields int
disk_space_reserved_for_merge fields int
distributed_files_to_insert fields int
distributed_send fields int
ephemeral_node fields int
global_thread fields int
global_thread_active fields int
http_connection fields int
interserver_connection fields int
local_thread fields int
local_thread_active fields int
memory_tracking fields int
memory_tracking_for_merges fields int
memory_tracking_in_background_buffer_flush_schedule_pool fields int
memory_tracking_in_background_distributed_schedule_pool fields int
memory_tracking_in_background_move_processing_pool fields int
memory_tracking_in_background_processing_pool fields int
memory_tracking_in_background_schedule_pool fields int
merge fields int
my_sql_connection fields int
open_file_for_read fields int
open_file_for_write fields int
part_mutation fields int
postgre_sql_connection fields int
query fields int
query_preempted fields int
query_thread fields int
read fields int
readonly_replica fields int
replicated_checks fields int
replicated_fetch fields int
replicated_send fields int
revision fields int
rw_lock_active_readers fields int
rw_lock_active_writers fields int
rw_lock_waiting_readers fields int
rw_lock_waiting_writers fields int
send_external_tables fields int
send_scalars fields int
storage_buffer_bytes fields int
storage_buffer_rows fields int
tcp_connection fields int
version_integer fields int
write fields int
zoo_keeper_request fields int
zoo_keeper_session fields int
zoo_keeper_watch fields int

详情查看 system.metrics

  • 指标集 clickhouse_asynchronous_metrics
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
compiled_expression_cache_count fields int
cpu_frequency_m_hz_0 fields int
cpu_frequency_m_hz_1 fields int
cpu_frequency_m_hz_2 fields int
cpu_frequency_m_hz_3 fields int
cpu_frequency_m_hz_4 fields int
cpu_frequency_m_hz_5 fields int
cpu_frequency_m_hz_6 fields int
jemalloc.active fields int
jemalloc.allocated fields int
jemalloc.arenas.all.dirty_purged fields int
jemalloc.arenas.all.muzzy_purged fields int
jemalloc.arenas.all.pactive fields int
jemalloc.arenas.all.pdirty fields int
jemalloc.arenas.all.pmuzzy fields int
jemalloc.background_thread.num_runs fields int
jemalloc.background_thread.num_threads fields int
jemalloc.background_thread.run_intervals fields int
jemalloc.epoch fields int
jemalloc.mapped fields int
jemalloc.metadata fields int
jemalloc.metadata_thp fields int
jemalloc.resident fields int
jemalloc.retained fields int
mark_cache_bytes fields int
mark_cache_files fields int
max_part_count_for_partition fields int
memory_code fields int
memory_data_and_stack fields int
memory_resident fields int
memory_shared fields int
memory_virtual fields int
number_of_databases fields int
number_of_tables fields int
replicas_max_absolute_delay fields int
replicas_max_inserts_in_queue fields int
replicas_max_merges_in_queue fields int
replicas_max_queue_size fields int
replicas_max_relative_delay fields int
replicas_sum_inserts_in_queue fields int
replicas_sum_merges_in_queue fields int
replicas_sum_queue_size fields int
uncompressed_cache_bytes fields int
uncompressed_cache_cells fields int
uptime fields int

详情查看 system.asynchronous_metrics

  • 指标集 clickhouse_tables
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
database tags string
table tags string
bytes fields int
parts fields int
rows fields int
  • 指标集 clickhouse_zookeeper
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
root_nodes fields int

root_nodes system.zookeeper 节点数,其中 path = /

  • 指标集 clickhouse_replication_queue
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
too_many_tries_replicas fields int

too_many_tries_replicas 是在 system.replication_queuenum_tries 大于 1 的副本数量。

  • 指标集 clickhouse_detached_parts
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
detached_parts fields int

detached_parts 是从 system.detached_parts 中分离的表和数据库的总数。

  • 指标集 clickhouse_dictionaries
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
dict_origin fields int
is_loaded fields int
bytes_allocated fields int

dict_origin xml Filename when dictionary created from *_dictionary.xml, database.table when dictionary created from DDL

is_loaded 0 - when dictionary data not successful load, 1 - when dictionary data loading fail, see system.dictionaries for details

bytes_allocated how many bytes allocated in RAM after a dictionary loaded)

  • 指标集 clickhouse_mutations
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
running fields int
failed fields int
completed fields int

running 尚未完成的数量

failed 执行失败数量

completed 执行成功数量

详情查看 system.mutations

  • 指标集 clickhouse_disks
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
name tags string
path tags string
free_space_percent fields int
keep_free_space_percent fields int

free_space_percent 衡量显示可用磁盘空间字节相对于总磁盘空间字节的当前百分比

keep_free_space_percent 测量显示当前所需的保留可用磁盘字节相对于总磁盘空间字节的百分比

详情查看 system.disks

  • 指标集 clickhouse_processes
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
percentile_50 fields int
percentile_90 fields int
longest_running fields int

percentile_50 显示正在运行的进程的已用字段为50%(分位数0.5)

percentile_90 显示正在运行的进程的已使用字段的百分比为90%(分位数0.9)

longest_running 示正在运行的进程的经过字段的最大值

详情查看 system.processes

  • 指标集 clickhouse_text_log
指标 类型 单位
source tags string
cluster tags string
shard_num tags string
level tags string
messages_last_10_min fields int

level 消息级别,仅收集具有小于或等于“通知”级别的消息,详情见 system.text_log

示例输出

clickhouse_asynchronous_metrics,cluster=test_cluster_two_shards_localhost,host=ubuntu-server,name=admin,shard_num=1,source=localhost cpu_frequency_m_hz_0=2194i,cpu_frequency_m_hz_6=2194i,replicas_max_absolute_delay=0i,cpu_frequency_m_hz_2=2194i,cpu_frequency_m_hz_1=2194i,number_of_databases=3i,replicas_max_merges_in_queue=0i,memory_virtual=1326022656i,jemalloc.arenas.all.dirty_purged=87415i,replicas_max_inserts_in_queue=0i,memory_data_and_stack=673378304i,number_of_tables=57i,cpu_frequency_m_hz_5=2194i,replicas_max_queue_size=0i,mark_cache_bytes=0i,memory_resident=170627072i,replicas_sum_merges_in_queue=0i,replicas_sum_inserts_in_queue=0i,jemalloc.arenas.all.pmuzzy=1i,jemalloc.mapped=125575168i,jemalloc.epoch=12i,jemalloc.background_thread.num_threads=0i,jemalloc.arenas.all.muzzy_purged=81497i,mark_cache_files=0i,jemalloc.retained=56877056i,jemalloc.allocated=46833320i,replicas_sum_queue_size=0i,memory_code=320724992i,uncompressed_cache_bytes=0i,jemalloc.background_thread.num_runs=0i,jemalloc.active=51195904i,max_part_count_for_partition=5i,memory_shared=89858048i,uncompressed_cache_cells=0i,cpu_frequency_m_hz_4=2194i,jemalloc.resident=104243200i,replicas_max_relative_delay=0i,jemalloc.arenas.all.pdirty=11502i,jemalloc.background_thread.run_intervals=0i,jemalloc.metadata=7318064i,uptime=615i,jemalloc.metadata_thp=0i,compiled_expression_cache_count=0i,cpu_frequency_m_hz_3=2194i,jemalloc.arenas.all.pactive=12499i 1597644720000000000

clickhouse_metrics,cluster=test_cluster_two_shards_localhost,host=ubuntu-server,name=admin,shard_num=1,source=localhost memory_tracking_in_background_distributed_schedule_pool=0i,local_thread=0i,cache_dictionary_update_queue_keys=0i,disk_space_reserved_for_merge=0i,replicated_checks=0i,background_pool_task=0i,tcp_connection=0i,send_external_tables=0i,global_thread_active=46i,replicated_send=0i,background_buffer_flush_schedule_pool_task=0i,revision=54436i,open_file_for_write=0i,ephemeral_node=0i,delayed_inserts=0i,postgre_sql_connection=0i,context_lock_wait=0i,interserver_connection=0i,open_file_for_read=7i,memory_tracking_in_background_schedule_pool=0i,storage_buffer_bytes=0i,query_preempted=0i,memory_tracking_in_background_move_processing_pool=0i,rw_lock_waiting_writers=0i,rw_lock_active_writers=0i,merge=0i,cache_dictionary_update_queue_batches=0i,readonly_replica=0i,distributed_send=0i,send_scalars=0i,storage_buffer_rows=0i,dict_cache_requests=0i,memory_tracking_for_merges=1108200i,zoo_keeper_request=0i,local_thread_active=0i,query=1i,http_connection=1i,read=2i,query_thread=0i,memory_tracking_in_background_processing_pool=1108200i,version_integer=20006003i,global_thread=48i,distributed_files_to_insert=0i,part_mutation=0i,my_sql_connection=0i,memory_tracking=170627072i,zoo_keeper_session=0i,replicated_fetch=0i,background_move_pool_task=0i,background_schedule_pool_task=0i,memory_tracking_in_background_buffer_flush_schedule_pool=0i,background_distributed_schedule_pool_task=0i,write=0i,zoo_keeper_watch=0i,rw_lock_waiting_readers=0i,rw_lock_active_readers=1i 1597644720000000000

clickhouse_events,cluster=test_cluster_two_shards_localhost,host=ubuntu-server,name=admin,shard_num=1,source=localhost select_query=11i,read_buffer_from_file_descriptor_read=13084i,read_compressed_bytes=977530i,created_read_buffer_ordinary=216i,merged_rows=5512i,merge_tree_data_writer_rows=1097i,os_write_bytes=4096i,query=11i,disk_write_elapsed_microseconds=10946i,rw_lock_acquired_read_locks=1410i,read_buffer_from_file_descriptor_read_bytes=1810450i,created_write_buffer_ordinary=963i,merge_tree_data_writer_uncompressed_bytes=1246838i,system_time_microseconds=673i,arena_alloc_chunks=3i,disk_read_elapsed_microseconds=570682766i,merge_tree_data_writer_compressed_bytes=1422177i,context_lock=1448i,os_read_chars=4083i,function_execute=147i,merged_uncompressed_bytes=10053922i,oscpu_virtual_time_microseconds=4101i,os_write_chars=10966i,io_buffer_alloc_bytes=556479885i,arena_alloc_bytes=12288i,merge_tree_data_writer_blocks_already_sorted=102i,io_buffer_allocs=2431i,compressed_read_buffer_blocks=23214i,compressed_read_buffer_bytes=10051706i,merge_tree_data_writer_blocks=102i,soft_page_faults=123i,write_buffer_from_file_descriptor_write=1276i,write_buffer_from_file_descriptor_write_bytes=2944754i,inserted_rows=1097i,inserted_bytes=1246838i,merge=216i,merges_time_milliseconds=156i,real_time_microseconds=11650i,user_time_microseconds=3444i,file_open=1206i 1597644720000000000

clickhouse_tables,cluster=test_cluster_two_shards_localhost,database=system,host=ubuntu-server,name=admin,shard_num=1,source=localhost,table=trace_log bytes=1735i,parts=2i,rows=8i 1597644720000000000

clickhouse_tables,cluster=test_cluster_two_shards_localhost,database=system,host=ubuntu-server,name=admin,shard_num=1,source=localhost,table=metric_log bytes=99366i,parts=5i,rows=639i 1597644720000000000

clickhouse_tables,cluster=test_cluster_two_shards_localhost,database=system,host=ubuntu-server,name=admin,shard_num=1,source=localhost,table=asynchronous_metric_log rows=450i,bytes=5720i,parts=5i 1597644720000000000