0

I've got 256GB RAM. InnoDB pool buffer size is 128GB, and InnoDB data + indexes in all databases are about 105GB. Most of that data is frequently queried so, correct me if I am wrong, it should reside in buffer_pool?

I thought that innodb_buffer_pool resides in RAM, but top command shows:

MiB Mem : 257873.0 total,    747.0 free,  74239.4 used, 182886.6 buff/cache

Most of users complain about high RAM usage, and in my case it seems that MariaDB is not using all the RAM it should?

ulimit -v says 'unlimited'.

Is that ok? Can I force putting entire innodb_buffer_pool in RAM, and if so, then how?

2
  • 1
    You should be concerned about the bufferpool hit ratio, not its sheer volume. – mustaccio Dec 22 '20 at 15:37
  • MariaDB's memory usage grows over time. It stops growing when certain configurable settings are hit. In practice, it mostly stops growing long before the 'theoretical' limit is hit. – Rick James Dec 22 '20 at 17:36
1

MariaDB's memory usage grows over time. It stops growing when certain configurable settings are hit. In practice, it mostly stops growing long before the 'theoretical' limit is hit.

The buffer_pool is allocated only in RAM. It is the main memory user that grows over time, stopping when it hits innodb_buffer_pool_size.

Since the buffer_pool is a "cache", the data can be much bigger than the buffer_pool; the downside is the need to do more I/O to pull "blocks" of data (or indexes) into the cache, bumping out other blocks.

If all of the activity is concentrated on a subset of the data (eg, "recent" items), then the buffer_pool grows until "big enough" and may not actually expand to innodb_buffer_pool_size. I/O activity will be low.

If the buffer_pool is bigger than all the InnoDB data and indexes you have, it will grow to about that size and then simply stay at that size.

The innodb_buffer_pool_size should not be so large that you run out of RAM. When that happens, swapping occurs. If there is no swap space, then the process is killed.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.