MySQL Bugs: #19494: Out of memory according to error log (but not..)

Bug #19494	Out of memory according to error log (but not..)
Submitted:	2 May 2006 21:29	Modified:	28 Jan 2007 11:05
Reporter:	P-O Yliniemi	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server	Severity:	S1 (Critical)
Version:	5.0.20	OS:	Other (OpenBSD 3.9)
Assigned to:		CPU Architecture:	Any

Description:
from the mysql error log:

060426  6:04:09 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
060427  6:04:10 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
060428  6:04:11 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
060429  6:04:12 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
060430  6:04:14 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
060501  6:04:15 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
060502  6:04:17 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

really odd that this occur exactly the same time every day...
mysqld is automatically restarted after this, with the warning about high memory usage:

060502  6:04:17 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
mysqld got signal 11;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=402653184
read_buffer_size=2093056
max_used_connections=143
max_connections=500
threads_connected=131
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2439212 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.

060502 06:04:17  mysqld restarted
060502  6:04:17  InnoDB: Started; log sequence number 0 43685
060502  6:04:18 [Note] /usr/local/libexec/mysqld: ready for connections.
Version: '5.0.20'  socket: '/tmp/mysql.sock'  port: 3306  Source distribution

The system has 4GB RAM, of which 2.5GB was unused when mysqld got the problem (for debugging, I have set up logging of the first lines of 'top' every minute):

Before the crash:
load averages:  0.72,  0.91,  0.75    06:04:01
24 processes:  1 running, 22 idle, 1 on processor

Memory: Real: 174M/706M act/tot  Free: 2535M  Swap: 0K/16G used/tot

  PID USERNAME PRI NICE  SIZE   RES STATE    WAIT     TIME    CPU COMMAND
27732 _mysql     2    0  514M  166M sleep    poll    25:35  4.93% mysqld

After the crash:
load averages:  0.79,  0.89,  0.75    06:05:01
24 processes:  1 running, 22 idle, 1 on processor

Memory: Real: 107M/637M act/tot  Free: 2604M  Swap: 0K/16G used/tot

  PID USERNAME PRI NICE  SIZE   RES STATE    WAIT     TIME    CPU COMMAND
11455 _mysql     2    0  455M   99M sleep    poll     0:02  4.54% mysqld

(I restarted mysqld before midnight prior to this crash, and that seems not to do any difference - mysqld crashes and restarts exactly 06:04 every morning)

How to repeat:
I really don't know what to do to stop it from crashing..

As far as I remember, *BSD systems have 512MB per process memory limitation by default. This may apply to OpenBSD. Please, check. It can be changed.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

Do you have a dump script running at 0600 ?  We see the same problem I think its related to max thread buffer size

Yes, it was a cron-job that caused the out-of-memory crash. Not a database dump, but some stupid enormous cleanup-query, if I remember it right.

Thank you for the report.

Please could you try using current version?

I use OpenBSD 3.8, mysql-5.0.27
Have the same problem.

Please provide output of the command `ulimit -Ha`

There is no ulimit in OpenBSD (see OS: OpenBSD 3.9)

this is /etc/loging.conf which provide all nececarry output of linux ulimit
mysql:\
       :datasize=infinity:\
       :maxproc=infinity:\
       :openfiles-cur=nfinity:\
       :openfiles-max=infinity:\
       :stacksize-cur=32M:\
       :localcipher=blowfish,8:
       :tc=default:

this is output of limit (BSD ulimit)
~% id
uid=502(_mysql) gid=502(_mysql) groups=502(_mysql)
~% limit
cputime      unlimited
filesize     unlimited
datasize     524288 kbytes
stacksize    4096 kbytes
coredumpsize unlimited
memoryuse    200792 kbytes
descriptors  164
memorylocked 67441 kbytes
maxproc      128

Thank you for the output.

Looks like you should increase memoryuse value.

If you find such memory usage is could be result of mysql bug, provide query and output of SHOW TABLE STATUS and SHOW CREATE TABLE statements for all tables from the query.

Now, after upgrading, the "out of memory" messages reappear in the log every now and then randomly:

061124 16:15:20 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
061124 18:42:33 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
061124 20:32:41 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
061124 21:55:58 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
061124 23:04:43 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
061125 10:49:52 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
061125 11:46:35 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
061125 13:42:54 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
061125 22:19:34 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space
061126 21:26:51 [ERROR] Out of memory; check if mysqld or some other process uses all available memory; if not, you may have to use 'ulimit' to allow mysqld to use more memory or you can add more swap space

According to 'top', there was 2054M free RAM, and 0K/16G used swap at the time of the last error.

