MySQL Query slows down on concurrent requests to different subpartitions of a partitioned table

Question

We have partitioned a large table (with over 100 million records) into multiple partitions and subpartitions in our Serverless Aurora to query different subpartitions in parallel. But we find that increasing the number of parallel queries targeting different partitions decreases the query performance.

SELECT DISTINCT qcid, mid 
FROM partitioned_table_name 
WHERE mid IN (<Few Thousands IDs>) 
AND qcid IN (<Few hundreds IDs>)
AND qid = <qid>
AND qtype = <qtype>
AND event IN ('create', 'destroy')
GROUP BY mid, qcid
HAVING ((CHAR_LENGTH(GROUP_CONCAT(event SEPARATOR '')) % 13) != 0) /* 13 is part of business logic */

The params are given such that it queries only specific partitions (as given by EXPLAIN PARTITIONS)

# Parallel queries	Time taken for single query	Subpartitions Queried
1	10s	pq_1816014_20104926_sp0
2	12s	pq_1816014_20104926_sp0, pq_1816014_20104926_sp1
4	14s-15s	pq_1816014_20104926_sp[0-3]
6	21s-22s	pq_1816014_20104926_sp[0-5]
8	28s-30s	pq_1816014_20104926_sp[0-7] (~3x more time than single query)

According to their blog, "Partitions (and subpartitions) are a tool to mitigate performance decreases in large tables. Because each partition is stored in a separate tablespace by default, ..."

I understand that as every subpartition is in a separate tablespace, parallel queries targeting different tablespaces should not be affected. Please help me understand why the query takes up 3x more time when the number of queries is increased.

EXPLAIN PARTITION QUERY:

{
  "id"=>1,
  "select_type"=>"SIMPLE",
  "table"=>"sharded_acvs_7_sub",
  "partitions"=> "pq_1816014_20104926_pq_1816014_20104926sp0",
  "type"=>"ref",
  "possible_keys" => "idx_acvs_on_q_id_qtype_qc_id_mid_event_created_at,idx_acvs_on_mid,idx_acvs_on_qcid",
  "key"=>"idx_acvs_on_q_id_qtype_qc_id_mid_event_created_at",
  "key_len"=>"772",
  "ref"=>"const,const",
  "rows"=>498216,
  "Extra"=>"Using where; Using index; Using filesort"
}

SHOW CREATE TABLE QUERY:

CREATE TABLE `sharded_acvs_7_sub` (
  `itype` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `iid` int(11) NOT NULL,
  `mid` int(11) DEFAULT NULL,
  `qcid` int(11) DEFAULT NULL,
  `event` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `created_at` datetime NOT NULL,
  `qid` int(11) DEFAULT NULL,
  `qtype` varchar(191) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  KEY `idx_acvs_on_iid_and_itype` (`iid`,`itype`),
  KEY `idx_acvs_on_q_id_qtype_qc_id_mid_event_created_at` (`qid`,`qtype`,`qcid`,`mid`,`event`,`created_at`),
  KEY `idx_acvs_on_mid` (`mid`),
  KEY `idx_acvs_on_qcid` (`qcid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci

PARTITION BY RANGE  COLUMNS(qtype,qid,qcid)
SUBPARTITION BY HASH ( `mid`)
SUBPARTITIONS 7
(
 ....
 PARTITION pq_1816013_20104817 VALUES LESS THAN ('PQ',1816013,20104817) ENGINE = InnoDB,
 PARTITION pq_1816014_20104926 VALUES LESS THAN ('PQ',1816014,20104926) ENGINE = InnoDB,
 PARTITION pq_1816014_20104964 VALUES LESS THAN ('PQ',1816014,20104964) ENGINE = InnoDB,
 PARTITION pq_1816015_20105073 VALUES LESS THAN ('PQ',1816015,20105073) ENGINE = InnoDB,
 ...
)

Related Stats
Total # of sub partition	105
# Records in a single sub partition	1M - 1.5M records
Total RAM	64 GB
innodb_buffer_pool_size	46017806336
innodb_sort_buffer_size	1048576
sort_buffer_size	29547520

Answer 1

SELECT DISTINCT is redundant with GROUP BY. Remove the DISTINCT.

In my opinion, BY HASH is totally useless.

Unless Aurora is doing something I don't know about, there is no parallelism when doing Partition lookups. (If I am wrong please provide a link describing such.)

I would expect a non-partitioned table to run just as fast by using KEY idx_acvs_on_q_id_qtype_qc_id_mid_event_created_at (qid,qtype,qcid,mid,event,created_at),