This is an automated email from the ASF dual-hosted git repository.
yiguolei pushed a commit to branch master
in repository https://gitbox.apache.org/repos/asf/doris.git
The following commit(s) were added to refs/heads/master by this push:
new 45b39c5aaf [enhancement](regression-test) Support BenchmarkAction
(#16071)
45b39c5aaf is described below
commit 45b39c5aaf5f00eab84dd41c8d0cf2589310d468
Author: 924060929 <[email protected]>
AuthorDate: Thu Jan 19 08:02:05 2023 +0800
[enhancement](regression-test) Support BenchmarkAction (#16071)
Support benchmarkAction for regression test, this action can help us to run
the benchmark queries and print the result
example:
benchmark {
executeTimes 3
warmUp true
skipFailure true
printResult true
sqls(["select 1", "select 2"])
}
---
.../doris/regression/action/BenchmarkAction.groovy | 154 ++++++++++++++++
.../org/apache/doris/regression/suite/Suite.groovy | 13 +-
.../apache/doris/regression/util/SuiteUtils.groovy | 27 +++
.../framework/src/main/groovy/suite.gdsl | 1 +
.../explain_clickbench_benchmark.groovy | 194 +++++++++++++++++++++
5 files changed, 383 insertions(+), 6 deletions(-)
diff --git
a/regression-test/framework/src/main/groovy/org/apache/doris/regression/action/BenchmarkAction.groovy
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/action/BenchmarkAction.groovy
new file mode 100644
index 0000000000..f39be3c011
--- /dev/null
+++
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/action/BenchmarkAction.groovy
@@ -0,0 +1,154 @@
+// 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.
+
+package org.apache.doris.regression.action
+
+import groovy.util.logging.Slf4j
+import org.apache.doris.regression.suite.SuiteContext
+import org.apache.doris.regression.util.JdbcUtils
+import org.apache.doris.regression.util.SuiteUtils
+
+import java.math.RoundingMode
+
+@Slf4j
+class BenchmarkAction implements SuiteAction {
+ private int executeTimes = 3
+ private boolean skipFailure = true
+ private boolean printResult = true
+ private boolean warmUp = true
+ private List<String> sqls
+
+ SuiteContext context
+
+ BenchmarkAction(SuiteContext context) {
+ this.context = context
+ }
+
+ void sqls(String... sqls) {
+ this.sqls = sqls.toList()
+ }
+
+ void sqls(List<String> sqls) {
+ this.sqls = sqls
+ }
+
+ void executeTimes(int times) {
+ this.executeTimes = times
+ }
+
+ void skipFailure(boolean skipFailure) {
+ this.skipFailure = skipFailure
+ }
+
+ void warmUp(boolean warmUp) {
+ this.warmUp = warmUp
+ }
+
+ void printResult(boolean printResult) {
+ this.printResult = printResult
+ }
+
+ @Override
+ void run() {
+ if (warmUp) {
+ log.info("start to warm up")
+ for (int i = 1; i <= sqls.size(); ++i) {
+ def sql = sqls[i - 1]
+ log.info("Start to warm up sql ${i}:\n${sql}".toString())
+
+ try {
+ JdbcUtils.executeToList(context.getConnection(), sql)
+ } catch (Throwable t) {
+ if (!skipFailure) {
+ throw t
+ }
+ }
+ }
+ }
+
+ log.info("start to run benchmark")
+ List<Map<String, Double>> results = []
+ for (int i = 1; i <= sqls.size(); ++i) {
+ try {
+ def sql = sqls[i - 1]
+ log.info("Start to execute sql ${i}:\n${sql}".toString())
+
+ List<Long> elapsedInfo = []
+ for (int times = 1; times <= executeTimes; ++times) {
+ log.info("Execute sql ${i} for the ${times == 1 ? "first"
: times} time${times > 1 ? "s" : ""}".toString())
+ def (_, elapsed) = SuiteUtils.timer {
+ JdbcUtils.executeToList(context.getConnection(), sql)
+ }
+ elapsedInfo.add(elapsed)
+ }
+
+ def avg = avg(elapsedInfo)
+ def min = min(elapsedInfo)
+ def max = max(elapsedInfo)
+
+ results.add([min: min, max: max, avg: avg])
+ log.info("Execute sql ${i} result: avg: ${avg} ms, min: ${min}
ms, max: ${max} ms".toString())
+ } catch (Throwable t) {
+ if (!skipFailure) {
+ throw t
+ }
+ }
+ }
+
+ if (printResult) {
+ String line =
"+-----------+---------------+---------------+---------------+\n"
+ String resultStrings = line + "| SQL ID | avg |
min | max |\n" + line
+ List<Double> avgResults = []
+ List<Long> minResults = []
+ List<Long> maxResults = []
+ for (int i = 1; i <= results.size(); ++i) {
+ def result = results[i - 1]
+ resultStrings += String.format("| SQL %-3d | %10.2f ms |
%10d ms | %10d ms |\n",
+ i, result["avg"], result["min"], result["max"])
+ avgResults.add(result["avg"])
+ minResults.add(result["min"].toLong())
+ maxResults.add(result["max"].toLong())
+ }
+ resultStrings += line +
+ String.format("| TOTAL AVG | %10.2f ms | %10.2f ms |
%10.2f ms |\n",
+ avg(avgResults), avg(minResults), avg(maxResults))
+
+ String.format("| TOTAL SUM | %10.2f ms | %10d ms | %10d ms
|\n",
+ sum(avgResults), sum(minResults), sum(maxResults))
+ line
+ log.info("bechmark result: \n${resultStrings}")
+ }
+ }
+
+ private Number max(List<Long> numbers) {
+ return numbers.stream()
+ .reduce{ n1, n2 -> return Math.max(n1, n2) }.orElse(0L)
+ }
+
+ private Number min(List<Number> numbers) {
+ return numbers.stream()
+ .reduce{ n1, n2 -> return Math.min(n1, n2) }.orElse(0L)
+ }
+
+ private Number sum(List<Number> numbers) {
+ return numbers.stream()
+ .reduce{ n1, n2 -> return n1 + n2 }.orElse(0L)
+ }
+
+ private double avg(List<Number> numbers) {
+ double result = numbers.isEmpty() ? 0 : sum(numbers) * 1.0 /
numbers.size()
+ return new BigDecimal(result).setScale(2,
RoundingMode.HALF_UP).toDouble()
+ }
+}
diff --git
a/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy
index cc82bf4e50..7e5005c7b1 100644
---
a/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy
+++
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/suite/Suite.groovy
@@ -22,6 +22,7 @@ import com.google.common.util.concurrent.ListenableFuture
import com.google.common.util.concurrent.MoreExecutors
import groovy.json.JsonSlurper
import com.google.common.collect.ImmutableList
+import org.apache.doris.regression.action.BenchmarkAction
import org.apache.doris.regression.util.DataUtils
import org.apache.doris.regression.util.OutputUtils
import org.apache.doris.regression.action.ExplainAction
@@ -32,6 +33,7 @@ import org.apache.doris.regression.action.TestAction
import org.apache.doris.regression.action.HttpCliAction
import org.apache.doris.regression.util.JdbcUtils
import org.apache.doris.regression.util.Hdfs
+import org.apache.doris.regression.util.SuiteUtils
import org.junit.jupiter.api.Assertions
import org.slf4j.Logger
import org.slf4j.LoggerFactory
@@ -45,8 +47,6 @@ import java.util.stream.LongStream
import static org.apache.doris.regression.util.DataUtils.sortByToString
-import java.io.File
-
@Slf4j
class Suite implements GroovyInterceptable {
final SuiteContext context
@@ -139,10 +139,7 @@ class Suite implements GroovyInterceptable {
}
public <T> Tuple2<T, Long> timer(Closure<T> actionSupplier) {
- long startTime = System.currentTimeMillis()
- T result = actionSupplier.call()
- long endTime = System.currentTimeMillis()
- return [result, endTime - startTime]
+ return SuiteUtils.timer(actionSupplier)
}
public <T> ListenableFuture<T> thread(String threadName = null, Closure<T>
actionSupplier) {
@@ -274,6 +271,10 @@ class Suite implements GroovyInterceptable {
runAction(new TestAction(context), actionSupplier)
}
+ void benchmark(Closure actionSupplier) {
+ runAction(new BenchmarkAction(context), actionSupplier)
+ }
+
String getBrokerName() {
String brokerName = context.config.otherConfigs.get("brokerName")
return brokerName
diff --git
a/regression-test/framework/src/main/groovy/org/apache/doris/regression/util/SuiteUtils.groovy
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/util/SuiteUtils.groovy
new file mode 100644
index 0000000000..a181e8e1c1
--- /dev/null
+++
b/regression-test/framework/src/main/groovy/org/apache/doris/regression/util/SuiteUtils.groovy
@@ -0,0 +1,27 @@
+// 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.
+
+package org.apache.doris.regression.util
+
+class SuiteUtils {
+ static <T> Tuple2<T, Long> timer(Closure<T> actionSupplier) {
+ long startTime = System.currentTimeMillis()
+ T result = actionSupplier.call()
+ long endTime = System.currentTimeMillis()
+ return [result, endTime - startTime]
+ }
+}
diff --git a/regression-test/framework/src/main/groovy/suite.gdsl
b/regression-test/framework/src/main/groovy/suite.gdsl
index e72afd7c5f..7b50692038 100644
--- a/regression-test/framework/src/main/groovy/suite.gdsl
+++ b/regression-test/framework/src/main/groovy/suite.gdsl
@@ -48,6 +48,7 @@ bindAction("test",
"org.apache.doris.regression.action.TestAction")
bindAction("explain", "org.apache.doris.regression.action.ExplainAction")
bindAction("streamLoad", "org.apache.doris.regression.action.StreamLoadAction")
bindAction("httpTest", "org.apache.doris.regression.action.HttpCliAction")
+bindAction("benchmark", "org.apache.doris.regression.action.BenchmarkAction")
// bind qt_xxx and order_qt_xxx methods
contributor([suiteContext]) {
diff --git
a/regression-test/suites/nereids_benchmark_p2/explain_clickbench_benchmark.groovy
b/regression-test/suites/nereids_benchmark_p2/explain_clickbench_benchmark.groovy
new file mode 100644
index 0000000000..a8c491d855
--- /dev/null
+++
b/regression-test/suites/nereids_benchmark_p2/explain_clickbench_benchmark.groovy
@@ -0,0 +1,194 @@
+// 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.
+
+// This suit test the `backends` information_schema table
+suite("explain_clickbench_benchmark") {
+ sql """CREATE TABLE IF NOT EXISTS hits (
+ CounterID INT NOT NULL,
+ EventDate Datev2 NOT NULL,
+ UserID BIGINT NOT NULL,
+ EventTime DateTimev2 NOT NULL,
+ WatchID BIGINT NOT NULL,
+ JavaEnable SMALLINT NOT NULL,
+ Title STRING NOT NULL,
+ GoodEvent SMALLINT NOT NULL,
+ ClientIP INT NOT NULL,
+ RegionID INT NOT NULL,
+ CounterClass SMALLINT NOT NULL,
+ OS SMALLINT NOT NULL,
+ UserAgent SMALLINT NOT NULL,
+ URL STRING NOT NULL,
+ Referer STRING NOT NULL,
+ IsRefresh SMALLINT NOT NULL,
+ RefererCategoryID SMALLINT NOT NULL,
+ RefererRegionID INT NOT NULL,
+ URLCategoryID SMALLINT NOT NULL,
+ URLRegionID INT NOT NULL,
+ ResolutionWidth SMALLINT NOT NULL,
+ ResolutionHeight SMALLINT NOT NULL,
+ ResolutionDepth SMALLINT NOT NULL,
+ FlashMajor SMALLINT NOT NULL,
+ FlashMinor SMALLINT NOT NULL,
+ FlashMinor2 STRING NOT NULL,
+ NetMajor SMALLINT NOT NULL,
+ NetMinor SMALLINT NOT NULL,
+ UserAgentMajor SMALLINT NOT NULL,
+ UserAgentMinor VARCHAR(255) NOT NULL,
+ CookieEnable SMALLINT NOT NULL,
+ JavascriptEnable SMALLINT NOT NULL,
+ IsMobile SMALLINT NOT NULL,
+ MobilePhone SMALLINT NOT NULL,
+ MobilePhoneModel STRING NOT NULL,
+ Params STRING NOT NULL,
+ IPNetworkID INT NOT NULL,
+ TraficSourceID SMALLINT NOT NULL,
+ SearchEngineID SMALLINT NOT NULL,
+ SearchPhrase STRING NOT NULL,
+ AdvEngineID SMALLINT NOT NULL,
+ IsArtifical SMALLINT NOT NULL,
+ WindowClientWidth SMALLINT NOT NULL,
+ WindowClientHeight SMALLINT NOT NULL,
+ ClientTimeZone SMALLINT NOT NULL,
+ ClientEventTime DateTimev2 NOT NULL,
+ SilverlightVersion1 SMALLINT NOT NULL,
+ SilverlightVersion2 SMALLINT NOT NULL,
+ SilverlightVersion3 INT NOT NULL,
+ SilverlightVersion4 SMALLINT NOT NULL,
+ PageCharset STRING NOT NULL,
+ CodeVersion INT NOT NULL,
+ IsLink SMALLINT NOT NULL,
+ IsDownload SMALLINT NOT NULL,
+ IsNotBounce SMALLINT NOT NULL,
+ FUniqID BIGINT NOT NULL,
+ OriginalURL STRING NOT NULL,
+ HID INT NOT NULL,
+ IsOldCounter SMALLINT NOT NULL,
+ IsEvent SMALLINT NOT NULL,
+ IsParameter SMALLINT NOT NULL,
+ DontCountHits SMALLINT NOT NULL,
+ WithHash SMALLINT NOT NULL,
+ HitColor CHAR NOT NULL,
+ LocalEventTime DateTimev2 NOT NULL,
+ Age SMALLINT NOT NULL,
+ Sex SMALLINT NOT NULL,
+ Income SMALLINT NOT NULL,
+ Interests SMALLINT NOT NULL,
+ Robotness SMALLINT NOT NULL,
+ RemoteIP INT NOT NULL,
+ WindowName INT NOT NULL,
+ OpenerName INT NOT NULL,
+ HistoryLength SMALLINT NOT NULL,
+ BrowserLanguage STRING NOT NULL,
+ BrowserCountry STRING NOT NULL,
+ SocialNetwork STRING NOT NULL,
+ SocialAction STRING NOT NULL,
+ HTTPError SMALLINT NOT NULL,
+ SendTiming INT NOT NULL,
+ DNSTiming INT NOT NULL,
+ ConnectTiming INT NOT NULL,
+ ResponseStartTiming INT NOT NULL,
+ ResponseEndTiming INT NOT NULL,
+ FetchTiming INT NOT NULL,
+ SocialSourceNetworkID SMALLINT NOT NULL,
+ SocialSourcePage STRING NOT NULL,
+ ParamPrice BIGINT NOT NULL,
+ ParamOrderID STRING NOT NULL,
+ ParamCurrency STRING NOT NULL,
+ ParamCurrencyID SMALLINT NOT NULL,
+ OpenstatServiceName STRING NOT NULL,
+ OpenstatCampaignID STRING NOT NULL,
+ OpenstatAdID STRING NOT NULL,
+ OpenstatSourceID STRING NOT NULL,
+ UTMSource STRING NOT NULL,
+ UTMMedium STRING NOT NULL,
+ UTMCampaign STRING NOT NULL,
+ UTMContent STRING NOT NULL,
+ UTMTerm STRING NOT NULL,
+ FromTag STRING NOT NULL,
+ HasGCLID SMALLINT NOT NULL,
+ RefererHash BIGINT NOT NULL,
+ URLHash BIGINT NOT NULL,
+ CLID INT NOT NULL
+ )
+ DUPLICATE KEY (CounterID, EventDate, UserID, EventTime, WatchID)
+ DISTRIBUTED BY HASH(UserID) BUCKETS 48
+ PROPERTIES ( "replication_num"="1");
+ """
+
+ def queries = """
+ SELECT COUNT(*) FROM hits;
+ SELECT COUNT(*) FROM hits WHERE AdvEngineID <> 0;
+ SELECT SUM(AdvEngineID), COUNT(*), AVG(ResolutionWidth) FROM hits;
+ SELECT AVG(UserID) FROM hits;
+ SELECT COUNT(DISTINCT UserID) FROM hits;
+ SELECT COUNT(DISTINCT SearchPhrase) FROM hits;
+ SELECT MIN(EventDate), MAX(EventDate) FROM hits;
+ SELECT AdvEngineID, COUNT(*) FROM hits WHERE AdvEngineID <> 0
GROUP BY AdvEngineID ORDER BY COUNT(*) DESC;
+ SELECT RegionID, COUNT(DISTINCT UserID) AS u FROM hits GROUP BY
RegionID ORDER BY u DESC LIMIT 10;
+ SELECT RegionID, SUM(AdvEngineID), COUNT(*) AS c,
AVG(ResolutionWidth), COUNT(DISTINCT UserID) FROM hits GROUP BY RegionID ORDER
BY c DESC LIMIT 10;
+ SELECT MobilePhoneModel, COUNT(DISTINCT UserID) AS u FROM hits
WHERE MobilePhoneModel <> '' GROUP BY MobilePhoneModel ORDER BY u DESC LIMIT 10;
+ SELECT MobilePhone, MobilePhoneModel, COUNT(DISTINCT UserID) AS u
FROM hits WHERE MobilePhoneModel <> '' GROUP BY MobilePhone, MobilePhoneModel
ORDER BY u DESC LIMIT 10;
+ SELECT SearchPhrase, COUNT(*) AS c FROM hits WHERE SearchPhrase <>
'' GROUP BY SearchPhrase ORDER BY c DESC LIMIT 10;
+ SELECT SearchPhrase, COUNT(DISTINCT UserID) AS u FROM hits WHERE
SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY u DESC LIMIT 10;
+ SELECT SearchEngineID, SearchPhrase, COUNT(*) AS c FROM hits WHERE
SearchPhrase <> '' GROUP BY SearchEngineID, SearchPhrase ORDER BY c DESC LIMIT
10;
+ SELECT UserID, COUNT(*) FROM hits GROUP BY UserID ORDER BY
COUNT(*) DESC LIMIT 10;
+ SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID,
SearchPhrase ORDER BY COUNT(*) DESC LIMIT 10;
+ SELECT UserID, SearchPhrase, COUNT(*) FROM hits GROUP BY UserID,
SearchPhrase LIMIT 10;
+ SELECT UserID, extract(minute FROM EventTime) AS m, SearchPhrase,
COUNT(*) FROM hits GROUP BY UserID, m, SearchPhrase ORDER BY COUNT(*) DESC
LIMIT 10;
+ SELECT UserID FROM hits WHERE UserID = 435090932899640449;
+ SELECT COUNT(*) FROM hits WHERE URL LIKE '%google%';
+ SELECT SearchPhrase, MIN(URL), COUNT(*) AS c FROM hits WHERE URL
LIKE '%google%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC
LIMIT 10;
+ SELECT SearchPhrase, MIN(URL), MIN(Title), COUNT(*) AS c,
COUNT(DISTINCT UserID) FROM hits WHERE Title LIKE '%Google%' AND URL NOT LIKE
'%.google.%' AND SearchPhrase <> '' GROUP BY SearchPhrase ORDER BY c DESC LIMIT
10;
+ SELECT * FROM hits WHERE URL LIKE '%google%' ORDER BY EventTime
LIMIT 10;
+ SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY
EventTime LIMIT 10;
+ SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY
SearchPhrase LIMIT 10;
+ SELECT SearchPhrase FROM hits WHERE SearchPhrase <> '' ORDER BY
EventTime, SearchPhrase LIMIT 10;
+ SELECT CounterID, AVG(length(URL)) AS l, COUNT(*) AS c FROM hits
WHERE URL <> '' GROUP BY CounterID HAVING COUNT(*) > 100000 ORDER BY l DESC
LIMIT 25;
+ SELECT REGEXP_REPLACE(Referer,
'^https?://(?:www\\.)?([^/]+)/.*\$', '\\1') AS k, AVG(length(Referer)) AS l,
COUNT(*) AS c, MIN(Referer) FROM hits WHERE Referer <> '' GROUP BY k HAVING
COUNT(*) > 100000 ORDER BY l DESC LIMIT 25;
+ SELECT SUM(ResolutionWidth), SUM(ResolutionWidth + 1),
SUM(ResolutionWidth + 2), SUM(ResolutionWidth + 3), SUM(ResolutionWidth + 4),
SUM(ResolutionWidth + 5), SUM(ResolutionWidth + 6), SUM(ResolutionWidth + 7),
SUM(ResolutionWidth + 8), SUM(ResolutionWidth + 9), SUM(ResolutionWidth + 10),
SUM(ResolutionWidth + 11), SUM(ResolutionWidth + 12), SUM(ResolutionWidth +
13), SUM(ResolutionWidth + 14), SUM(ResolutionWidth + 15), SUM(ResolutionWidth
+ 16), SUM(ResolutionWidth + 17), S [...]
+ SELECT SearchEngineID, ClientIP, COUNT(*) AS c, SUM(IsRefresh),
AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY
SearchEngineID, ClientIP ORDER BY c DESC LIMIT 10;
+ SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh),
AVG(ResolutionWidth) FROM hits WHERE SearchPhrase <> '' GROUP BY WatchID,
ClientIP ORDER BY c DESC LIMIT 10;
+ SELECT WatchID, ClientIP, COUNT(*) AS c, SUM(IsRefresh),
AVG(ResolutionWidth) FROM hits GROUP BY WatchID, ClientIP ORDER BY c DESC LIMIT
10;
+ SELECT URL, COUNT(*) AS c FROM hits GROUP BY URL ORDER BY c DESC
LIMIT 10;
+ SELECT 1, URL, COUNT(*) AS c FROM hits GROUP BY 1, URL ORDER BY c
DESC LIMIT 10;
+ SELECT ClientIP, ClientIP - 1, ClientIP - 2, ClientIP - 3,
COUNT(*) AS c FROM hits GROUP BY ClientIP, ClientIP - 1, ClientIP - 2, ClientIP
- 3 ORDER BY c DESC LIMIT 10;
+ SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62
AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits =
0 AND IsRefresh = 0 AND URL <> '' GROUP BY URL ORDER BY PageViews DESC LIMIT 10;
+ SELECT Title, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62
AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND DontCountHits =
0 AND IsRefresh = 0 AND Title <> '' GROUP BY Title ORDER BY PageViews DESC
LIMIT 10;
+ SELECT URL, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62
AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0
AND IsLink <> 0 AND IsDownload = 0 GROUP BY URL ORDER BY PageViews DESC LIMIT
10 OFFSET 1000;
+ SELECT TraficSourceID, SearchEngineID, AdvEngineID, CASE WHEN
(SearchEngineID = 0 AND AdvEngineID = 0) THEN Referer ELSE '' END AS Src, URL
AS Dst, COUNT(*) AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >=
'2013-07-01' AND EventDate <= '2013-07-31' AND IsRefresh = 0 GROUP BY
TraficSourceID, SearchEngineID, AdvEngineID, Src, Dst ORDER BY PageViews DESC
LIMIT 10 OFFSET 1000;
+ SELECT URLHash, EventDate, COUNT(*) AS PageViews FROM hits WHERE
CounterID = 62 AND EventDate >= '2013-07-01' AND EventDate <= '2013-07-31' AND
IsRefresh = 0 AND TraficSourceID IN (-1, 6) AND RefererHash =
3594120000172545465 GROUP BY URLHash, EventDate ORDER BY PageViews DESC LIMIT
10 OFFSET 100;
+ SELECT WindowClientWidth, WindowClientHeight, COUNT(*) AS
PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-01' AND
EventDate <= '2013-07-31' AND IsRefresh = 0 AND DontCountHits = 0 AND URLHash =
2868770270353813622 GROUP BY WindowClientWidth, WindowClientHeight ORDER BY
PageViews DESC LIMIT 10 OFFSET 10000;
+ SELECT DATE_FORMAT(EventTime, '%Y-%m-%d %H:%i:00') AS M, COUNT(*)
AS PageViews FROM hits WHERE CounterID = 62 AND EventDate >= '2013-07-14' AND
EventDate <= '2013-07-15' AND IsRefresh = 0 AND DontCountHits = 0 GROUP BY
DATE_FORMAT(EventTime, '%Y-%m-%d %H:%i:00') ORDER BY DATE_FORMAT(EventTime,
'%Y-%m-%d %H:%i:00') LIMIT 10 OFFSET 1000;
+ """
+ .split(";\n")
+ .collect {it.trim() }
+ .findAll{!it.isEmpty() }
+ .collect{ "explain ${it}".toString()}
+
+ sql "SET enable_nereids_planner=true"
+ sql "SET enable_vectorized_engine=true"
+ sql "SET enable_fallback_to_original_planner=false"
+
+ benchmark {
+ warmUp true
+ executeTimes 3
+ skipFailure false
+ printResult true
+
+ sqls queries
+ }
+}
---------------------------------------------------------------------
To unsubscribe, e-mail: [email protected]
For additional commands, e-mail: [email protected]
