多梦笔记

通过ClickHouse 查询缓存

白发任 笔记 52

为了实现最佳性能，数据库需要优化其内部数据存储和处理管道的每一步。但是数据库执行的最好的工作是根本没有完成的工作！缓存是一种特别流行的技术，它通过存储早期计算的结果或远程数据来避免不必要的工作，而访问这些数据的成本往往很高。在今天的博文中，介绍一下 clickhouse 缓存系列的最新成员——查询缓存，在v23.1版本中作为实验性特性。

一、缓存一致性问题

在实操 clickhouse 的查询缓存前需要先了解一下缓存事务问题，查询缓存通常可以分为事务一致和事务不一致。
在事务一致缓存中，如果 SELECT 查询的结果发生更改或可能发生更改，则数据库会使缓存的查询结果无效（丢弃）。在 ClickHouse 中，更改数据的操作包括在表中插入/更新/删除或折叠合并。事务一致性缓存特别适合 OLTP 数据库，例如 MySQL（在v8.0之后删除了查询缓存）和 Oracle。
在事务不一致缓存中，所有缓存条目都被分配了一个有效期，之后它们就会过期，并且基础数据在此期间仅发生很小的变化，那么查询结果中的轻微不准确是可以接受的，这种方法总体上更适合 OLAP 数据库。在一些应用场景中数据的变化假如很慢，数据库就只需要计算一次报告（由第一个 SELECT 查询表示）。可以直接从查询缓存提供进一步的查询。

事务上不一致的缓存通常是由与数据库交互的客户端工具或代理包提供的

二、查询缓存实操

2.1 前期准备

这里使用 clickhouse 官方提供的 Anonymized Web Analytics Data，点击下载 ：https://datasets.clickhouse.com/hits/tsv/hits_100m_obfuscated_v1.tsv.xz准备数据表


CREATE TABLE hits_100m_obfuscated
(
    WatchID               UInt64,
    JavaEnable            UInt8,
    Title                 String,
    GoodEvent             Int16,
    EventTime             DateTime,
    EventDate             Date,
    CounterID             UInt32,
    ClientIP              UInt32,
    RegionID              UInt32,
    UserID                UInt64,
    CounterClass          Int8,
    OS                    UInt8,
    UserAgent             UInt8,
    URL                   String,
    Referer               String,
    Refresh               UInt8,
    RefererCategoryID     UInt16,
    RefererRegionID       UInt32,
    URLCategoryID         UInt16,
    URLRegionID           UInt32,
    ResolutionWidth       UInt16,
    ResolutionHeight      UInt16,
    ResolutionDepth       UInt8,
    FlashMajor            UInt8,
    FlashMinor            UInt8,
    FlashMinor2           String,
    NetMajor              UInt8,
    NetMinor              UInt8,
    UserAgentMajor        UInt16,
    UserAgentMinor        FixedString(2),
    CookieEnable          UInt8,
    JavascriptEnable      UInt8,
    IsMobile              UInt8,
    MobilePhone           UInt8,
    MobilePhoneModel      String,
    Params                String,
    IPNetworkID           UInt32,
    TraficSourceID        Int8,
    SearchEngineID        UInt16,
    SearchPhrase          String,
    AdvEngineID           UInt8,
    IsArtifical           UInt8,
    WindowClientWidth     UInt16,
    WindowClientHeight    UInt16,
    ClientTimeZone        Int16,
    ClientEventTime       DateTime,
    SilverlightVersion1   UInt8,
    SilverlightVersion2   UInt8,
    SilverlightVersion3   UInt32,
    SilverlightVersion4   UInt16,
    PageCharset           String,
    CodeVersion           UInt32,
    IsLink                UInt8,
    Isdownload            UInt8,
    IsNotBounce           UInt8,
    FUniqID               UInt64,
    OriginalURL           String,
    HID                   UInt32,
    IsOldCounter          UInt8,
    IsEvent               UInt8,
    IsParameter           UInt8,
    DontCountHits         UInt8,
    WithHash              UInt8,
    HitColor              FixedString(1),
    LocalEventTime        DateTime,
    Age                   UInt8,
    Sex                   UInt8,
    Income                UInt8,
    Interests             UInt16,
    Robotness             UInt8,
    RemoteIP              UInt32,
    WindowName            Int32,
    OpenerName            Int32,
    HistoryLength         Int16,
    BrowserLanguage       FixedString(2),
    BrowserCountry        FixedString(2),
    SocialNetwork         String,
    SocialAction          String,
    HTTPError             UInt16,
    SendTiming            UInt32,
    DNSTiming             UInt32,
    ConnectTiming         UInt32,
    ResponseStartTiming   UInt32,
    ResponseEndTiming     UInt32,
    FetchTiming           UInt32,
    SocialSourceNetworkID UInt8,
    SocialSourcePage      String,
    ParamPrice            Int64,
    ParamOrderID          String,
    ParamCurrency         FixedString(3),
    ParamCurrencyID       UInt16,
    OpenstatServiceName   String,
    OpenstatCampAIgnID    String,
    OpenstatAdID          String,
    OpenstatSourceID      String,
    UTMSource             String,
    UTMMedium             String,
    UTMCampaign           String,
    UTMContent            String,
    UTMTerm               String,
    FromTag               String,
    HasGCLID              UInt8,
    RefererHash           UInt64,
    URLHash               UInt64,
    CLID                  UInt32
)
    ENGINE = MergeTree()
        PARTITION BY toYYYYMM(EventDate)
        ORDER BY (CounterID, EventDate, intHash32(UserID))
        SAMPLE BY intHash32(UserID) SETTINGS index_granularity = 8192;


