MySQL: Creating a Multiple-Column Index on a 4.4 TB Partitioned Table

It's unlikely you've come across such large data volumes in MySQL. However, for some, this might be of interest.

This note covers testing on a standalone MySQL 8.0.34 server, where there was no load except for the index creation process.

In the near future, the same process will be carried out in a production environment on a MySQL 8.0.34 InnoDB cluster consisting of 3 nodes. There may be another follow-up note if this proves to be of interest.

Spoiler! The process of creating a composite index on two fields of a 4.4 TB partitioned table with a parallelism level of 8 took 9 hours, 46 minutes, and 41 seconds. The final index size was 139 GB. With a parallelism level of 12 took 7 hours, 43 minutes, and 56 seconds.

Disk Space:

During the operation, up to 37 GB of additional disk space was required to store temporary files.

RAM:

An additional 15 GB of RAM was utilized during the index creation process, and this memory was not released after the indexing was completed.

CPU:

The virtual machine used 16 vCPUs. The average CPU usage was 3.9 GHz, with peak usage reaching 11.5 GHz. CPU utilization varied, peaking at 35%.

I/O:

The average I/O throughput was 101,032 KBps, with a peak of 339,704 KBps, using a 16 KB block size. The average IOPS was 6.3, with a peak of 21.3 IOPS. For our disk system, the load was minimal, with latency consistently under 1ms.

The following parameters were applied for index creation:

SET session innodb_parallel_read_threads = 8;
SET session innodb_ddl_threads = 8;
CREATE INDEX index_name ON db.table (column1, column2) ALGORITHM INPLACE LOCK NONE;        

Database Memory and Buffer Settings:

innodb_buffer_pool_instances = 32;
innodb_buffer_pool_size = 20,312M;
innodb_log_file_size = 2,048M;