SRE文档

深浅模式

1. 连接数据库 ​

bash
#有密码
clickhouse-client -h 127.0.0.1 -u default --password 123456

#无密码
clickhouse-client
#有密码
clickhouse-client -h 127.0.0.1 -u default --password 123456

#无密码
clickhouse-client

2. 查询 ​

1. 查询存储空间 ​

sql
SELECT name, path, formatReadableSize(free_space) AS free, formatReadableSize(total_space) AS total, formatReadableSize(keep_free_space) AS reserved FROM system.disks;
SELECT name, path, formatReadableSize(free_space) AS free, formatReadableSize(total_space) AS total, formatReadableSize(keep_free_space) AS reserved FROM system.disks;

image-20241105211231782

2. 查看数据库大小 ​

sql
SELECT
    database,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
GROUP BY database
ORDER BY size_on_disk DESC
SELECT
    database,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
GROUP BY database
ORDER BY size_on_disk DESC

image-20241106163456603

3. 查看表大小 ​

sql
SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
GROUP BY table
ORDER BY size_on_disk DESC
SELECT
    table,
    formatReadableSize(sum(bytes_on_disk)) AS size_on_disk
FROM system.parts
WHERE active
GROUP BY table
ORDER BY size_on_disk DESC

1.查看系统表 ​

sql
select
　　database,
　　table,
　　formatReadableSize(size) as size,
　　formatReadableSize(bytes_on_disk) as bytes_on_disk,
　　formatReadableSize(data_uncompressed_bytes) as data_uncompressed_bytes,
　　formatReadableSize(data_compressed_bytes) as data_compressed_bytes,
　 compress_rate,
　　rows,
　　days,
　　formatReadableSize(avgDaySize) as avgDaySize
from
　(
　　　select
　　　　　　database,
　　　　　　table,
　　　　　　sum(bytes) as size,
　　　　　　sum(rows) as rows,
　　　　　　min(min_date) as min_date,
　　　　　　max(max_date) as max_date,
　　　　　　sum(bytes_on_disk) as bytes_on_disk,
　　　　　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　　　　　sum(data_compressed_bytes) as data_compressed_bytes,
　　　　　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　　　　　max_date - min_date as days,
　　　　　　size / (max_date - min_date) as avgDaySize
　　　　from system.parts
　　　　where active
　　　　and database = 'system'
　　　　and `table` in('query_thread_log','query_log')
　　　　and `partition` ='202411'
　　　　group by
　　　　　　database,
　　　　　　table
　　　　order by data_compressed_bytes desc
);
select
　　database,
　　table,
　　formatReadableSize(size) as size,
　　formatReadableSize(bytes_on_disk) as bytes_on_disk,
　　formatReadableSize(data_uncompressed_bytes) as data_uncompressed_bytes,
　　formatReadableSize(data_compressed_bytes) as data_compressed_bytes,
　 compress_rate,
　　rows,
　　days,
　　formatReadableSize(avgDaySize) as avgDaySize
from
　(
　　　select
　　　　　　database,
　　　　　　table,
　　　　　　sum(bytes) as size,
　　　　　　sum(rows) as rows,
　　　　　　min(min_date) as min_date,
　　　　　　max(max_date) as max_date,
　　　　　　sum(bytes_on_disk) as bytes_on_disk,
　　　　　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　　　　　sum(data_compressed_bytes) as data_compressed_bytes,
　　　　　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　　　　　max_date - min_date as days,
　　　　　　size / (max_date - min_date) as avgDaySize
　　　　from system.parts
　　　　where active
　　　　and database = 'system'
　　　　and `table` in('query_thread_log','query_log')
　　　　and `partition` ='202411'
　　　　group by
　　　　　　database,
　　　　　　table
　　　　order by data_compressed_bytes desc
);

202411,query_thread_log','query_log' 注意修改时间

2.查看所有表 ​

sql
select
　　database,
　　table,
　　formatReadableSize(size) as size,
　　formatReadableSize(bytes_on_disk) as bytes_on_disk,
　　formatReadableSize(data_uncompressed_bytes) as data_uncompressed_bytes,
　　formatReadableSize(data_compressed_bytes) as data_compressed_bytes,
　 compress_rate,
　　rows,
　　days,
　　formatReadableSize(avgDaySize) as avgDaySize
from
　(
　　　select
　　　　　　database,
　　　　　　table,
　　　　　　sum(bytes) as size,
　　　　　　sum(rows) as rows,
　　　　　　min(min_date) as min_date,
　　　　　　max(max_date) as max_date,
　　　　　　sum(bytes_on_disk) as bytes_on_disk,
　　　　　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　　　　　sum(data_compressed_bytes) as data_compressed_bytes,
　　　　　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　　　　　max_date - min_date as days,
　　　　　　size / (max_date - min_date) as avgDaySize
　　　　from system.parts
　　　　where active
　　　　group by
　　　　　　database,
　　　　　　table
　　　　order by data_compressed_bytes desc
);
select
　　database,
　　table,
　　formatReadableSize(size) as size,
　　formatReadableSize(bytes_on_disk) as bytes_on_disk,
　　formatReadableSize(data_uncompressed_bytes) as data_uncompressed_bytes,
　　formatReadableSize(data_compressed_bytes) as data_compressed_bytes,
　 compress_rate,
　　rows,
　　days,
　　formatReadableSize(avgDaySize) as avgDaySize
from
　(
　　　select
　　　　　　database,
　　　　　　table,
　　　　　　sum(bytes) as size,
　　　　　　sum(rows) as rows,
　　　　　　min(min_date) as min_date,
　　　　　　max(max_date) as max_date,
　　　　　　sum(bytes_on_disk) as bytes_on_disk,
　　　　　　sum(data_uncompressed_bytes) as data_uncompressed_bytes,
　　　　　　sum(data_compressed_bytes) as data_compressed_bytes,
　　　　　　(data_compressed_bytes / data_uncompressed_bytes) * 100 as compress_rate,
　　　　　　max_date - min_date as days,
　　　　　　size / (max_date - min_date) as avgDaySize
　　　　from system.parts
　　　　where active
　　　　group by
　　　　　　database,
　　　　　　table
　　　　order by data_compressed_bytes desc
);

4.查询当前所在库 ​

sql
select currentDatabase() FORMAT TabSeparatedWithNamesAndTypes
select currentDatabase() FORMAT TabSeparatedWithNamesAndTypes