Lazy deletion of table rows

Question

is there any software that does "lazy" deletion of the rows from the table. I would like to do maintenance of my tables when my server is idle, and ideally i should be able to define what "idle" is (num of database connections/system load/ requests per second). Is there anything remotely similar to this?

Answer 1

If you are on a linux server, you can make your table cleanup scripts only run based on the output of the command "w" which will show you a system load. If your system load is under say .25 you can run your script. Do this with shell scripting.

Answer 2

To some degree, from an internal perspective InnoDB already does this. Rows are initially marked as deleted, but only made free as part of a background operation.

My advice: You can get in to needlessly complicated problems if you try and first check if the server is idle. i.e.

• What if it was idle, but the cleanup takes 2 minutes. During that 2 minutes the server load peaks?

• What if the server never becomes idle enough? Now you just have an unlimited backlog.

If you just background the task you might improve performance enough, since now at least no users will be sitting in front of web pages waiting for it to complete. Look at activity graphs as to what is the best time to schedule it (3am, 5am etc).