MySQL Bugs: #27039: out of memory-crash on innodb shutdown when using file-per-table with many table

Bug #27039	out of memory-crash on innodb shutdown when using file-per-table with many table
Submitted:	11 Mar 2007 23:51	Modified:	19 Apr 2007 14:21
Reporter:	Anders Henke	Email Updates:
Status:	No Feedback	Impact on me:	None
Category:	MySQL Server: InnoDB storage engine	Severity:	S3 (Non-critical)
Version:	5.0.26	OS:	Linux (Linux)
Assigned to:	Assigned Account	CPU Architecture:	Any
Tags:	qc

Description:
Hi,

I'm using MySQL 5.0.26 (static binary tarball release "mysql-standard-5.0.26-linux-i686" from mysql.com); after setting the file-per-table option and altering a lot of innodb tables, about 200k of tables have been migrated from shared space to seperate file space successfully.

However, after issuing the shutdown command for a graceful restart to temporarily recover from bug #22017, mysql/innodb tries to grab some memory and runs into a segfault, reproducable.

How to repeat:
-Configure file-per-table
mysql> alter table foo type=innodb
to migrate from shared to seperate space for a large amount of tables.
In my installs, this varies between 75k and 230k of distinct tables.

Suggested fix:
:-)

MySQL error log with various (similar) stack traces

Attachment: traces.gz (application/x-gzip, text), 2.89 KiB.

Typical stack trace, resolved by resolve_stack_dump:

0x80a0a12 handle_segfault + 430
0x82fb4e8 pthread_sighandler + 184
0x8271ce9 ut_malloc_low + 249
0x8271d72 ut_malloc + 22
0x8270ae7 mem_area_alloc + 471
0x827013f mem_heap_create_block + 103
0x8270291 mem_heap_add_block + 197
0x81cbeef row_sel_store_mysql_rec + 967
0x81cec76 row_search_for_mysql + 8674
0x8141264 general_fetch__11ha_innobasePcUiUi + 72
0x81414ff rnd_next__11ha_innobasePc + 95
0x8132819 rr_sequential__FP14st_read_record + 125
0x80f09c0 sub_select__FP4JOINP13st_join_tableb + 200
0x80f06c0 do_select__FP4JOINPt4List1Z4ItemP8st_tableP9Procedure + 492
0x80e218f exec__4JOIN + 5059
0x80e2677 mysql_select__FP3THDPPP4ItemP13st_table_listUiRt4List1Z4ItemP4ItemUiP8st_orderT7T5T7UlP13select_resultP18st_select_lex_unitP13s + 887
0x80dea51 handle_select__FP3THDP6st_lexP13select_resultUl + 225
0x80b2975 mysql_execute_command__FP3THD + 1377
0x80b944a mysql_parse__FP3THDPcUi + 286
0x80b0e54 dispatch_command__F19enum_server_commandP3THDPcUi + 1876
0x80b06f3 do_command__FP3THD + 195
0x80afc84 handle_one_connection + 764
0x82f8c9c pthread_start_thread + 220
0x83412ea thread_start + 4
New value of fp=(nil) failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow 
instructions on how to resolve the stack trace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do 
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd->query at 0x974d6c68  is invalid pointer
thd->thread_id=59402

After removing off the file-per-table option, the crashes lasted. After converting the tables back into the shared space (via ALTER TABLE foo TYPE=InnoDB for each table), the crashes vanished.

This has been reproduced on multiple servers during the last few days/weeks.

Thank you for a problem report. 200K of tables is a lot. Please, send your my.cnf and the results of

free

before you start a shutdown.

The used my.cnf can be found in http://bugs.mysql.com/bug.php?id=22017, the "free" output in a "graphical" representation in the attached graph rdb225-mem.gif.

Sorry, but rdb2250-mem.gif (from the referenced bug) doesn't give the full details; but in short, before a shutdown there are one or two hundred MB left, usually enough memory for most things one would require for a clean shutdown.

Anders,

InnoDB has allocated almost 2 GB of memory, and it cannot allocate about 1 MB more. Looks like this is a perfectly ordinary case of running out of memory. 

I replied to Bug #22017, too. There the problem seems to be that you have > 100,000 tables, which causes InnoDB's internal data dictionary to exhaust the memory.

Regards,

Heikki

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".