MySQL and default memory limits (mysqld: Out of memory)

classic Classic list List threaded Threaded
20 messages Options
Reply | Threaded
Open this post in threaded view
|

MySQL and default memory limits (mysqld: Out of memory)

Mike Jakubik
I just setup a new system with MySQL 5.0.22, and to my surprise i get
this error in MySQL's log.

/usr/local/libexec/mysqld: Out of memory (Needed 237527040 bytes)
/usr/local/libexec/mysqld: Out of memory (Needed 178145280 bytes)

The system has 1GB of ram, which is plenty for MySQLs configuration (its
using the my-large.cnf, which is tuned for a system of 512MB)


Why am i getting this error? I read somewhere that FreeBSD by default
limits process size to 512MB, however the variables used to tune it do
not seems to exist in FreeBSD-6.1 any more. How can i let MySQL use more
memory?


_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Mathieu Arnold-2
+-Le 09/07/2006 16:49 -0400, Mike Jakubik a dit :
| I just setup a new system with MySQL 5.0.22, and to my surprise i get
| this error in MySQL's log.
|
| /usr/local/libexec/mysqld: Out of memory (Needed 237527040 bytes)
| /usr/local/libexec/mysqld: Out of memory (Needed 178145280 bytes)
|
| The system has 1GB of ram, which is plenty for MySQLs configuration (its
| using the my-large.cnf, which is tuned for a system of 512MB)
|
|
| Why am i getting this error? I read somewhere that FreeBSD by default
| limits process size to 512MB, however the variables used to tune it do
| not seems to exist in FreeBSD-6.1 any more. How can i let MySQL use more
| memory?

If you're using a i386, the max process memory size limit is at 512M,
you'll have to tune kern.maxdsiz in /boot/loader.conf to say 1G.

--
Mathieu Arnold

