MySQL Bugs: #2136: ALPHA THREADS and the mysql "out of memory" problem

Bug #2136	ALPHA THREADS and the mysql "out of memory" problem
Submitted:	17 Dec 2003 1:08	Modified:	6 Jan 2004 8:57
Reporter:	Are you mortal Then prepare to die.	Email Updates:
Status:	Not a Bug	Impact on me:	None
Category:	MySQL Server	Severity:	S2 (Serious)
Version:	4.0.15a	OS:	dec-osf5.1 V5.1 732 alpha
Assigned to:	Alexander Keremidarski	CPU Architecture:	Any

Description:

See also 

http://www.geocrawler.com/archives/3/8/1997/8/0/51385/

This appears to be a problem on ALPHA machines.

The server spews up an 'out of memory' warning on certain selects.

I would provide more details, but my last bug report was binned by programmers over eager to up their bug clearance rate.

Perhaps the statistics page should include 'complaints against this programmer' and a 'bugs reopened on this programmers ass' columns?

How to repeat:
chia!

Suggested fix:
monty

Can you please explain what do you want to say with this:

> Description:
> See also

> http://www.geocrawler.com/archives/3/8/1997/8/0/51385/

> This appears to be a problem on ALPHA machines.

The URL you posted says:
<quote>

DATE: 08/29/1997 10:20:12
SUBJECT: mysql: Out of memory with 128 MB of memory on DU4.0.

Hi,

I have installed mysql3.21.5-alpha on DU4.0.
All works fine, but on large but simple queries (400.000 records x 15 fields)
MysqlPerl error message report:

mysql3.21.5-alpha is very old MySQL release from 3.21 branch which is not supported anymore. Today supported branches are 3.23, 4.0 and 4.1

You state under Version: 4.0.15a OS: dec-osf5.1 V5.1 732 alpha
4.0.15a is stable release not alpha and is released at during September 2003 - 6 years after posting you refer to.

-alpha suffix has nothing to do with "ALPHA machines"

Stable binaries for Alpha servers are named like:

mysql-4.0.16-dec-osf5.1-alphaev67.tar.gz

Compare to -alpha binary named:
mysql-standard-4.1.1-alpha-dec-osf5.1-alphaev67.tar.gz

-alpha means release is in stage of very early development.

Next.
http://www.geocrawler.com/archives/3/8/1997/8/0/51385/

says that problem is with MysqlPerl, not MySQL!
If I'm not mistaken MysqlPerl refers to deprecated perl module.

"I would provide more details,"

Please do so instead of refering to six years old posting.

One more time.
This bugs tracking system is dedicated to bug reports with Repeatable Test Cases.
If you don't tell us how to reproduce bug we have no way to test and fix it.

OK, let me make it simple for you...

++ Bug Database--

> Can you please explain what do you want to say with this:
> 
>> Description: 
>> See also 
> 
>> http://www.geocrawler.com/archives/3/8/1997/8/0/51385/
> 
>> This appears to be a problem on ALPHA machines.
> 
> The URL you posted says:
> <quote>

I have installed mysql3.21.5-alpha on DU4.0.

DECthreads Last Chance handler: thread 2 exiting on status exception...

Seems to me that the problem is in ALPHA threads library.

> <end of quote>

He uses a true64 dec alpha and reports problems with memory. I am using a true64 dec alpha and finding problems with memory. Further more...

http://www.bitmechanic.com/mail-archives/mysql/Sep1997/0876.html

Another user of DU4.0 with mysql memory problems related to process memory limits. A concidence?

in fact

http://www.google.com/search?hl=en&ie=UTF-8&oe=UTF-8&q=DU4.0+mysql&btnG=Google+Search

turns up many such cases.

> Next.
> http://www.geocrawler.com/archives/3/8/1997/8/0/51385/
> 
> says that problem is with MysqlPerl, not MySQL!
> If I'm not mistaken MysqlPerl refers to deprecated perl module. 

Are you stupid?

> 
> 
> "I would provide more details,"
> 
> Please do so instead of refering to six years old posting.
> 
> One more time.
> This bugs tracking system is dedicated to bug reports with Repeatable Test Cases. 
> If you don't tell us how to reproduce bug we have no way to test and fix it.

try it on a true64-linux dec alpha du4.0

Can you say something more specific instead of calling people stupid please?

You did the same in bug report #2118 
http://bugs.mysql.com/bug.php?id=2118

and even now you claim it is closed. 2118 is put in status "Can't repeat" which means information is not enough yet to decide if it is bug and if yes to investigate and fix it.

Sorry,

I am angry in general. I appologise again. I just feel so frustrated these days. I am sure you are a good programmer and that you take every bug report seriously. I will do my best to do anything you need to help.

