2

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
New contributor
PrathapB is a new contributor to this site. Take care in asking for clarification, commenting, and answering. Check out our Code of Conduct.
6
  • What is the total number of [sub]partitions in the table?
    – Rick James
    Jun 22 at 20:26
  • Is the "% 13" part of the business logic? Or were you just sampling the output?
    – Rick James
    Jun 22 at 20:31
  • By "# Parallel queries" you mean the number of "Subpartitions Queried"?
    – Rick James
    Jun 22 at 21:52
  • @RickJames The table has 105 subpartitions. Yes '% 13' is part of business logic. The parallel queries target only a single partition. When there are 2 parallel queries, Query1 targets pq_1816014_20104926_sp0 and Query 2 targets pq_1816014_20104926_sp1 .
    – PrathapB
    Jun 23 at 3:06
  • Are the "parallel queries" run from separate connections?
    – Rick James
    Jun 23 at 4:44

1 Answer 1

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),

4
  • I have removed DISTINCT, thanks. But the same query run on un-partitioned table took me 52 seconds. So we chose to partition the table. Also I saw the query performed better with a lesser number of records in a single partition (~1.5M - 2M). Our data could not be partitioned with just RANGE to achieve lesser records. That's why I went ahead with HASH sub partitioning. And regarding parallelism, I have raised a ticket to AWS inquiring the same.
    – PrathapB
    Jun 23 at 5:47
  • We will have to hit the table likewise parallelly for multiple reports. I felt instead of hitting the entire table parallelly, hitting specific partitions might help lessen the lookup space.. Am I missing anything at MySQL end w.r.t handling multiple concurrent queries like tweaking any variables/configs/parameters.. Or isn't MySQL capable of concurrently querying tables with millions of records?
    – PrathapB
    Jun 23 at 8:12
  • @PrathapB - With ENGINE=InnoDB, MySQL is happy to have multiple connections SELECTing (and, to some extent, INSERTing) into the same table at the same time. So, I don't have enough detail to explain the 52s.
    – Rick James
    Jun 23 at 14:38
  • @PrathapB - But even 10s bothers me. How many rows are returned? How many rows have a given qid = <qid> AND qtype = <qtype>? (The second question says how many rows will be looked at using that index.) If you have the slowlog turned on, the info may be in there.
    – Rick James
    Jun 23 at 14:40

Your Answer

PrathapB is a new contributor. Be nice, and check out our Code of Conduct.

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.