MySQL 8.0 performance issue with INSERT SELECT operations on large partitioned tables

Other Questions MySQL
1

I’m facing a challenge with performance during data transfers in MySQL 8.0. My system processes around 80 to 100 records every second, and currently, I have about 300 million records stored. To enhance query efficiency, I’ve modified the table structure.

Originally, my table design was straightforward:

CREATE TABLE IF NOT EXISTS `communications` (
  `RecordID` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `ReceivedAt` timestamp NOT NULL DEFAULT current_timestamp(),
  `UserID` int(10) unsigned NOT NULL,
-- Additional columns here
  PRIMARY KEY (`RecordID`),
  KEY `UserID` (`UserID`),
  KEY `ReceivedAt` (`ReceivedAt`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Now, I’m implementing a new structure:

CREATE TABLE `communications` (
    `RecordID` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
    `ReceivedAt` TIMESTAMP NOT NULL,
    `UserID` INT UNSIGNED NOT NULL,
    `IsInbound` TINYINT(1) NOT NULL,
    `Category` BINARY(4) NOT NULL,
    `Details` TINYTEXT NOT NULL COLLATE 'utf8mb4_unicode_ci',
    `Flag1` TINYINT NOT NULL,
    `Flag2` TINYINT NOT NULL,
-- Additional columns omitted
    PRIMARY KEY (`UserID`, `ReceivedAt`, `RecordID`) USING BTREE,
    INDEX `RecordID` (`RecordID`) USING BTREE,
    INDEX `ReceivedAt_UserID` (`ReceivedAt`, `UserID`) USING BTREE,
    INDEX `Category_UserID_ReceivedAt` (`Category`, `Flag2`, `UserID`, `ReceivedAt`) USING BTREE
)
COLLATE='utf8mb4_unicode_ci'
PARTITION BY RANGE (unix_timestamp(`ReceivedAt`))
(PARTITION old_data VALUES LESS THAN (1717189200) ENGINE = InnoDB,
 PARTITION p202406 VALUES LESS THAN (1719781200) ENGINE = InnoDB,
-- More partitions here
 PARTITION p202512 VALUES LESS THAN (1767214800) ENGINE = InnoDB,
 PARTITION newest VALUES LESS THAN MAXVALUE ENGINE = InnoDB);

Data copying is executed with the following queries:

INSERT INTO `newdb`.`communications` SELECT * FROM `olddb`.`communications` WHERE `RecordID` BETWEEN 1 AND 10000000;
INSERT INTO `newdb`.`communications` SELECT * FROM `olddb`.`communications` WHERE `RecordID` BETWEEN 10000001 AND 20000000;

Typically, this transfer takes about 1 to 2 days overall; however, with the partitioned table, the process is dreadfully slow. For instance, the initial chunk required 29 hours! Disabling innodb_adaptive_hash_index did not resolve the issue either, as it continues to perform poorly. After an initial 2 million rows were inserted in 10 minutes, the speed plummeted significantly.

My server is equipped with 16GB of RAM, an innodb_buffer_pool_size of 128MB, and has 1.5TB of unoccupied disk space. I also have roughly 12,000 distinct UserID entries. Can anyone explain why the partitioned table experiences such a drastic drop in data copying speed?

2

That compound primary key (UserID, ReceivedAt, RecordID) is killing your bulk insert performance. MySQL has to maintain this complex key structure across partitions during INSERT SELECT operations, and it gets exponentially worse with large datasets. I hit the same wall migrating a 200M row table - switched to a simple AUTO_INCREMENT primary key for the migration, then added the compound key after. Cut my transfer time from 36 hours to 6 hours. Try creating your new table with just RecordID as the primary key, run your data transfer, then ALTER to add the compound key structure. Your partition pruning isn’t helping here anyway since you’re selecting by RecordID ranges, not timestamps.

3

your innodb_buffer_pool_size is way too small at 128MB for a 300M record table! with 16GB RAM, bump it to at least 8-10GB. the partitioned table’s hammering your disk because the buffer can’t keep enough data pages in memory. also, if you don’t need replication, try turning off binary logging during the migration - it’ll help speed things up.