Deleting Row Store Table Data When at the Memory Limit

If you are running near the memory limit, you may get ERROR 1712 indicating that you have exceeded the maximum_memory setting. Running delete from tableName; in this situation may also cause an ERROR 1712 for a large table because deleting data takes memory for each row you delete, while the transaction containing the delete operation is running. To work around this, you can repeatedly run a command that deletes a batch of rows. For example, you could delete the oldest 10000 rows, then next oldest 10000 rows, and so on, either by hand or in a loop using a stored procedure, until you have reduced memory usage sufficiently. DELETE with a LIMIT clause may be useful for the purpose of batching deletes. If you have no further need for the data, or you have another way to recover it, you can either truncate the table or drop the table, since truncate and drop operations do not take any extra memory per row.

Last modified: April 26, 2021

Was this article helpful?