Mysql – innodb_buffer_pool_size more data than pool size

innodbmariadbMySQL

I have 64gb ram on server.
InnoDB buffer pool is set to 26.1gb

[OK] InnoDB buffer pool / data size: 26.1G/24.3G

Im wondering what happens if data over grow current pool size, as i understand it should store more important data and data more often used in pool and other access from hard drive ?

I had similar situation before, and as soon it has overgrown pool size mariadb server become unstable, i have been trying to lift up innodb_buffer_pool_size to 32gb but it caused stability problem as well. Even that most of time memory usage is 33gb/64gb and most of this ram is used by mariadb server, only 500mb ram is used by other applications. There other half seems not used. I have temporary solved it by truncating unnecessary data but this time i can't do it.

I have considered to use swap but since i have only 2x2TB Soft Raid Mode 1 and if InnoDb will read from disk anyways there is no point to do so i guess.

SELECT CEILING(Total_InnoDB_Bytes*1.6/POWER(1024,3)) RIBPS FROM (SELECT SUM(data_length+index_length) Total_InnoDB_Bytes FROM information_schema.tables WHERE engine='InnoDB') A

Shows that i should use 60gb innodb pool size.

If it's important innodb_flush_method=O_DIRECT

Best Answer

If the buffer_pool is set at 26GB in a 64GB server, what is the rest of RAM being used for? Normally you should simply set it to 45-50G and not worry.

On the what-if question:

Case 1: There is high "locality of reference". A buffer_pool that is much smaller than the total data (24.3G in your case) works fine. The buffer_pool is a "cache", and this Case is an example where caching is important.

Case 2: Lots of random access or lots of table scans. In this Case performance will degrade, possibly seriously, if the buffer_pool is not big enough for the dataset. The caching fails, and I/O ensues.

UUID primary keys or other indexes can lead to random accesses and performance problems in Case 2.

I see no clues in what you said that could explain "32gb .. caused stability problem".

In most servers, O_DIRECT is good.

innodb_buffer_pool_instances should probably be 16 in your range.

Related Question