Deleting data from large database

Deleting from a large DB might be a common question although what I want to ask is not how to do this.

1. From what I've read online, the best/efficient way to delete from a large DB is to delete small amounts of data at intervals. In my case, I can't do a TRUNCATE, since data are written in the DB consta开发者_如何学运维ntly and I might end up losing some while I perform the TRUNCATE, right?

2. When deleting data from the DB, in small or large amounts, it creates an overhead which can degrade performance. I can handle this by optimizing the table after the deletes. What I don't know is would optimizing cause any problems on the data been read or written to the DB at the moment?

Maybe I am wrong on my approach on deleting data. If you have any recommendations that would be of great help.

---

Maybe you should take any table you are deleting from and do the following

For this example the table mytable

1. CREATE TABLE newtable LIKE mytable;

2. ALTER TABLE mytable RENAME oldtable;

3. ALTER TABLE newtable RENAME mytable;

4. TRUNCATE TABLE oldtable;

That way the table is available to write new data and you can spend time zapping the old table at the same time.

---

1. MySQL won't lose data. What may happen is a query/update of data where you've just deleted, but a client called for more details, etc. Sometimes on large deletes or truncates, your database will timeout on calls from its clients instead. How clients react to a timeout is up to them. In that case you'd have to inspect how clients handle that error.

2. Any 'optimizations' (what specifically were you thinking of?) should happen during off-peak hours. Measure you database's activity, and find what time of day is best to perform maintenance (indexes, rebuilding statistics) relative to user traffic patterns. Consider a formal scheduled downtime/outage

---

I'm not sure what large amounts of data you need to delete. But if it is "old" data that you need to periodically delete, you may look into using MERGE tables. You can create a MERGE table out of any MyISAM tables that have an identical structure.

For example, you could create a table for each calendar quarter. At the end of each quarter, you create a new, empty table and redeclare the tables that make up the MERGE table. So the MERGE table always has the past years worth of data. If your code always references the MERGE table, you won't need to change anything when "migrating" data. Redeclaring the UNION tables of a MERGE table is nearly instantaneous.

That's just an example, but you can separate your data any way you like.

http://dev.mysql.com/doc/refman/5.0/en/merge-storage-engine.html