attachment0 (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Darren Pilgrim-2
Mathieu Arnold wrote:
> +-Le 09/07/2006 16:49 -0400, Mike Jakubik a dit :
 > | I read somewhere that FreeBSD by default
> | limits process size to 512MB, however the variables used to tune it do
> | not seems to exist in FreeBSD-6.1 any more. How can i let MySQL use more
> | memory?
>
> If you're using a i386, the max process memory size limit is at 512M,
> you'll have to tune kern.maxdsiz in /boot/loader.conf to say 1G.

That OID doesn't seem to exist:

 > uname -pr
6.0-RELEASE-p1 i386
 > sysctl -N kern | grep max
kern.maxvnodes
kern.maxproc
kern.maxfiles
kern.argmax
kern.maxfilesperproc
kern.maxprocperuid
kern.ipc.maxsockbuf
kern.ipc.somaxconn
kern.ipc.max_linkhdr
kern.ipc.max_protohdr
kern.ipc.max_hdr
kern.ipc.max_datalen
kern.ipc.maxpipekva
kern.ipc.msgmax
kern.ipc.shmmax
kern.ipc.maxsockets
kern.iov_max
kern.kq_calloutmax
kern.maxusers
kern.threads.max_threads_per_proc
kern.threads.max_groups_per_proc
kern.threads.max_threads_hits
kern.smp.maxcpus

--
Darren Pilgrim
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Mathieu Arnold-2
+-Le 09/07/2006 14:14 -0700, Darren Pilgrim a dit :
| Mathieu Arnold wrote:
|> +-Le 09/07/2006 16:49 -0400, Mike Jakubik a dit :
|  > | I read somewhere that FreeBSD by default
|> | limits process size to 512MB, however the variables used to tune it do
|> | not seems to exist in FreeBSD-6.1 any more. How can i let MySQL use
|> | more memory?
|>
|> If you're using a i386, the max process memory size limit is at 512M,
|> you'll have to tune kern.maxdsiz in /boot/loader.conf to say 1G.
|
| That OID doesn't seem to exist:
|
|  > uname -pr
| 6.0-RELEASE-p1 i386
|  > sysctl -N kern | grep max

That's not a sysctl, it's a tunable thing, which don't appear in sysctl.

--
Mathieu Arnold

attachment0 (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Darren Pilgrim-2
Mathieu Arnold wrote:
> [kern.maxdsiz is not] a sysctl, it's a tunable thing, which don't
> appear in sysctl.

Gotta love namespace collisions.

--
Darren Pilgrim
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Mathieu Arnold-2
+-Le 09/07/2006 14:25 -0700, Darren Pilgrim a dit :
| Mathieu Arnold wrote:
|> [kern.maxdsiz is not] a sysctl, it's a tunable thing, which don't
|> appear in sysctl.
|
| Gotta love namespace collisions.

Well, in fact, most of the tunables do have a read only sysctl so that
people don't have to search the code for the value it has/takes :-)
Not this one though :-)

--
Mathieu Arnold

attachment0 (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Mike Jakubik
In reply to this post by Mathieu Arnold-2
Mathieu Arnold wrote:

> +-Le 09/07/2006 16:49 -0400, Mike Jakubik a dit :
> | I just setup a new system with MySQL 5.0.22, and to my surprise i get
> | this error in MySQL's log.
> |
> | /usr/local/libexec/mysqld: Out of memory (Needed 237527040 bytes)
> | /usr/local/libexec/mysqld: Out of memory (Needed 178145280 bytes)
> |
> | The system has 1GB of ram, which is plenty for MySQLs configuration (its
> | using the my-large.cnf, which is tuned for a system of 512MB)
> |
> |
> | Why am i getting this error? I read somewhere that FreeBSD by default
> | limits process size to 512MB, however the variables used to tune it do
> | not seems to exist in FreeBSD-6.1 any more. How can i let MySQL use more
> | memory?
>
> If you're using a i386, the max process memory size limit is at 512M,
> you'll have to tune kern.maxdsiz in /boot/loader.conf to say 1G.
>
>  

Why are the limits so low by default? In any case, this is what i found
in LINT.

options         MAXDSIZ=(1024UL*1024*1024)
options         MAXSSIZ=(128UL*1024*1024)
options         DFLDSIZ=(1024UL*1024*1024)

I have no idea what those values mean, what should i set them to to be
safe? A limit 768MB should work for me.


_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Mike Jakubik
In reply to this post by Mathieu Arnold-2
Mathieu Arnold wrote:

> +-Le 09/07/2006 14:25 -0700, Darren Pilgrim a dit :
> | Mathieu Arnold wrote:
> |> [kern.maxdsiz is not] a sysctl, it's a tunable thing, which don't
> |> appear in sysctl.
> |
> | Gotta love namespace collisions.
>
> Well, in fact, most of the tunables do have a read only sysctl so that
> people don't have to search the code for the value it has/takes :-)
> Not this one though :-)
>  

Exactly, its nice being able to see the current values. How else can i
see what the values are set to?


_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Mathieu Arnold-2
+-Le 09/07/2006 17:36 -0400, Mike Jakubik a dit :
| Mathieu Arnold wrote:
|> +-Le 09/07/2006 14:25 -0700, Darren Pilgrim a dit :
|> | Mathieu Arnold wrote:
|> |> [kern.maxdsiz is not] a sysctl, it's a tunable thing, which don't
|> |> appear in sysctl.
|> |
|> | Gotta love namespace collisions.
|>
|> Well, in fact, most of the tunables do have a read only sysctl so that
|> people don't have to search the code for the value it has/takes :-)
|> Not this one though :-)
|>  
|
| Exactly, its nice being able to see the current values. How else can i
| see what the values are set to?

As I previously said, it's 512M on i386, and 1G on 64 bit platforms.

--
Mathieu Arnold

