Slow HashGroupBy in columnstore table

Help Query Tuning
1

I just analyzed a slow query and noticed a HashGroupBy bottleneck.

The Visual Explain feature in MemSQL Studio gives these messages:

encoded_group_by_unsupported: not a columnstore table

encoded_join_unsupported: probe side is not an unordered scan of columnstore

I was’t able to find any documentation regarding the above issues, but wonders why it mentions a join as this query only targets a single columnstore table.

How can I improve the query/schema to avoid the issues?

The Visual Explain feature indicate the first HashGroupBy executes fine but the second targeting remote_0 has some troubles. Is this caused by some data repartitioning?

I tried to add url_id and keyword_id to the clusted columnstore index but without any performance improvement.

The query below has also been optimized from the original query which didn’t have the url_id and keyword_id is not null clauses. Adding those two improved performance.

Without the url_id and keyword_id in the group by the query executes in less than 100 ms.

Query:

select
  keyword_id,
  url_id,
  device,
  date,
  sum(impressions) as impressions
from
  gsc_search_analytics
where
  site_id = 1
  and search_type = "web"
  and device is not null
  and query is not null
  and date between "2019-06-09 00:00:00"
  and "2019-06-23 00:00:00"
  and url_id is not null
  and keyword_id is not null
group by
  keyword_id,
  url_id,
  device,
  date;

Schema:

CREATE TABLE `gsc_search_analytics` (
  `id` bigint(20) unsigned NOT NULL,
  `site_id` bigint(20) unsigned NOT NULL,
  `url_id` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `keyword_id` char(32) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `date` date NOT NULL,
  `search_type` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci NOT NULL,
  `country` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `device` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `page` varchar(2048) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `query` varchar(255) CHARACTER SET utf8 COLLATE utf8_unicode_ci DEFAULT NULL,
  `clicks` int(10) unsigned NOT NULL,
  `impressions` int(10) unsigned NOT NULL,
  `ctr` double NOT NULL,
  `position` double NOT NULL,
KEY `site_id` (`site_id`,`date`,`search_type`,`country`,`device`,`page`,`query`) USING CLUSTERED COLUMNSTORE,
SHARD KEY `date` (`date`,`id`),
FULLTEXT KEY `page` (`page`,`query`) );

Profile:

