Bug #19800 MySQL crashes with out of memory error whereas memory is still available
Submitted: 14 May 2006 11:17 Modified: 4 Aug 2006 14:48
Reporter: jocelyn fournier (Silver Quality Contributor) Email Updates:
Status: No Feedback Impact on me:
None 
Category:MySQL Server Severity:S2 (Serious)
Version:5.1.9-beta OS:Linux (Linux)
Assigned to: CPU Architecture:Any

[14 May 2006 11:17] jocelyn fournier
Description:
Hi,

I've just encountered the following crash while testing MySQL 5.1.9-beta :

060514  9:30:48 [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;

key_buffer_size=536870912
read_buffer_size=1044480
max_used_connections=62
max_connections=350
threads_connected=36
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 1598085 K
bytes of memory

Stack trace :

0x81d31a8 handle_segfault + 464
0x4002a825 _end + 931110685
0x858c921 QUICK_RANGE::QUICK_RANGE(char const *, unsigned int, char const *, unsigned int, unsigned int) + 53
0x8284e04 get_quick_keys(PARAM *, QUICK_RANGE_SELECT *, st_key_part *, SEL_ARG *, char *, unsigned int, char *, unsigned int) + 856
0x82849e6 get_quick_select(PARAM *, unsigned int, SEL_ARG *, st_mem_root *) + 318
0x858e697 TRP_RANGE::make_quick(PARAM *, bool, st_mem_root *) + 39
0x827ba40 SQL_SELECT::test_quick_select(THD *, Bitmap<64>, unsigned long long, unsigned long long, bool) + 2512
0x858cc66 SQL_SELECT::check_quick(THD *, bool, unsigned long long) + 82
0x82504ca mysql_delete(THD *, st_table_list *, Item *, st_sql_list *, unsigned long long, unsigned long long, bool) + 1226
0x81eb44b mysql_execute_command(THD *) + 9895
0x81f19aa mysql_parse(THD *, char *, unsigned int) + 458
0x81e76b4 dispatch_command(enum_server_command, THD *, char *, unsigned int) + 1752
0x81e6fc6 do_command(THD *) + 294
0x81e62a9 handle_one_connection + 813
0x40024e51 _end + 931087689
0x401ac8aa _end + 932691874

This never occured with MySQL 5.0. 
According to the server monitoring, there was still 1.6 GB of memory available (and 1.4 GB used), and no swap used at all (so I do not reach the 2GB limitation of a 32 bit system).
I'm using the Linux (x86, glibc-2.2, "standard" is static, gcc) build.

Don't know if this could have an impact but myisam_use_mmap was set to ON.

Regards,
  Jocelyn

How to repeat:
Don't really know. According to the stack, it crashes during a range select, and I have this kind of queries :

CREATE TABLE a (a int, b int, c TEXT, PRIMARY KEY (a,b));
INSERT INTO a (a,b) VALUES (1,1),(1,2),(1,3),(1,4),(2,1),(2,2),(2,3),(2,4),(2,5);
SELECT a,b,c FROM a WHERE a=1 AND b BETWEEN 2 AND 4 LIMIT 20;

where the a= condition could match more than 500000 rows, but b BETWEEN condition should not match more than 40 rows.
[14 May 2006 13:53] Valeriy Kravchuk
Thank you for a problem report. Can you, please, try to repeat with myisam_use_mmap off? SHOW TABLE STATUS resutls for that "a" table can be also useful.

Have you got that 1.4 GB of memory usage after the crash or before it?
[14 May 2006 14:08] jocelyn fournier
Hi,

When MySQL is in a stable state, it consumes 1.4 GB of memory (it was consuming 1.4 GB before the crash, but currently it's also @ 1.4 GB without issue so far).
You can see the memory graph here :

http://forum.hardware.fr/rrd/forum-sql/ 

(the crash occurs at 9h30)

SHOW TABLES STATUS for the table I suspect :

mysql> SHOW TABLE STATUS WHERE Name='threadhardwarefr13';
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+-------------------+---------+
| 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 |
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+-------------------+---------+
| threadhardwarefr13 | MyISAM | 10      | Dynamic    | 8179675 | 328            | 2688742296  | 281474976710655 | 360098816    | 316       | 8402906        | 2005-10-23 04:12:32 | 2006-05-14 16:02:26 | 2006-01-10 01:49:50 | latin1_swedish_ci | NULL     | delay_key_write=1 |         |
+--------------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+---------------------+-------------------+----------+-------------------+---------+
1 row in set (0.00 sec)

(delay_key_write is disabled in the my.cnf)

I'll set myisam_use_mmap to off and see if this happens again.

Regards,
  Jocelyn
[14 May 2006 14:18] Valeriy Kravchuk
According to your graph, 2.2 Gb of RAM were used at the time of crash. Please, send the uname -a results and ulimit -a results for the user running MySQL server. It is possible that you hit process memory limit...
[14 May 2006 14:22] jocelyn fournier
Hi,

The green part of the graph is the free memory, it's the blue one which indicates the memory used :) (and the used memory drop down after the crash which is normal because of the key_buffer of 512 MB).

Regards,
  Jocelyn
[14 May 2006 16:03] jocelyn fournier
About uname and ulimit :

[root@forum-sql] /home/mysql> uname -a                                                                                                                                                                  <18:00:05
Linux forum-sql 2.4.29ayzo-l #2 SMP Fri Mar 11 05:53:16 CET 2005 i686 GNU/Linux
[root@forum-sql] /home/mysql> ulimit -a                                                                                                                                                                 <18:00:08
-t: cpu time (seconds)         unlimited
-f: file size (blocks)         unlimited
-d: data seg size (kbytes)     unlimited
-s: stack size (kbytes)        8192
-c: core file size (blocks)    0
-m: resident set size (kbytes) unlimited
-u: processes                  unlimited
-n: file descriptors           1024
-l: locked-in-memory size (kb) unlimited
-v: address space (kb)         unlimited
-x: file locks                 unlimited
[16 May 2006 11:55] jocelyn fournier
Hi,

No problem so far with myisam_use_mmap set to OFF.
I'm switching it back to ON to see if the crash occurs again.

Regards,
  Jocelyn
[17 May 2006 0:11] jocelyn fournier
Hi,

MySQL crashes again with myisam_use_mmap=ON.
This time there was no out of memory error, and a different stack (but still during TRP_RANGE::make_quick call). (note it crashes with a signal 6).

The new stack :

0x81d31a8 handle_segfault + 464
0x4002a825 _end + 931110685
0x400fe741 _end + 931978809
0x40027a7b _end + 931098995
0x400fe4d4 _end + 931978188
0x400ffa08 _end + 931983616
0x855db28 __default_terminate + 24
0x855db4d __terminate + 29
0x855e606 throw_helper + 582
0x855e7d5 __throw + 149
0x855d93c __builtin_new + 188
0x8284981 get_quick_select(PARAM *, unsigned int, SEL_ARG *, st_mem_root *) + 217
0x858e697 TRP_RANGE::make_quick(PARAM *, bool, st_mem_root *) + 39
0x827ba40 SQL_SELECT::test_quick_select(THD *, Bitmap<64>, unsigned long long, unsigned long long, bool) + 2512
0x822585c get_quick_record_count(THD *, SQL_SELECT *, st_table *, Bitmap<64> const *, unsigned long long) + 80
0x8226a95 make_join_statistics(JOIN *, st_table_list *, Item *, st_dynamic_array *) + 4549
0x8222428 JOIN::optimize(void) + 1460
0x82dbed2 st_select_lex_unit::exec(void) + 766
0x82dadeb mysql_union(THD *, st_lex *, select_result *, st_select_lex_unit *, unsigned long) + 59
0x8221322 handle_select(THD *, st_lex *, select_result *, unsigned long) + 66
0x81ea064 mysql_execute_command(THD *) + 4800
0x81f19aa mysql_parse(THD *, char *, unsigned int) + 458
0x81e76b4 dispatch_command(enum_server_command, THD *, char *, unsigned int) + 1752
0x81e6fc6 do_command(THD *) + 294
0x81e62a9 handle_one_connection + 813
0x40024e51 _end + 931087689
0x401ac8aa _end + 932691874

Regards,
  Jocelyn
[17 May 2006 4:40] Valeriy Kravchuk
How large is that "a" table used in you "range" queries? Can you send the results of 

EXPLAIN SELECT a,b,c FROM a WHERE a=1 AND b BETWEEN 2 AND 4 LIMIT 20;

for the problematic queries?

What if you'll increase number of allowed file descriptors for process, say, to 2048?
[17 May 2006 11:33] jocelyn fournier
Hi,

Here are some infos about the query & tables I suspect (because I already experienced error 12 with it in the past when the MySQL server variable was set too high and I hit the 2GB limit) :

SELECT COUNT(*) FROM Hardwarefr.inscrit;
+----------+
| COUNT(*) |
+----------+
| 423315   |
+----------+

SELECT COUNT(*) FROM threadhardwarefr13;
+----------+
| COUNT(*) |
+----------+
| 8204726  |
+----------+

EXPLAIN SELECT t.id,icone,contenu,DATE_FORMAT(date,"%d-%m-%Y&nbsp;à&nbsp;%H:%i:%s"),numreponse,signature,DATE_FORMAT(edit,"%d-%m-%Y&nbsp;à&nbsp;%H:%i:%s"), pedit,t.nb_quoteurs,password,mesimages_aff,online,pseudo,citation,image_forum,validation,aim,icq,google,jabber,msn,skype,yahoo, homepage,signature_forum,configuration,certif,status,0 FROM threadhardwarefr13 as t USE INDEX (PRIMARY) LEFT JOIN Hardwarefr.inscrit as i USING (id)  WHERE numeropost=4643 AND numreponse BETWEEN 6239508 AND 6425150 ORDER BY numreponse ASC LIMIT 41;
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------+
| id | select_type | table | type   | possible_keys | key     | key_len | ref                   | rows | Extra       |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------+
| 1  | SIMPLE      | t     | range  | PRIMARY       | PRIMARY | 7       |                       | 57   | Using where |
| 1  | SIMPLE      | i     | eq_ref | PRIMARY       | PRIMARY | 3       | forum_hardwarefr.t.id | 1    |             |
+----+-------------+-------+--------+---------------+---------+---------+-----------------------+------+-------------+

I'll try again with a high number of file descriptors.

  Jocelyn
[18 May 2006 23:40] jocelyn fournier
Hi,

It seems with an increased number of allowed file descriptors, the problem doesn't occur anymore.

  Jocelyn
[19 May 2006 5:27] Valeriy Kravchuk
So, it can be just a configuration issue. Please, reopen this bug report in case of similar out of memory error will appear even with a new value file descriptors available.
[19 May 2006 14:26] jocelyn fournier
Hi,

Is this still expected to have a crash in this kind of condition ?
I would have expect an out of memory error and a failure during the query, but not a server crash.

Thanks,
  Jocelyn
[8 Jun 2006 17:17] Valeriy Kravchuk
Please, try to repeat with a newer version, 5.1.11-beta, and inform about the results.
[12 Jun 2006 8:04] jocelyn fournier
Hi,

Still crashing with 5.1.11-beta.

Stack :

0x81d02a8 handle_segfault + 356
0x40027825 _end + 932353045
0x4014caff _end + 933553903
0x83f4afa mi_mmap_pread + 114
0x83f03a7 _mi_read_static_record + 127
0x83ea79d mi_rkey + 505
0x828141f ha_myisam::index_read(char*, char const*, unsigned int, ha_rkey_function) + 51
0x827c536 handler::read_range_first(st_key_range const*, st_key_range const*, bool, bool) + 150
0x827c3ba handler::read_multi_range_first(st_key_multi_range**, st_key_multi_range*, unsigned int, bool, st_handler_buffer*) + 126
0x826a9d7 QUICK_RANGE_SELECT::get_next() + 259
0x82763e6 rr_quick(st_read_record*) + 26
0x823f92e mysql_delete(THD*, st_table_list*, Item*, st_sql_list*, unsigned long long, unsigned long long, bool) + 1918
0x81e92f7 mysql_execute_command(THD*) + 4663
0x81ef817 mysql_parse(THD*, char*, unsigned int) + 359
0x81e6ccf dispatch_command(enum_server_command, THD*, char*, unsigned int) + 1007
0x81e68a5 do_command(THD*) + 129
0x81e5d9d handle_one_connection + 621
0x40021e51 _end + 932330049
0x401ac8aa _end + 933946522

Regards,
  Jocelyn
[14 Jun 2006 14:55] Jeff C
I am getting this also on 5.1.11-Beta.

With an established server, run "top" and note the VIRT/RES ...

Add in : myisam_use_mmap, and restart mysql.

Then watch "top" and wait for the crash.  Have a few queries running. 

My server segfaults 11 over and over.  Disabling myisam_use_mmap, it returns to being stable.
[4 Jul 2006 14:48] Valeriy Kravchuk
Jeff C:

If you can provide a repeatable test case that will demonstrate incorrent/unexpected usage of memory with myisam_use_mmap=1, please, send it.

Jocelyn: 

I still think that this is not a bug, but a configuration/documentation problem.
[19 Jul 2006 9:11] Vadim Tkachenko
size of mmaped file is included in 2GB limit of memory size.
We can do nothing with that.
Best way - don't use myisam_use_mmap on 32bit boxes.
[4 Aug 2006 23:00] Bugs System
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".