attachment0 (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Mathieu Arnold-2
In reply to this post by Mike Jakubik


+-Le 09/07/2006 17:36 -0400, Mike Jakubik a dit :
| Mathieu Arnold wrote:
|> +-Le 09/07/2006 16:49 -0400, Mike Jakubik a dit :
|> | I just setup a new system with MySQL 5.0.22, and to my surprise i get
|> | this error in MySQL's log.
|> |
|> | /usr/local/libexec/mysqld: Out of memory (Needed 237527040 bytes)
|> | /usr/local/libexec/mysqld: Out of memory (Needed 178145280 bytes)
|> |
|> | The system has 1GB of ram, which is plenty for MySQLs configuration
|> | (its using the my-large.cnf, which is tuned for a system of 512MB)
|> |
|> |
|> | Why am i getting this error? I read somewhere that FreeBSD by default
|> | limits process size to 512MB, however the variables used to tune it do
|> | not seems to exist in FreeBSD-6.1 any more. How can i let MySQL use
|> | more memory?
|>
|> If you're using a i386, the max process memory size limit is at 512M,
|> you'll have to tune kern.maxdsiz in /boot/loader.conf to say 1G.
|>
|>  
|
| Why are the limits so low by default? In any case, this is what i found
| in LINT.
|
| options         MAXDSIZ=(1024UL*1024*1024)
| options         MAXSSIZ=(128UL*1024*1024)
| options         DFLDSIZ=(1024UL*1024*1024)

That's the LINT value, the *real* default values are in
<arch>/include/vmparam.h that is, 1G on alpha, ia64 and sparc64, 512K on
i386, powerpc and arm and 32G (?) on amd64

| I have no idea what those values mean, what should i set them to to be
| safe? A limit 768MB should work for me.

You can put 1G and you'd be safe for some time. It has no real, hum,
relation with the amount of ram you get, because some parts of your process
will get swapped out :
  PID USERNAME  THR PRI NICE   SIZE    RES STATE  C   TIME   WCPU COMMAND
 9860 mysql      18  96    0   614M   257M ucond  1   1:22  1.22% mysqld

so, as I understand it, mysqld has 614M allocated, from which 257 are
actually in RAM (the rest being swapped out.)

--
Mathieu Arnold

attachment0 (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Mike Jakubik
In reply to this post by Mathieu Arnold-2
Mathieu Arnold wrote:
> | Exactly, its nice being able to see the current values. How else can i
> | see what the values are set to?
>
> As I previously said, it's 512M on i386, and 1G on 64 bit platforms.
>
>  

Right, this explains why my amd64 system works just fine. Shouldn't this
be a dynamic value based on total amount of ram? Say 70% of RAM, I mean
servers have plenty of RAM nowadays, this seems like an old hard limit,
which many new users will trip over.... I set my value to 805306368, and
mysql seems to be happy now.

_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Stephen Montgomery-Smith
In reply to this post by Mike Jakubik
Mike Jakubik wrote:

> Mathieu Arnold wrote:
>
>> +-Le 09/07/2006 14:25 -0700, Darren Pilgrim a dit :
>> | Mathieu Arnold wrote:
>> |> [kern.maxdsiz is not] a sysctl, it's a tunable thing, which don't
>> |> appear in sysctl.
>> | | Gotta love namespace collisions.
>>
>> Well, in fact, most of the tunables do have a read only sysctl so that
>> people don't have to search the code for the value it has/takes :-)
>> Not this one though :-)
>>  
>
>
> Exactly, its nice being able to see the current values. How else can i
> see what the values are set to?

The commands limit and limits (one of them is a csh only command) will
tell you (although they might be set lower for a particular user via
/etc/login.conf, or for a particular process, but that's unlikely).

Stephen
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Peter Jeremy
In reply to this post by Mathieu Arnold-2
On Sun, 2006-Jul-09 23:45:44 +0200, Mathieu Arnold wrote:
>+-Le 09/07/2006 17:36 -0400, Mike Jakubik a dit :
>| Exactly, its nice being able to see the current values. How else can i
>| see what the values are set to?
>
>As I previously said, it's 512M on i386, and 1G on 64 bit platforms.

That doesn't answer Mike's question.  The _default_ i386 size is
512M, the _current_ values can be found using ulimit (getrlimit(2)).

Note that on non-PAE i386, the maximum process size is limited by the
kernel size - there is a total of 4GB address space available and by
default, the kernel has 2GB allocated to it.  This isn't quite enough
if you have 4GB RAM.

--
Peter Jeremy

attachment0 (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Konstantin Belousov
On Mon, Jul 10, 2006 at 05:41:16PM +1000, Peter Jeremy wrote:

> On Sun, 2006-Jul-09 23:45:44 +0200, Mathieu Arnold wrote:
> >+-Le 09/07/2006 17:36 -0400, Mike Jakubik a dit :
> >| Exactly, its nice being able to see the current values. How else can i
> >| see what the values are set to?
> >
> >As I previously said, it's 512M on i386, and 1G on 64 bit platforms.
>
> That doesn't answer Mike's question.  The _default_ i386 size is
> 512M, the _current_ values can be found using ulimit (getrlimit(2)).
>
> Note that on non-PAE i386, the maximum process size is limited by the
> kernel size - there is a total of 4GB address space available and by
> default, the kernel has 2GB allocated to it.  This isn't quite enough
                ^^^^^^^^^^^^^ 1Gb+4Mb.
> if you have 4GB RAM.
>
> --
> Peter Jeremy



attachment0 (194 bytes) Download Attachment
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Chuck Swiger-2
In reply to this post by Mike Jakubik
Mike Jakubik wrote:
[ ... ]
> Why are the limits so low by default? In any case, this is what i found
> in LINT.
>
> options         MAXDSIZ=(1024UL*1024*1024)
> options         MAXSSIZ=(128UL*1024*1024)
> options         DFLDSIZ=(1024UL*1024*1024)
>
> I have no idea what those values mean, what should i set them to to be
> safe? A limit 768MB should work for me.

512MB is more than enough for almost all processes to run just fine, and is
only really inappropriate for the case where you've got 1-plus GB of physical
RAM and want to dedicate the system to a single large task, or perhaps a
single-digit number of processes if you've got several GB of physical RAM.

--
-Chuck

PS: On the other hand, as time passes and 64-bit hardware with 4+ GB of RAM
becomes more common, this 512MB limit starts resembling the "640K is more than
enough" comment from years past.  Core memory used to cost a dollar a bit,
rather than a dollar a megabit today, but that was before my time... :-)
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Gót András
Hi,

IMHO on servers 2G is common these these days, and 4G will be common very soon. As people use the
websites and the internet more, the needed resources are growing fast. This 512MB will be soon
revised imho. :)

For example I run a little hosting server for my company and 2 years ago a 2,4Ghz xeon was more than
enough. This year I had to replace that machine with an X2100 with 2G RAM because of the customer
needs. The number of the pages hadn't grown significally, just the number of served pages and of
course the mysql databases.

Andras


Chuck Swiger wrote:

> Mike Jakubik wrote:
> [ ... ]
>> Why are the limits so low by default? In any case, this is what i
>> found in LINT.
>>
>> options         MAXDSIZ=(1024UL*1024*1024)
>> options         MAXSSIZ=(128UL*1024*1024)
>> options         DFLDSIZ=(1024UL*1024*1024)
>>
>> I have no idea what those values mean, what should i set them to to be
>> safe? A limit 768MB should work for me.
>
> 512MB is more than enough for almost all processes to run just fine, and
> is only really inappropriate for the case where you've got 1-plus GB of
> physical RAM and want to dedicate the system to a single large task, or
> perhaps a single-digit number of processes if you've got several GB of
> physical RAM.
>
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Mike Jakubik
In reply to this post by Chuck Swiger-2
Chuck Swiger wrote:
> 512MB is more than enough for almost all processes to run just fine,
> and is only really inappropriate for the case where you've got 1-plus
> GB of physical RAM and want to dedicate the system to a single large
> task, or perhaps a single-digit number of processes if you've got
> several GB of physical RAM.
>

Such as a database server. I just think it would be nicer if this limit
was dynamically set, based on your configuration. Just like MAXUSERS was
a kernel variable, it is now dynamically set based on your resources.


_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Chuck Swiger-2
Mike Jakubik wrote:
> Chuck Swiger wrote:
>> 512MB is more than enough for almost all processes to run just fine,
>> and is only really inappropriate for the case where you've got 1-plus
>> GB of physical RAM and want to dedicate the system to a single large
>> task, or perhaps a single-digit number of processes if you've got
>> several GB of physical RAM.
>
> Such as a database server.

Yes, exactly.  :-)

