MySQL Bugs: #35755: Cannot create index on Falcon table(record memory exhausted)

Bug #35755	Cannot create index on Falcon table(record memory exhausted)
Submitted:	1 Apr 2008 23:46	Modified:	26 May 2010 17:46
Reporter:	Vladislav Vaintroub	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S1 (Critical)
Version:	6.0	OS:	Any
Assigned to:	Christopher Powers	CPU Architecture:	Any
Tags:	F_ONLINE ALTER

Description:
Falcon seems to have problems with moderate/large amounts of data.
I cannot "create index" on a table with 380 MB data (it is 64bit machine with 4GB RAM and 4GB pagefile). 

The error is "ERROR 1296 (HY000): Got error 305 'record memory is exhausted' from Falcon" with falcon_record_memory_max is set to 300MB.

How to repeat:
1)Create large data file(10 mio uuids). On Windows, I use uuidgen,that comes with  Visual Studio or Windows SDK. 

uuidgen -n10000000 -oC:\tmp\uuids.txt

2)From mysql client

use test;
drop table if exists t;
create table t(uid char(38)) engine=falcon;
load data infile 'C:/tmp/uuids.txt' into table t;
create index t_i on t(uid);

"create index" terminates with the error described above.

hakan@lu0011:~$ more /home/hakan/work/mysql/falcon/ten_mio_rows.php
<?php
for ($i = 0; $i < 10000000; $i++) {
    echo md5($i) . "\n";
}
?>

[12:43] root@test>create table t(uid char(32)) engine=falcon;
Query OK, 0 rows affected (0.07 sec)

[12:43] root@test>load data infile '/home/hakan/work/mysql/falcon/ten_mio_rows.txt' into table t;
Query OK, 10000000 rows affected (1 min 18.70 sec)
Records: 10000000  Deleted: 0  Skipped: 0  Warnings: 0

[12:44] root@test>create index t_i on t(uid);
ERROR 1296 (HY000): Got error 305 'record memory is exhausted' from Falcon

The problem is partially, but not completely, addressed in recent push.  Two problems were fixed. One was  failure to do regular auto-commits when doing a table re-creation as part of "create index".  The other was a change in a forced record scavenge to savenge everything available, regardless of age.

There is still fragmentation in the record memory pool during index rebuild that is prevent this case from working correctly.  This is be addressed in the next beta release.

changing "assigned" to Jim, as he's already on it.

Chris, can you retest this 'out of memory' situation once Online Add Index is available?  There is still a lot of talk about not being able to load large tables.  Do we still have crashes, ect.

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/63302

2959 Christopher Powers	2009-01-14
      Bug #35755, "Cannot create index on Falcon table(record memory exhausted)"
      
      CREATE INDEX for large tables can fail during Table::populateIndex() because
      the Scavenger is blocked by syncSysDDL.
      
      Eliminated dependency of Scavenger on syncSysDDL:
      
      1. Moved updateCardinalities() out of the Scavenger to a separate thread.
      2. For load-based (unscheduled) scavenges, don't commit pending system transactions.
      3. For scheduled scavenges, check syncSysDDL.isLocked() before committing pending system transactions

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/63315

2960 Christopher Powers	2009-01-14
      Bug #35755, "Cannot create index on Falcon table(record memory exhausted)"
      
      Revisions per code review

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/63319

2960 Christopher Powers	2009-01-15
      Bug #35755, "Cannot create index on Falcon table(record memory exhausted)"
            
      CREATE INDEX for large tables can fail during Table::populateIndex() because
      the Scavenger is blocked by syncSysDDL.
            
      Resolved by eliminating the dependency of Scavenger on syncSysDDL.

Pushed into 6.0.10-alpha (revid:alik@sun.com-20090211182317-uagkyj01fk30p1f8) (version source revid:cpowers@mysql.com-20090115075232-vzt1b7z8xrvto8f2) (merge vers: 6.0.10-alpha) (pib:6)