Mysql – Setting innodb_buffer_pool_size in for Mysql

The biggest table you have makes up 16.47% (28/170) of the total data. Even if the table was highly written and highly read, not all 28G of the table is loaded in the buffer pool at one given moment. What you need to calculate is how much of the InnoDB Buffer Pool is loaded at any given moment on the current DB Server.

Here is a more granular way to determine innodb_buffer_pool_size for a new DB Server given the dataset currently loaded in the current DB Server's InnoDB Buffer Pool.

Run the following on your current MySQL Instance (server you are migrating from)

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_data';  -- IBPDataPages 
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_total'; -- IBPTotalPages
SHOW GLOBAL STATUS LIKE 'Innodb_page_size'; -- IPS

Run the formula IBPPctFull = IBPDataPages * 100.0 / IBPTotalPages.

SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPTotalPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_total'); -- SELECT @IBPTotalPages;
SET @IBPPctFull = CAST(@IBPDataPages * 100.0 / @IBPTotalPages AS DECIMAL(5,2));
SELECT @IBPPctFull;

If IBPPctFull is 95% or more, you should set innodb_buffer_pool_size to 75% of the DB Server's RAM.

If IBPPctFull is less than 95%, run this formula : IBPSize = IPS X IBPDataPages / (1024*1024*1024) X 1.05.

SET @IBPSize = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_page_size'); -- SELECT @IBPSize;
SET @IBPDataPages = (SELECT VARIABLE_VALUE FROM information_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_pages_data'); -- SELECT @IBPDataPages;
SET @IBPSize = concat(ROUND(@IBPSize * @IBPDataPages / (1024*1024*1024) * 1.05, 2), ' GB' );
SELECT @IBPSize;

The number for IBPSize (in GB) is the number that more closely fits your actual working dataset.

Now, if IBPSize is still too big for the biggest Amazon EC2 RAM Config, use 75% of the RAM for the Amazon EC2 DB Server.

A few suggestions:

1. You might want to increase the size of your log files. They are currently set to 250MB a piece. A larger value there (perhaps a gig or two) should help reduce IO a bit.

2. Turn off the slow query logging

3. Decrease the buffer pool size a bit (or get more memory). You might be starving threads for memory by using everything for the buffer pool.