My guess is that the problem is to do with the kernel level memory allocation on the true64 machine conflicting with the mysql buffer sizes. I dont think mysql knows how to allocate memory between buffers if they independantly exceed the kernel level limits (imposed on a per process level). 

I am trying to persuade the IT people here to adjust the per process memory allocation to see if the problem can be made to go away. 

Here is the contents of my.cnf - please feel free to critisize me vocally ;)

[mysqld]
 
skip-innodb
skip-bdb
 
socket=/usr/local/mysql-standard-4.0.15a-dec-osf5.1-alphaev67/data/mysql.sock
 
datadir                 = /bioinformatics/Databases
tmpdir                  = /bioinformatics/Databases
log-error               = /bioinformatics/Databases/error.log
log-slow-queries        = /bioinformatics/Databases/slow.log

key_buffer_size         = 1073741824
sort_buffer_size        =  268435456
read_buffer_size        =  268435456
read_rnd_buffer_size    =  268435456

bulk_insert_buffer_size =  268435456
 
net_read_timeout        =        300
net_write_timeout       =        300
slave_net_timeout       =        300
 
This represents my best effort to maximize our performace of the server.

I am sorry for showing my own ignorance in such an unpleasant bug report.

Thanks again,
Dan.

ERROR 5: Out of memory (Needed 16777184 bytes)

The query I am running now simply returns 1 row in set (0.18 sec), but as soon as I run with the INSERT INTO part I get the above. I thought I had made the problem go away by making a series of individual queries, each with smaller results sets, but this shows that something fundamental is going wrong.

Is there any way to find out which buffer is causing this error?

I just tried re-running the query minus my server settings and (ridiculous) key buffer sizes. So far so good, the query is running fine, but a little slow - I can't even find any cpu usage on the server. I guess it is all IO at the moment.

Found it...

584964 mysql     42    0   18M 5767K sleep   0:12  3.60% mysqld

having a nice sleep! Well I am off to bed, any advice on how to avoid this bug?

How do I know how to set my buffer sizes? What would normally happen if you gave mysql a huge buffer size without the hardware? Is the kernel memory limit the  real problem? or is it my maniacal buffer sizes?

Yo dudes!

You might like to know that I made my personal problems go away with the following two commands suggested by IT...

ulimit -d unlimited
ulimit -s unlimited

Which remove default kernel limits on data and stack size. 

I stop mysqld, ulimit the kernel, and then run mysqld again.

The results of ulimit -a are ...

core file size (blocks)     0
data seg size (kbytes)      3145728
file size (blocks)          unlimited
max memory size (kbytes)    4096808
open files                  4096
pipe size (512 bytes)       8
stack size (kbytes)         1048576
cpu time (seconds)          unlimited
max user processes          256
virtual memory (kbytes)     4194304

Cheers my deers

Stan.

If ulimit solves the problem this makes clear there was no MySQL *bug* at all.

OS settings and tunning is beyond the scope of Bugs handling database.

And so if mysql doesnt work with a certain kernel (one which it is supossed to work with) then that isn't your problem right? Why bother developing software at all eh? - if the kernel should handle these things there is no point am I right? In fact lets none of us do anything at all!

you suck

MySQL as any other program depends on settings of your machine.

Now you simply claim it is MySQL *bug* that it can't use more resources than OS and kernel settings provide. 

In my humble opinion ulimits are implemented for reason and they give system administrator way keep resources under control. 

Claiming it is MySQL *bug* that it can't avoid limits set by system administratior is strange to say the least.

With the same extent you can claim that it is MySQL *bug* the fact that it can't allocating more than 1GB memory on machine which has ony 32MB RAM + 32MB swap.

Yes MySQL can't do this. 
No it is not a *bug*

I will not agree this is a bug and I don't believe there is anyone else in the world will agree it is a bug.

If you need advice, help, assistance with configuring kernel parameters and/or MySQL startup parameters there are proper places, proper way and proper language to do it.

If you are just very angry and pissed of there are also proper places to go.

http://bugs.mysql.com is not the place for any of the above.

This is resource for MySQL *bug* reports - reports for problems caused by msitakes in MySQL code.

OK, point taken, but I think the error message should be more informative, stating which buffer caused the problem and why and on which machine the problem occured (client or server). 

To clarify, I think that mysql was not in fact out of memory, it just balked when the kernel said it couldn't have any more, and it didn't know what to do. This is different from physically having no memory left (in my opinion). 

I guess you are saying that mysql always beleives it has the amount of memory that  you tell it it has, but this should not cause problems with small queries.

Additionally, why did the query run fine without the INSERT INTO part, and then fall over when we added the INSERT INTO?

I think all these things add up to perculular behaviour - but if you insist this is mysql out of normal opperating conditions then at least tell me where to find out how to configure mysql server ;)

I recon you will have to face up to this behaviour sooner or later, cos people with alpha machines are going to keep complaining.

just my opinion, 
enjoy