MariaDB – Fix Crashing When DELETE/TRUNCATE from Large Table

deletemariadbperformancequery-performance

I try to execute the two following queries in MariaDB :

DELETE FROM `db_test`.`table1`;
DELETE FROM `db_test`.`table2`;

The problem is : deleting from the first table is ok (it takes a few seconds, nothing not usual), but deleting from the second table seems impossible. The query runs for a long time (more than 12 hours), then MariaDB crashes. The only solution here is to reinstall completely MariaDB, because MariaDB service won't launch anymore.

I think that's because table2 is very large, at the opposite of table1 :

  • table1 contains 135k lines, 18 MiB
  • table2 contains 6,2 millions of lines, 1,1 GiB

The same goes with TRUNCATE. The software I use is HeidiSQL.

Do you happen to know how to handle tables like this one ?

Best Answer

You can follow below steps, it may help you.

CREATE TABLE db_test.table2_tmp LIKE db_test.table2;

]RENAME TABLE db_test.table2 TO db_test.table2_to_be_deleted;

RENAME TABLE db_test.table2_tmp TO db_test.table2;

DROP TABLE db_test.table2_to_be_deleted;

Related Question