MySQL Bugs: #42510: Falcon: Default memory parameters must be changed

Bug #42510	Falcon: Default memory parameters must be changed
Submitted:	1 Feb 2009 9:33	Modified:	15 May 2009 13:00
Reporter:	Christopher Powers	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Server: Falcon storage engine	Severity:	S2 (Serious)
Version:	6.0.9	OS:	Any
Assigned to:	Christopher Powers	CPU Architecture:	Any
Tags:	F_MEMORY

Description:
The default Falcon memory parameters are:

falcon_record_memory_max = 250 MB
falcon_page_cache_size = 4 MB
falcon_record_scavenge_threshold = 67% (of record memory max)
falcon_record_scavenge_floor = 50% (of scavenge threshold)
falcon_record_chill_threshold = 5 MB
falcon_index_chill_threshold = 4 MB

I propose the following changes:

falcon_record_memory_max = 250 MB
falcon_page_cache_size = 250 MB
falcon_record_scavenge_threshold = 90% (of record memory max)
falcon_record_scavenge_floor = 80% (of scavenge threshold)
falcon_record_chill_threshold = 5 MB
falcon_index_chill_threshold = 4 MB

Reasoning:

1. Increase the page cache to 250 MB.

A properly sized page cache can make a tremendous difference in performance. For example, on Xeno the testcase for Bug #36442 required 5 hours to complete with a 4 MB page cache, but less than 5 *minutes* with a page cache of 500 MB.

Note that the page cache size is fixed, and that the page cache is pre-allocated during engine initialization. The record cache is not pre-allocated, and grows up to the record memory max.

2. Increase record scavenge threshold to 90%.

Load-based scavenging now ensures a better response to rapid filling of the record cache, so we can afford to operate closer to capacity, keeping more records in cache--a good thing.

3. Increase record scavenge floor to 80%.

This will reduce the cost of scavenging by avoiding unnecessarily deep scavenges. A higher floor will also keep more records in cache.

A final note: Backlogging is enabled by scavenging, but the backlog operation is only triggered by transaction chill operation. So, for now, no matter how desperately a backlog is needed, it will not happen unless a transaction exceeds the chill threshold. I am revisiting this policy.

How to repeat:
Run the testcase for Bug#36442 (omit the TABLE OPTIMIZE) with the default Falcon parameters.

Exit mysqld, change falcon_page_cache_size = 250MB

Re-run the test. It will complete within minutes rather than hours.

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

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

2994 Christopher Powers	2009-02-01
      Bug #42510, "Falcon: Default memory parameters must be changed"
      
      Updated Falcon options tests.

patch approved.  Please note that 80% of the record cache threshold of 90% is actually 72% of the record cache.  In my opinion, 18% of cache is still a lot of records to scavenge.  But it is much netter than before.  

I would also like to point out that the record-cache-floor aught to have a more intuitive meaning, reflecting the percentage of falcon-record-memory-max.  The downside to this proposed change in meaning is that there must be extra precautions against setting the floor as high or higher than the threshold.  The current setting protects that automatically by having an upper limit of 90.  If there is a consensus to change the meaning of falcon-record-scavenge-floor, it can be done in another bug.

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)

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