Database servers are a rather specialized role which differs in tuning
requirements from normal "general purpose"/"interactive use" workloads
substantially, and it is common for databases to work much better after the
system has been tuned appropriately.

This is partially because many databases want to do their own filesystem
management and control their own VM/paging behavior, which are uncommon
requirements.

> I just think it would be nicer if this limit
> was dynamically set, based on your configuration. Just like MAXUSERS was
> a kernel variable, it is now dynamically set based on your resources.

Yes, well, autotuning is nice but sometimes there isn't an "obviously correct
value" for this limit which is appropriate for all circumstances.

Anyone doing virtual hosting needs to keep this kind of thing under tighter
control, for example.  Setting the value lower is beneficial for some cases
because it prevents memory leaks in C code or the system libraries, or bloated
Java VM's, etc, from stealing too many resources from other processes.

--
-Chuck
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Paul Allen-3
In reply to this post by Mike Jakubik
>From Mike Jakubik <[hidden email]>, Sun, Jul 09, 2006 at 05:53:38PM -0400:
> Mathieu Arnold wrote:
> >| Exactly, its nice being able to see the current values. How else can i
> >| see what the values are set to?
> >
> >As I previously said, it's 512M on i386, and 1G on 64 bit platforms.
> >
> >  
>
> Right, this explains why my amd64 system works just fine. Shouldn't this
> be a dynamic value based on total amount of ram? Say 70% of RAM, I mean
> servers have plenty of RAM nowadays, this seems like an old hard limit,
> which many new users will trip over.... I set my value to 805306368, and
> mysql seems to be happy now.
>

