title: MySQL Performance issues with large amounts of data
body: <p>I have a software project that I am working on at work that has been driving me crazy. Here's our problem: we have a series data contacts that need to be logged every second. It needs to include time, bearing (array of 360-1080 bytes), range, and a few other fields. Our system also needs the capability to store this data for up to 30 days. In practice, there can be up to 100 different contacts, so at a maximum, there can be anywhere from around 150,000,000 points to about 1,000,000,000 different points in 30 days.</p>
  
  <p>I'm trying to think of the best method for storing all of this data and retrieving later on. My first thought was to use some RDBMS like MySQL. Being a embedded C/C++ programmer, I have very little experience working with MySQL with such large data sets. I've dabbled with it on small datasets, but nothing nearly as large. I generated the below schema for two tables that will store some of the data: </p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `HEADER_TABLE` (
    `header_id` tinyint(3) unsigned NOT NULL auto_increment,
    `sensor` varchar(10) NOT NULL,
    `bytes` smallint(5) unsigned NOT NULL,
    PRIMARY KEY  (`header_id`),
    UNIQUE KEY `header_id_UNIQUE` (`header_id`),
    UNIQUE KEY `sensor_UNIQUE` (`sensor`)
  ) ENGINE=MyISAM AUTO_INCREMENT=0 DEFAULT CHARSET=latin1;
  
  CREATE TABLE IF NOT EXISTS `RAW_DATA_TABLE` (
    `internal_id` bigint(20) NOT NULL auto_increment,
    `time_sec` bigint(20) unsigned NOT NULL,
    `time_nsec` bigint(20) unsigned NOT NULL,
    `transverse` bit(1) NOT NULL default b'0',
    `data` varbinary(1080) NOT NULL,
    PRIMARY KEY  (`internal_id`,`time_sec`,`time_nsec`),
    UNIQUE KEY `internal_id_UNIQUE` (`internal_id`),
    KEY `time` (`time_sec`)
    KEY `internal_id` (`internal_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
  
  CREATE TABLE IF NOT EXISTS `rel_RASTER_TABLE` (
    `internal_id` bigint(20) NOT NULL auto_increment,
    `raster_id` int(10) unsigned NOT NULL,
    `time_sec` bigint(20) unsigned NOT NULL,
    `time_nsec` bigint(20) unsigned NOT NULL,
    `header_id` tinyint(3) unsigned NOT NULL,
    `data_id` bigint(20) unsigned NOT NULL,
    PRIMARY KEY  (`internal_id`, `raster_id`,`time_sec`,`time_nsec`),
    KEY `raster_id` (`raster_id`),
    KEY `time` (`time_sec`),
    KEY `data` (`data_id`)
  ) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1;
  </code></pre>
  
  <p>The header table only contains 10 rows and is static. It just tells what sensor the raw data came from, and the number of bytes output by that type of sensor. The RAW_DATA_TABLE essentially stores the raw bearing data (an array of 360-1080 bytes, it represents up to three samples per degree). The rel_RASTER_TABLE holds meta data for the RAW_DATA_TABLE, there can be multiple contacts that refer to the same raw data row. The <code>data_id</code> found in rel_RASTER_TABLE points to the <code>internal_id</code> of some row in the RAW_DATA_TABLE, I did this to decrease the amount of writes needed.</p>
  
  <p>Obviously, as you can probably tell, I'm having performance issues when reading and deleting from this database. An operator to our software can see real time data as it comes across and also go into reconstruction mode and overlay a data range from the past, the past week for example. Our backend logging server grabs the history rows and sends them to a display via a CORBA interface. While all of this is happening, I have a worker thread that deletes 1000 rows at a time for data greater than 30 days. This is there in case a session runs longer than 30 days, which can happen.</p>
  
  <p>The system we currently have implemented works well for smaller sets of data, but not for large sets. Our select and delete statements can take upwards of 2 minutes to return results. This completely kills the performance of our real time consumer thread. I suspect we're not designing our schemas correctly, picking the wrong keys, not optimizing our SQL queries correctly, or some subset of each. Our writes don't see to be affected unless the other operations take too long to run.</p>
  
  <p>Here is an example SQL Query we use to get history data:</p>
  
  <pre><code>SELECT 
    rel_RASTER_TABLE.time_sec, 
    rel_RASTER_TABLE.time_nsec, 
    RAW_DATA_TABLE.transverse, 
    HEADER_TABLE.bytes, 
    RAW_DATA_TABLE.data 
  FROM 
    RASTER_DB.HEADER_TABLE, 
    RASTER_DB.RAW_DATA_TABLE, 
    RASTER_DB.rel_RASTER_TABLE 
  WHERE 
    rel_RASTER_TABLE.raster_id = 2952704 AND 
    rel_RASTER_TABLE.time_sec &gt;= 1315849228 AND 
    rel_RASTER_TABLE.time_sec &lt;= 1315935628 AND 
    rel_RASTER_TABLE.data_id = RAW_DATA_TABLE.internal_id AND 
    rel_RASTER_TABLE.header_id = HEADER_TABLE.header_id;
  </code></pre>
  
  <p>I apologize in advance for this being such a long question, but I've tapped out other resources and this is my last resort. I figure I'd try to be as descriptive as possible Do you guys see of any way I can improve upon our design at first glance? Or, anyway we can optimize our select and delete statements for such large data sets? We're currently running RHEL as the OS and unfortunately can't change our hardware configuration on the server (4 GB RAM, Quad Core). We're using C/C++ and the MySQL API. ANY speed improvements would be EXTREMELY beneficial. If you need me to clarify anything, please let me know. Thanks!</p>
  
  <p>EDIT: BTW, if you can't provide specific help, maybe you can link me to some excellent tutorials you've come across for optimizing SQL queries, schema design, or MySQL tuning?</p>