Changes in Twitter MySQL 5.5.28.t8

Earlier this week we pushed to Github the eighth iteration of Twitter MySQL. Here are some of the highlights. But, before that, a quick plug. If you are looking for a new opportunity and enjoy working on database internals, we should talk!

Bugs Fixed

• Bug#67433: Using SET GLOBAL SQL_LOG_BIN should not be allowed
  Earlier in MySQL 5.5 development cycle, the SQL_LOG_BIN variable was made to be both global and session-scoped, instead of only session as it was in previous releases. We believe that usage of SQL_LOG_BIN at the global scope can be quite dangerous and offers little to no benefit, hence we made SQL_LOG_BIN once again a session-only variable, generating an error if it is used with SET GLOBAL.
• Bug#67476: Innodb_buffer_pool_read_ahead_evicted is inaccurate
  If a page being read into the buffer pool is made "young" (moved to the head of the LRU), its time of first access was not being properly set. When the page eventually gets evicted, it would be incorrectly counted as a read-ahead page that was evicted without having been accessed. This lack of access time could also affect linear read-ahead, potentially causing InnoDB to calculate multiple times whether to trigger read-ahead whenever the page was accessed.

Functionality Added or Changed

• Increased time resolution of expire_logs_days
  In some scenarios, it might be necessary to purge the binary logs more frequently than once a day. In these cases, expire_logs_days cannot be used for automatic binary log file removal because of its whole-day resolution. In order to allow for more fine-grained control over the automatic removal period, the type of the expire_logs_days variable was changed to decimal where the fractional part represents the fraction of one day. For example, 12 hours can be expressed as 0.5 day.
• Thread state is "query end" while blocked writing to binary log
  When binary logging is enable, the thread state is set to "query end" most recently before writing to the binary log, so any time taken in that (due to contention or I/O) shows up as "query end", which makes it difficult to determine what is the current state of the thread.
  
  This change introduces new and more appropriate thread states that are set in any binary log related activities that may take time. For example, now whenever an event is being writing to the binary log, the thread state is set to "Writing an event to the binary log".
• Expose the level of each B-tree page in the buffer pool
  Although the INNODB_BUFFER_PAGE table shows the number of records within a page, it is not clear whether these records are row data or internal b-tree pointers. For this reason, a new LEVEL column is introduced which shows the level of a page in the B-tree.
  
  Additionally, the direction of insertion encoded in InnoDB data pages is now available in the DIRECTION column, and the number of consecutive inserts in that direction is available in the N_DIRECTION column. These columns are useful to determine whether records are being inserted in an ascending or descending sequence.
• Exposed the index fill factor as a configurable setting
  When extending a clustered index to the left or right, InnoDB attempts to fill leaf pages only up to a certain percentage (known as the fill factor), at which point the page is split. The remaining space is reserved for updating rows belonging to the page, attempting to make it likely that an updated row will be stored on the same page. This change introduces a new variable name innodb_index_fill_factor that can be used to control the fill factor for clustered indexes. Its value specifies the percentage of space on a leaf page to be used, reserving the remainder as free space.
• A non-blocking mode for ALTER TABLE
  While an alter table is executing, writes to the table whose definition is being changed are blocked until the operation completes or a lock wait timeout occurs. In a sharded system, it would be better if conflicting operations were aborted rather than blocked, thereby eliminating delays due to lock waits and allowing them to be redirect to another shard.
  
  In order to allow for this behavior, this change introduces a new alter table option named NO_WAIT. When set, conflicting locks requests that would normally block are aborted instead.

For a more complete look at what's new in this version, please see the change history and documentation. Feedback, bug reports, etc, can be submitted directly to the issue tracker.