You are viewing a plain text version of this content. The canonical link for it is here.
Posted to commits@doris.apache.org by GitBox <gi...@apache.org> on 2020/08/28 02:25:49 UTC
[GitHub] [incubator-doris] songenjie opened a new pull request #4479: [Apache][Doris]add query cache docs
songenjie opened a new pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479
Add Query Cache Docs
some issues
- https://github.com/apache/incubator-doris/issues/2581
- https://github.com/apache/incubator-doris/issues/4283
/Assignee @kangkaisen
/cc @morningman
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] WuZixing commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
WuZixing commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478859585
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
+
+Usually, we have cache in the corresponding database layer which is focus on the query content.However the data granularity in the cache is too slow and it would have effect only when data in the table didn't change.Obviously, it could not reduce the heavy IO load from the CRUD of the business system. So it comes the database caching technology, which could highspeed caching the hot data, improve the response speed of the application and greatly relieve the load of the backend database.
Review comment:
*However the data granularity in the cache is too **slow** and ...*
Is it *slow* or *small*?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] songenjie commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
songenjie commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r481783404
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
+
+Usually, we have cache in the corresponding database layer which is focus on the query content.However the data granularity in the cache is too slow and it would have effect only when data in the table didn't change.Obviously, it could not reduce the heavy IO load from the CRUD of the business system. So it comes the database caching technology, which could highspeed caching the hot data, improve the response speed of the application and greatly relieve the load of the backend database.
Review comment:
the data granularity in the cache is too slow
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
kangkaisen commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478804028
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,184 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好的支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,在应用层的解决方案,把查询结果放到Redis中,周期性的更新缓存或者用户手工刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
Review comment:
空行有点太多了。
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
kangkaisen commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478803671
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,184 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
Review comment:
前面是应用层?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] HangyuanLiu commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
HangyuanLiu commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r506869859
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,183 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好地支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,一种在应用层的解决方案是把查询结果放到Redis中,周期性地更新缓存或者用户手动刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+
+
+## 三 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+
+
+## 四 设计原理
+
+### 1 结果缓存 `result_cache`
+
+result_cache 分两层 第一层为 result_cache_ttl 第二层为 result_cache_version
+
+
+
+#### `result_cache_ttl`
+
+result_cache_ttl 变量设置在用户Session中,用户可自定义是否开启,通过ttl时间来确定用户的sql是否使用缓存,`这里数据变更时不保证数据的正确性`
+
+按照 用户 connectid,和查询的sql 来存储和获取缓存,超过缓存失效时间则命中不了缓存,该缓存也会被清理
+
+
+
+#### ` result_cache_version`
+
+result_cache_version 按SQL的签名、查询的表的分区ID、分区最新版本来存储和获取缓存。三者组合确定一个缓存数据集,任何一个变化了,如SQL有变化,如查询字段或条件不一样,或数据更新后版本变化了,会导致命中不了缓存。
+
+如果多张表Join,使用最近更新的分区ID和最新的版本号,如果其中一张表更新了,会导致分区ID或版本号不一样,也一样命中不了缓存。
+
+
+
+### 2 分区缓存 `partition_cache`
+
+1. SQL可以并行拆分,Q = Q1 ∪ Q2 ... ∪ Qn,R= R1 ∪ R2 ... ∪ Rn,Q为查询语句,R为结果集
+
+2. 拆分为只读分区和可更新分区,只读分区缓存,更新分区不缓存
+
+
+
+## 五 参数
+
+### fe
+
+- cache 开关
+
+```
+enable_result_cache_ttl
Review comment:
以下参数是config参数还是variable参数。希望能够说明
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,183 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好地支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,一种在应用层的解决方案是把查询结果放到Redis中,周期性地更新缓存或者用户手动刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+
+
+## 三 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+
+
+## 四 设计原理
+
+### 1 结果缓存 `result_cache`
+
+result_cache 分两层 第一层为 result_cache_ttl 第二层为 result_cache_version
+
+
+
+#### `result_cache_ttl`
+
+result_cache_ttl 变量设置在用户Session中,用户可自定义是否开启,通过ttl时间来确定用户的sql是否使用缓存,`这里数据变更时不保证数据的正确性`
+
+按照 用户 connectid,和查询的sql 来存储和获取缓存,超过缓存失效时间则命中不了缓存,该缓存也会被清理
+
+
+
+#### ` result_cache_version`
+
+result_cache_version 按SQL的签名、查询的表的分区ID、分区最新版本来存储和获取缓存。三者组合确定一个缓存数据集,任何一个变化了,如SQL有变化,如查询字段或条件不一样,或数据更新后版本变化了,会导致命中不了缓存。
+
+如果多张表Join,使用最近更新的分区ID和最新的版本号,如果其中一张表更新了,会导致分区ID或版本号不一样,也一样命中不了缓存。
+
+
+
+### 2 分区缓存 `partition_cache`
+
+1. SQL可以并行拆分,Q = Q1 ∪ Q2 ... ∪ Qn,R= R1 ∪ R2 ... ∪ Rn,Q为查询语句,R为结果集
Review comment:
这里并没有太理解,能举例一个SQL说明吗?
1、何种SQL能应用分区缓存功能
2、何种SQL或者操作可能导致分区缓存功能失效
3、缓存的是分区数据,还是结果集数据。希望可以简要介绍下原理
4、result_cache_version缓存策略和result_cache_version缓存策略间是什么关系?互斥的,还是互相兼容的
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
kangkaisen commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478803671
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,184 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
Review comment:
第一句 里面的 数据库层 是应用层?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] HangyuanLiu commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
HangyuanLiu commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r506866776
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,183 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好地支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,一种在应用层的解决方案是把查询结果放到Redis中,周期性地更新缓存或者用户手动刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+
+
+## 三 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+
+
+## 四 设计原理
+
+### 1 结果缓存 `result_cache`
+
+result_cache 分两层 第一层为 result_cache_ttl 第二层为 result_cache_version
+
+
+
+#### `result_cache_ttl`
+
+result_cache_ttl 变量设置在用户Session中,用户可自定义是否开启,通过ttl时间来确定用户的sql是否使用缓存,`这里数据变更时不保证数据的正确性`
+
+按照 用户 connectid,和查询的sql 来存储和获取缓存,超过缓存失效时间则命中不了缓存,该缓存也会被清理
+
+
+
+#### ` result_cache_version`
+
+result_cache_version 按SQL的签名、查询的表的分区ID、分区最新版本来存储和获取缓存。三者组合确定一个缓存数据集,任何一个变化了,如SQL有变化,如查询字段或条件不一样,或数据更新后版本变化了,会导致命中不了缓存。
+
+如果多张表Join,使用最近更新的分区ID和最新的版本号,如果其中一张表更新了,会导致分区ID或版本号不一样,也一样命中不了缓存。
+
+
+
+### 2 分区缓存 `partition_cache`
+
+1. SQL可以并行拆分,Q = Q1 ∪ Q2 ... ∪ Qn,R= R1 ∪ R2 ... ∪ Rn,Q为查询语句,R为结果集
Review comment:
这里并没有太理解,能举例一个SQL说明吗?
1、何种SQL能应用分区缓存功能
2、何种SQL或者操作可能导致分区缓存功能失效
3、缓存的是分区数据,还是结果集数据。希望可以简要介绍下原理
4、result_cache_ttl缓存策略和result_cache_version缓存策略间是什么关系?互斥的,还是互相兼容的
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] WuZixing commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
WuZixing commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478873939
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
+
+Usually, we have cache in the corresponding database layer which is focus on the query content.However the data granularity in the cache is too slow and it would have effect only when data in the table didn't change.Obviously, it could not reduce the heavy IO load from the CRUD of the business system. So it comes the database caching technology, which could highspeed caching the hot data, improve the response speed of the application and greatly relieve the load of the backend database.
+
+- High concurrency scenarios
+ Doris have a well support for high concurrency while single sever is unable to load too high QPS.
+
+
+
+- Complex Graph Dashboard
+ It is not uncommon to see that, data of the complex Dashboard and the lage screen applications come from many table together which have tens of queries in a single page.Even though every single query cost only few milliseconds, the total queries would cost seconds.
+
+
+- Trend Analysis
+ In some scenarios, the queries are in a given date range , the index is shown by date.For example, we want to query the treend of the number of user in the last 7 days.This type of queries has a large amount of data and a wide range of fields, and the queries often takes tens of seconds.
+
+
+- User repeated query
+ If the product does not have an anti-re-flash mechanism, the user accidentally refreshes the page repeatedly due many reasons, which resulting in submitting a large number of repeated SQL
+
+
+
+In the above four scenarios, we have solutions at the application layer. We put the result of queries in the Redis and update the cache periodically or the user update the cache manually.However, this solution has the following problems:
+
+
+
+- Inconsistence of data , we are unable to sense the update of data, causing users to often see old data
+
+- Low hit rate, we usually cache the whole result of query.If the data is writed real-time, we would often failed in cache, resulting in low hit rate and overload for the system.
+
+- Extra Cost we introduce external cache components, which will bring system complexity and increase additional costs.
+
+
+
+## 二 Solutions
+
+At present, we design two modules: result cache and partition cache
+
+
+
+## 三 Explanation of terms
+
+1. result cache
+
+SQL directly caches the result collection of queries for users
+
+
+
+2. partition cache
+
+In the partition granularity, cache the results of each partition query
+
+
+
+## 四 Design
+
+### 1 `result cache`
+
+result_cache is divided into two layers. The first layer is result_ cache_ The second layer of TTL is result_ cache_ version
+
+
+
+#### `result_cache_ttl`
+
+result_ cache_ ttl variable is set in the user session. The user can customize whether to turn it on or not. The TTL time is used to determine whether the user's SQL uses cache. The correctness of the data is not guaranteed when the data is changed`
+
+
+
+The cache is stored and retrieved according to the user connected and the query SQL. If it exceeds the cache expiration time, the cache will not be hit and the cache will be cleaned
+
+
+
+#### ` result_cache_version`
+
+result_ cache_ version stores and fetches the cache according to the signature of SQL, partition ID of the query table, latest version of partition. The combination of the three determines a cache dataset. If any one of them changes, such as SQL changes, query fields or conditions are not the same, or the version after data update changes, the cache will not be hit.
+
+
+
+If multiple tables are joined, the latest partition ID and the latest version number are used. If one of the tables is updated, the partition ID or version number will be different, and the cache will not be hit.
+
+
+
+### 2 `partition_cache`
+
+1. SQL can be split in parallel,Q = Q1 ∪ Q2 ... ∪ Qn,R= R1 ∪ R2 ... ∪ Rn,Q is the query statement and R is the result set
+
+2. Split into read-only partition and updatable partition, read-only partition cache, update partition not cache
+
+
+
+## 五 parameter
+
+### fe
+
+- Cache switch
+
+```
+enable_result_cache_ttl
+
+enable_result_cache_version
+
+enable_partition_cache
+```
+
+- Whether each query is cached or not
+
+```
+cache_per_query_max_row_count
+
+cache_per_query_max_size_in_bytes
+```
+
+- result cache til
+
+```
+result_cache_ttl_in_milliseconds
+```
+
+
+
+### be
+
+- Be maximum number of partitions cache_ max_ partition_ Count refers to the maximum number of partitions corresponding to each SQL. If the partition is based on date, the data can be cached for more than 2 years. If you want to keep the cache for a longer time, please set this parameter larger and modify the cache_ result_ max_ row_ Count parameter.
Review comment:
*... data can be cached for more than 2 years. If you want to keep the cache for a longer time, ...*.
I am not very clear about why count of cached partitions can affect the lifetime of the cache.
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] WuZixing commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
WuZixing commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478860111
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
+
+Usually, we have cache in the corresponding database layer which is focus on the query content.However the data granularity in the cache is too slow and it would have effect only when data in the table didn't change.Obviously, it could not reduce the heavy IO load from the CRUD of the business system. So it comes the database caching technology, which could highspeed caching the hot data, improve the response speed of the application and greatly relieve the load of the backend database.
+
+- High concurrency scenarios
+ Doris have a well support for high concurrency while single sever is unable to load too high QPS.
+
+
+
+- Complex Graph Dashboard
+ It is not uncommon to see that, data of the complex Dashboard and the lage screen applications come from many table together which have tens of queries in a single page.Even though every single query cost only few milliseconds, the total queries would cost seconds.
Review comment:
```suggestion
It is not uncommon to see that, data of the complex Dashboard and the large screen applications come from many table together which have tens of queries in a single page.Even though every single query cost only few milliseconds, the total queries would cost seconds.
```
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
Review comment:
```suggestion
## 1 Demand
```
Mixing Chinese and English is a little bit weird...
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
+
+Usually, we have cache in the corresponding database layer which is focus on the query content.However the data granularity in the cache is too slow and it would have effect only when data in the table didn't change.Obviously, it could not reduce the heavy IO load from the CRUD of the business system. So it comes the database caching technology, which could highspeed caching the hot data, improve the response speed of the application and greatly relieve the load of the backend database.
+
+- High concurrency scenarios
+ Doris have a well support for high concurrency while single sever is unable to load too high QPS.
+
+
+
+- Complex Graph Dashboard
+ It is not uncommon to see that, data of the complex Dashboard and the lage screen applications come from many table together which have tens of queries in a single page.Even though every single query cost only few milliseconds, the total queries would cost seconds.
+
+
+- Trend Analysis
+ In some scenarios, the queries are in a given date range , the index is shown by date.For example, we want to query the treend of the number of user in the last 7 days.This type of queries has a large amount of data and a wide range of fields, and the queries often takes tens of seconds.
Review comment:
```suggestion
In some scenarios, the queries are in a given date range , the index is shown by date.For example, we want to query the trend of the number of user in the last 7 days.This type of queries has a large amount of data and a wide range of fields, and the queries often takes tens of seconds.
```
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
+
+Usually, we have cache in the corresponding database layer which is focus on the query content.However the data granularity in the cache is too slow and it would have effect only when data in the table didn't change.Obviously, it could not reduce the heavy IO load from the CRUD of the business system. So it comes the database caching technology, which could highspeed caching the hot data, improve the response speed of the application and greatly relieve the load of the backend database.
+
+- High concurrency scenarios
+ Doris have a well support for high concurrency while single sever is unable to load too high QPS.
+
+
+
+- Complex Graph Dashboard
+ It is not uncommon to see that, data of the complex Dashboard and the lage screen applications come from many table together which have tens of queries in a single page.Even though every single query cost only few milliseconds, the total queries would cost seconds.
+
+
+- Trend Analysis
+ In some scenarios, the queries are in a given date range , the index is shown by date.For example, we want to query the treend of the number of user in the last 7 days.This type of queries has a large amount of data and a wide range of fields, and the queries often takes tens of seconds.
+
+
+- User repeated query
+ If the product does not have an anti-re-flash mechanism, the user accidentally refreshes the page repeatedly due many reasons, which resulting in submitting a large number of repeated SQL
+
+
+
+In the above four scenarios, we have solutions at the application layer. We put the result of queries in the Redis and update the cache periodically or the user update the cache manually.However, this solution has the following problems:
+
+
+
+- Inconsistence of data , we are unable to sense the update of data, causing users to often see old data
+
+- Low hit rate, we usually cache the whole result of query.If the data is writed real-time, we would often failed in cache, resulting in low hit rate and overload for the system.
+
+- Extra Cost we introduce external cache components, which will bring system complexity and increase additional costs.
+
+
+
+## 二 Solutions
+
+At present, we design two modules: result cache and partition cache
+
+
+
+## 三 Explanation of terms
+
+1. result cache
+
+SQL directly caches the result collection of queries for users
+
+
+
+2. partition cache
+
+In the partition granularity, cache the results of each partition query
+
+
+
+## 四 Design
+
+### 1 `result cache`
+
+result_cache is divided into two layers. The first layer is result_ cache_ The second layer of TTL is result_ cache_ version
Review comment:
*The first layer is **result_ cache_** The second layer of TTL is **result_ cache_** version*
What do these underscore suffixes mean?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] WuZixing commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
WuZixing commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478866543
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
+
+Usually, we have cache in the corresponding database layer which is focus on the query content.However the data granularity in the cache is too slow and it would have effect only when data in the table didn't change.Obviously, it could not reduce the heavy IO load from the CRUD of the business system. So it comes the database caching technology, which could highspeed caching the hot data, improve the response speed of the application and greatly relieve the load of the backend database.
+
+- High concurrency scenarios
+ Doris have a well support for high concurrency while single sever is unable to load too high QPS.
+
+
+
+- Complex Graph Dashboard
+ It is not uncommon to see that, data of the complex Dashboard and the lage screen applications come from many table together which have tens of queries in a single page.Even though every single query cost only few milliseconds, the total queries would cost seconds.
+
+
+- Trend Analysis
+ In some scenarios, the queries are in a given date range , the index is shown by date.For example, we want to query the treend of the number of user in the last 7 days.This type of queries has a large amount of data and a wide range of fields, and the queries often takes tens of seconds.
+
+
+- User repeated query
+ If the product does not have an anti-re-flash mechanism, the user accidentally refreshes the page repeatedly due many reasons, which resulting in submitting a large number of repeated SQL
+
+
+
+In the above four scenarios, we have solutions at the application layer. We put the result of queries in the Redis and update the cache periodically or the user update the cache manually.However, this solution has the following problems:
+
+
+
+- Inconsistence of data , we are unable to sense the update of data, causing users to often see old data
+
+- Low hit rate, we usually cache the whole result of query.If the data is writed real-time, we would often failed in cache, resulting in low hit rate and overload for the system.
+
+- Extra Cost we introduce external cache components, which will bring system complexity and increase additional costs.
+
+
+
+## 二 Solutions
+
+At present, we design two modules: result cache and partition cache
+
+
+
+## 三 Explanation of terms
+
+1. result cache
+
+SQL directly caches the result collection of queries for users
+
+
+
+2. partition cache
+
+In the partition granularity, cache the results of each partition query
+
+
+
+## 四 Design
+
+### 1 `result cache`
+
+result_cache is divided into two layers. The first layer is result_ cache_ The second layer of TTL is result_ cache_ version
Review comment:
*The first layer is **result_ cache_** The second layer of TTL is **result_ cache_** version*
What do these underscore suffixes mean? Are they **result_cache** without separated space?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
kangkaisen commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478804908
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,184 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好的支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,在应用层的解决方案,把查询结果放到Redis中,周期性的更新缓存或者用户手工刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+
+
+## 三 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+
+
+## 四 设计原理
+
+### 1 结果缓存 `result_cache`
Review comment:
每种cache最好给下 典型的应用场景。
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] songenjie commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
songenjie commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r512500061
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,183 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好地支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,一种在应用层的解决方案是把查询结果放到Redis中,周期性地更新缓存或者用户手动刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+
+
+## 三 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+
+
+## 四 设计原理
+
+### 1 结果缓存 `result_cache`
+
+result_cache 分两层 第一层为 result_cache_ttl 第二层为 result_cache_version
+
+
+
+#### `result_cache_ttl`
+
+result_cache_ttl 变量设置在用户Session中,用户可自定义是否开启,通过ttl时间来确定用户的sql是否使用缓存,`这里数据变更时不保证数据的正确性`
+
+按照 用户 connectid,和查询的sql 来存储和获取缓存,超过缓存失效时间则命中不了缓存,该缓存也会被清理
+
+
+
+#### ` result_cache_version`
+
+result_cache_version 按SQL的签名、查询的表的分区ID、分区最新版本来存储和获取缓存。三者组合确定一个缓存数据集,任何一个变化了,如SQL有变化,如查询字段或条件不一样,或数据更新后版本变化了,会导致命中不了缓存。
+
+如果多张表Join,使用最近更新的分区ID和最新的版本号,如果其中一张表更新了,会导致分区ID或版本号不一样,也一样命中不了缓存。
+
+
+
+### 2 分区缓存 `partition_cache`
+
+1. SQL可以并行拆分,Q = Q1 ∪ Q2 ... ∪ Qn,R= R1 ∪ R2 ... ∪ Rn,Q为查询语句,R为结果集
+
+2. 拆分为只读分区和可更新分区,只读分区缓存,更新分区不缓存
+
+
+
+## 五 参数
+
+### fe
+
+- cache 开关
+
+```
+enable_result_cache_ttl
Review comment:
```
MySQL [(none)]> show variables;
+-------------------------------------+-------------------------+
| Variable_name | Value |
+-------------------------------------+-------------------------+
| enable_result_cache_ttl | false |
| enable_result_cache_version | false |
| enable_partition_cache | true |
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] caiconghui commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
caiconghui commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r479602225
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,184 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
Review comment:
虽然在数据库层 -> 虽然在数据库存储层?
一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用 -> 查询结果的高速缓存的实现方案也是需要数据没有变更?
但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力 -> 但这并不能减少业务系统对数据库进行增删改查所带来的庞大的IO压力?
所以数据库缓存技术 在此诞生, -> 数据库计算层的缓存技术?
实现热点数据 -> 实现查询结果??
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] HangyuanLiu commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
HangyuanLiu commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r513149723
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,147 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+## 1 需求
+
+虽然在数据库存储层也做了对应的缓存,但这种数据库存储层的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。 但这并不能减少业务系统对数据库进行增删改查所带来的庞大的IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好地支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+以上四种场景,一种在应用层的解决方案是把查询结果放到Redis中,周期性地更新缓存或者用户手动刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+## 2 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+## 3 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+## 4 设计原理
+
+### 1 结果缓存 `result_cache`
+
+result_cache 分两种 第一种为 result_cache_ttl 第二种为 result_cache_version
+
+#### `result_cache_ttl`
+
+result_cache_ttl 变量设置在用户Session中,用户可自定义是否开启,通过ttl时间来确定用户的sql是否使用缓存,`这里数据变更时不保证数据的正确性`
+按照 用户 connectid,和查询的sql 来存储和获取缓存,超过缓存失效时间则命中不了缓存,该缓存也会被清理
+
+#### ` result_cache_version`
+
+result_cache_version 按SQL的签名、查询的表的分区ID、分区最新版本来存储和获取缓存。三者组合确定一个缓存数据集,任何一个变化了,如SQL有变化,如查询字段或条件不一样,或数据更新后版本变化了,会导致命中不了缓存。
+
+如果多张表Join,使用最近更新的分区ID和最新的版本号,如果其中一张表更新了,会导致分区ID或版本号不一样,也一样命中不了缓存。
+
+### 2 分区缓存 `partition_cache`
+
+1. SQL可以并行拆分,Q = Q1 ∪ Q2 ... ∪ Qn,R= R1 ∪ R2 ... ∪ Rn,Q为查询语句,R为结果集
+2. 拆分为只读分区和可更新分区,只读分区缓存,更新分区不缓存
+
+## 5 参数
+
+### fe
+
+#### cache 开关
+
+1. `enable_result_cache_ttl`
Review comment:
```suggestion
1. `enable_result_cache_ttl`
```
目前代码中并没有看到关于TTL的相关实现逻辑,请确认是否存在这个功能?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] caiconghui commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
caiconghui commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r479602284
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,184 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好的支持高并发,但单台服务器无法承载太高的QPS
Review comment:
```suggestion
Doris可以较好地支持高并发,但单台服务器无法承载太高的QPS
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
kangkaisen commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478804734
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,184 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好的支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,在应用层的解决方案,把查询结果放到Redis中,周期性的更新缓存或者用户手工刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+
+
+## 三 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+
+
+## 四 设计原理
+
+### 1 结果缓存 `result_cache`
+
+result_cache 分两层 第一层为 result_cache_ttl 第二层为 result_cache_version
+
+
+
+#### `result_cache_ttl`
+
+result_cache_ttl 变量设置在用户Session中,用户可自定义是否开启,通过ttl时间来确定用户的sql是否使用缓存,`这里数据变更时不保证数据的正确性`
+
+按照 用户 connectid,和查询的sql 来存储和获取缓存,超过缓存失效时间则命中不了缓存,该缓存也会被清理
+
+
+
+#### ` result_cache_version`
+
+result_cache_version 按SQL的签名、查询的表的分区ID、分区最新版本来存储和获取缓存。三者组合确定一个缓存数据集,任何一个变化了,如SQL有变化,如查询字段或条件不一样,或数据更新后版本变化了,会导致命中不了缓存。
+
+如果多张表Join,使用最近更新的分区ID和最新的版本号,如果其中一张表更新了,会导致分区ID或版本号不一样,也一样命中不了缓存。
+
+
+
+### 2 分区缓存 `partition_cache`
+
+1. SQL可以并行拆分,Q = Q1 ∪ Q2 ... ∪ Qn,R= R1 ∪ R2 ... ∪ Rn,Q为查询语句,R为结果集
+
+2. 拆分为只读分区和可更新分区,只读分区缓存,更新分区不缓存
+
+
+
+## 五 参数
+
+### fe
+
+- cache 开关
Review comment:
配置给个简单的解释。 和默认值。 以及说明下这3种cache是否可以同时开启。
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] yangzhg commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
yangzhg commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r504350597
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
+
+Usually, we have cache in the corresponding database layer which is focus on the query content.However the data granularity in the cache is too slow and it would have effect only when data in the table didn't change.Obviously, it could not reduce the heavy IO load from the CRUD of the business system. So it comes the database caching technology, which could highspeed caching the hot data, improve the response speed of the application and greatly relieve the load of the backend database.
+
+- High concurrency scenarios
+ Doris have a well support for high concurrency while single sever is unable to load too high QPS.
+
+
+
+- Complex Graph Dashboard
+ It is not uncommon to see that, data of the complex Dashboard and the lage screen applications come from many table together which have tens of queries in a single page.Even though every single query cost only few milliseconds, the total queries would cost seconds.
+
+
+- Trend Analysis
+ In some scenarios, the queries are in a given date range , the index is shown by date.For example, we want to query the treend of the number of user in the last 7 days.This type of queries has a large amount of data and a wide range of fields, and the queries often takes tens of seconds.
+
+
+- User repeated query
+ If the product does not have an anti-re-flash mechanism, the user accidentally refreshes the page repeatedly due many reasons, which resulting in submitting a large number of repeated SQL
+
+
+
+In the above four scenarios, we have solutions at the application layer. We put the result of queries in the Redis and update the cache periodically or the user update the cache manually.However, this solution has the following problems:
+
+
+
+- Inconsistence of data , we are unable to sense the update of data, causing users to often see old data
+
+- Low hit rate, we usually cache the whole result of query.If the data is writed real-time, we would often failed in cache, resulting in low hit rate and overload for the system.
+
+- Extra Cost we introduce external cache components, which will bring system complexity and increase additional costs.
+
+
+
+## 二 Solutions
Review comment:
don`t mix use 一二三四 in en doc
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] songenjie commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
songenjie commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r512495692
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,183 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好地支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,一种在应用层的解决方案是把查询结果放到Redis中,周期性地更新缓存或者用户手动刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+
+
+## 三 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+
+
+## 四 设计原理
+
+### 1 结果缓存 `result_cache`
+
+result_cache 分两层 第一层为 result_cache_ttl 第二层为 result_cache_version
+
+
+
+#### `result_cache_ttl`
+
+result_cache_ttl 变量设置在用户Session中,用户可自定义是否开启,通过ttl时间来确定用户的sql是否使用缓存,`这里数据变更时不保证数据的正确性`
+
+按照 用户 connectid,和查询的sql 来存储和获取缓存,超过缓存失效时间则命中不了缓存,该缓存也会被清理
+
+
+
+#### ` result_cache_version`
+
+result_cache_version 按SQL的签名、查询的表的分区ID、分区最新版本来存储和获取缓存。三者组合确定一个缓存数据集,任何一个变化了,如SQL有变化,如查询字段或条件不一样,或数据更新后版本变化了,会导致命中不了缓存。
+
+如果多张表Join,使用最近更新的分区ID和最新的版本号,如果其中一张表更新了,会导致分区ID或版本号不一样,也一样命中不了缓存。
+
+
+
+### 2 分区缓存 `partition_cache`
+
+1. SQL可以并行拆分,Q = Q1 ∪ Q2 ... ∪ Qn,R= R1 ∪ R2 ... ∪ Rn,Q为查询语句,R为结果集
Review comment:
1. 分区缓存,即用户按照天维度查询的,查询的分区数据,没有新的数据导入,即命中 partition cache
2. 分区缓存失效,缓存结果所在的分区有新的数据导入
3. 缓存的结果集数据
4. version 针对当前表没有数据变更生效,ttl 不保证数据的准确性,新数据导入,在ttl时间内,仍然会命中
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] kangkaisen commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
kangkaisen commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478804798
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,184 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好的支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,在应用层的解决方案,把查询结果放到Redis中,周期性的更新缓存或者用户手工刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+
+
+## 三 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+
+
+## 四 设计原理
+
+### 1 结果缓存 `result_cache`
+
+result_cache 分两层 第一层为 result_cache_ttl 第二层为 result_cache_version
Review comment:
似乎不是层次关系?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] morningman merged pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
morningman merged pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] WuZixing commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
WuZixing commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r478866543
##########
File path: docs/en/administrator-guide/query_cache.md
##########
@@ -0,0 +1,190 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "en"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+
+# QUERY CACHE
+
+
+## 一 Demond
+
+Usually, we have cache in the corresponding database layer which is focus on the query content.However the data granularity in the cache is too slow and it would have effect only when data in the table didn't change.Obviously, it could not reduce the heavy IO load from the CRUD of the business system. So it comes the database caching technology, which could highspeed caching the hot data, improve the response speed of the application and greatly relieve the load of the backend database.
+
+- High concurrency scenarios
+ Doris have a well support for high concurrency while single sever is unable to load too high QPS.
+
+
+
+- Complex Graph Dashboard
+ It is not uncommon to see that, data of the complex Dashboard and the lage screen applications come from many table together which have tens of queries in a single page.Even though every single query cost only few milliseconds, the total queries would cost seconds.
+
+
+- Trend Analysis
+ In some scenarios, the queries are in a given date range , the index is shown by date.For example, we want to query the treend of the number of user in the last 7 days.This type of queries has a large amount of data and a wide range of fields, and the queries often takes tens of seconds.
+
+
+- User repeated query
+ If the product does not have an anti-re-flash mechanism, the user accidentally refreshes the page repeatedly due many reasons, which resulting in submitting a large number of repeated SQL
+
+
+
+In the above four scenarios, we have solutions at the application layer. We put the result of queries in the Redis and update the cache periodically or the user update the cache manually.However, this solution has the following problems:
+
+
+
+- Inconsistence of data , we are unable to sense the update of data, causing users to often see old data
+
+- Low hit rate, we usually cache the whole result of query.If the data is writed real-time, we would often failed in cache, resulting in low hit rate and overload for the system.
+
+- Extra Cost we introduce external cache components, which will bring system complexity and increase additional costs.
+
+
+
+## 二 Solutions
+
+At present, we design two modules: result cache and partition cache
+
+
+
+## 三 Explanation of terms
+
+1. result cache
+
+SQL directly caches the result collection of queries for users
+
+
+
+2. partition cache
+
+In the partition granularity, cache the results of each partition query
+
+
+
+## 四 Design
+
+### 1 `result cache`
+
+result_cache is divided into two layers. The first layer is result_ cache_ The second layer of TTL is result_ cache_ version
Review comment:
*The first layer is **result_ cache_** The second layer of TTL is **result_ cache_** version*
What do these underscore suffixes mean? Are they **result_cache**?
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] songenjie commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
songenjie commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r512496228
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,183 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好地支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,一种在应用层的解决方案是把查询结果放到Redis中,周期性地更新缓存或者用户手动刷新缓存,但是这个方案有如下问题:
+
+- 数据不一致
+ 无法感知数据的更新,导致用户经常看到旧的数据
+
+- 命中率低
+ 缓存整个查询结果,如果数据实时写入,缓存频繁失效,命中率低且系统负载较重
+
+- 额外成本
+ 引入外部缓存组件,会带来系统复杂度,增加额外成本
+
+
+
+## 二 解决方案
+
+目前我们设计出结果缓存和分区缓存两个模块
+
+
+
+## 三 名词解释
+
+1. 结果缓存 result_cache
+
+针对用户的sql直接缓存查询的结果集合
+
+
+
+2. 分区缓存 partition_cache
+
+在partition粒度做针对每个分区查询的结果缓存
+
+
+
+## 四 设计原理
+
+### 1 结果缓存 `result_cache`
+
+result_cache 分两层 第一层为 result_cache_ttl 第二层为 result_cache_version
+
+
+
+#### `result_cache_ttl`
+
+result_cache_ttl 变量设置在用户Session中,用户可自定义是否开启,通过ttl时间来确定用户的sql是否使用缓存,`这里数据变更时不保证数据的正确性`
+
+按照 用户 connectid,和查询的sql 来存储和获取缓存,超过缓存失效时间则命中不了缓存,该缓存也会被清理
+
+
+
+#### ` result_cache_version`
+
+result_cache_version 按SQL的签名、查询的表的分区ID、分区最新版本来存储和获取缓存。三者组合确定一个缓存数据集,任何一个变化了,如SQL有变化,如查询字段或条件不一样,或数据更新后版本变化了,会导致命中不了缓存。
+
+如果多张表Join,使用最近更新的分区ID和最新的版本号,如果其中一张表更新了,会导致分区ID或版本号不一样,也一样命中不了缓存。
+
+
+
+### 2 分区缓存 `partition_cache`
+
+1. SQL可以并行拆分,Q = Q1 ∪ Q2 ... ∪ Qn,R= R1 ∪ R2 ... ∪ Rn,Q为查询语句,R为结果集
+
+2. 拆分为只读分区和可更新分区,只读分区缓存,更新分区不缓存
+
+
+
+## 五 参数
+
+### fe
+
+- cache 开关
+
+```
+enable_result_cache_ttl
Review comment:
都是
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org
[GitHub] [incubator-doris] caiconghui commented on a change in pull request #4479: [Apache][Doris]add query cache docs
Posted by GitBox <gi...@apache.org>.
caiconghui commented on a change in pull request #4479:
URL: https://github.com/apache/incubator-doris/pull/4479#discussion_r479602472
##########
File path: docs/zh-CN/administrator-guide/query_cache.md
##########
@@ -0,0 +1,184 @@
+---
+{
+ "title": "QUERY CACHE",
+ "language": "zh-CN"
+}
+---
+
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+# QUERY CACHE
+
+
+
+## 一 需求
+
+虽然在数据库层也做了对应的缓存,但这种数据库层次的缓存一般针对的是查询内容,而且粒度也太小,一般只有表中数据没有变更的时候,数据库对应的cache才发挥了作用。但这并不能减少业务系统对数据库产生的增、删、查、改的庞大IO压力。所以数据库缓存技术在此诞生,实现热点数据的高速缓存,提高应用的响应速度,极大缓解后端数据库的压力
+
+- 高并发场景
+ Doris可以较好的支持高并发,但单台服务器无法承载太高的QPS
+
+- 复杂图表的看板
+ 复杂的Dashboard或者大屏类应用,数据来自多张表,每个页面有数十个查询,虽然每个查询只有数十毫秒,但是总体查询时间会在数秒
+
+- 趋势分析
+ 给定日期范围的查询,指标按日显示,比如查询最近7天内的用户数的趋势,这类查询数据量大,查询范围广,查询时间往往需要数十秒
+
+- 用户重复查询
+ 如果产品没有防重刷机制,用户因手误或其他原因重复刷新页面,导致提交大量的重复的SQL
+
+
+
+以上四种场景,在应用层的解决方案,把查询结果放到Redis中,周期性的更新缓存或者用户手工刷新缓存,但是这个方案有如下问题:
Review comment:
```suggestion
以上四种场景,一种在应用层的解决方案是把查询结果放到Redis中,周期性地更新缓存或者用户手动刷新缓存,但是这个方案有如下问题:
```
----------------------------------------------------------------
This is an automated message from the Apache Git Service.
To respond to the message, please log on to GitHub and use the
URL above to go to the specific comment.
For queries about this service, please contact Infrastructure at:
users@infra.apache.org
---------------------------------------------------------------------
To unsubscribe, e-mail: commits-unsubscribe@doris.apache.org
For additional commands, e-mail: commits-help@doris.apache.org