Estimation of appropriate innodb buffer pool size

Question

I'm reading this thread How large should be mysql innodb_buffer_pool_size?

And perform the querys suggested by RolandoMySQLDBA and I get 307 RIBPS and 264G. That's mean that I need 307GB of RAM?

Currently I have a innodb_buffer_pool_size value of 2097152000 but the server has 128GB of RAM and according to MySQL Workbench Server Status the InnoDB BufferUsage is at 100%

This is wrong rigth? I mean, I must to increase the innodb_buffer_pool_size?

MySQL version is 5.5.29

• RolandoMySQLDBA: Hello. My answer to that post was for a person who had way too much RAM and so little data. Your case is the total opposite. So, the answer from @RickJames is far more appropriate.
• E_Blue: So, 128GB of RAM is to low for this specific scenario? I'm new on this, I'm mainly a programmer and designer, I'm just curious about what is happening. Thanks.

Answer 1

Bad! The innodb_buffer_pool_size should never be set bigger than the amount of RAM you have. An general rule is to set it to 70% of available RAM. For 128GB of RAM, 100G is probably fine, assuming you don't have a lot of other applications running on it.

The buffer_pool is a cache. If the data that you actively use is only 20G, then about 20G will work fine, almost as good as 100G. And you don't need to increase your RAM.

If you are repeatedly reading all of your 307G of data and indexes, then you do need 350GB of RAM. But probably you could make some simple changes to avoid such repeated reading, thereby getting down to a civilized "working set", such as 20G. (No, there is no simple way to estimate the "working set size".)

Bottom line: Don't get more RAM. Set the buffer_pool to 100G. Fix any slow queries.

More discussion.

• E_Blue: I have a Web page for map location services that uses the MySQL as database engine. Also some JAVA application running in the background and remotely about 50 PC with a .NET application that uses the same database. Also, on the server, is running a PostgresSQL; all that takes about 110GB of RAM. The server has 32 cores. I have a lot of Client connections from the server itself in sleep mode. If I hide the sleeping connections in MySQL Workbench Server Status I get les than 10 connections at same time, so I think the slow querys are not my problem.
• E_Blue: Anyway, the .NET remote applications and the map at Web page time to time is a bit slow, that's why I'm here. Because I wonder why is happening that and I have no clue about where to start to find the problem an solve it.
• Rick James: "50 PC"?? Most big MySQL installations run quite happily with a buffer_pool that is smaller than the amount of data. Most MySQL installations rarely use more than 1 core, regardless of how big.
• Rick James: Set long_query_time = 1; turn on the slowlog; wait a day; use pt-query-digest to find the naughtiest queries; tackle them. (We can help.)
• E_Blue: The 50PC are on the intranet and make multiple querys and nonquerys every 3 seconds, the Web page uses Tomcat 7. All that you mention is in my.cnf file? The OS is CentOS.