Removing from the large DB may well be a common query although what I wish to request isn't how to get this done.

  1. From what I have read online, the very bestOreffective method to remove from the large DB would be to remove small quantities of data at times. During my situation, I can not perform a TRUNCATE, since data are designed in the DB constantly and that i might finish up losing some as i carry out the TRUNCATE, right?

  2. When removing data in the DB, in big or small amounts, it produces an overhead which could degrade performance. I'm able to handle this by optimizing the table following the removes. What I'm not sure is would optimizing cause any problems around the data been read or written towards the DB right now?

Maybe I'm wrong on my small approach on removing data. For those who have any recommendations that might be a big help.

  1. MySQL will not lose data. What can happen is really a query/update of information where you've just erased, but a customer known as for additional particulars, etc. Sometimes on large removes or truncates, your database will timeout on calls from the clients rather. How clients respond to a timeout can be them. For the reason that situation you'd need to inspect how clients handle that error.

  2. Any 'optimizations' (what particularly had you been considering?) should happen throughout off-peak hrs. Measure you database's activity, and discover what time is better to do maintenance (indexes, repairing statistics) in accordance with user traffic designs. Think about a formal scheduled down time/outage

Perhaps you should take any table you're removing from and perform the following

Let's imagine the table mytable

  1. CREATE TABLE newtable LIKE mytable

  2. ALTER TABLE mytable Relabel oldtable

  3. ALTER TABLE newtable Relabel mytable

  4. TRUNCATE TABLE oldtable

This way the table can be obtained to create new data and you will spend some time zapping that old table simultaneously.

I am unsure what considerable amounts of information you have to remove. But when it's "old" data you need to periodically remove, you might consider using MERGE tables. You may create a MERGE table from any MyISAM tables with an identical structure.

For instance, you can produce a table for every calendar quarter. In the finish of every quarter, you produce a new, empty table and redeclare the tables that comprise the MERGE table. Therefore the MERGE table has yesteryear years price of data. In case your code always references the MERGE table, you will not have to change anything when "moving" data. Redeclaring the UNION tables of the MERGE table is almost immediate.

That's just a good example, however, you can separate your computer data in fashion.

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