| Bug #36442 | Falcon: crash during optimize table | ||
|---|---|---|---|
| Submitted: | 30 Apr 2008 21:51 | Modified: | 15 May 2009 13:01 |
| Reporter: | Peter Gulutzan | Email Updates: | |
| Status: | Closed | Impact on me: | |
| Category: | MySQL Server: Falcon storage engine | Severity: | S3 (Non-critical) |
| Version: | 6.0.6-alpha-debug | OS: | Linux (SUSE 10 | 32-bit) |
| Assigned to: | Christopher Powers | CPU Architecture: | Any |
| Tags: | F_MEMORY | ||
[1 May 2008 1:58]
MySQL Verification Team
Thank you for the bug report. 080430 22:47:48 [Note] 6.0\bin\mysqld: ready for connections. Version: '6.0.6-alpha-nt' socket: '' port: 3306 Source distribution 080430 22:55:50 - mysqld got exception 0xe06d7363 ; 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=8388600 read_buffer_size=131072 max_used_connections=1 max_threads=151 thread_count=1 connection_count=1 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 337727 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd: 0x1b6d930 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... 7C812A5B kernel32.dll!RaiseException() 0077A472 mysqld.exe!_CxxThrowException() 006E5FFF mysqld.exe!MemMgr::allocRaw()[memmgr.cpp:811] 006E6E13 mysqld.exe!MemMgr::alloc()[memmgr.cpp:464] 006E6F6B mysqld.exe!MemMgr::allocate()[memmgr.cpp:506] 006E72EE mysqld.exe!MemMgrPoolAllocate()[memmgr.cpp:161] 006EEF43 mysqld.exe!Record::allocRecordData()[record.cpp:926] 006EF707 mysqld.exe!Record::setEncodedRecord()[record.cpp:702] 006EF92F mysqld.exe!Record::Record()[record.cpp:97] 006F1E96 mysqld.exe!Table::allocRecord()[table.cpp:3649] 006F2847 mysqld.exe!Table::databaseFetch()[table.cpp:663] 006F4ECF mysqld.exe!Table::fetchNext()[table.cpp:506] 006F6066 mysqld.exe!Table::optimize()[table.cpp:3578] 006DF3A2 mysqld.exe!StorageTable::optimize()[storagetable.cpp:577] 006DDA01 mysqld.exe!StorageInterface::optimize()[ha_falcon.cpp:686] 00422B3C mysqld.exe!handler::ha_optimize()[handler.cc:3071] 0054FB84 mysqld.exe!mysql_admin_table()[sql_table.cc:4287] 00550299 mysqld.exe!mysql_optimize_table()[sql_table.cc:4525] 00502C4A mysqld.exe!mysql_execute_command()[sql_parse.cc:2794] 00506441 mysqld.exe!mysql_parse()[sql_parse.cc:5752] 00506D63 mysqld.exe!dispatch_command()[sql_parse.cc:1047] 005079C7 mysqld.exe!do_command()[sql_parse.cc:726] 00579379 mysqld.exe!handle_one_connection()[sql_connect.cc:1134] 005DED3B mysqld.exe!pthread_start()[my_winthread.c:86] 0077619C mysqld.exe!_threadstart()[thread.c:196] 7C80B683 kernel32.dll!GetModuleFileNameA() Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd->query at 059F1990=optimize table t thd->thread_id=1 thd->killed=NOT_KILLED The manual page at http://dev.mysql.com/doc/mysql/en/crashing.html contains information that should help you find out what is causing the crash. c:\dbs>
[1 Feb 2009 9:08]
Christopher Powers
A fix for this bug was included in a patch for a related bug: http://lists.mysql.com/commits/64769 2992 Christopher Powers 2009-02-01 Bug #42424, "Serious performance degradation after new scavenger" Increased scavenger wait increment from 10 to 20ms. Use progressive scavenger wait timeout for record allocation methods.
[1 Feb 2009 9:40]
Christopher Powers
Several factors contributed to the failure of the testcase in this bug. 1. Record cache overflow. The primary issue with this bug is that TABLE OPTIMIZE overwhelms the Falcon record cache such that the record scavenger cannot keep up. This is true even for very large (>1GB) cache sizes. The Falcon scavenger has been reworked and is now more responsive, however, the fixes in Bug#42424 further improve the scavenger response, and are necessary for the testcase in this bug to complete successfully. 2. Table::optimize() is very inefficient. Table::optimize() refreshes the record and index cardinality for a table, but does so very inefficiently. The record cardinality is determined by fetching every record in the table. Each fetch loads a record into the record cache, where it remains unscavengeable (useCount > 1) until the transaction completes. This is easily corrected by calling release() for each record fetched. The index cardinality is computed by Index::optimize, which executes a SELECT for each index, like this: SELECT count(*) FROM (SELECT DISTINCT S1 FROM TEST.T) The SELECT fetches each record in the index. If the table is large enough, this will overflow the record cache very quickly. The fix for this is more complex. Index::optimize() must be rewritten to use the IndexWalker class, which obviates the need to fetch any records. However, each segment of the index must be enumerated separately--simply instantiating an IndexWalker and calling ::getNextNode() is not sufficient. Index::optimize() has been disabled until it is rewritten using the IndexWalker. 3. The default Falcon memory parameters must be changed. The default Falcon record cache size is 250MB, but the default page cache size is only 4MB--much too small for memory-intensive operations such as the testcase in this bug. Changing the page cache size from 4MB to 250MB or even 500MB reduces the test run time from hours to minutes. This is addressed in Bug #42510, "Falcon: Default memory parameters must be changed". 4. The record backlog facility is never enabled by the Scavenger. The record backlog is a fallback mechanism triggered by a low-memory condition. The new scavenger did not properly enable record backlogging. This has been corrected in Bug #42505, "Falcon: Record backlogging not enabled by the Scavenger".
[1 Feb 2009 9:53]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/64771 2993 Christopher Powers 2009-02-01 Bug #42510, "Falcon: Default memory parameters must be changed" Bug #36442, "Falcon: crash during optimize table" Increased default page cache size from 4MB to 250MB Increased record scavenge threshold from 67 to 90 Increased record scavenge floor from 50 to 80
[2 Feb 2009 7:00]
Kevin Lewis
I agree that 250Mb page cache is the most appropriate setting when someone is using Falcon. But Vlad has pointed out that it increases the amount of memory that MySQL 6.0 initializes with by 246Mb. This will be a major inconvenience to any MySQL customer that does not plan to use Falcon. Is there any way to make Falcon allocate this page cache in smaller segments as needed? The original default of 4Mb never really got in anyones way. But 250Mb will be noticed.
[2 Feb 2009 14:36]
Kevin Lewis
After reconsideration, I think we can go ahead and use this setting. If a customer is not interested in using Falcon, they can use -loose-skip-falcon at startup. So this memory use is still optional. If customers are using Falcon, the 'memory hog', then I say 'this is what we reccommend'. 'If you don't like it change it'. That said, it would still be less intrusive if the page cache was allocated as needed.
[3 Feb 2009 18:13]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/65037 2999 Christopher Powers 2009-02-03 Bug #36442, "Falcon: crash during optimize table" Refined Table::optimize() to release each base record. Records having newer record versions were not being released.
[10 Feb 2009 22:11]
Bugs System
A patch for this bug has been committed. After review, it may be pushed to the relevant source trees for release in the next version. You can access the patch from: http://lists.mysql.com/commits/65817 3012 Christopher Powers 2009-02-10 Bug #36442, "Falcon: crash during optimize table" Refined Table::optimize() to release each base record. Records having newer record versions were not being released.
[13 Feb 2009 7:25]
Bugs System
Pushed into 6.0.10-alpha (revid:alik@sun.com-20090211182317-uagkyj01fk30p1f8) (version source revid:hky@sun.com-20090203204730-3k0pc0uoxp6wqr1i) (merge vers: 6.0.10-alpha) (pib:6)
[2 Mar 2009 14:13]
Bugs System
Pushed into 6.0.11-alpha (revid:alik@sun.com-20090302140208-lfdejjbcyezlhhjt) (version source revid:vvaintroub@mysql.com-20090211225555-gcuo9fv97xlhydd8) (merge vers: 6.0.10-alpha) (pib:6)
[15 May 2009 13:01]
MC Brown
An entry has been added to the 6.0.10 changelog: The default Falcon memory parameters have been updated. The new settings for all the memory parameters are as follows: falcon_record_memory_max is now 250 MB falcon_page_cache_size is now 250 MB falcon_record_scavenge_threshold is 90% (of record memory max) falcon_record_scavenge_floor is 80% (of scavenge threshold) falcon_record_chill_threshold is 5 MB falcon_index_chill_threshold is now 4MB
Description: I create a Falcon table. I insert and update 400,000 rows. I say "OPTIMIZE TABLE". Crash. How to repeat: /* This will take a while, there are 400,000 rows. */ delimiter // set @@autocommit=0// drop table if exists t// drop procedure if exists p// create procedure p () begin declare v int default 0; declare r int default 0; declare c varchar(100) character set utf32; declare c2 varchar(1) character set utf32; declare ch char(8); create table t (s1 int, s2 varchar(100) character set utf32, s3 text character set utf8) engine=falcon; /* create index i1 on t (s2, s1); */ create index i2 on t (s1); create index i3 on t (s2); while v < 400000 do begin declare continue handler for sqlexception begin end; insert into t values (v,null,null); set ch = hex(v); while length(ch) < 8 do set ch = concat('0',ch); end while; set @x = concat('update t set s2 = repeat(0x',ch,',100) where s1 = ',v-1); if v mod 1000 = 0 then select v,@x; end if; prepare stmt1 from @x; execute stmt1; update t set s3 = s2 where s1 = v - 2; end; set v = v + 1; if v mod 100 = 0 then commit; end if; end while; end// delimiter ; call p(); alter table t modify column s3 varchar(100) CHARACTER SET utf32; optimize table t;