导入数据建议使用 clickhouse-client 来操作，下面基于 centos 或 rpm 安装客户端


SHELL
yum install -y yum-utils
yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
yum install -y clickhouse-client


导入数据


SHELL
# 解压数据文件
xz -dk hits_100m_obfuscated_v1.tsv.xz
# 导入数据
cat hits_100m_obfuscated_v1.tsv | clickhouse-client -h 192.168.0.190 -u admin --password admin --query "insert into hits_100m_obfuscated FORMAT TSV" --max_insert_block_size=100000

查看数据量


SQL
select count() from hits_100m_obfuscated;

Query id: 9152e4a1-fea1-4869-9857-656fc0d4d68a

┌───count()─┐
│ 100000000 │
└───────────┘

1 row in set. Elapsed: 0.007 sec.


2.2 查询缓存

假象一个需求：根据操作系统、浏览器和引用页面（Referer），计算总访问量和访问者数量，sql 极其执行结果如下:


SQL

SELECT OS                                                  AS OperatingSystem,
       UserAgent                                           AS Browser,
       Referer                                             AS ReferringPage,
       COUNT(*)                                            AS TotalVisits,
       COUNT(DISTINCT UserID)                              AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10;

Query id: 458deafe-25fb-4695-bbbf-87bd14e0b7ff

┌─OperatingSystem─┬─Browser─┬─ReferringPage───────┬─TotalVisits─┬─UniqueVisitors─┐
│              44 │       5 │                     │     2724345 │        1261517 │
│              44 │       7 │                     │     2236143 │         798198 │
│              44 │       2 │                     │     1713149 │         633544 │
│              44 │       3 │                     │     1815864 │         625035 │
│               2 │       5 │                     │     1075898 │         515312 │
│               2 │       3 │                     │     1378892 │         504849 │
│             159 │      32 │                     │      924871 │         432929 │
│               2 │       2 │                     │     1064491 │         407627 │
│               2 │       7 │                     │      914442 │         338232 │
│              44 │       5 │ http://новострашная │      464194 │         316512 │
└─────────────────┴─────────┴─────────────────────┴─────────────┴────────────────┘

10 rows in set. Elapsed: 6.145 sec. Processed 100.00 million rows, 8.56 GB (16.27 million rows/s., 1.39 GB/s.)



平均执行时长 6 秒。
作为实验性功能查询缓存默认关闭，通过下面方式开启


SQL
set allow_experimental_query_cache = true;


同时在查询语句中显式指定启用缓存


SQL

