Allocating 8GB memory to MySQL on a 64bit system

Question

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.

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.