Tags: allocate, certain, database, fairly, freebsd, innodb, isinnodb, limits, memory, mysql, oracle, raising, related, sql, summarywhile

InnoDB, FreeBSD and Memory: Cannot Allocate memory - raising limits

On Database » MySQL

18,442 words with 3 Comments; publish: Sat, 10 May 2008 18:59:00 GMT; (250109.38, « »)

SUMMARY

While I am fairly certain the problem is with FreeBSD, since this is

InnoDB/MySQL related, I thought I would post here to see if others have had

this problem. I have googled several different phrases to find this answer

-- how does FreeBSD 5 set resource limits, and how do I override them

without hard-coding them into a recompiled kernel?

DETAIL

I just doubled the amount of memory in my FreeBSD 5.3 box. Celeron (yes, I

know) 1.7Ghz, 1GB of memory now. Before I had 512MB of memory, and was

able to run MySQL with InnoDB at 384MB innodb_buffer_pool_size. Everything

was fine. Now I must admit, I played with my .cnf file a bit between the

nicely running 512MB memory and restarting with 1GB, but I don't think

that's why I'm having this problem.

The box runs only sshd and mysql in addition to processes required for the

S. Currently with mysql running at innodb pool size of 384M, my memory

usage on the box as reported by top:

Mem: 146M Active, 17M Inact, 114M Wired, 105M Buf, 723M Free

Swap: 2048M Total, 2048M Free

I have enough memory available it seems. No errors on boot, no o errors in

the /var/log(s).

I'm quite certain that the error I got was a result of resource limits set

by the kernel too low. However, I can't, for the life of me, figure out

where they are being set, or by what. My current theory is if I can

increase the datasize limits, I can run MySQL with InnoDB Buffer Pool Size

of 820M, or about 80% of available memory.

~ limits -H (display Hard Limits)

Resource limits (current):

cputime infinity secs

filesize infinity kb

datasize 524288 kb

stacksize 65536 kb

coredumpsize infinity kb

memoryuse infinity kb

memorylocked infinity kb

maxprocesses 5547

openfiles 11095

sbsize infinity bytes

vmemoryuse infinity kb

Nowhere in login.conf are any user or class limits being set (all are

defaulting to infinity). I can't find anything in sysctl that would set my

datasize and stacksize to the values set here. My kernel config has no

numbers in it -- no max size set; only NMBCLUSTERS at 16384 and a 15 second

SCSI delay. I can't find any documentation on how to change hard limits;

everything I've read says that FreeBSD 5 modifies those limits at boot

"intelligently", and if so, should be able to be set somewhere without

recompiling the kernel using sysctl (or something else). I've tried

setting it with limits and ulimit (as root):

# ulimit -a

data seg size (kbytes, -d) 524288

# ulimit -d 524287; ulimit -a

data seg size (kbytes, -d) 524287

# ulimit -d 524288; ulimit -a

data seg size (kbytes, -d) 524288

# ulimit -d 524289; ulimit -a (also tried with 786432, same result)

data seg size (kbytes, -d) 524288

I can set it lower, but I can't set it higher. From dmesg.boot:

real memory = 1072627712 (1022 MB)

avail memory = 1044291584 (995 MB)

The error from mysql:

050823 06:57:16 mysqld started

050823 6:57:17 InnoDB: Error: cannot allocate 859848704 bytes of

InnoDB: memory with malloc! Total allocated memory

InnoDB: by InnoDB 19166088 bytes. system errno: 12

InnoDB: Check if you should increase the swap file or

InnoDB: ulimits of your operating system.

InnoDB: FreeBSD check you have compiled the S with

InnoDB: a big enough maximum process size.

InnoDB: We keep retrying the allocation for 60 seconds

InnoDB: Fatal error: cannot allocate the memory for the buffer pool

050823 6:58:17 [ERRR] Can't init databases

050823 6:58:17 [ERRR] Aborting

