title: handling/compressing large datasets in multiple tables
body: <p>In an application at our company we collect statistical data from our servers (load, disk usage and so on). Since there is a huge amount of data and we don't need all data at all times we've had a "compression" routine that takes the raw data and calculates min. max and average for a number of data-points, store these new values in the same table and removes the old ones after some weeks. </p>
  
  <p>Now I'm tasked with rewriting this compression routine and the new routine must keep all uncompressed data we have for one year in one table and "compressed" data in another table. My main concerns now are how to handle the data that is continuously written to the database and whether or not to use a "transaction table" (my own term since I cant come up with a better one, I'm not talking about the commit/rollback transaction functionality).</p>
  
  <p>As of now our data collectors insert all information into a table named <code>ovak_result</code> and the compressed data will end up in <code>ovak_resultcompressed</code>. But are there any specific benefits or drawbacks to creating a table called <code>ovak_resultuncompressed</code> and just use <code>ovak_result</code> as a "temporary storage"? <code>ovak_result</code> would be kept minimal which would be good for the compressing routine, but I would need to shuffle all data from one table into another continually, and there would be constant reading, writing and deleting in <code>ovak_result</code>.</p>
  
  <p>Are there any mechanisms in MySQL to handle these kind of things? </p>
  
  <p>(<em>Please note:</em> We are talking about quite large datasets here (about 100 M rows in the uncompressed table and about 1-10 M rows in the compressed table). Also, I can do pretty much what I want with both software and hardware configurations so if you have any hints or ideas involving MySQL configurations or hardware set-up, just bring them on.)</p>