CODEWITHSUNDEEP
mysqlsizebufferinnodbpool
Michael
Michael

Reputation: 431

mysql innodb buffer pool size which dependencies

I want to dimension RAM dependent on the innodb_buffer_pool_size. But all what I find is the dependence of the innodb_buffer_pool_size on RAM.

Can you tell me on which parameters on the innodb_buffer_pool_size depends? Number of queries? Number of transactions?

I want to find out the formula which calculates the RAM dependent on innodb_buffer_pool_size.

Can you help me? Thank you very much for your advice!

Upvotes: 0

Views: 599

Answers (1)

Rick James
Rick James

Reputation: 142518

Sizing innodb_buffer_pool_size:

  • If all the queries/transactions are touching tiny tables or tiny parts or big tables, then a tiny buffer_pool is sufficient.
  • If you have UUIDs, you need a big buffer pool -- because you can't touch only 'tiny' parts of the table/index via UUIDs; they are too 'random'.
  • Bigger than 70% of RAM is dangerous because it could lead to swapping, which is terrible for performance.
  • That's 70% of available RAM -- need to leave room for the OS, code, other caches, etc.
  • When you change RAM, change the buffer_pool_size. Or the other way around.
  • Bigger is better.
  • Much bigger than the total size of all data and indexes is unnecessary (wastes RAM).
  • Too small leads to inefficient operation of most operations involving InnoDB.
  • the buffer_pool is a cache. Computer Science discusses what that means and what the ramifications are.

So, the simple formula "set it to 70% of available RAM".

There are various metrics in SHOW GLOBAL STATUS that can say whether the buffer_pool is "big enough". But they are useful only after setting the size, then running for a while. And they only say "there are lots of reads/writes", they don't say whether or by how much to change the size.

If you would like me to analyze the STATUS (after it has been up at least a day), provide

  • How much RAM
  • SHOW VARIABLES;
  • SHOW GLOBAL STATUS;

I will check those formulas, plus a couple hundred others.

Upvotes: 2

Related Questions