Clickhouse 系统表

Clickhouse自带系统库system，启动时创建系统表，无数据文件，主要用于记录系统信息，我们可以同过系统表来查看clickhouse运行状态。

processes：当前连接进程信息

>select query_id,initial_user,initial_address,elapsed,query from system.processes;

is_initial_query:	1
user:	default
query_id:	b589a0a1-440a-4cd7-a03e-e6da0ddc427b
address:	127.0.0.1
port:	39714
initial_user:	default
initial_query_id:	b589a0a1-440a-4cd7-a03e-e6da0ddc427b
initial_address:	127.0.0.1
initial_port:	39714
interface:	1
os_user:	root
client_hostname:	A01-R21-I55-99-9001887.LOCAL
client_name:	ClickHouse
client_version_major:	18
client_version_minor:	14
client_version_patch:	19
client_revision:	54409
http_method:	0
http_user_agent:
quota_key:
elapsed:	0.001502786
is_cancelled:	0
read_rows:	0
read_bytes:	0
total_rows_approx:	0
written_rows:	0
written_bytes:	0
memory_usage:	0
peak_memory_usage:	121
query:	Select * from …
thread_numbers:	[74]
ProfileEvents.Names:	['Query','SelectQuery','ReadCompressedBytes','CompressedReadBufferBlocks','CompressedReadBufferBytes','IOBufferAllocs','IOBufferAllocBytes','ContextLock','RWLockAcquiredReadLocks']
ProfileEvents.Values:	[1,1,36,1,10,1,57,4,1]
Settings.Names:	['max_threads','max_query_size','queue_max_wait_ms','use_uncompressed_cache','background_pool_size','load_balancing','max_memory_usage']
Settings.Values:	['48','262144000','5000000','0','64','random','50000000000']

replicas：查看当前复制信息

>select database,table,is_leader,is_readonly,is_session_expired,future_parts,parts_to_check,columns_version,queue_size,inserts_in_queue,merges_in_queue,log_max_index,log_pointer,total_replicas,active_replicasFROM system.replicas WHERE is_readonly OR is_session_expired OR future_parts > 20 OR parts_to_check > 10 OR  queue_size > 20 OR  inserts_in_queue > 10 OR log_max_index  - log_pointer > 10 OR total_replicas < 2 OR active_replicas < total_replicas;

如果结果返回有值，则有延迟，需要排查。

asynchronous_metrics：系统性能表，非实时

与metrics计算方式不同，jemalloc是内存分配算法:

 

metrics：系统信息指标，实时

Query	当前连接数量
Merge	当前后台合并数量
PartMutation	当前更改的数量delete\update
ReplicatedFetch	从副本获取的数据
ReplicatedSend	发送到副本的数据
ReplicatedChecks	一致性校验的数量
BackgroundPoolTask
BackgroundSchedulePoolTask
DiskSpaceReservedForMerge
DistributedSend	分布式表发送远程的连接数
QueryPreempted
TCPConnection	TCP连接数量
HTTPConnection	HTTP连接数量
InterserverConnection
OpenFileForRead	打开读文件数
OpenFileForWrite	打开写文件数
Read
Write
SendExternalTables
QueryThread	查询处理线程数
ReadonlyReplica
LeaderReplica
MemoryTracking	当前查询内存分配的数量
MemoryTrackingInBackgroundProcessingPool
MemoryTrackingInBackgroundSchedulePool
MemoryTrackingForMerges	合并分配的内存数量
LeaderElection
EphemeralNode
ZooKeeperSession
ZooKeeperWatch
ZooKeeperRequest
DelayedInserts
ContextLockWait
StorageBufferRows
StorageBufferBytes
DictCacheRequests
Revision
RWLockWaitingReaders
RWLockWaitingWriters
RWLockActiveReaders
RWLockActiveWriters

build_options：软件信息

