clickhouse的sql语法4之system的了解-查看当前实时连接数-clickhouse入门-利志分享

clickhouse的sql语法4之system的了解-查看当前实时连接数

阅读：1094 分享次数：0

关于clickhouse的system的库，里面是所有的系统所有的配置都在里面这里存着，我这里就挑几个比较重要的讲一下。

clusters表保存着所有的集群信息
databases表保存着当前所有的库信息
disks表保存当前磁盘信息
functions表保存着当前clickhouse支持的系统的方法
grants表保存着clickhouse授权的信息
query_log表保存着所有的sql执行日志
metrics表保存着当前的查询信息
processes表表示当前正在进行的系统操作信息表
table_engines保存着所有的表引擎
table_functions保存着所有的表函数
parts系统库表配置信息

关于system的库有几个比较重点的命令是常用的
1：查询当前clickhouse的查看当前实时连接数

select * from metrics where metric like '%Connection'
    
    ┌─metric────────────────┬─value─┬─description────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
    │ TCPConnection         │     1 │ Number of connections to TCP server (clients with native interface), also included server-server distributed query connections │
    │ MySQLConnection       │     0 │ Number of client connections using MySQL protocol                                                                              │
    │ HTTPConnection        │     0 │ Number of connections to HTTP server                                                                                           │
    │ InterserverConnection │     0 │ Number of connections from other replicas to fetch parts                                                                       │
    │ PostgreSQLConnection  │     0 │ Number of client connections using PostgreSQL protocol                                                                         │
    └───────────────────────┴───────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
	
2：clickhouse查询正在执行的查询

show processlist 或者
    SELECT query_id, user, address, query  FROM system.processes ORDER BY query_id;
    
    ┌─query_id─────────────────────────────┬─user────┬─address──────────┬─query───────────────────────────────────────────────────────────────────────────┐
    │ d570334d-e889-4388-9a7f-8ac1d4b7953f │ default │ ::ffff:127.0.0.1 │ SELECT query_id, user, address, query  FROM system.processes ORDER BY query_id; │
    └──────────────────────────────────────┴─────────┴──────────────────┴─────────────────────────────────────────────────────────────────────────────────┘
	
3：clickhouse终止查询
通过 KILL QUERY语句，可以终止正在执行的查询:
KILL QUERY WHERE query_id = ‘query_id’
例如，终止刚才的 INSERT 查询 :

KILL QUERY WHERE query_id='e9395abd-9367-4796-a6ec-a4e8a639aaea';

4：查看各个数据库占用空间统计

SELECT database, formatReadableSize(sum(bytes_on_disk)) AS on_disk FROM system.parts GROUP BY database
	
    ┌─database─┬─on_disk────┐
    │ system   │ 165.67 MiB │
    │ test     │ 4.73 KiB   │
    └──────────┴────────────┘
	
5：查询执行完的日志，这个可以看每个执行的sql的耗时，客户端是什么等。

