4

Specs -

MySQL 5.0.x  ,  Redhat 5.9  ,  Physical memory - 16GB

I am trying to set innodb buffer pool size to 8GB (innodb_buffer_pool_size=8G). When i do, and start mysql, i get following error -

InnoDB: Error: cannot allocate 8589950976 bytes of
InnoDB: memory with malloc! Total allocated memory
InnoDB: by InnoDB 37679104 bytes. Operating system errno: 12
InnoDB: Check if you should increase the swap file or
InnoDB: ulimits of your operating system.

Here is output of free -m -

             total       used       free     shared    buffers     cached
Mem:         15922      15269        653          0        248       4275
-/+ buffers/cache:      10744       5178
Swap:         2047          0       2047

Here is output of ulimit -a -

core file size          (blocks, -c) 0
data seg size           (kbytes, -d) unlimited
scheduling priority             (-e) 0
file size               (blocks, -f) unlimited
pending signals                 (-i) 127352
max locked memory       (kbytes, -l) 32
max memory size         (kbytes, -m) unlimited
open files                      (-n) 1024
pipe size            (512 bytes, -p) 8
POSIX message queues     (bytes, -q) 819200
real-time priority              (-r) 0
stack size              (kbytes, -s) 10240
cpu time               (seconds, -t) unlimited
max user processes              (-u) 127352
virtual memory          (kbytes, -v) unlimited
file locks                      (-x) unlimited

I checked file '/etc/security/limits.conf'. Nothing in there. All lines are commented (start with #). Checked directory '/etc/security/limits.d/', empty.

Something is preventing allocation of more than 4GB of memory to MySQL. Not sure what.

Any ideas?

Danke.

5
  • Lower it back to 4G, restart mysql, and run this one also : SELECT SUM(data_length + index_length) POWER(1024,3) innodb_space FROM information_schema.tables WHERE engine='InnoDB'; May 18, 2013 at 20:42
  • You should try to post your entire my.cnf into the question May 18, 2013 at 20:48
  • Please run this query : SHOW VARIABLES LIKE 'version_comment';. This will tell us the OS and bit version of mysqld. May 18, 2013 at 20:50
  • Version info - | version | 5.0.95 | | version_bdb | Sleepycat Software: Berkeley DB 4.1.24: (December 16, 2011) | | version_comment | Source distribution | | version_compile_machine | x86_64 | | version_compile_os | redhat-linux-gnu | May 18, 2013 at 20:53
  • Solved the problem by increasing the memlock in OS settings. I will post the detailed solution here soon. May 18, 2013 at 20:54

1 Answer 1

3

memlock was the issue. turns out, memlock settings were at default (see above), and this might have been preventing the allocation of 4+gb of memory to mysql.

changed memlock settings in '/etc/security/limits.conf', and limit to 8GB. then, allocated 7GB to mysql buffer pool. worked. mysql now starts and stops without throwing any errors in the log.

a few things to note here -

  1. from what i have read online, the amount of memory allocated to mysql is slightly more than the setting for buffer pool. so lets say you assign 7GB to pool, actual memory assigned to mysql would be closer to 8gb (this is an example, not real numbers).

  2. turning the mysql service on/off has to be done very carefully. i have noticed that when i do a mysqld-start followed by quickly by a mysqld-stop, mysql responds with [FAILED]. logs says allocation of 7GB failed.

3
  • which can be accomplished by eiting /etc/security/limits.conf: mysql - memlock 67108864 \r\n @mysql - memlock 67108864 \n\n root - memlock 67108864
    – kellogs
    Oct 24, 2013 at 16:32
  • @quest monger, Could you please post your my.cnf file. I am having a 8G machine with mysql & redis running. I need to increase the max_connection & ram to my mysql server to avoid "Failed attempt" + "Aborted conn" errors Apr 24, 2015 at 12:50
  • @suriya - unfortunately i have moved on to postgres since this post was last updated. Apr 24, 2015 at 20:01

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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