...

The speed up for this particular query is roughly 30 %. Note, there is no penalty in terms of the disk space utilization for partitioning tables.

Improved performance for parallel loading into multiple partitions

Replacing the combined PRIMRY KEY:

PRIMARY KEY (`id`,`qserv_load_id`)

with a simple non-unique KEY:

KEY (`qserv_load_id`)

has improved the overall performance for the loading into 8 partitions simultaneously:

# load time for each partition
8 minutes * 60 seconds + 14 seconds = 494 seconds


# Aggregate I/O rate 
(8 partitions * 9000 MB) / 494 seconds =  146 MB/s

NOTE: the CPU utilization during the loading was close 800 %.

References

https://dev.mysql.com/doc/refman/8.0/en/partitioning.html

...