clickhouse日常维护
1.查询正在执行的sql
select query_id, user, address, query FROM system.processes ORDER BY query_id;
终止查询:
kill query where query_id = 'query_id'
2.查询mutations
SELECT 和 INSERT 之外，在 ClickHouse 中还存在一类被称作 Mutation 的操作，也就是 ALTER DELETE 和 ALTER UPDATE
SELECT database, table, mutation_id, command, create_time, is_done FROM system.mutations;
终止mutation
kill mutation where mutation_id = 'mutation_id';
3.各数据库占用空间统计
SELECT
database,
formatReadableSize(sum(bytes_on_disk)) AS on_disk
FROM system.parts
GROUP BY database;
4.查看字段压缩比
SELECT
database,
table,
column,
any(type),
sum(column_data_compressed_bytes) AS compressed,
sum(column_data_uncompressed_bytes) AS uncompressed,
round(uncompressed / compressed, 2) AS ratio,
compressed / sum(rows) AS bpr,
sum(rows)
FROM system.parts_columns
WHERE active AND (database != 'system')
GROUP BY
database,
table,
column
ORDER BY
database ASC,
table ASC,
column ASC
Query id: afcc3d43-73cb-4be6-a2c4-b0de1d0d88d4
┌─database────┬─table──┬─column────────┬─any(type)────────┬──compressed─┬─uncompressed─┬──ratio─┬──────────────────bpr─┬──sum(rows)─┐
│ db_sentinel │ metric │ app │ String │ 756331102 │ 37651779156 │ 49.78 │ 0.5223987357655436 │ 1447804235 │
│ db_sentinel │ metric │ block_qps │ Int64 │ 51523505 │ 11582153888 │ 224.79 │ 0.03558734237298318 │ 1447804235 │
│ db_sentinel │ metric │ count │ Int64 │ 204264903 │ 11582153888 │ 56.7 │ 0.14108599633982974 │ 1447804235 │
│ db_sentinel │ metric │ exception_qps │ Int64 │ 54114898 │ 11582153888 │ 214.03 │ 0.03737722040853127 │ 1447804235 │
│ db_sentinel │ metric │ gmt_create │ DateTime │ 1461204548 │ 5791076944 │ 3.96 │ 1.0092556111358522 │ 1447804235 │
│ db_sentinel │ metric │ gmt_modified │ DateTime │ 1461204548 │ 5791076944 │ 3.96 │ 1.0092556111358522 │ 1447804235 │
│ db_sentinel │ metric │ id │ Int64 │ 5983378896 │ 11582153888 │ 1.94 │ 4.132726477347264 │ 1447804235 │
│ db_sentinel │ metric │ machine_ip │ Nullable(String) │ 2310772922 │ 21136005999 │ 9.15 │ 1.5960534346689488 │ 1447804235 │
│ db_sentinel │ metric │ month │ Date │ 12930487 │ 2895538472 │ 223.93 │ 0.008931101793606785 │ 1447804235 │
│ db_sentinel │ metric │ pass_qps │ Int64 │ 3362071882 │ 11582153888 │ 3.44 │ 2.32218679896319 │ 1447804235 │
│ db_sentinel │ metric │ resource │ String │ 13376802422 │ 116862047689 │ 8.74 │ 9.239372353403843 │ 1447804235 │
│ db_sentinel │ metric │ resource_code │ Int64 │ 3054311246 │ 11582153888 │ 3.79 │ 2.1096161844007866 │ 1447804235 │
│ db_sentinel │ metric │ rt │ Float64 │ 3896826770 │ 11582153888 │ 2.97 │ 2.691542596572112 │ 1447804235 │
│ db_sentinel │ metric │ success_qps │ Int64 │ 3361088814 │ 11582153888 │ 3.45 │ 2.3215077927990726 │ 1447804235 │
│ db_sentinel │ metric │ timestamp │ DateTime │ 31010619 │ 5791076944 │ 186.74 │ 0.021419069132644165 │ 1447804235 │
└─────────────┴────────┴───────────────┴──────────────────┴─────────────┴──────────────┴────────┴──────────────────────┴────────────┘
15 rows in set. Elapsed: 0.057 sec.
5.慢查询
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
ORDER BY query_duration_ms DESC
LIMIT 10;
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;
Query id: fd716e4c-0df7-4cba-a36e-6bab3f870db4
┌─database────┬─table──┬─────总行数─┬─原始大小─┬─压缩大小───┬─压缩率/%─┐
│ db_sentinel │ metric │ 5656975301 │ 1.03 TiB │ 142.68 GiB │ 13.53 │
└─────────────┴────────┴────────────┴──────────┴────────────┴──────────┘
阿里云云服务控制台查询
SELECT `database`,table,sum(rows) as rows,
round(sum(bytes_on_disk)/1024/1024/1024,2) as "bytes_on_disk(GB)",
round(sum(data_compressed_bytes)/1024/1024/1024,2) as "data_compressed_bytes(GB)",
round(sum(data_uncompressed_bytes)/1024/1024/1024,2) as "data_uncompressed_bytes_bytes(GB)"
FROM system.parts
where `database` not in ('system')
group by `database`,table order by "data_compressed_bytes(GB)" desc
表各字段压缩前后对比
SELECT table,
formatReadableSize(sum(data_compressed_bytes)) AS tc,
formatReadableSize(sum(data_uncompressed_bytes)) AS tu,
round((sum(data_compressed_bytes) / sum(data_uncompressed_bytes)) * 100,2) AS ratio
FROM system.columns
WHERE database = 'db_inoculate_stat'
and table = 'tb_test'
GROUP BY table
ORDER BY sum(data_compressed_bytes) ASC
7.查询分区并删除分区
SELECT
database,
table,
partition,
partition_id,
name,
path
FROM system.parts
WHERE table = 'metric';
或是通过如下方式查询
SELECT
distinct
database,
table,
partition,
partition_id
FROM system.parts
WHERE table = 'metric'
┌─database────┬─table──┬─partition─┬─partition_id─┬─name───────────────────────┬─path─────────────────────────────────────────────────────────────────────────────────────────────┐
│ db_sentinel │ metric │ 202109 │ 202109 │ 202109_3_7275_13 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202109_3_7275_13/ │
│ db_sentinel │ metric │ 202109 │ 202109 │ 202109_7276_14061_13 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202109_7276_14061_13/ │
│ db_sentinel │ metric │ 202109 │ 202109 │ 202109_14062_14508_11 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202109_14062_14508_11/ │
│ db_sentinel │ metric │ 202110 │ 202110 │ 202110_14509_545862_86 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202110_14509_545862_86/ │
│ db_sentinel │ metric │ 202110 │ 202110 │ 202110_545863_907579_60 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202110_545863_907579_60/ │
│ db_sentinel │ metric │ 202110 │ 202110 │ 202110_907580_992881_45 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202110_907580_992881_45/ │
│ db_sentinel │ metric │ 202110 │ 202110 │ 202110_992882_994869_15 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202110_992882_994869_15/ │
│ db_sentinel │ metric │ 202110 │ 202110 │ 202110_994870_995092_9 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202110_994870_995092_9/ │
│ db_sentinel │ metric │ 202110 │ 202110 │ 202110_995093_995163_8 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202110_995093_995163_8/ │
│ db_sentinel │ metric │ 202111 │ 202111 │ 202111_995164_1464969_94 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202111_995164_1464969_94/ │
│ db_sentinel │ metric │ 202111 │ 202111 │ 202111_1464970_1788824_72 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202111_1464970_1788824_72/ │
│ db_sentinel │ metric │ 202111 │ 202111 │ 202111_1788825_1865494_101 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202111_1788825_1865494_101/ │
│ db_sentinel │ metric │ 202111 │ 202111 │ 202111_1865495_1906236_36 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202111_1865495_1906236_36/ │
│ db_sentinel │ metric │ 202111 │ 202111 │ 202111_1906237_1943298_71 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202111_1906237_1943298_71/ │
│ db_sentinel │ metric │ 202111 │ 202111 │ 202111_1943299_1963966_54 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202111_1943299_1963966_54/ │
│ db_sentinel │ metric │ 202111 │ 202111 │ 202111_1963967_1964265_15 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202111_1963967_1964265_15/ │
│ db_sentinel │ metric │ 202111 │ 202111 │ 202111_1964266_1964266_0 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202111_1964266_1964266_0/ │
│ db_sentinel │ metric │ 202112 │ 202112 │ 202112_1964267_2360310_118 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202112_1964267_2360310_118/ │
│ db_sentinel │ metric │ 202112 │ 202112 │ 202112_2360311_2361260_16 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202112_2360311_2361260_16/ │
│ db_sentinel │ metric │ 202112 │ 202112 │ 202112_2361261_2361329_9 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202112_2361261_2361329_9/ │
│ db_sentinel │ metric │ 202112 │ 202112 │ 202112_2361330_2361347_4 │ /home/clickhouse/data/store/802/8025b932-31a2-4cbc-8025-b93231a25cbc/202112_2361330_2361347_4/ │
└─────────────┴────────┴───────────┴──────────────┴────────────────────────────┴──────────────────────────────────────────────────────────────────────────────────────────────────┘
┌─database────┬─table──┬─partition─┬─partition_id─┐
│ db_sentinel │ metric │ 202203 │ 202203 │
│ db_sentinel │ metric │ 202204 │ 202204 │
│ db_sentinel │ metric │ 202205 │ 202205 │
│ db_sentinel │ metric │ 202206 │ 202206 │
└─────────────┴────────┴───────────┴──────────────┘
阿里云控制台查询
SELECT `database`,partition,name,table,rows,modification_time,
round(bytes_on_disk/1024/1024/1024,2) as "bytes_on_disk(GB)",
round(data_compressed_bytes/1024/1024/1024,2) as "data_compressed_bytes(GB)",
round(data_uncompressed_bytes/1024/1024/1024,2) as "data_uncompressed_bytes_bytes(GB)"
FROM system.parts t
where table = 'tb_test'
and `database` = 'my_dm'
order by modification_time
查询具体某个分区
SELECT `database`,partition,name,table,rows,modification_time,
round(bytes_on_disk/1024/1024/1024,2) as "bytes_on_disk(GB)",
round(data_compressed_bytes/1024/1024/1024,2) as "data_compressed_bytes(GB)",
round(data_uncompressed_bytes/1024/1024/1024,2) as "data_uncompressed_bytes_bytes(GB)"
FROM system.parts t
where table = 'tb_test'
and `database` = 'edw'
and partition='(20221104,1705)'
order by modification_time
删除分区:
alter table metric drop partition 202111;
发现24.8.11.5版本的删除分区 字典信息没有立即删除，表数据是立即删除了的
查看表字典信息，有这么一句:Part hasn't reached removal time yet
要等1-2分钟字典信息才会删除
SELECT DISTINCT
database,
`table`,
partition,
partition_id,
removal_state
FROM system.parts
WHERE `table` = 'hxl_metric'
Query id: 8f6e3485-6b7e-42b0-ab6a-fa105e8fcb78
┌─database─┬─table──────┬─partition─┬─partition_id─┬─removal_state────────────────────────────┐
1. │ default │ hxl_metric │ 202501 │ 202501 │ Part hasn't reached removal time yet │
2. │ default │ hxl_metric │ 202502 │ 202502 │ Cleanup thread hasn't seen this part yet │
└──────────┴────────────┴───────────┴──────────────┴──────────────────────────────────────────┘
#!/bin/bash
##month=202208
##保留4个月数据
delete_month=`date -d"-4 month" +%Y%m`
echo $delete_month
clickhouse-client -h 192.168.1.100 --port=9000 -m -u default<<EOF
alter table tb_test drop partition ${delete_month};
EOF
查没有数据量
select formatDateTime(month,'%Y%m'),count(1) from metric group by formatDateTime(month,'%Y%m');
删除分区有可能报错:
Received exception from server (version 21.6.6):
Code: 359. DB::Exception: Received from 172.17.10.61:9000. DB::Exception: Table or Partition in db_sentinel.metric was not dropped.
Reason:
1. Size (50.00 GB) is greater than max_[table/partition]_size_to_drop (50.00 GB)
2. File '/home/clickhouse/data/flags/force_drop_table' intended to force DROP doesn't exist
How to fix this:
1. Either increase (or set to zero) max_[table/partition]_size_to_drop in server config
2. Either create forcing file /home/clickhouse/data/flags/force_drop_table and make sure that ClickHouse has write permission for it.
Example:
sudo touch '/home/clickhouse/data/flags/force_drop_table' && sudo chmod 666 '/home/clickhouse/data/flags/force_drop_table'.
解决办法:
修改配置文件
/etc/clickhouse-server/config.xml
将这两句注释的打开:
<max_table_size_to_drop>0</max_table_size_to_drop>
<max_partition_size_to_drop>0</max_partition_size_to_drop>
然后重新启动
systemctl stop clickhouse-server.service
systemctl start clickhouse-server.service
systemctl status clickhouse-server.service
8.创建分区表并写入数据
CREATE TABLE hxl_metric
(
`app` String,
`block_qps` Int64,
`count` Int64,
`exception_qps` Int64,
`gmt_create` DateTime DEFAULT now(),
`gmt_modified` DateTime DEFAULT now(),
`id` Int64,
`machine_ip` Nullable(String),
`pass_qps` Int64,
`resource` String,
`resource_code` Int64,
`rt` Float64,
`success_qps` Int64,
`timestamp` DateTime DEFAULT now(),
`month` Date DEFAULT now()
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(month)
ORDER BY (timestamp, app, resource)
SETTINGS index_granularity = 8192;
insert into hxl_metric(app,timestamp,resource) values('app1',now(),'res01');
insert into hxl_metric(app,timestamp,resource,month) values('app1',now(),'res01',now());
insert into hxl_metric(app,timestamp,resource,month) values('app1',now(),'res01',addDays(now(),30));
浙公网安备 33010602011771号