Improving remake of primary key performance I'm using a MySQL 5.7 hosted on AWS that has a table with about 500,000,000 rows and 124GB in size.
I want to drop the existing primary key (id) and create a new composite primary key of 2 columns:
ALTER TABLE tableName DROP PRIMARY KEY;
ALTER TABLE tableName ADD PRIMARY KEY(column1, column2);

I have already done this query in a testing environment with the same data, and it works fine.
The problem is that running those 2 queries took over 20 hours, and that is unacceptable in a production scenario.
The way I understand it, InnoDB runs these queries by creating a copy of the table (tmp table) and applying the changes to it - and swapping that table.
I've tried scaling it to a db.m5.24xlarge instance, in the hope that the 384GB memory would allow InnoDB to store the entire tmpTable in memory, but it's not working very well - it seems the like the memory is not being utilized.
I have also set the following configuration values:
tmp_table_size = 18446744073709551615;
max_heap_table_size = 1844674407370954752;
innodb_online_alter_log_max_size = 184467440737095;
You should use pt-online-schema-change.
This will allow for no downtime and you can pause it in the middle of a production period
Provided you have twice as much space free (at least 248GB)
STEP 01 : Select Folder
For this example, let's choose /home/whoeveriam/online_change
mkdir -p /home/whoeveriam/online_change
cd /home/whoeveriam/online_change

STEP 02 : Create local my.cnf
vi mysql_creds.cnf

The contents of mysql_creds.cnf should be
[client]
host     = (RDS Endpoint)
user     = blahblahblah
password = blahblahblahblahblahblah

STEP 03 : Create Dry Run Script
Create a file called Dry_Run.sh with this
BASE_FOLDER=(select absolute path)
MYCNF=$BASE_FOLDER}/mysql_creds.cnf
MYSQL_AUTH=&quot;--defaults-file=${MYCNF}&quot;
cd ${BASE_FOLDER}

DB=mydb (put your DBName here)
TB=mytable (put your tablename here)
DBTB=&quot;${DB}.${TB}&quot;

OPER=Change ; PHASE=Before ; STEP=DryRun ; EXEC_TYPE=--dry-run
RUNLOG=${BASE_FOLDER}/${TB}-${OPER}-${STEP}.log
ERRLOG=${BASE_FOLDER}/${TB}-${OPER}-${STEP}.err
SCTLOG=${BASE_FOLDER}/${TB}-${OPER}-${PHASE}.log
[ -f ${RUNLOG} ] &amp;&amp; echo -e &quot;\nAlready Executed&quot; &amp;&amp; cat ${RUNLOG} &amp;&amp; exit

PAUSE_FILE=${BASE_FOLDER}/pause.txt
PTOSC_OPTS1=&quot;--recursion-method=none&quot;
PTOSC_OPTS1=&quot;${PTOSC_OPTS1} --pause-file=${PAUSE_FILE}&quot;
PTOSC_OPTS2=&quot;${PTOSC_OPTS2} --max-load      Threads_running=100,Threads_connected=500&quot;
PTOSC_OPTS2=&quot;${PTOSC_OPTS2} --critical-load Threads_running=200,Threads_connected=1000&quot;

ALTER_TABLE_STMT=&quot;DROP PRIMARY KEY,ADD PRIMARY KEY(column1, column2)&quot;

T1=`date +%s`
pt-online-schema-change ${PTOSC_AUTH} D=${DB},t=${TB} ${PTOSC_OPTS1} --alter &quot;${ALTER_TABLE_STMT}&quot; ${PTOSC_OPTS2} ${EXEC_TYPE} &gt;${RUNLOG} 2&gt;${ERRLOG}
T2=`date +%s`
mysql ${MYSQL_AUTH} -ANe&quot;SHOW CREATE TABLE ${DBTB}\G&quot; &gt; ${SCTLOG}

SQL=&quot;SET @t1=${T1}; SET @t2=${T2}; SELECT&quot;
SQL=&quot;${SQL} FROM_UNIXTIME(@t1) ${STEP}_Initiated&quot;
SQL=&quot;${SQL},FROM_UNIXTIME(@t2) ${STEP}_Completed&quot;
SQL=&quot;${SQL},SEC_TO_TIME(@t2 - @t1) ${STEP}_RunTime&quot;
mysql ${MYSQL_AUTH} --table -Ae&quot;${SQL}&quot; &gt;&gt; ${RUNLOG}
cat ${RUNLOG}

STEP 04 : Create Live Run Script
cp Dry_Run.sh Live_Run.sh

Change the line that has
OPER=Change ; PHASE=Before ; STEP=DryRun ; EXEC_TYPE=--dry-run

to
OPER=Change ; PHASE=After ; STEP=LiveRun ; EXEC_TYPE=--execute

STEP 05 : Start up Linux Screen Session
screen -S BigChange

STEP 06 : Execute Dry Run
chmod +x Dry_Run.sh
./Dry_Run.sh

STEP 07 : Execute Live Run
chmod +x Live_Run.sh
./Live_Run.sh

FINAL NOTES
If you wish pause the change in the middle of Production Time, run this
cd /home/whoeveriam/online_change
touch pause.txt

It will pause whether it is. Incoming changes will be handled by the triggers
When you want to continue, remove the file
cd /home/whoeveriam/online_change
rm pause.txt

You can monitor progress in the Log and Error Files