Can I kill a process in the "query end" state in Aurora MySql

Question

I have a large table hosted on Aurora at Amazon using MySql 5.7

2 days ago, I ran this command:

     insert IGNORE into archiveDataNEW
         (`DateTime-UNIX`,`pkl_PPLT_00-PIndex`,`DataValue`)
         SELECT `DateTime-UNIX`,`pkl_PPLT_00-PIndex`,`DataValue`
             FROM offlineData 
             order by id 
             limit  600000000,  200000000

Yesterday afternoon, my computer crashed so the connection to mysql was severed.

sometime last night the status of the query was "query end"

today the status of the query is still "query end"

Questions: Can I stop this process - or with that only make things worse?

Does MySQL innodb unwind a query when the connection to the server drops? Is there any way to tell it to proceed instead?

will I need to re-run the command when it finally completes the query end process ?

Here is the table I am loading data into, any thoughts or suggestions will be appreciated.

CREATE TABLE `archiveDataNEW` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `DateTime-UNIX` bigint(20) NOT NULL DEFAULT '0',
  `pkl_PPLT_00-PIndex` int(11) NOT NULL DEFAULT '0',
  `DataValue` decimal(14,4) NOT NULL DEFAULT '0.0000',
  PRIMARY KEY (`id`,`DateTime-UNIX`),
  UNIQUE KEY `Unique2` (`pkl_PPLT_00-PIndex`,`DateTime-UNIX`) USING BTREE,
  KEY `DateTime` (`DateTime-UNIX`) USING BTREE,
  KEY `pIndex` (`pkl_PPLT_00-PIndex`) USING BTREE,
  KEY `DataIndex` (`DataValue`),
  KEY `pIndex-Data` (`pkl_PPLT_00-PIndex`,`DataValue`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=736142506 DEFAULT CHARSET=utf8
/*!50100 PARTITION BY RANGE (`DateTime-UNIX`)
(PARTITION p2016 VALUES LESS THAN (1483246800) ENGINE = InnoDB,
 PARTITION p2017 VALUES LESS THAN (1514782800) ENGINE = InnoDB,
 PARTITION p2018 VALUES LESS THAN (1546318800) ENGINE = InnoDB,
 PARTITION p2019 VALUES LESS THAN (1577854800) ENGINE = InnoDB,
 PARTITION p2020 VALUES LESS THAN (1609477200) ENGINE = InnoDB,
 PARTITION p2021 VALUES LESS THAN (1641013200) ENGINE = InnoDB,
 PARTITION p2022 VALUES LESS THAN (1672549200) ENGINE = InnoDB,
 PARTITION p2023 VALUES LESS THAN (1704085200) ENGINE = InnoDB,
 PARTITION pMAX VALUES LESS THAN MAXVALUE ENGINE = InnoDB) */;```

Answer 1

There's no way to complete that statement and commit the row it inserted.

This is apparently a bug in MySQL 5.7 code, discussed here: https://bugs.mysql.com/bug.php?id=91078

The symptom is that a query is stuck in "query end" state and there's no way to kill it or finish it except by restarting the MySQL Server. But this is not possible on AWS Aurora, right?

There's some back and forth in that bug log about whether it's caused by the query cache. The query cache is deprecated, but in Aurora they have reenabled it and changed its implementation. They are convinced that their query cache code solves the disadvantages of MySQL's query cache implementation, so they leave it on in Aurora (this is one of many reasons you should think of Aurora as a fork of MySQL, not necessarily compatible with MySQL itself).

Answer 2

Kill it, if you can. It is either busy committing (which will take a long time) or busy undoing (which will take even longer). If it won't kill, you are stuck with waiting it out.

A better way.

Using OFFSET in limit 600000000, 200000000 will only get slower and slower as you do the chunks. This is because it must step over the 600M rows.

Also, INSERTing 200M rows at a time is quite inefficient. The system must prepare to UNDO the action in case of crash.

So, it is better to "remember where you left off". Or do it in explicit chunks like WHERE id BETWEEN 12345000 AND 12345999. Also, do only 1K rows at a time.

But, what are your trying to do?

If you are adding Partitioning, let's discuss whether there will be any benefit. It looks like you are adding yearly partitioning. Possibly the only advantage is when you need to DROP PARTITION to get rid of "old" data. It is unlikely that any queries will run any faster.

Likely optimizations:

Shrink:

`DateTime-UNIX` bigint(20)

This seems to be a unix timestamp, that fits nicely in a 4-byte INT or a 5-byte TIMESTAMP; why use an 8-byte BIGINT? TIMESTAMP has the advantage of allowing lots of datetime functions. A 5-byte DATETIME or a 3-byte DATE will last until the end of the year 9999. We are 17 years from overflow of TIMESTAMP; what computer systems do you know of that have been around since 2004 (today - 17 years)? Caveat: There will be timezone issues to address (or ignore) if you switch from TIMESTAMP. (If you need the time part, do not split a DATETIME into two columns; it is likely to add complexity.)

Drop KEY pIndex (pkl_PPLT_00-PIndex) USING BTREE, it is redundant with two other indexes.

Do not pre-build future partitions; it hurts performance (a small amount). At the end of the current year, build the next year's partition with REORGANIZE. Details here: http://mysql.rjweb.org/doc.php/partitionmaint

This will improve performance in multiple ways:

PRIMARY KEY (`id`,`DateTime-UNIX`),
UNIQUE KEY `Unique2` (`pkl_PPLT_00-PIndex`,`DateTime-UNIX`) USING BTREE,

-->

PRIMARY KEY(`pkl_PPLT_00-PIndex`,`DateTime-UNIX`),
INDEX(id)   -- sufficient for AUTO_INCREMENT

It may run faster if you leave off the non-UNIQUE indexes until the table is loaded. Then do ALTER(s) to add them.