aderm commented on issue #4291: Improve ES query performance URL: https://github.com/apache/skywalking/issues/4291#issuecomment-584063442 ### Based on the previous situation, a 60W test data was created. <img width="381" alt="WX20200210-183335@2x" src="https://user-images.githubusercontent.com/2892433/74142455-e9eeb600-4c33-11ea-918a-8c8375224b95.png";> ### Designed four test scenarios - Comparing dynamic and non-dynamic query condition scenarios - Comparing `ConstantScoreQueryBuilder` and non `ConstantScoreQueryBuilder` query condition scenarios - Comparing `boolQuery().filter` and non `boolQuery().filter` query condition scenarios - Comparing all filter and part filter query condition scenarios - Comparing all constant score and part constant score query condition scenarios ``` /** * @author aderm */ @Warmup(iterations = 5, time = 5, timeUnit = TimeUnit.SECONDS) @Measurement(iterations = 5, time = 5, timeUnit = TimeUnit.SECONDS) @State(Scope.Benchmark) public class FilterQueryTest { private RestHighLevelClient client; private final long startTimestamp = 1580287478790L; private final long endTimestamp = 1680480640000L; private final long MULTIPLE = 10000L; private final int SEARCH_SIZE = 5000; private final String INDEX_NAME = "service_inventory"; @Setup public void createClient() { client = new RestHighLevelClient((RestClient.builder(new HttpHost("127.0.0.1", 9200, "http")))); } @TearDown public void closeClient() throws IOException { client.close(); } /** * Benchmark test fix es query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testFixEsQuery() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.should().add(timeRangeQueryBuild(startTimestamp, endTimestamp)); boolQueryBuilder.should().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(boolQueryBuilder); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test fix es part const query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testFixEsConstQueryPart() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.should().add(new ConstantScoreQueryBuilder(timeRangeQueryBuild(startTimestamp, endTimestamp))); boolQueryBuilder.should().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(boolQueryBuilder); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test fix es all const query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testFixEsConstQueryAll() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.should().add(timeRangeQueryBuild(startTimestamp, endTimestamp)); boolQueryBuilder.should().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(new ConstantScoreQueryBuilder(boolQueryBuilder)); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test fix es part filter query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testFixEsFilterQueryPart() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.must().add(QueryBuilders.boolQuery().filter(timeRangeQueryBuild(startTimestamp, endTimestamp))); boolQueryBuilder.must().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(boolQueryBuilder); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test fix es all filter query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testFixEsFilterQueryAll() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.must().add(timeRangeQueryBuild(startTimestamp, endTimestamp)); boolQueryBuilder.must().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(QueryBuilders.boolQuery().filter(boolQueryBuilder)); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test dynamic es query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testDynamicEsQuery() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); long ranFactor = (long) (Math.random() * MULTIPLE); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.should().add(timeRangeQueryBuild(startTimestamp + ranFactor, endTimestamp + ranFactor)); boolQueryBuilder.should().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(boolQueryBuilder); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test dynamic es part const query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testDynamicEsConstQueryPart() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); long ranFactor = (long) (Math.random() * MULTIPLE); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.should().add(new ConstantScoreQueryBuilder(timeRangeQueryBuild(startTimestamp + ranFactor, endTimestamp + ranFactor))); boolQueryBuilder.should().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(boolQueryBuilder); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test dynamic es all const query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testDynamicEsConstQueryAll() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); long ranFactor = (long) (Math.random() * MULTIPLE); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.should().add(timeRangeQueryBuild(startTimestamp + ranFactor, endTimestamp + ranFactor)); boolQueryBuilder.should().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(new ConstantScoreQueryBuilder(boolQueryBuilder)); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test dynamic es part filter query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testDynamicEsFilterQueryPart() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); long ranFactor = (long) (Math.random() * MULTIPLE); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.should().add(QueryBuilders.boolQuery().filter(timeRangeQueryBuild(startTimestamp + ranFactor, endTimestamp + ranFactor))); boolQueryBuilder.should().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(boolQueryBuilder); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } /** * Benchmark test dynamic es all filter query clause */ @Benchmark @BenchmarkMode(Mode.AverageTime) @OutputTimeUnit(TimeUnit.MICROSECONDS) public void testDynamicEsFilterQueryAll() throws IOException { SearchSourceBuilder sourceBuilder = SearchSourceBuilder.searchSource(); long ranFactor = (long) (Math.random() * MULTIPLE); BoolQueryBuilder boolQueryBuilder = QueryBuilders.boolQuery(); boolQueryBuilder.should().add(timeRangeQueryBuild(startTimestamp + ranFactor, endTimestamp + ranFactor)); boolQueryBuilder.should().add(QueryBuilders.termQuery("is_address", 0)); boolQueryBuilder.should().add(QueryBuilders.termQuery("node_type", 0)); sourceBuilder.query(QueryBuilders.boolQuery().filter(boolQueryBuilder)); sourceBuilder.size(SEARCH_SIZE); SearchRequest searchRequest = new SearchRequest(INDEX_NAME); searchRequest.source(sourceBuilder); SearchResponse response = client.search(searchRequest, RequestOptions.DEFAULT); } public static void main(String[] args) throws RunnerException, IOException { Options opt = new OptionsBuilder() .include(FilterQueryTest.class.getSimpleName()) .forks(1) .build(); new Runner(opt).run(); // FilterQueryTest filterQueryTest = new FilterQueryTest(); // filterQueryTest.createClient(); // filterQueryTest.testDynamicEsQuery(); // filterQueryTest.testDynamicEsQuery(); // filterQueryTest.testDynamicEsFilterQuery(); // filterQueryTest.closeClient(); } private BoolQueryBuilder timeRangeQueryBuild(long startTimestamp, long endTimestamp) { BoolQueryBuilder boolQuery1 = QueryBuilders.boolQuery(); boolQuery1.should().add(QueryBuilders.rangeQuery("heartbeat_time").gte(startTimestamp)); boolQuery1.should().add(QueryBuilders.rangeQuery("heartbeat_time").lte(endTimestamp)); BoolQueryBuilder boolQuery2 = QueryBuilders.boolQuery(); boolQuery2.should().add(QueryBuilders.rangeQuery("register_time").gte(startTimestamp)); boolQuery2.should().add(QueryBuilders.rangeQuery("register_time").lte(endTimestamp)); BoolQueryBuilder timeBoolQuery = QueryBuilders.boolQuery(); timeBoolQuery.should().add(boolQuery1); timeBoolQuery.should().add(boolQuery2); return timeBoolQuery; } } ``` ### Test Results <img width="764" alt="WX20200210-183029@2x" src="https://user-images.githubusercontent.com/2892433/74143043-05a68c00-4c35-11ea-8c89-d5b866888efc.png";> ### conclusion * Do as many filters as possible to query. Under the condition that the query conditions are unchanged, it can indeed improve the query efficiency. If the query conditions change dynamically, the improvement effect will not be obvious.Put all the query conditions into the filter, the effect is not very good.
---------------------------------------------------------------- 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: [email protected] With regards, Apache Git Services
![https://user-images.githubusercontent.com/2892433/74142455-e9eeb600-4c33-11ea-918a-8c8375224b95.png"](https://user-images.githubusercontent.com/2892433/74142455-e9eeb600-4c33-11ea-918a-8c8375224b95.png"){kind=link}
![https://user-images.githubusercontent.com/2892433/74143043-05a68c00-4c35-11ea-8c89-d5b866888efc.png"](https://user-images.githubusercontent.com/2892433/74143043-05a68c00-4c35-11ea-8c89-d5b866888efc.png"){kind=link}