# su - _mysql
$ ulimit -Ha
time(cpu-seconds)    unlimited
file(blocks)         unlimited
coredump(blocks)     unlimited
data(kbytes)         1048576
stack(kbytes)        32768
lockedmem(kbytes)    unlimited
memory(kbytes)       unlimited
nofiles(descriptors) 13196
processes            4087

>really odd that this occur exactly the same time every day...

Please provide query causes error.

You can find it in general query log.

i use another server with OpenBSD 4.0 and mysql 5.0.24a, the same bug.
I create only one databse and load some data on it.

this is my ulimit -Ha
time(cpu-seconds)    unlimited
file(blocks)         unlimited
coredump(blocks)     unlimited
data(kbytes)         1048576
stack(kbytes)        32768
lockedmem(kbytes)    249968
memory(kbytes)       249968
nofiles(descriptors) 1024
processes            256

SHOW TABLE STATUS from database mysql:
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------------------------------------------------------------------+
| Name                      | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length    | Index_length | Data_free | Auto_increment | Create_time         | Update_time         | Check_time | Collation       | Checksum | Create_options | Comment                                                             |
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+---------------------------------------------------------------------+
| columns_priv              | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 227994731135631359 |         1024 |         0 |           NULL | 2006-12-06 22:42:38 | 2006-12-06 22:42:38 | NULL       | utf8_bin        |     NULL |                | Column privileges                                                   | 
| db                        | MyISAM |      10 | Fixed      |    2 |            438 |         876 | 123286039799267327 |         4096 |         0 |           NULL | 2006-12-06 22:42:38 | 2006-12-06 22:42:38 | NULL       | utf8_bin        |     NULL |                | Database privileges                                                 | 
| func                      | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/func.MYD' not found (Errcode: 9)                      | 
| help_category             | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/help_category.MYD' not found (Errcode: 9)             | 
| help_keyword              | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/help_keyword.MYD' not found (Errcode: 9)              | 
| help_relation             | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/help_relation.MYD' not found (Errcode: 9)             | 
| help_topic                | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/help_topic.MYD' not found (Errcode: 9)                | 
| host                      | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 109775240917155839 |         1024 |         0 |           NULL | 2006-12-06 22:42:38 | 2006-12-06 22:42:38 | NULL       | utf8_bin        |     NULL |                | Host privileges;  Merged with database privileges                   | 
| proc                      | MyISAM |      10 | Dynamic    |    0 |              0 |           0 |    281474976710655 |         1024 |         0 |           NULL | 2006-12-06 22:42:38 | 2006-12-06 22:42:38 | NULL       | utf8_general_ci |     NULL |                | Stored Procedures                                                   | 
| procs_priv                | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 239253730204057599 |         1024 |         0 |           NULL | 2006-12-06 22:42:38 | 2006-12-06 22:42:38 | NULL       | utf8_bin        |     NULL |                | Procedure privileges                                                | 
| tables_priv               | MyISAM |      10 | Fixed      |    0 |              0 |           0 | 239535205180768255 |         1024 |         0 |           NULL | 2006-12-06 22:42:38 | 2006-12-06 22:42:38 | NULL       | utf8_bin        |     NULL |                | Table privileges                                                    | 
| time_zone                 | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/time_zone.MYD' not found (Errcode: 9)                 | 
| time_zone_leap_second     | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/time_zone_leap_second.MYD' not found (Errcode: 9)     | 
| time_zone_name            | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/time_zone_name.MYD' not found (Errcode: 9)            | 
| time_zone_transition      | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/time_zone_transition.MYD' not found (Errcode: 9)      | 
| time_zone_transition_type | NULL   |    NULL | NULL       | NULL |           NULL |        NULL |               NULL |         NULL |      NULL |           NULL | NULL                | NULL                | NULL       | NULL            |     NULL | NULL           | File './mysql/time_zone_transition_type.MYD' not found (Errcode: 9) | 
| user                      | MyISAM |      10 | Dynamic    |    1 |             48 |          48 |    281474976710655 |         2048 |         0 |           NULL | 2006-12-06 22:42:38 | 2006-12-06 22:42:38 | NULL       | utf8_bin        |     NULL |                | Users and global privileges                                         | 
+---------------------------+--------+---------+------------+------+----------------+-------------+--------------------+--------------+-----------+----------------+----------------

and one of SHOW CREATE TABLE:
mysql> show create table time_zone;
ERROR 29 (HY000): File './mysql/time_zone.MYD' not found (Errcode: 9)

thx for quick answer.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".

I fixed my problem.
in OpenBSD 4.0 works well.

Спасибо.

All reporters:

Please, try to repeat with a newer version, 5.0.27. In case of similar problems, please, send your my.cnf, exact ./configure command line used, and exact steps to repeat this problem. In case of:

"I create only one databse and load some data on it."

please, upload dump of that database and typical queries that lead to this out of memory situation.

No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".