青岛出版集团网站,企业网站服务器建设,wordpress文章查看次数,wordpress超链接工信部为了实现最佳性能#xff0c;数据库需要优化其内部数据存储和处理管道的每一步。但是数据库执行的最好的工作是根本没有完成的工作#xff01;缓存是一种特别流行的技术#xff0c;它通过存储早期计算的结果或远程数据来避免不必要的工作#xff0c;而访问这些数据的成本往…为了实现最佳性能数据库需要优化其内部数据存储和处理管道的每一步。但是数据库执行的最好的工作是根本没有完成的工作缓存是一种特别流行的技术它通过存储早期计算的结果或远程数据来避免不必要的工作而访问这些数据的成本往往很高。在今天的博文中介绍一下 ClickHouse 缓存系列的最新成员——查询缓存在 v23.1 版本中作为实验性特性。 一、缓存一致性问题
在实操 clickhouse 的查询缓存前需要先了解一下缓存事务问题查询缓存通常可以分为事务一致和事务不一致。
在事务一致缓存中如果 SELECT 查询的结果发生更改或可能发生更改则数据库会使缓存的查询结果无效丢弃。在 ClickHouse 中更改数据的操作包括在表中插入/更新/删除或折叠合并。事务一致性缓存特别适合 OLTP 数据库例如MySQL在v8.0之后删除了查询缓存和 Oracle。
在事务不一致缓存中所有缓存条目都被分配了一个有效期之后它们就会过期并且基础数据在此期间仅发生很小的变化那么查询结果中的轻微不准确是可以接受的这种方法总体上更适合 OLAP 数据库。在一些应用场景中数据的变化假如很慢数据库就只需要计算一次报告由第一个 SELECT 查询表示。可以直接从查询缓存提供进一步的查询。 事务上不一致的缓存通常是由与数据库交互的客户端工具或代理包提供的 二、查询缓存实操
2.1 前期准备
这里使用 clickhouse 官方提供的 Anonymized Web Analytics Data数据集下载
准备数据表
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 安装客户端
yum install -y yum-utils
yum-config-manager --add-repo https://packages.clickhouse.com/rpm/clickhouse.repo
yum install -y clickhouse-client导入数据
# 解压数据文件
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_size100000查看数据量
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 极其执行结果如下
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 秒。
作为实验性功能查询缓存默认关闭通过下面方式开启
set allow_experimental_query_cache true;同时在查询语句中显式指定启用缓存
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.上述结果是第二次查询发现几乎不消耗时间同时打印查询日志
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 缓存配置
如何确定查询是否命中缓存语法如下
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 QueryFinishand 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 表(结果展示太长直接使用工具查询后截图) 其中
query缓存的查询语句result_size缓存数据的大小单位 bytestale缓存是否可用0 表示可用否则为不可用需要重新执行缓存 query 将新的结果存储到缓存中shard缓存所存储的分片节点compressed是否被压缩1 表示压缩否则缓存没有被压缩expires_at缓存过期时间key_hash缓存的唯一标识 key_hash 主要被用来标识哪个缓存在 clickhouse 中查询缓存会以 hash 表的形式存储在内存中 下面来介绍一下缓存的高级用法及其配置
3.1.1 更精细的缓存控制
use_query_cache 用户开启查询缓存但如果我们需要更精细的控制查询缓存则需要额外的配置例如我只需要从缓存中读取数据而不想将新的查询结果写入缓存中。
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该配置允许用户根据实际业务需求自己配置
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中
query_cache!-- 查询缓存总大小单位byte --size1073741824/size!-- 可以缓存的查询条数 --max_entries1024/max_entries!-- 允许缓存的单个查询最大容量 单位byte --max_entry_size1048576/max_entry_size!-- 许缓存的单个查询最大行数 --max_entry_records30000000/max_entry_records
/query_cachesize限制节点可以缓存的总大小上面配置了 1G如果超过阈值会删除所有过期的缓存此时如果没有足够空间则不会插入新的条目max_entries限制节点可以缓存的总条数上面配置了 1024 条如果超过阈值会删除所有过期的缓存此时如果没有足够空间则不会插入新的条目max_entry_size限制单个查询可以缓存的容留上限上面配置了 1M如果超过这个阈值该查询不会被缓存max_entry_records限制单个查询可以缓存的行数上线上面配置了 3000w如果超过这个阈值该查询不会被缓存
从用户细粒度控制可以缓存的大小和个数在用户独立的配置文件或用户配置域内
profilesdefault!-- default 用户可以缓存的最大空间单位字节 --query_cache_max_size_in_bytes10000/query_cache_max_size_in_bytes!-- default 用户可以缓存的查询条数 --query_cache_max_entries100/query_cache_max_entries!-- 设置配置只读不允许修改 --constraintsquery_cache_max_size_in_bytesreadonly//query_cache_max_size_in_bytesquery_cache_max_entriesreadonly/query_cache_max_entries/constraints/default
/profiles如果用户需要尽可能多的缓存大数据集的话可以开启缓存压缩当然默认就是开启的。
SELECT ...
SETTINGS use_query_cache true,query_cache_compress_entries true;缓存压缩可以大幅降低内存消耗但查询缓存的写入和读取效率将会被降低 3.1.4 缓存行为控制
为了让缓存可以被应用在频繁且耗时的查询中可以控制查询次数和查询耗时来避免一些本身相对较快的查询来消耗缓存空间
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() 函数例如
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
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开启
SELECT ...
SETTINGS use_query_cache true, query_cache_share_between_users true;3.1.7 删除缓存
system drop query cache [on cluster cluster_name];此操作会删除该节点所有缓存过期不过期都会被删除
3.2 不足
从目前来看我并没有找到删除指定缓存的方式只能删除全部缓存显然这个操作是被禁止的暂不支持缓存淘汰策略如LRU当前的做法是当缓存达到上限自动删除所有过期缓存当前缓存被存储在内存的哈希表中并没有持久化当服务器重启后缓存将会失效
当然上述的不足在 clickhouse 的 roadmap 均有体现相信在不久将来的新版本中查询缓存将越来越优秀