maxdsiz sets the largest brk(2) allocation.  mmap(2) allocations are made
from virtual addresses above maxdsiz.

Raising maxdsiz squeezes mmap and lowering it squeezes brk.

The system allocator (phkmalloc) uses the brk system call to get memory--as
do many other allocators but notable not ptmalloc2 or the new 7.x jmalloc

IMO, the kernel is just being obnoxious here.  It would be better if the mmap
address space allocation algorithm just tried to work from the stack down
(growing toward the brk where-ever it happened to be).

What is old and out of date is that the amount of physical RAM has caught up
to the size of the virtual address space of a process.  Historically, it
was cheap/free to use a boundary like this.  Now i386 is on its last legs and
thus the wastefulness (in exchange for simplicity) has a noticable cost.


_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"
Reply | Threaded
Open this post in threaded view
|

Re: MySQL and default memory limits (mysqld: Out of memory)

Paul Allen-3
In reply to this post by Mike Jakubik
>From Mike Jakubik <[hidden email]>, Mon, Jul 10, 2006 at 01:16:47PM -0400:
> Chuck Swiger wrote:
> >512MB is more than enough for almost all processes to run just fine,
> >and is only really inappropriate for the case where you've got 1-plus
> >GB of physical RAM and want to dedicate the system to a single large
> >task, or perhaps a single-digit number of processes if you've got
> >several GB of physical RAM.
> >
>
> Such as a database server. I just think it would be nicer if this limit
> was dynamically set, based on your configuration. Just like MAXUSERS was
> a kernel variable, it is now dynamically set based on your resources.
This is nonsense.  maxdsiz is not related to rlimit per se.

There is no way to examine a system's hardware to pick this value.

The proper value depends entirely on whether your programs place more load
on brk or mmap.





>
>
> _______________________________________________
> [hidden email] mailing list
> http://lists.freebsd.org/mailman/listinfo/freebsd-stable
> To unsubscribe, send any mail to "[hidden email]"
_______________________________________________
[hidden email] mailing list
http://lists.freebsd.org/mailman/listinfo/freebsd-stable
To unsubscribe, send any mail to "[hidden email]"