SELECT OS                                                  AS OperatingSystem,
       UserAgent                                           AS Browser,
       Referer                                             AS ReferringPage,
       COUNT(*)                                            AS TotalVisits,
       COUNT(DISTINCT UserID)                              AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS use_query_cache = true;

Query id: 93098a52-adcb-421f-bc68-acbfd5b1af8b

┌─OperatingSystem─┬─Browser─┬─ReferringPage───────┬─TotalVisits─┬─UniqueVisitors─┐
│              44 │       5 │                     │     2724345 │        1261517 │
│              44 │       7 │                     │     2236143 │         798198 │
│              44 │       2 │                     │     1713149 │         633544 │
│              44 │       3 │                     │     1815864 │         625035 │
│               2 │       5 │                     │     1075898 │         515312 │
│               2 │       3 │                     │     1378892 │         504849 │
│             159 │      32 │                     │      924871 │         432929 │
│               2 │       2 │                     │     1064491 │         407627 │
│               2 │       7 │                     │      914442 │         338232 │
│              44 │       5 │ http://новострашная │      464194 │         316512 │
└─────────────────┴─────────┴─────────────────────┴─────────────┴────────────────┘

10 rows in set. Elapsed: 0.003 sec.



上述结果是第二次查询，发现几乎不消耗时间，同时打印查询日志


SQL
select query_duration_ms, read_rows, read_bytes, memory_usage
from system.query_log
where query_id in (
	'93098a52-adcb-421f-bc68-acbfd5b1af8b', 
	'458deafe-25fb-4695-bbbf-87bd14e0b7ff'
  )
  and type = 'QueryFinish';
  
Query id: b224a866-6eed-42a5-b81f-d186568e2570

┌─query_duration_ms─┬─read_rows─┬─read_bytes─┬─memory_usage─┐
│              6125 │ 100000000 │ 8562787759 │  14943181799 │
│                 2 │        10 │        301 │         9912 │
└───────────────────┴───────────┴────────────┴──────────────┘

2 rows in set. Elapsed: 0.049 sec. Processed 1.97 thousand rows, 153.00 KB (40.50 thousand rows/s., 3.15 MB/s.)

可以看出查询缓存对用户体验的提升是极高的

虽然可以在配置文件中全局开启查询缓存，但是这样所有的 SELECT 查询（包括对系统表的监视或调试查询）都可能会返回缓存，所以还是针对特定查询语句提供缓存功能

三、进阶

3.1 缓存配置

如何确定查询是否命中缓存？语法如下


SQL

select query_id,
       ProfileEvents['QueryCacheHits']   AS query_cache,
       query_duration_ms / 1000          AS query_duration,
       formatReadableSize(memory_usage)  AS memory_usage,
       formatReadableQuantity(read_rows) AS read_rows,
       formatReadableSize(read_bytes)    AS read_data
from system.query_log
where type = 'QueryFinish'
  and query_id in (
	  '93098a52-adcb-421f-bc68-acbfd5b1af8b', 
	  '458deafe-25fb-4695-bbbf-87bd14e0b7ff'
  );
  
Query id: 04744ba4-d3cb-4f28-84fc-81a2e7598789

┌─query_id─────────────────────────────┬─query_cache─┬─query_duration─┬─memory_usage─┬─read_rows──────┬─read_data─┐
│ 458deafe-25fb-4695-bbbf-87bd14e0b7ff │           0 │          6.125 │ 13.92 GiB    │ 100.00 million │ 7.97 GiB  │
│ 93098a52-adcb-421f-bc68-acbfd5b1af8b │           1 │          0.002 │ 9.68 KiB     │ 10.00          │ 301.00 B  │
└──────────────────────────────────────┴─────────────┴────────────────┴──────────────┴────────────────┴───────────┘

2 rows in set. Elapsed: 0.024 sec. Processed 2.00 thousand rows, 364.80 KB (83.00 thousand rows/s., 15.17 MB/s.)


如果想要更详细的了解系统中存在哪些缓存，可以查询 system.query_cache 表(结果展示太长，直接使用工具查询后截图)

通过ClickHouse 查询缓存-第1张图片-多梦笔记

