Optimize MySQL innodb_buffer_pool_size

Optimize MySQL innodb_buffer_pool_size

InnoDB buffer pool is the size in bytes of the buffer pool, the memory area where InnoDB caches table and index data. The larger you set this value, the less disk I/O is needed to access the same data in tables more than once. On a dedicated database server, you might set this to up to 80% of the machine physical memory size. MyISAM engine table has no effect for this variable. If your all tables are MyISAM you should set it very low.
In generally innodb_buffer_pool_size should be as much as sum of InnoDB data and indexes’s size. InnoDB reserves additional memory for buffers and control structures, so that the total allocated space is approximately 10% greater than the specified size ( http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size). That means if you have 5GB InnoDB table data and 3 GB InnoDB index data, the innodb_buffer_pool_size should be 8GB but MySQL internally reserve 8*1.10=8.8GB.
In my opinion, you first need to decide how much memory you want to allocate for MySQL. I also think at least 10% memory always should be reserved for OS itself. Here are some guidelines to take the decision:

1. Dedicate Database Server
In a dedicated database server you may allocate 90% of total physical memory for MySQL. So it would be better first tune and sum up all other important MySQL system variables like query_cache_size, key_buffer_size, table_open_cache,tmp_table_size etc. The allocation for innodb_buffer_pool_size would be as follows:
a) Find out the server physical memory (PM)

# free -m | awk '/Mem/{print 2}'
28028

b) Find out configured maximum memory (CMM)

CMM= global_buffers + per_thred_buffer*max_connections

Global_buffers=(innodb_buffer_pool_size+innodb_additional_mem_pool_size+innodb_log_buffer_size+key_buffer_size+query_cache_size)

mysql> select sum(if(VARIABLE_NAME='innodb_buffer_pool_size',VARIABLE_VALUE,0) + if(VARIABLE_NAME='innodb_additional_mem_pool_size',VARIABLE_VALUE,0) + if(VARIABLE_NAME='innodb_additional_mem_pool_size',VARIABLE_VALUE,0) +if(VARIABLE_NAME='innodb_log_buffer_size',VARIABLE_VALUE,0) +if(VARIABLE_NAME='query_cache_size',VARIABLE_VALUE,0)) / (1024*1024) 'Global_buffers(MB)' from information_schema.global_variables ;
+--------------------+
| Global_buffers(MB) |
+--------------------+
| 2352 |
+--------------------+
1 row in set (0.00 sec)

per_thred_buffer = (read_buffer_size + read_rnd_buffer_size+sort_buffer_size+thread_stack + join_buffer_size+binlog_cache_size)

mysql> select sum(if(VARIABLE_NAME='read_buffer_size ',VARIABLE_VALUE,0) + if(VARIABLE_NAME='read_rnd_buffer_size',VARIABLE_VALUE,0) + if(VARIABLE_NAME='sort_buffer_size',VARIABLE_VALUE,0) + if(VARIABLE_NAME='thread_stack',VARIABLE_VALUE,0) + if(VARIABLE_NAME='join_buffer_size',VARIABLE_VALUE,0) + if(VARIABLE_NAME='binlog_cache_size',VARIABLE_VALUE,0)) / (1024*1024) 'per_thred_buffers(MB)' from information_schema.global_variables;
+-----------------------+
| per_thred_buffers(MB) |
+-----------------------+
| 1.15625 |
+-----------------------+
1 row in set (0.00 sec)

mysql> show global variables like 'max_connections';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| max_connections | 500 |
+-----------------+-------+
1 row in set (0.00 sec)

CMM= global_buffers + per_thred_buffer*max_connections
CMM = 2352 + (1.15625 * 500)
= 2930 MB

c) Find out sum of InnoDB Data and Index size (IDIS)

mysql> SELECT SUM(data_length+index_length)/(1024*1024) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB';
+--------------------+
| Total_InnoDB_Bytes |
+--------------------+
| 64280.0313 |
+--------------------+
1 row in set (0.07 sec)

In my server I have huge records set and size is more than 64GB but most of the Web application may require 5 to 6 GB space.

d) Find out configured innodb_buffer_pool_size

mysql> select variable_value/(1024*1024) innodb_buffer_pool_size from information_schema.global_variables where Variable_name='innodb_buffer_pool_size(MB)';
+-------------------------+
| innodb_buffer_pool_size(MB) |
+-------------------------+
| 2048 |
+-------------------------+
1 row in set (0.00 sec)

e) Configure possible value for innodb_buffer_pool_size
As total InnoDB data size is 64280.0313 We need to allocate 64280.0313MB for innodb_buffer_pool_size but MySQL actually allocate 64280.0313*1.10 for setting this value which exceeds 90% of Physical memory. So, we need to configure maximum possible value.

Other MySQL system variables allocation (OMSVA) = CMM - configured innodb_buffer_pool_size
= 2930 – 2048
= 882 MB

Allowable innodb_buffer_pool_size = (Physical Memory) – (10% of Physical Memory) – OMSVA
28028 – 28028*0.10 – 882
= 27146 MB

* 10% is reserved for OS

But as discussed earlier setting innodb_buffer_pool_size = 27146 MB actually enable MySQL to allocate 27146*1.10= 29860.6 MB which exceeds 90% of physical memory. So we should allocate innodb_buffer_pool_size as follows

innodb_buffer_pool_size = 27146/1.10 = 24678.18 MB = 24678MB

So the general equation,

innodb_buffer_pool_size = (PM – PM*0.10 – OMSVA)/1.10

2. Database server and Web server

If you have single physical server for both Web server and Database server, then you need to first decide how much memory you would like to allocate for Web server. Then you may allocate memory for MySQL server, The rest of the procedure will be same as earlier just deduct Webserver memory allocation. The equation then becomes considering Web Server allocation memory(WSAM) is 1024 MB

innodb_buffer_pool_size = (PM – PM*0.10 - WSAM – OMSVA)/1.10

Now consider your InnoDB data and index size is 5GB and other conditions are same as stated above. So the scenario becomes

Physical Memory (PM) = 28028 MB
InnoDB Data and Index size (IDIS) = 5GB= 5120MB
Configured Maximum Memory(CMM)= 2930 MB
configured innodb_buffer_pool_size= 2048
Other MySQL system variables allocation (OMSVA) = 882 MB
Recommended allocation for innodb_buffer_pool_size = 27146 MB

As you have only 5GB InnoDB data , don’t need to allocate full 27146 MB. Unnecessary memory allocation is not required. Rather than you can allocate 1.6 times of 5GB. SO InnoDB memory allocation becomes

innodb_buffer_pool_size = 5129 * 1.6 = 8192 MB.

When your InnoDB data exceed 8192 MB you can reallocate 8192*1.16 and so on until it reaches 27146 MB.

Configure New InnoDB buffer pool size
Innodb_buffer_pool is not a dynamic variable. You have to restart the database to reallocate its value

1) Stop the database
# /etc/init.d/mysqld stop

2) vi /etc/my.cnf
innodb_buffer_pool_size = 24.678M

3) Start the database
# /etc/init.d/mysqld start

Optimize MySQL innodb_buffer_pool_size is a tricky thing. The larger you set innodb_buufer_pool_size value, the fewer disk I/O is needed to access the same data in tables more than once and database should be faster. However, do not set it too large because competition for physical memory might cause paging in the operating system. So, an optimum value you need to configure for best performance.