title: "innodb_buffer_pool_size" on MySQL with DB bigger than RAM
body: <p>I had one Linux Server with MySQL working, with:</p>
  
  <pre><code>-12 Gb RAM 
  -4 x Intel(R) Xeon(R) CPU E6510  @ 1.73GHz
  -CentOS release 6.3
  -MySQL 5.1.61
  </code></pre>
  
  <p>Because of some technical problems, we have had to reduce the RAM memory of the server to 8 GB and for now, we are not able to have any memory. And now, because of this we are having a lot of performance problems on our server. This is the size of our DB:</p>
  
  <pre><code>+--------+--------------------+---------+--------+--------+------------+---------+
  | tables | table_schema       | rows    | data   | idx    | total_size | idxfrac |
  +--------+--------------------+---------+--------+--------+------------+---------+
  |     43 | XXXXXXXX           | 142.81M | 10.52G | 13.31G | 23.83G     |    1.27 |
  |     44 | Test_XXXXXXXX      | 55.20M  | 3.57G  | 4.77G  | 8.33G      |    1.34 |
  |     34 | XXXXXXXXXXXXXXXX   | 23.04M  | 1.39G  | 1.84G  | 3.24G      |    1.32 |
  |     23 | mysql              | 0.00M   | 0.00G  | 0.00G  | 0.00G      |    0.16 |
  |     28 | information_schema | NULL    | 0.00G  | 0.00G  | 0.00G      |    NULL |
  +--------+--------------------+---------+--------+--------+------------+---------+
  </code></pre>
  
  <p>This is the content of /etc/my.cnf:</p>
  
  <pre><code>[mysqld]
  
  max_allowed_packet = 1024M
  sort_buffer_size = 512M
  max_connections=500
  
  query_cache_size = 512M
  query_cache_limit = 512M
  query-cache-type = 2
  
  table_cache = 800
  
  thread_cache_size=8
  key_buffer_size = 512M
  read_buffer_size=64M
  read_rnd_buffer_size=64M
  myisam_sort_buffer_size=64M
  
  innodb_flush_log_at_trx_commit=2
  innodb_buffer_pool_size=7000M
  innodb_additional_mem_pool_size=100M
  
  ...
  </code></pre>
  
  <p>I don't know if I'm able to really identify the relation between the size and the RAM. But the situation is that when I had 12GB RAM everything was working fine. The "innodb_buffer_pool_size" value was 10000M and the performance was really good. But now, takes making the same operation like 4 times more. </p>
  
  <p>Our app is basically one DB exporter and acceses mainly to only one table, and it has 72,314,541 registers.</p>
  
  <pre><code>+--------+---------+--------+--------+------------+---------+
  | tables | rows    | data   | idx    | total_size | idxfrac |
  +--------+---------+--------+--------+------------+---------+
  |      9 | 159.12M | 11.07G | 15.87G | 26.94G     |    1.43 |
  +--------+---------+--------+--------+------------+---------+
  </code></pre>
  
  <p>For now we are making tests changing the values of the "innodb_buffer_pool_size", but looks like we are not going to get more performnace. Now, the question is, what can we do to have more performance on our MySQL?</p>
  
  <pre><code>-Put more RAM (obviously)
  -Change more variables on /etc/my.cnf?
  -MySQL Partitioning for Performance
  -...
  </code></pre>
  
  <p>All the ideas and contributions will be welcome,</p>
  
  <p>Thanks in advance</p>
  
  <p><strong>Edit: Added all the info of the table and the query</strong></p>
  
  <p>The structure of the DB is a system to receive information from some sensors and store it.</p>
  
  <p>Measurement table: The measurements that we receive from the sensors.</p>
  
  <pre><code>+--------------------+------------+------+-----+---------+----------------+
  | Field              | Type       | Null | Key | Default | Extra          |
  +--------------------+------------+------+-----+---------+----------------+
  | id                 | bigint(20) | NO   | PRI | NULL    | auto_increment |
  | version            | bigint(20) | NO   |     | NULL    |                |
  | counter            | char(2)    | YES  |     | NULL    |                |
  | datemeasurement_id | datetime   | NO   | MUL | NULL    |                |
  | datereal_id        | datetime   | NO   | MUL | NULL    |                |
  | delayed            | bit(1)     | NO   |     | NULL    |                |
  | frequency          | tinyint(4) | YES  |     | NULL    |                |
  | measuringentity_id | bigint(20) | NO   | MUL | NULL    |                |
  | real               | bit(1)     | NO   |     | NULL    |                |
  | tamper             | bit(1)     | NO   |     | NULL    |                |
  | value              | float      | NO   |     | NULL    |                |
  +--------------------+------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>measuring_entity table: One sensor can measure more than one thing (Temperature, Humidity). And these are the entitys.</p>
  
  <pre><code>+--------------+------------+------+-----+---------+----------------+
  | Field        | Type       | Null | Key | Default | Extra          |
  +--------------+------------+------+-----+---------+----------------+
  | id           | bigint(20) | NO   | PRI | NULL    | auto_increment |
  | version      | bigint(20) | NO   |     | NULL    |                |
  | household_id | varchar(4) | NO   | MUL | NULL    |                |
  | operative    | bit(1)     | NO   |     | NULL    |                |
  | type         | char(20)   | NO   |     | NULL    |                |
  | unit         | char(3)    | NO   |     | NULL    |                |
  | interval     | float      | YES  |     | NULL    |                |
  +--------------+------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>sensor_measuring_entity: One sensor can have more than one entity associated.</p>
  
  <pre><code>+--------------------+------------+------+-----+---------+-------+
  | Field              | Type       | Null | Key | Default | Extra |
  +--------------------+------------+------+-----+---------+-------+
  | sensor_id          | bigint(20) | NO   | PRI | NULL    |       |
  | measuringentity_id | bigint(20) | NO   | PRI | NULL    |       |
  | version            | bigint(20) | NO   |     | NULL    |       |
  +--------------------+------------+------+-----+---------+-------+
  </code></pre>
  
  <p>Sensor table: The info of the sensor, related with the measuring entity in the previous table.</p>
  
  <pre><code>+---------------------+-------------+------+-----+---------+----------------+
  | Field               | Type        | Null | Key | Default | Extra          |
  +---------------------+-------------+------+-----+---------+----------------+
  | id                  | bigint(20)  | NO   | PRI | NULL    | auto_increment |
  | version             | bigint(20)  | NO   |     | NULL    |                |
  | battery             | bit(1)      | NO   |     | NULL    |                |
  | identifier          | char(6)     | NO   |     | NULL    |                |
  | installationdate_id | datetime    | NO   | MUL | NULL    |                |
  | lastreceiveddate_id | datetime    | YES  | MUL | NULL    |                |
  | location_id         | bigint(20)  | NO   | MUL | NULL    |                |
  | operative           | bit(1)      | NO   |     | NULL    |                |
  | tampererror         | smallint(6) | NO   |     | NULL    |                |
  +---------------------+-------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>Location table: Where is placed the sensor.</p>
  
  <pre><code>+------------+------------+------+-----+---------+----------------+
  | Field      | Type       | Null | Key | Default | Extra          |
  +------------+------------+------+-----+---------+----------------+
  | id         | bigint(20) | NO   | PRI | NULL    | auto_increment |
  | version    | bigint(20) | NO   |     | NULL    |                |
  | height     | tinyint(4) | YES  |     | NULL    |                |
  | operative  | bit(1)     | NO   |     | NULL    |                |
  | place      | char(15)   | NO   | MUL | NULL    |                |
  | room       | char(15)   | NO   |     | NULL    |                |
  | typesensor | char(15)   | NO   |     | NULL    |                |
  | formaster  | bit(1)     | YES  |     | NULL    |                |
  +------------+------------+------+-----+---------+----------------+
  </code></pre>
  
  <p>This is the query (for all houses, and all sensors):</p>
  
  <pre><code>for (int z = 0; z &lt; allHouses.length; z++) {
  for (int j = 0; j &lt; sensorlist.length; j++) {
  
  sql.eachRow ("SELECT m.datemeasurement_id, s.identifier, me.type, m.value"
  + " FROM measurement as m"
  + " JOIN measuring_entity as me ON m.measuringentity_id = me.id"
  + " JOIN sensor_measuring_entity as sme ON sme.measuringentity_id = me.id"
  + " JOIN sensor as s ON sme.sensor_id = s.id"
  + " WHERE me.id = $actualmeid"
  + " AND me.household_id = '$mHouse'"
  + " AND m.datemeasurement_id &gt;= '$cons_startDate'"
  + " AND m.datemeasurement_id &lt;= '$cons_endDate'"
  + " AND m.datemeasurement_id &gt; '$startDate'"
  + " AND m.datemeasurement_id &lt; '$endDate'"
  + " ORDER BY datemeasurement_id")
  {
  }}
  </code></pre>
  
  <p>PD: everything is part of one Grails app.</p>