050823 6:58:17 [Note] /usr/local/libexec/mysqld: Shutdown complete

050823 06:58:17 mysqld ended

My Config:

[mysqld]

max_connections = 100

thread_cache_size = 20

table_cache = 64

max_allowed_packet = 32M

sort_buffer_size = 512K

read_buffer_size = 256K

read_rnd_buffer_size = 512K

tmp_table_size = 64M

wait_timeout = 900

key_buffer = 16M

myisam_sort_buffer_size = 8M

innodb_data_home_dir = /usr/local/db/mysql/

innodb_data_file_path = ibdata1:50M:autoextend

innodb_log_group_home_dir = /usr/local/db/mysql/

innodb_log_arch_dir = /usr/local/db/mysql/

# was 820M (80% of 1024MB)

innodb_buffer_pool_size = 384M

= 8M

innodb_log_file_size = 10M

innodb_log_buffer_size = 8M

innodb_flush_log_at_trx_commit = 1

query_cache_size = 20M

query_cache_limit = 1M

long_query_time = 5

log-slow-queries = /var/log/mysql-slow-queries.log

My DB specs:

ibdata1 is 2.6GB or so, 20 or so tables, couple million rows

I would appreciate any help.

Beckman

Peter Beckman Internet Guy

beckman (AT) purplecow (DOT) com http://www.purplecow.com/

All Comments