name	value
VERSION_FULL	ClickHouse 18.14.19
VERSION_DESCRIBE	v18.14.19-testing
VERSION_GITHASH	8883b8888de0a4b9b8a1687b450c4dd99a97f066
VERSION_REVISION	54409
BUILD_DATE	2018-12-20
BUILD_TYPE	Release
SYSTEM	Linux-3.10.0-957.1.3.el7.x86_64
SYSTEM_PROCESSOR	x86_64
LIBRARY_ARCHITECTURE
CMAKE_VERSION	3.12.2
C_COMPILER	/opt/rh/devtoolset-7/root/usr/bin/gcc
C_COMPILER_VERSION	7.3.1
CXX_COMPILER	/opt/rh/devtoolset-7/root/usr/bin/g++
CXX_COMPILER_VERSION	7.3.1
C_FLAGS	-pipe -msse4.1 -msse4.2 -mpopcnt  -fno-omit-frame-pointer  -Wall  -no-pie -O2 -DNDEBUG
CXX_FLAGS	-pipe -msse4.1 -msse4.2 -mpopcnt  -fno-omit-frame-pointer  -Wall   -Wnon-virtual-dtor -no-pie -O2 -DNDEBUG
LINK_FLAGS	-static-libgcc -static-libstdc++
BUILD_COMPILE_DEFINITIONS
BUILD_INCLUDE_DIRECTORIES
STATIC	ON
USE_EMBEDDED_COMPILER	0
USE_INTERNAL_MEMCPY	ON
USE_GLIBC_COMPATIBILITY	ON
USE_JEMALLOC	1
USE_TCMALLOC
USE_UNWIND	1
USE_ICU	1
USE_MYSQL	1
USE_RE2_ST	1
USE_VECTORCLASS
USE_RDKAFKA	1
USE_CAPNP	1
USE_POCO_SQLODBC
USE_POCO_DATAODBC	1
USE_POCO_MONGODB	1
USE_POCO_NETSSL	1

Clusters：集群信息

>select c.shard_num,c.host_name as master_host,d.host_name as rep_host from (select shard_num,host_name from system.clusters  where replica_num=1) as c left join (select shard_num,host_name from  system.clusters where replica_num=2) as d on c.shard_num=d.shard_num

 

cluster：群集名称

shard_num：集群中的分片编号，从1开始

shard_weight：写入数据时分片的相对权重，1

replica_num：分片中的副本号，从1开始

host_name：主机名，在config中指定

host_address：从DNS获取的主机IP地址

port：用于连接服务器的端口，9000

is_local： 是不是当前所在机器，1

user：用于连接服务器的用户的名称，默认default

 

data_type_families：数据类型

ClickHouse release 18.16.1版本新加的系统表

name：数据类型名称

case_insensitive：是否区分大小写，0不区分，1区分

alias_to：别名，BIGINT=Int64，TEXT=String

collations：字符集排序规则

databases、tables、columns

库、表、列信息

functions：函数信息

formats：输出格式

XML、JSON、Vertical、CSV等等

SELECT * FROM system.formats  FORMAT JSON；

events：已处理的系统事件监控

Query	总查询数
SelectQuery	仅select数量
InsertQuery	仅insert数量
FileOpen	打开文件数量
ReadBufferFromFileDescriptorRead	文件描述符的读取次数
ReadBufferFromFileDescriptorReadBytes	从文件描述符读取的字节数
WriteBufferFromFileDescriptorWrite	文件描述符的写入次数
WriteBufferFromFileDescriptorWriteBytes	写入文件描述符的字节数
InsertedRows	插入到所有表的行数
MergedRows	合并前的行数
MergesTimeMilliseconds	后台合并花费总时间

merges：正在进行合并的信息

database:	db
table:	table
elapsed:	0.038135789
progress:	0.294799805
num_parts:	5
source_part_names:	['20181231_12026210_12026567_44','20181231_12026568_12026568_0','20181231_12026570_12026570_0','20181231_12026572_12026572_0','20181231_12026574_12026574_0']
result_part_name:	20181231_12026210_12026574_45
partition_id:	20181231
total_size_bytes_compressed:	560012
total_size_marks:	7
bytes_read_uncompressed:	3824250
rows_read:	16905
bytes_written_uncompressed:	1852376
rows_written:	8192
columns_written:	0
memory_usage:	152843009
thread_number:	62

numbers 、numbers_mt：测试使用，从零开始的所有自然数

one：测试使用，类似于其他DBMS中的DUAL表

settings：查看当前设置信息

max_threads	最大线程
max_block_size	读取最大块大小
load_balancing	random分布式随机选取副本
force_primary_key	强制使用主键，无主键报错
force_index_by_date	强制使用时间，无时间报错
log_query_settings	将日志写入系统表query_log
log_queries	是否打开全日志
max_memory_usage	单个查询最大使用内存，0为无限制

parts、parts_columns：查看分区表信息

>select database,table,partition_id,partition,name,active,rows,path,modification_time from system.parts where table='';

zookeeper ：未配置ZooKeeper，则表不存在

>select * from system.zookeeper WHERE path = '/clickhouse/tables/02-02/cluster/' and name='metadata' FORMAT Vertical