1

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

Community
  • 1
E_Blue
  • 111
  • 4
  • 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. – RolandoMySQLDBA Sep 07 '16 at 20:15
  • 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. – E_Blue Sep 07 '16 at 20:38

1 Answers1

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.

Rick James
  • 78,038
  • 5
  • 47
  • 113
  • 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 Sep 07 '16 at 18:43
  • 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. – E_Blue Sep 07 '16 at 18:45
  • "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 Sep 07 '16 at 18:48
  • 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.) – Rick James Sep 07 '16 at 18:50
  • 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. – E_Blue Sep 07 '16 at 19:04
  • 50 percent? 50 PC clients? Something else? 1000 queries per second should be no problem; if it is, then you have slow queries. – Rick James Sep 07 '16 at 19:16
  • 50 PC clients. MySQL Server Status says that the "Selects per Seconds" is from 2.5k to 5k at this very moment. Traffic is from 7MB/s to 10MB/s. connections 1587. InnoDB Reads per second from 700 to 950. InnoDB Writes per second 3 to 160. Key efficiency 99.5% – E_Blue Sep 07 '16 at 20:33
  • ("key efficiency" refers to MyISAM, so irrelevant.) OK, the numbers are high enough to dig into them. Do the slowlog thing I mentioned, but set long_query_time = 0.5. Also, please provide SHOW VARIABLES; and SHOW GLOBAL STATUS; so I can analyze a couple hundred other things. (You may need post.it or something; this forum has a size limit.) Those will provide the "key efficiency for InnoDB, which is relevant. They will also say whether your RAM is "big enough". – Rick James Sep 08 '16 at 18:39