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):

http://dev.mysql.com/doc/mysql/en/freebsd.html



----- Original Message ----- From: "Peter Beckman" <[EMAIL PROTECTED]>
To: <mysql@lists.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
OS.  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. Operating system errno: 12
    InnoDB: Check if you should increase the swap file or
    InnoDB: ulimits of your operating system.
    InnoDB: On FreeBSD check you have compiled the OS 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 [ERROR] Can't init databases
    050823  6:58:17 [ERROR] 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
    innodb_additional_mem_pool_size = 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 [EMAIL PROTECTED] http://www.purplecow.com/
---------------------------------------------------------------------------

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to