其中：

  1. query：缓存的查询语句

  2. result_size：缓存数据的大小，单位 byte

  3. stale：缓存是否可用，0 表示可用，否则为不可用，需要重新执行缓存 query 将新的结果存储到缓存中

  4. shard：缓存所存储的分片节点

  5. compressed：是否被压缩，1 表示压缩，否则缓存没有被压缩

  6. expires_at：缓存过期时间

  7. key_hash：缓存的唯一标识

    key_hash主要被用来标识哪个缓存，在 clickhouse 中查询缓存会以 hash 表的形式存储在内存中

下面来介绍一下缓存的高级用法及其配置

3.1.1 更精细的缓存控制

use_query_cache用户开启查询缓存，但如果我们需要更精细的控制查询缓存则需要额外的配置，例如：我只需要从缓存中读取数据而不想将新的查询结果写入缓存中。


SQL
SELECT OS                     AS OperatingSystem,
       UserAgent              AS Browser,
       Referer                AS ReferringPage,
       COUNT(*)               AS TotalVisits,
       COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true,
enable_writes_to_query_cache = false,
enable_reads_from_query_cache = true;


enable_writes_to_query_cache: 是否将查询缓存写入缓存中，禁止时所有的缓存都不会被写入。即：缓存如果存在直接获取，缓存失效后改查询不在缓存
enable_reads_from_query_cache: 是否从缓存中读取数据，禁止时及时缓存命中也不会获取缓存数据而是直接查询原始数据
该参数可以精细控制缓存，让用户可以精准把控业务查询是否要走缓存，因为缓存在带来查询效率提升的同时，也带来了查询不一致的情况需要在生产中结合实际场景进行合理配置

上述的两个配置需要在use_query_cache开启的情况下才会起作用

3.1.2 缓存时间控制

从system.query_cache表的 expires_at 字段可以获知缓存的过期时间，默认为 1min，该配置允许用户根据实际业务需求自己配置


SQL
SELECT OS                     AS OperatingSystem,
       UserAgent              AS Browser,
       Referer                AS ReferringPage,
       COUNT(*)               AS TotalVisits,
       COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true,
query_cache_ttl = 30;


query_cache_ttl: 缓存的过期时间，单位：秒
该配置交给各位看官自己去验证

3.1.3 缓存大小控制

缓存虽好，但不能过度使用。如果不加以限制服务器 OOM 随时可能发生，例如某个用户在查询明细表时开启了缓存那么将是灾难级的。好在 clickhouse 提供了缓存大小的控制。
从粗粒度层面可以控制当前节点的缓存大小和个数，在config.xml中


XML
<query_cache>
    <!-- 查询缓存总大小，单位：byte  -->
    <size>1073741824</size>
    <!-- 可以缓存的查询条数  -->
    <max_entries>1024</max_entries>
    <!-- 允许缓存的单个查询最大容量 单位：byte  -->
    <max_entry_size>1048576</max_entry_size>
    <!-- 许缓存的单个查询最大行数  -->
    <max_entry_records>30000000</max_entry_records>
</query_cache>


  1. size：限制节点可以缓存的总大小，上面配置了 1G，如果超过阈值会删除所有过期的缓存，此时如果没有足够空间则不会插入新的条目

  2. max_entries：限制节点可以缓存的总条数，上面配置了 1024 条，如果超过阈值会删除所有过期的缓存，此时如果没有足够空间则不会插入新的条目

  3. max_entry_size：限制单个查询可以缓存的容留上限，上面配置了 1M，如果超过这个阈值该查询不会被缓存

  4. max_entry_records：限制单个查询可以缓存的行数上线，上面配置了 3000w，如果超过这个阈值该查询不会被缓存
    从用户细粒度控制可以缓存的大小和个数，在用户独立的配置文件或用户配置域内


XML
<profiles>
    <default>
        <!-- default 用户可以缓存的最大空间，单位字节 -->
        <query_cache_max_size_in_bytes>10000</query_cache_max_size_in_bytes>
        <!-- default 用户可以缓存的查询条数 -->
        <query_cache_max_entries>100</query_cache_max_entries>
        <!-- 设置配置只读，不允许修改 -->
        <constraints>
            <query_cache_max_size_in_bytes>
                <readonly/>
            </query_cache_max_size_in_bytes>
            <query_cache_max_entries>
                <readonly/>
            <query_cache_max_entries>
        </constraints>
    </default>