SELECT
      user,
      client_hostname AS host,
      client_name AS client,
      formatDateTime(query_start_time, '%T') AS started,
      query_duration_ms / 1000 AS sec,
      round(memory_usage / 1048576) AS MEM_MB,
      result_rows AS RES_CNT,
      result_bytes / 1048576 AS RES_MB,
      read_rows AS R_CNT,
      round(read_bytes / 1048576) AS R_MB,
      written_rows AS W_CNT,
      round(written_bytes / 1048576) AS W_MB,
      query
    FROM
      system.query_log
    WHERE
      (type = 2)
      AND (query LIKE '%insert%')
    ORDER BY
      query_duration_ms DESC
    LIMIT
      10
     ┌─user────┬─host───────────┬─client──────┬─started──┬───sec─┬─MEM_MB─┬─RES_CNT─┬─────────────────RES_MB─┬─R_CNT─┬─R_MB─┬─W_CNT─┬─W_MB─┬─query──────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┐
    │ default │ VM-0-13-centos │ ClickHouse  │ 19:21:37 │ 0.177 │      0 │       0 │                      0 │     0 │    0 │     0 │    0 │ insert into test_aggremergetree (id,name,money,create_at) values                                                                                                                                                                                           │
    │ default │ VM-0-13-centos │ ClickHouse  │ 13:35:36 │ 0.015 │      4 │       0 │                      0 │ 36405 │    8 │     0 │    0 │ SELECT user, client_hostname AS host, client_name AS client, formatDateTime(query_start_time, '%T') AS started, query_duration_ms / 1000 AS sec, round(memory_usage / 1048576) AS MEM_MB, result_rows AS RES_CNT, result_bytes / 1048576 AS RES_MB, read_rows AS R_CNT, round(read_bytes / 1048576) AS R_MB, written_rows AS W_CNT, round(written_bytes / 1048576) AS W_MB, query FROM system.query_log WHERE type = 2 and query like '%insert' ORDER BY query_duration_ms DESC LIMIT 10 │
    │ default │ VM-0-13-centos │ ClickHouse  │ 19:26:09 │ 0.007 │      4 │       0 │                      0 │     1 │    0 │     1 │    0 │ insert into table test_aggremergetree select 1,'测试',sumState(toDecimal32(10,2)),'2021-03-21 00:00:00';                                                                                                                                                   │
    │ default │ VM-0-13-centos │ ClickHouse  │ 19:11:00 │ 0.005 │      0 │       1 │ 0.00002574920654296875 │     0 │    0 │     1 │    0 │ insert into test_aggremergetree (id,name,age,create_at) values                                                                                                                                                                                             │
    │ default │ VM-0-13-centos │ ClickHouse  │ 22:40:03 │ 0.005 │      4 │       1 │    0.00000762939453125 │     0 │    0 │     1 │    0 │ insert into action1 values                                                                                                                                                                                                                                 │
    │ default │ VM-0-13-centos │ ClickHouse  │ 22:35:10 │ 0.004 │      4 │       1 │ 0.00002193450927734375 │     0 │    0 │     1 │    0 │ insert into action values                                                                                                                                                                                                                                  │
    │ default │ VM-0-13-centos │ ClickHouse  │ 19:10:58 │ 0.003 │      0 │       1 │ 0.00002574920654296875 │     0 │    0 │     1 │    0 │ insert into test_aggremergetree (id,name,age,create_at) values                                                                                                                                                                                             │
    │ default │ VM-0-13-centos │ ClickHouse  │ 19:44:37 │ 0.002 │      4 │       0 │                      0 │     1 │    0 │     1 │    0 │ insert into table test_summergetree select 1,'测试222',600,now();                                                                                                                                                                                          │
    │ default │ VM-0-13-centos │ ClickHouse  │ 19:10:56 │ 0.002 │      4 │       1 │ 0.00002574920654296875 │     0 │    0 │     1 │    0 │ insert into test_aggremergetree (id,name,age,create_at) values                                                                                                                                                                                             │
    │ default │ VM-0-13-centos │ ClickHouse  │ 19:45:54 │ 0.002 │      4 │       0 │                      0 │     1 │    0 │     1 │    0 │ insert into table test_summergetree select 11,'测试222',800,now();                                                                                                                                                                                         │
    └─────────┴────────────────┴─────────────┴──────────┴───────┴────────┴─────────┴────────────────────────┴───────┴──────┴───────┴──────┴────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────────┘
    
	
6：查询当前库表资源占用情况：

select
      database,
      table,
      sum(rows) AS "总行数",
      formatReadableSize(sum(data_uncompressed_bytes)) as "原始大小",
      formatReadableSize(sum(data_compressed_bytes)) AS "压缩大小",
      round(
        (
          sum(data_compressed_bytes) / sum(data_uncompressed_bytes)
        ) * 100.,
        2
      ) AS "压缩率/%"
    from
      system.parts
    group by
      database,
      table
    order by
      database

┌─database─┬─table───────────────────┬──总行数─┬─原始大小───┬─压缩大小───┬─压缩率/%─┐
    │ system   │ query_thread_log        │   43608 │ 26.13 MiB  │ 3.85 MiB   │    14.73 │
    │ system   │ metric_log              │ 5527039 │ 10.82 GiB  │ 234.20 MiB │     2.11 │
    │ system   │ query_log               │   43709 │ 32.88 MiB  │ 3.44 MiB   │    10.47 │
    │ system   │ asynchronous_metric_log │ 4314984 │ 179.81 MiB │ 10.00 MiB  │     5.56 │
    │ system   │ trace_log               │    4768 │ 1.00 MiB   │ 96.96 KiB  │     9.45 │
    │ test     │ action4                 │       2 │ 16.00 B    │ 120.00 B   │      750 │
    │ test     │ action2                 │       5 │ 40.00 B    │ 300.00 B   │      750 │
    │ test     │ action3                 │       4 │ 32.00 B    │ 240.00 B   │      750 │
    │ test     │ test_summergetree       │       2 │ 41.00 B    │ 146.00 B   │    356.1 │
    │ test     │ action1                 │       6 │ 48.00 B    │ 308.00 B   │   641.67 │
    │ test     │ action                  │      17 │ 255.00 B   │ 719.00 B   │   281.96 │
    │ test     │ test_aggremergetree     │       1 │ 31.00 B    │ 131.00 B   │   422.58 │
    └──────────┴─────────────────────────┴─────────┴────────────┴────────────┴──────────┘

感觉本站内容不错，读后有收获？

我要小额打赏，鼓励作者写出更好的教程

扫码关注公众号：talk_lizhi