title: How to handle large amounts of data in MySQL database?
body: <h2>Background</h2>
  
  <p>I have spent couple of days trying to figure out how I should handle large amounts of data in MySQL. I have selected some programs and techniques for the new server for the software. I am probably going to use Ubuntu 14.04LTS running nginx, <strong>Percona Server</strong> and will be using <strong>TokuDB</strong> for the 3 tables I have planned and InnoDB for the rest of the tables.</p>
  
  <p>But yet I have the major problem unresolved. How to handle the huge amount of data in database?</p>
  
  <h2>Data</h2>
  
  <p>My estimates for the possible data to receive is <strong>500 million rows a year</strong>. I will be receiving measurement data from sensors every <strong>4 minutes</strong>.</p>
  
  <h2>Requirements</h2>
  
  <p>Insertion speed is not very critical, but I want to be able to select few hundred measurements in 1-2 seconds. Also the amount of required resources is a key factor.</p>
  
  <h2>Current plan</h2>
  
  <p>Now I have thought of splitting the sensor data in 3 tables.</p>
  
  <p><strong>EDIT</strong>:
  On every table:</p>
  
  <p>id = PK, AI</p>
  
  <p>sensor_id will be indexed</p>
  
  <pre><code>CREATE TABLE measurements_minute(
    id bigint(20),
    value float,
    sensor_id mediumint(8),
    created timestamp
  ) ENGINE=TokuDB;
  
  CREATE TABLE measurements_hour(
    id bigint(20),
    value float,
    sensor_id mediumint(8),
    created timestamp
  ) ENGINE=TokuDB;
  
  CREATE TABLE measurements_day(
    id bigint(20),
    value float,
    sensor_id mediumint(8),
    created timestamp
  ) ENGINE=TokuDB;
  </code></pre>
  
  <p>So I would be storing this <em>4 minute</em> data for one month. After the data is 1 month old it would be deleted from minute table. Then average value would be calculated from the minute values and inserted into the measurements_hour table. Then again when the data is 1 year old all the <em>hour</em> data would be deleted and daily averages would be stored in measurements_day table.</p>
  
  <h2>Questions</h2>
  
  <p>Is this considered a good way of doing this? Is there something else to take in consideration? How about table partitioning, should I do that? How should I execute the splitting of the date into different tables? Triggers and procedures?</p>
  
  <h2>EDIT: My ideas</h2>
  
  <p>Any idea if <strong>MonetDB</strong> or <strong>Infobright</strong> would be any good for this?</p>