Singlestore (memsql) query causing High CPU usage and locks on the table-CodePudding

High CPU usage and locks on the table are being caused by my query. Performance has not improved despite my attempts with various cluster indexes and shard keys.

In Memsql we have created this table and performing this query. In my environment, this query is the top slow running query. Please advice on how to optimise this Memsql table or query.

Table:

CREATE TABLE `mobile_flow` (
  `marker` varchar(8) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `teqthry` varchar(12) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,
  `stry_on` bigint(20) NOT NULL,
  `stry_clsd` bigint(20) NOT NULL,
  `matr_start` bigint(20) NOT NULL,
  `cost_no` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `prc_findr` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `cost_row` varchar(5) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `prd_sol` varchar(50) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL,
  `prd_range` double NOT NULL,
  `prd_invty` double NOT NULL,
  `prd_hagx` int(11) DEFAULT NULL,
  UNIQUE KEY `unq_n1` (`marker`,`teqthry`,`matr_start`) USING HASH,
  KEY `index_1` (marker`,`teqthry`,`matr_start`) USING CLUSTERED COLUMNSTORE,
  SHARD KEY `shd_n1` (`matr_start`,`teqthry`)
);

Query:

SELECT count(*)
FROM mobile_flow
WHERE matr_start<1426393500 and matr_start>=1525202400 AND teqthry='kund' AND marker in ('scpopec', 'rdeg', 'dhendqs');

CodePudding user response：

Are the columns you use in the filter indexes? If not try to make as many indexes as possible from the columns used in filter.

You can try rearranging the filter order, keep the filter which removes the most number of rows first followed by next biggest filter. This will make sure that downstream filters need not work on large number of records. In Case you are unable to make all those columns as indexes, keep the biggest indexed column filter at the top followed by others.

CodePudding user response：

For your query you want a BTREE-like index on (marker, teqthry, matr_start). That matches your query pattern, which searches for equality on the first two columns and a range-scan on the third. The Singlestore people have a BTREE-like structure called a skiplist, declared by calling it BTREE.

You already have

I don't have access to a singlestore system or to your data. And, I'm no expert on it so I can't try this out. But you might try something like this:

ALTER TABLE mobile_flow
 DROP KEY index_1
  ADD KEY index_1 (marker, teqthry, matr_start) USING BTREE;

If I were you I'd ask Singlestore support for a bit of help if this doesn't work.