Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
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'; –  RolandoMySQLDBA May 18 '13 at 20:42
    
You should try to post your entire my.cnf into the question –  RolandoMySQLDBA May 18 '13 at 20:48
    
Please run this query : SHOW VARIABLES LIKE 'version_comment';. This will tell us the OS and bit version of mysqld. –  RolandoMySQLDBA May 18 '13 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 | –  Quest Monger May 18 '13 at 20:53
1  
+1 for finding your own solution –  RolandoMySQLDBA May 18 '13 at 20:56

1 Answer 1

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.

share|improve this answer
    
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 '13 at 16:32

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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