New values are added to a MySQL Database all day long. There are over 8 million rows in this table. Because there are a lot of old values, I want to clean these up without stopping the Java Program Inserting the values.
新值全天添加到MySQL数据库中。此表中有超过800万行。因为有很多旧值,我想在不停止Java程序插入值的情况下清理它们。
I am sending this query to the MySQL:
我将此查询发送到MySQL:
DELETE FROM `tablename`
WHERE `from` <= (date_add(now(), interval -20 DAY))
but what happens is that the Java Program stops adding new values to the table. It there any way I can handle this without editing the Java Program? I would be willing to run the query as a cronjob once a day.
但是,Java程序会停止向表中添加新值。有没有什么办法可以在不编辑Java程序的情况下处理这个问题?我愿意每天一次将查询作为cronjob运行。
I have also tried working with the parameter LOW_PRIORITY, but it doesn't make any difference.
我也尝试使用参数LOW_PRIORITY,但它没有任何区别。
2 个解决方案
#1
2
It seems that you are using old MyISAM db engine.
您似乎正在使用旧的MyISAM数据库引擎。
Unfortunately MyISAM uses table-level locking. When a row is inserted, updated or deleted, all other changes to that table are held up until that request has been completed. In your case long running DELETE blocks any INSERTs into that table.
不幸的是,MyISAM使用表级锁定。插入,更新或删除行时,将保留对该表的所有其他更改,直到该请求完成。在您的情况下,长时间运行DELETE会阻止任何INSERT进入该表。
You can improve it by changing your table engine to InnoDB like this:
您可以通过将表引擎更改为InnoDB来改进它,如下所示:
ALTER TABLE mytable ENGINE = innodb;
InnoDB engine is fully transactional and does not lock whole table during INSERT, UPDATE or DELETE, so your problem should go away.
InnoDB引擎是完全事务性的,并且在INSERT,UPDATE或DELETE期间不会锁定整个表,因此您的问题应该消失。
#2
0
I'd imagine you need to change your storage engine. Do this using ALTER TABLE tbl ENGINE = innodb, but do be aware of the differences between MyISAM and Innodb.
我想你需要改变你的存储引擎。使用ALTER TABLE tbl ENGINE = innodb执行此操作,但请注意MyISAM和Innodb之间的差异。
#1
2
It seems that you are using old MyISAM db engine.
您似乎正在使用旧的MyISAM数据库引擎。
Unfortunately MyISAM uses table-level locking. When a row is inserted, updated or deleted, all other changes to that table are held up until that request has been completed. In your case long running DELETE blocks any INSERTs into that table.
不幸的是,MyISAM使用表级锁定。插入,更新或删除行时,将保留对该表的所有其他更改,直到该请求完成。在您的情况下,长时间运行DELETE会阻止任何INSERT进入该表。
You can improve it by changing your table engine to InnoDB like this:
您可以通过将表引擎更改为InnoDB来改进它,如下所示:
ALTER TABLE mytable ENGINE = innodb;
InnoDB engine is fully transactional and does not lock whole table during INSERT, UPDATE or DELETE, so your problem should go away.
InnoDB引擎是完全事务性的,并且在INSERT,UPDATE或DELETE期间不会锁定整个表,因此您的问题应该消失。
#2
0
I'd imagine you need to change your storage engine. Do this using ALTER TABLE tbl ENGINE = innodb, but do be aware of the differences between MyISAM and Innodb.
我想你需要改变你的存储引擎。使用ALTER TABLE tbl ENGINE = innodb执行此操作,但请注意MyISAM和Innodb之间的差异。