MySQL Bugs: #42202: Unacceptable tablespace growth when doing rapid BLOB updates

Bug #42202	Unacceptable tablespace growth when doing rapid BLOB updates
Submitted:	19 Jan 2009 16:35	Modified:	26 May 2010 17:51
Reporter:	John Embretsen	Email Updates:
Status:	Unsupported	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S3 (Non-critical)
Version:	6.0.10-falcon-team-bzr	OS:	Any
Assigned to:	Kevin Lewis	CPU Architecture:	Any
Tags:	F_BLOB

Description:
When updating relatively large blobs in rapid succession the default Falcon user tablespace file (falcon_user.fts) may grow unacceptably large, given the current default value of the server option falcon_scavenge_schedule (default value is "15,45 * * * * *"), i.e. every 30 seconds.

The current Falcon scavenger prunes old records if either

 a) there is a scheduled scavenge cycle (occurs every 30 seconds by default)

or

 b) the incremental increase in record cache usage (since the last Scavenge Cycle) is greater than 2% of the cache. This is checked every 64 record allocations.

For details, see Bug#41870 and http://lists.mysql.com/falcon/388

Note that with an example BLOB of approximately 1 MB, the record cache increases by 162 bytes per update (even if the data itself is much larger), thus requiring almost 33000 updates in order to reach 2% (5 MB) of the default 250 MB record cache (load-based scavenging).

Until Falcon's BLOB performance is increased, however, this issue is probably not likely to occur often in a real-world scenario. On a traditional disk-based system, updating a 4 MB BLOB in Falcon may currently take as long as 15 seconds, so the schedule-based scavenging will help out in the meantime.

A work-around is to instruct the scavenger to run more often, e.g. every 5 seconds:

falcon_scavenge_schedule=1,6,11,16,21,26,31,36,41,46,51,56 * * * * *

Some kind of BLOB-specific scavenge cycle triggering may be needed to fully address this issue.

How to repeat:
Start the MySQL server, allowing relatively large blobs, e.g:

./sql/mysqld \
 --no-defaults
 --basedir=/path/to/mysql-6.0-falcon
 --language=/psth/to/mysql-6.0-falcon/sql/share/english
 --falcon_debug_mask=65535 
 --max_allowed_packet=5MB

In a mysql client connected to the server, create a table with a BLOB column, insert a 4 MB BLOB and update it a few times. Watch the size of the falcon_user.fts file (in the server's datadir). Tested on Linux, this currently results in a tablespace file of 21 MB.

CREATE TABLE t1 (pk INT AUTO_INCREMENT PRIMARY KEY, myblob LONGBLOB) Engine = Falcon;

INSERT INTO t1 (myblob) VALUES (repeat('a', 4*1024*1024));

DELIMITER //

CREATE PROCEDURE repeatBlobUpdate(reps INT)
 BEGIN
   SET @n = 1;
   REPEAT
     UPDATE t1 SET myblob = (repeat('b', 4*1024*1024)) WHERE pk = 1;
     SET @n = @n + 1;
   UNTIL @n > reps
   END REPEAT;
 END
 
DELIMITER ;

CALL repeatBlobUpdate(10);

Suggested fix:
Consider doing scavenging of old records more often in scenarios with many rapid BLOB updates and inserts.

I must disagree with the proposed triage values.  As John noted; "this issue is probably not likely to occur often in a real-world scenario. On a traditional disk-based system, updating a 4 MB BLOB in Falcon may currently take as long as 15 seconds, so the schedule-based scavenging will help out in the meantime."

I think the workaround of increasing the frequency of scavenging is acceptable.  The file size does not grow unbounded like it used to, even with default values fro scavenge schedule.  A slightly more frequent schedule will mostly alleviation this problem.  And if there is a lot of other activity going on in the engine besides updating blobs, the load based scavenge will kick in as well.  

I do not think this is an issue that must be fixed before beta.

A suggested fix is to add a third way to signal the scavenger.
Frist way is the scheduler. Second is load-based upon the number and size of records in the record cache.  The third would be the size or number of blob pages added.

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

3012 John H. Embretsen	2009-02-10
      New test related to Bug#41870 and Bug#42202, verifying that scheduled scavenging of old record versions in Falcon works with BLOBs, i.e. that the tablespace does not grow indefinitely when updating a BLOB multiple times.
      
      This test is perhaps more of a functional test of timing (schedule) based scavenging 
      related to BLOB updates than a real regression test for bug 41870 (Unbounded 
      tablespace growth when updating BLOB record). This is because in order to be as 
      timing-independent as possible, the test requires a fixed non-default value of 
      the option falcon_scavenge_schedule (currently set to 'every 5 seconds').
      
      On a standard disk based system the test may take a long time (~2 min on a relatively
      fast server with backround load) to run, and requires 30-50 MB of space, so this is
      a --big-test. In memory (--mem) the test should normally run in about 6-7 seconds.

New related test falcon.falcon_blob_space-big committed, see http://lists.mysql.com/falcon/505

A test was added for Bug#41870 which is the precursor to this one.  That test test succeeds now with scavenging every 5 seconds.  Once the this bug is fixed, the test can be updated to use default settings.

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)