Leave a comment...

  • 3 Comments
    • The limit you are running into is the maxdsiz or max data size for a

      process. It is defaulting to 512MB. In FreeBSD 5.x you don't have to

      recompile your kernel to set a different maxdsiz. See

      /boot/defaults/loader.conf. Putting kern.maxdsiz="1073741824" in

      /boot/loader.conf should work.

      This is somewhat covered here (but should be updated regarding loader.conf

      as a means to set maxdsiz):

      Message

      From: "Peter Beckman" <beckman (AT) purplecow (DOT) com>

      To: <mysql (AT) lists (DOT) mysql.com>

      Sent: Tuesday, August 23, 2005 5:45 AM

      Subject: InnoDB, FreeBSD and Memory: Cannot Allocate memory - raising limits

      SUMMARY

      While I am fairly certain the problem is with FreeBSD, since this is

      InnoDB/MySQL related, I thought I would post here to see if others have

      had

      this problem. I have googled several different phrases to find this

      answer

      -- how does FreeBSD 5 set resource limits, and how do I override them

      without hard-coding them into a recompiled kernel?

      DETAIL

      I just doubled the amount of memory in my FreeBSD 5.3 box. Celeron (yes,

      I

      know) 1.7Ghz, 1GB of memory now. Before I had 512MB of memory, and was

      able to run MySQL with InnoDB at 384MB innodb_buffer_pool_size.

      Everything

      was fine. Now I must admit, I played with my .cnf file a bit between the

      nicely running 512MB memory and restarting with 1GB, but I don't think

      that's why I'm having this problem.

      The box runs only sshd and mysql in addition to processes required for the

      S. Currently with mysql running at innodb pool size of 384M, my memory

      usage on the box as reported by top:

      Mem: 146M Active, 17M Inact, 114M Wired, 105M Buf, 723M Free

      Swap: 2048M Total, 2048M Free

      I have enough memory available it seems. No errors on boot, no o errors

      in

      the /var/log(s).

      I'm quite certain that the error I got was a result of resource limits set

      by the kernel too low. However, I can't, for the life of me, figure out

      where they are being set, or by what. My current theory is if I can

      increase the datasize limits, I can run MySQL with InnoDB Buffer Pool Size

      of 820M, or about 80% of available memory.

      ~ limits -H (display Hard Limits)

      Resource limits (current):

      cputime infinity secs

      filesize infinity kb

      datasize 524288 kb

      stacksize 65536 kb

      coredumpsize infinity kb

      memoryuse infinity kb

      memorylocked infinity kb

      maxprocesses 5547

      openfiles 11095

      sbsize infinity bytes

      vmemoryuse infinity kb

      Nowhere in login.conf are any user or class limits being set (all are

      defaulting to infinity). I can't find anything in sysctl that would set

      my

      datasize and stacksize to the values set here. My kernel config has no

      numbers in it -- no max size set; only NMBCLUSTERS at 16384 and a 15

      second

      SCSI delay. I can't find any documentation on how to change hard limits;

      everything I've read says that FreeBSD 5 modifies those limits at boot

      "intelligently", and if so, should be able to be set somewhere without

      recompiling the kernel using sysctl (or something else). I've tried

      setting it with limits and ulimit (as root):

      # ulimit -a

      data seg size (kbytes, -d) 524288

      # ulimit -d 524287; ulimit -a

      data seg size (kbytes, -d) 524287

      # ulimit -d 524288; ulimit -a

      data seg size (kbytes, -d) 524288

      # ulimit -d 524289; ulimit -a (also tried with 786432, same result)

      data seg size (kbytes, -d) 524288

      I can set it lower, but I can't set it higher. From dmesg.boot:

      real memory = 1072627712 (1022 MB)

      avail memory = 1044291584 (995 MB)

      The error from mysql:

      050823 06:57:16 mysqld started

      050823 6:57:17 InnoDB: Error: cannot allocate 859848704 bytes of

      InnoDB: memory with malloc! Total allocated memory

      InnoDB: by InnoDB 19166088 bytes. system errno: 12

      InnoDB: Check if you should increase the swap file or

      InnoDB: ulimits of your operating system.

      InnoDB: FreeBSD check you have compiled the S with

      InnoDB: a big enough maximum process size.

      InnoDB: We keep retrying the allocation for 60 seconds

      InnoDB: Fatal error: cannot allocate the memory for the buffer pool

      050823 6:58:17 [ERRR] Can't init databases

      050823 6:58:17 [ERRR] Aborting

      050823 6:58:17 [Note] /usr/local/libexec/mysqld: Shutdown complete

      050823 06:58:17 mysqld ended

      My Config:

      [mysqld]

      max_connections = 100

      thread_cache_size = 20

      table_cache = 64

      max_allowed_packet = 32M

      sort_buffer_size = 512K

      read_buffer_size = 256K

      read_rnd_buffer_size = 512K

      tmp_table_size = 64M

      wait_timeout = 900

      key_buffer = 16M

      myisam_sort_buffer_size = 8M

      innodb_data_home_dir = /usr/local/db/mysql/

      innodb_data_file_path = ibdata1:50M:autoextend

      innodb_log_group_home_dir = /usr/local/db/mysql/

      innodb_log_arch_dir = /usr/local/db/mysql/

      # was 820M (80% of 1024MB)

      innodb_buffer_pool_size = 384M

      = 8M

      innodb_log_file_size = 10M

      innodb_log_buffer_size = 8M

      innodb_flush_log_at_trx_commit = 1

      query_cache_size = 20M

      query_cache_limit = 1M

      long_query_time = 5

      log-slow-queries = /var/log/mysql-slow-queries.log

      My DB specs:

      ibdata1 is 2.6GB or so, 20 or so tables, couple million rows

      I would appreciate any help.

      Beckman

      Peter Beckman Internet

      Guy

      beckman (AT) purplecow (DOT) com

      http://www.purplecow.com/

      #1; Sat, 10 May 2008 19:00:00 GMT
    • Tue, 23 Aug 2005, Jeremiah Gowdy wrote:

      The limit you are running into is the maxdsiz or max data size for a process.

      It is defaulting to 512MB. In FreeBSD 5.x you don't have to recompile your

      kernel to set a different maxdsiz. See /boot/defaults/loader.conf. Putting

      kern.maxdsiz="1073741824" in /boot/loader.conf should work.

      This is somewhat covered here (but should be updated regarding loader.conf as

      a means to set maxdsiz):

      Excellent! Just what I needed.

      I've summarized my experience and posted a Tip on both the above mentioned

      page and the InnoDB S Error Codes manual page:

      My summary:

      FreeBSD 5.x, there is a hard limit default compiled into the kernel

      which limits the amount of memory a process can use to 512MB. You can

      see this by running the command "limits."

      You can, without recompiling the kernel, increase this maximum process

      size memory limit to any number, as long as that number is absolutely

      below your physical memory. I.e. if you have 1GB of memory, do not set

      your per process memory limit equal to or higher than that! FreeBSD 5.x

      will crash and not be able to be recovered without a local change being

      made if you set that limit higher than the amount of physical memory .

      (FreeBSD Bug: )

      In /boot/loader.conf (NT sysctl.conf), add these lines (on a box with

      1.5GB or more memory):

      kern.maxdsiz="1073741824" # 1GB

      kern.dfldsiz="1073741824" # 1GB

      kern.maxssiz="134217728" # 128MB

      Then reboot, and increase your my.cnf memory configuration values in

      order to increase mysql memory usage.

      The system error number you will see is 12 (ENMEM) of

      memory:

      InnoDB: Error: cannot allocate 797284 bytes of

      InnoDB: memory with malloc! Total allocated memory

      InnoDB: by InnoDB 6299504 bytes. system errno: 12

      InnoDB: Check if you should increase the swap file or

      InnoDB: ulimits of your operating system.

      InnoDB: FreeBSD check you have compiled the S with

      InnoDB: a big enough maximum process size.

      InnoDB: We keep retrying the allocation for 60 seconds

      InnoDB: Fatal error: cannot allocate the memory for the buffer pool

      Hope that helps someone else. Thanks for the help Jeremiah!

      Beckman

      Peter Beckman Internet Guy

      beckman (AT) purplecow (DOT) com http://www.purplecow.com/

      #2; Sat, 10 May 2008 19:02:00 GMT
    • The limit you are running into is the maxdsiz or max data size for a

      process. It is defaulting to 512MB. In FreeBSD 5.x you don't have to

      recompile your kernel to set a different maxdsiz. See

      /boot/defaults/loader.conf. Putting kern.maxdsiz="1073741824" in

      /boot/loader.conf should work.

      This is somewhat covered here (but should be updated regarding loader.conf

      as a means to set maxdsiz):

      Message

      From: "Peter Beckman" <beckman (AT) purplecow (DOT) com>

      To: <mysql (AT) lists (DOT) mysql.com>

      Sent: Tuesday, August 23, 2005 5:45 AM

      Subject: InnoDB, FreeBSD and Memory: Cannot Allocate memory - raising limits

      SUMMARY

      While I am fairly certain the problem is with FreeBSD, since this is

      InnoDB/MySQL related, I thought I would post here to see if others have

      had

      this problem. I have googled several different phrases to find this

      answer

      -- how does FreeBSD 5 set resource limits, and how do I override them

      without hard-coding them into a recompiled kernel?

      DETAIL

      I just doubled the amount of memory in my FreeBSD 5.3 box. Celeron (yes,

      I

      know) 1.7Ghz, 1GB of memory now. Before I had 512MB of memory, and was

      able to run MySQL with InnoDB at 384MB innodb_buffer_pool_size.

      Everything

      was fine. Now I must admit, I played with my .cnf file a bit between the

      nicely running 512MB memory and restarting with 1GB, but I don't think

      that's why I'm having this problem.

      The box runs only sshd and mysql in addition to processes required for the

      S. Currently with mysql running at innodb pool size of 384M, my memory

      usage on the box as reported by top:

      Mem: 146M Active, 17M Inact, 114M Wired, 105M Buf, 723M Free

      Swap: 2048M Total, 2048M Free

      I have enough memory available it seems. No errors on boot, no o errors

      in

      the /var/log(s).

      I'm quite certain that the error I got was a result of resource limits set

      by the kernel too low. However, I can't, for the life of me, figure out

      where they are being set, or by what. My current theory is if I can

      increase the datasize limits, I can run MySQL with InnoDB Buffer Pool Size

      of 820M, or about 80% of available memory.

      ~ limits -H (display Hard Limits)

      Resource limits (current):

      cputime infinity secs

      filesize infinity kb

      datasize 524288 kb

      stacksize 65536 kb

      coredumpsize infinity kb

      memoryuse infinity kb

      memorylocked infinity kb

      maxprocesses 5547

      openfiles 11095

      sbsize infinity bytes

      vmemoryuse infinity kb

      Nowhere in login.conf are any user or class limits being set (all are

      defaulting to infinity). I can't find anything in sysctl that would set

      my

      datasize and stacksize to the values set here. My kernel config has no

      numbers in it -- no max size set; only NMBCLUSTERS at 16384 and a 15

      second

      SCSI delay. I can't find any documentation on how to change hard limits;

      everything I've read says that FreeBSD 5 modifies those limits at boot

      "intelligently", and if so, should be able to be set somewhere without

      recompiling the kernel using sysctl (or something else). I've tried

      setting it with limits and ulimit (as root):

      # ulimit -a

      data seg size (kbytes, -d) 524288

      # ulimit -d 524287; ulimit -a

      data seg size (kbytes, -d) 524287

      # ulimit -d 524288; ulimit -a

      data seg size (kbytes, -d) 524288

      # ulimit -d 524289; ulimit -a (also tried with 786432, same result)

      data seg size (kbytes, -d) 524288

      I can set it lower, but I can't set it higher. From dmesg.boot:

      real memory = 1072627712 (1022 MB)

      avail memory = 1044291584 (995 MB)

      The error from mysql:

      050823 06:57:16 mysqld started

      050823 6:57:17 InnoDB: Error: cannot allocate 859848704 bytes of

      InnoDB: memory with malloc! Total allocated memory

      InnoDB: by InnoDB 19166088 bytes. system errno: 12

      InnoDB: Check if you should increase the swap file or

      InnoDB: ulimits of your operating system.

      InnoDB: FreeBSD check you have compiled the S with

      InnoDB: a big enough maximum process size.

      InnoDB: We keep retrying the allocation for 60 seconds

      InnoDB: Fatal error: cannot allocate the memory for the buffer pool

      050823 6:58:17 [ERRR] Can't init databases

      050823 6:58:17 [ERRR] Aborting

      050823 6:58:17 [Note] /usr/local/libexec/mysqld: Shutdown complete

      050823 06:58:17 mysqld ended

      My Config:

      [mysqld]

      max_connections = 100

      thread_cache_size = 20

      table_cache = 64

      max_allowed_packet = 32M

      sort_buffer_size = 512K

      read_buffer_size = 256K

      read_rnd_buffer_size = 512K

      tmp_table_size = 64M

      wait_timeout = 900

      key_buffer = 16M

      myisam_sort_buffer_size = 8M

      innodb_data_home_dir = /usr/local/db/mysql/

      innodb_data_file_path = ibdata1:50M:autoextend

      innodb_log_group_home_dir = /usr/local/db/mysql/

      innodb_log_arch_dir = /usr/local/db/mysql/

      # was 820M (80% of 1024MB)

      innodb_buffer_pool_size = 384M

      = 8M

      innodb_log_file_size = 10M

      innodb_log_buffer_size = 8M

      innodb_flush_log_at_trx_commit = 1

      query_cache_size = 20M

      query_cache_limit = 1M

      long_query_time = 5

      log-slow-queries = /var/log/mysql-slow-queries.log

      My DB specs:

      ibdata1 is 2.6GB or so, 20 or so tables, couple million rows

      I would appreciate any help.

      Beckman

      Peter Beckman Internet

      Guy

      beckman (AT) purplecow (DOT) com

      http://www.purplecow.com/

      #3; Sat, 10 May 2008 19:03:00 GMT