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: | |
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
[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 à %H:%i:%s"),numreponse,signature,DATE_FORMAT(edit,"%d-%m-%Y à %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".