</profiles>


如果用户需要尽可能多的缓存大数据集的话可以开启缓存压缩，当然默认就是开启的。


SQL
SELECT ...
SETTINGS use_query_cache = true,query_cache_compress_entries = true;


缓存压缩可以大幅降低内存消耗，但查询缓存的写入和读取效率将会被降低

3.1.4 缓存行为控制

为了让缓存可以被应用在频繁且耗时的查询中，可以控制查询次数和查询耗时来避免一些本身相对较快的查询来消耗缓存空间


SQL
SELECT OS                     AS OperatingSystem,
       UserAgent              AS Browser,
       Referer                AS ReferringPage,
       COUNT(*)               AS TotalVisits,
       COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true,
query_cache_min_query_duration = 5000,
query_cache_min_query_runs = 2;


use_query_cache_min_query_duration: 查询至少耗时 5000 毫秒才会被缓存
use_query_cache_min_query_runs: 查询至少运行 2 次以上才会被缓存
如果都配置则需要同时满足才会被缓存

上述配置主要是为了约束将缓存空间用在真正需要被缓存的 sql 上

3.1.5 事务不一致的缓存

在使用一些带有随机语义函数的查询时 clickhouse 默认是不缓存的，例如：now() 和 rand() 函数，例如：


SQL
SELECT OS                     AS OperatingSystem,
       UserAgent              AS Browser,
       Referer                AS ReferringPage,
       COUNT(*)               AS TotalVisits,
       COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
where EventDate >= toDateTime('2013-07-10 00:00:00')
  and EventDate <= now()
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true;


即使开启了use_query_cache也不会被缓存，因为查询中存在不确定函数 now()，clickhouse 并不知道原表的数据何时发生变化，这就会导致此类函数的查询存在数据不一致情况。当然如果业务场景允许，需要追求极致的查询体验，可以开启query_cache_store_results_of_queries_with_nondeterministic_functions


SQL
SELECT OS                     AS OperatingSystem,
       UserAgent              AS Browser,
       Referer                AS ReferringPage,
       COUNT(*)               AS TotalVisits,
       COUNT(DISTINCT UserID) AS UniqueVisitors
FROM hits_100m_obfuscated
where EventDate >= toDateTime('2013-07-10 00:00:00')
  and EventDate <= now()
GROUP BY OperatingSystem, Browser, ReferringPage
ORDER BY UniqueVisitors DESC
LIMIT 10
SETTINGS
use_query_cache = true,
query_cache_store_results_of_queries_with_nondeterministic_functions = true;


此时查询system.query_cache就可以看到

3.1.6 缓存共享

clickhouse 默认不允许多个用户之间共享缓存，因为这个操作太过于危险。如果有必要通过query_cache_share_between_users开启


SQL
SELECT ...
SETTINGS use_query_cache = true, query_cache_share_between_users = true;


3.1.7 删除缓存


SQL
system drop query cache [on cluster cluster_name];


此操作会删除该节点所有缓存（过期不过期都会被删除）

3.2 不足

  1. 从目前来看，我并没有找到删除指定缓存的方式只能删除全部缓存，显然这个操作是被禁止的

  2. 暂不支持缓存淘汰策略（如：LRU），当前的做法是当缓存达到上限自动删除所有过期缓存

  3. 当前缓存被存储在内存的哈希表中并没有持久化，当服务器重启后缓存将会失效

当然上述的不足在 clickhouse 的 roadmap 均有体现，相信在不久将来的新版本中查询缓存将越来越优秀


分享到：

标签： clickhouse SQL Anonymized Web Analytics Data

版权声明：除非特别标注，否则均为本站原创文章，转载时请以链接形式注明文章出处。

上一篇基于 metabase + clickhouse 的流量分析

下一篇123云盘限时福利：登录即送1个月VIP尊享权益！

相关文章