0

I am getting following error after modifying the innodb buffer pool size values.

InnoDB: mmap(137428992 bytes) failed; errno 12
InnoDB: Cannot allocate memory for the buffer pool

137428992 is innodb_buffer_pool_chunk_size.

I am having a machine with 256 GB RAM.

Following is what I added to my.cnf

innodb_buffer_pool_size=200G 
innodb_buffer_pool_instances=40

All the other values are default. Resolution for similar questions are either increase RAM or decrease buffer pool size. If I monitor the memory usage when mysqld is starting, I see that it increases to 8-9 GB before the failure occurs.

Following is free -g output

              total        used        free      shared  buff/cache   available
Mem:            251           2         247           0           1         248
Swap:             3           0           3

What could be causing this issue?

  • how are you running this? Is there a ulimit set? Is this running in a systemd service where someone has added a memory limit? – danblack Oct 5 '18 at 6:04
  • It is a systemd service. limit is unlimited. There is nothing specific in systemd mysqld.service. – digitizedx Oct 5 '18 at 6:41
  • Do you have large_pages set? – Rick James Oct 16 '18 at 0:12
1

You are probably running the service on Linux. I've had similar problems with allocating large amounts of memory on Linux (as opposed to BSD) even if the amount of memory I was trying to allocate was reasonable in proportion to the system's total memory.

MySQL suggests having innodb_buffer_pool_size / innodb_buffer_pool_chunk_size < 1000. https://dev.mysql.com/doc/refman/5.7/en/innodb-parameters.html#sysvar_innodb_buffer_pool_chunk_size

Right now you are at about 1455. Try setting innodb_buffer_pool_chunk_size = 274857984. Or even 549715968.

This will also change the allocation pattern on the OS level which might help the operation to succeed. Also adding some swap could help the allocation to succeed by providing even more addressable virtual memory - not that the swap would ever actually be used.

  • Tried. but still same. Earlier innodb_buffer_pool_chunk_size was 1G. I went back to default value. – digitizedx Oct 5 '18 at 10:36
  • Would you be able to add 32-64gb of swap to the server? Otherwise see if you can decrease the buffer pool by 10-20% and see if the allocation will go through. – MarCPlusPlus Oct 5 '18 at 11:24
  • With the following values, it is working, innodb_buffer_pool_size=100G innodb_buffer_pool_instances=16 innodb_buffer_pool_chunk_size=256M, if I increase innodb_buffer_pool_size above 100G with any combination of other values, it fails . I will try to add swap. – digitizedx Oct 5 '18 at 14:17
  • Yea I think it is just because of the os allocator internals. Adding to the virtual address space with swap should help. – MarCPlusPlus Oct 5 '18 at 14:23
0

This was because /proc/sys/vm/overcommit_ratio was 50. It is able to allocate higher if overcommit_ratio is modified.

  • Glad you figured it out. Seems strange though because overcommit_ratio only has an effect if the overcommit_memory value has been changed from it's default. You might consider changing /proc/sys/vm/overcommit_memory to 0. – MarCPlusPlus Oct 9 '18 at 9:18
  • For now I actually don't want it to overcommit for performance reasons. If it overcommits then it will have to eventually use the swap. For now I increased the ratio to 90%. There is nothing else that is going to run in that except MySQL. I hope MySQL will not try to mmap more than 90%. – digitizedx Oct 9 '18 at 9:30

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.