{ "profile":[ { "executor":"Project", "out":[ { "alias":"", "projection":"remote_0.keyword_id" }, { "alias":"", "projection":"remote_0.url_id" }, { "alias":"", "projection":"remote_0.device" }, { "alias":"", "projection":"remote_0.date" }, { "alias":"", "projection":"impressions" } ], "subselects":[], "actual_row_count":{ "value":300 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":2054 }, "network_traffic":{ "value":25960 }, "network_time":{ "value":0 }, "inputs":[ { "executor":"Top", "limit":"@@SESSION.`sql_select_limit`", "actual_row_count":{ "value":300 }, "actual_total_time":{ "value":0 }, "inputs":[ { "executor":"HashGroupBy", "out":[ { "alias":"impressions", "projection":"SUM(remote_0.impressions)" } ], "groups":[ "remote_0.keyword_id", "remote_0.url_id", "remote_0.device", "remote_0.date" ], "encoded_group_by_unsupported":"not a columnstore table: 0tmp", "encoded_join_unsupported":"probe side is not an unordered scan of columnstore", "subselects":[], "actual_row_count":{ "value":850654 }, "actual_total_time":{ "value":1260 }, "start_time":{ "value":441 }, "memory_usage":{ "value":427819032 }, "inputs":[ { "executor":"Gather", "partitions":"all", "query":"SELECT `gsc_search_analytics`.`keyword_id` AS `keyword_id`, `gsc_search_analytics`.`url_id` AS `url_id`, `gsc_search_analytics`.`device` AS `device`, `gsc_search_analytics`.`date` AS `date`, SUM(`gsc_search_analytics`.`impressions`) AS `impressions` FROM `laravel_0`.`gsc_search_analytics` as `gsc_search_analytics` WHERE ((`gsc_search_analytics`.`site_id` = 1) AND (NOT ISNULL(`gsc_search_analytics`.`device`)) AND (NOT ISNULL(`gsc_search_analytics`.`query`)) AND (`gsc_search_analytics`.`date` BETWEEN '2019-06-09 00:00:00' AND '2019-06-23 00:00:00') AND (NOT ISNULL(`gsc_search_analytics`.`url_id`)) AND (NOT ISNULL(`gsc_search_analytics`.`keyword_id`)) AND (`gsc_search_analytics`.`search_type` = 'web')) GROUP BY 1, 2, 3, 4 OPTION(NO_QUERY_REWRITE=1, INTERPRETER_MODE=INTERPRET_FIRST)", "alias":"remote_0", "actual_row_count":{ "value":850654 }, "actual_total_time":{ "value":0 }, "start_time":{ "value":2054 }, "end_time":{ "value":2054 }, "inputs":[ { "executor":"Project", "out":[ { "alias":"", "projection":"gsc_search_analytics.keyword_id" }, { "alias":"", "projection":"gsc_search_analytics.url_id" }, { "alias":"", "projection":"gsc_search_analytics.device" }, { "alias":"", "projection":"gsc_search_analytics.date" }, { "alias":"", "projection":"impressions" } ], "subselects":[], "actual_row_count":{ "value":850654, "avg":212663.500000, "stddev":219.824362, "max":212850, "maxPartition":0 }, "actual_total_time":{ "value":247, "avg":242.500000, "stddev":4.500000, "max":247, "maxPartition":0 }, "start_time":{ "value":439, "avg":452.500000, "stddev":0.000000, "max":469, "maxPartition":3 }, "network_traffic":{ "value":89550478, "avg":22387619.500000, "stddev":23762.370899, "max":22407954, "maxPartition":0 }, "network_time":{ "value":1315, "avg":1296.000000, "stddev":19.000000, "max":1315, "maxPartition":0 }, "inputs":[ { "executor":"HashGroupBy", "out":[ { "alias":"impressions", "projection":"SUM(gsc_search_analytics.impressions)" } ], "groups":[ "gsc_search_analytics.keyword_id", "gsc_search_analytics.url_id", "gsc_search_analytics.device", "gsc_search_analytics.date" ], "subselects":[], "actual_row_count":{ "value":850654, "avg":212663.500000, "stddev":219.824362, "max":212850, "maxPartition":0 }, "actual_total_time":{ "value":186, "avg":183.000000, "stddev":3.000000, "max":186, "maxPartition":0 }, "start_time":{ "value":13, "avg":16.000000, "stddev":0.000000, "max":20, "maxPartition":3 }, "memory_usage":{ "value":408551488, "avg":102137872.000000, "stddev":108679.161130, "max":102236176, "maxPartition":0 }, "inputs":[ { "executor":"Filter", "condition":[ "gsc_search_analytics.site_id = ? AND gsc_search_analytics.device IS NOT NULL AND gsc_search_analytics.query IS NOT NULL AND gsc_search_analytics.date >= ? AND gsc_search_analytics.date <= ? AND gsc_search_analytics.url_id IS NOT NULL AND gsc_search_analytics.keyword_id IS NOT NULL AND gsc_search_analytics.search_type = ?" ], "subselects":[], "actual_row_count":{ "value":850654, "avg":212663.500000, "stddev":219.824362, "max":212850, "maxPartition":0 }, "actual_total_time":{ "value":7, "avg":5.500000, "stddev":1.500000, "max":7, "maxPartition":1 }, "start_time":{ "value":7, "avg":8.500000, "stddev":0.000000, "max":10, "maxPartition":1 }, "inputs":[ { "executor":"ColumnStoreScan", "db":"laravel", "table":"gsc_search_analytics", "alias":"gsc_search_analytics", "index":"KEY site_id (site_id, date, search_type, country, device, page, query) USING CLUSTERED COLUMNSTORE", "storage":"columnar", "est_table_rows":"1291561255", "est_filtered":"1", "actual_row_count":{ "value":9216000, "avg":2304000.000000, "stddev":443405.006738, "max":3072000, "maxPartition":3 }, "actual_total_time":{ "value":256, "avg":253.500000, "stddev":2.500000, "max":256, "maxPartition":1 }, "start_time":{ "value":0, "avg":0.750000, "stddev":0.000000, "max":1, "maxPartition":1 }, "memory_usage":{ "value":11010048, "avg":2752512.000000, "stddev":0.000000, "max":2752512, "maxPartition":0 }, "segments_scanned":{ "value":9, "avg":2.250000, "stddev":0.433013, "max":3, "maxPartition":3 }, "segments_skipped":{ "value":1260, "avg":315.000000, "stddev":0.000000, "max":315, "maxPartition":0 }, "segments_fully_contained":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "segments_filter_encoded_data":{ "value":9, "avg":2.250000, "stddev":0.433013, "max":3, "maxPartition":3 }, "segments_encoded_group_by":{ "value":0, "avg":0.000000, "stddev":0.000000, "max":0, "maxPartition":0 }, "encoded_group_by_bailout":{ "Number of segments that reached dynamic dictionary size limit":"9" }, "inputs":[] } ] } ] } ] } ] } ] } ] } ] } ], "version":"2", "info":{ "memsql_version":"6.8.1", "memsql_version_hash":"d3d220c2727a9c1574b748f4ef8481830a4c766d", "num_online_leaves":"2", "num_online_aggs":"1", "context_database":"laravel" } }
2

I think this interesting.

3

You’re correct that the encoded_join_unsupported message you see doesn’t make much sense being there. The message no longer appears for non-join queries in 7.0.

I see the Gather executor in your profile, which means that results from the first groupby/filter are being sent to the aggregator, which performs the second groupby. This is based on an estimation that there aren’t very many groups. Looking at the profile, this estimation appears to be inaccurate.

You can try forcing the entire groupby to be done on the leaves with select with (leaf_pushdown=1) .... This will cause a repartition on the leaves and prevent you from bottlenecking on the aggregator. You can also try running ANALYZE table to update estimates so this happens without a query hint.

Alternatively, I see you tried changing your keys to improve performance. The columnstore key only defines the sort order, which I don’t expect to help things very much. To avoid data movement entirely (which should give even better performance than the hint), you can try making your shard key a subset of your groupby columns.

4

@cwatts thanks for the explanation behind the multiple HashGroupBy.

I’ve already tried ANALYZE table without any improvement probably because this is ran on a regular basis, but the leaf_pushdown=1 reduced the load time by 75%!

Now, the largest bottleneck became ShuffleGroupBy, which is probably because our clustered index doesn’t contain two of the group by columns. By creating a new columnstore table with these columns in the clustered index, the load time reduced further (86% compared to the original load time before leaf_pushdown=1). Keep in mind this table only contains the data of the needed days by the query and therefore is much smaller. This might affect performance as well, I guess.

With the optimized columnstore table schema, HashGroupBy becomes the slowes block in the Visual Explain again, as ShuffleGroupBy improved. What can be done to improve this further? Do we have to wait on MemSQL 7.0 with secondary indexes?

Do you have any documentation for these hints like leaf_pushdown and descriptions of the executors from the Visual Explain?

1 Like
5

Here’s some documentation mentioning leaf_pushdown: SingleStoreDB Cloud · SingleStore Documentation

This page has some documentation regarding executor descriptions: SingleStoreDB Cloud · SingleStore Documentation

As far as optimizing the performance of the HashGroupBy - there’s not much you can do. I do see that the query is bailing out of encoded group by: "encoded_group_by_bailout":{ "Number of segments that reached dynamic dictionary size limit":"9"} (documentation here: SingleStoreDB Cloud · SingleStore Documentation). It means that you have too many groups to be eligible for encoded group by. You may see a performance improvement by disabling encoded group by like from gsc_search_analytics with (disable_encoded_group_by=true).

1 Like
6

All the planner/optimizer hints (documented or undocumented) would be nice to have, given this is a low latency product ,and the hints although short-lived as versions /architecture change would be a nice way to get some heady performance out of memsql.

something like in sqlserver/Sybase dbcc trace hints like (302,310) etc…

7

For grins… can you replace your between with a greater and less than? I’ve seen some … behavior with columnstore. Seems the simpler the query, the easier it is to breakdown and delegate to the nodes. I’ve seen some function force a push up to agg and back down. Just a thought. Let me know if it helps please.

8

I ran into similar issues with HashGroupBy and I had to add all the columns in COLUMNSTORE INDEX to the group by clause to enforce StreamingGroupBy which made the query faster and also required lesser memory(I am guessing temp hash tables take up extra space).

So my question would be, with single column hash indexes in 7.1, can we make HashGroupBy efficient by adding hash index for the column on which I want to do a group by? This will help a case where we can’t modify INDEX and also can’t include all INDEX columns in group by for a particular query.

Also, if I want to do group by on multiple columns, can multiple single column hash indices help?

Thanks and Regards,
Praveen

9

This is a very late reply, but hash indices are currently unused for hash groupby execution.