Bug #56340 innodb updates index stats too frequently after non-index updates

• Submitted: 28 Aug 2010 0:16
• Modified: 14 Dec 2010 19:52
• Reporter: Mark Callaghan
• Status: Closed
• Category: MySQL Server: InnoDB Plugin storage engine
• Severity: S5 (Performance)
• Version: 5.1.50
• OS: Any
• Assigned to: Vasil Dimov
• CPU Architecture: Any
• Tags: innodb, statistics, UPDATE

[28 Aug 2010 0:16] Mark Callaghan

Description:
InnoDB increments stat_modified_counter after row updates that don't change indexed columns. I don't think that needs to be done and the current behavior forces index stats to be recollected too soon for workloads that do many updates to non-indexed columns.

Index stats collection isn't free. It is also an occasional bottleneck for us on frequently updates tables that maintain counts.

How to repeat:
read row_update_statistics_if_needed

Suggested fix:
Don't update stat_modified_counter for updates that don't modify indexed columns. This code added to row_update_for_mysql might fix it:

 if (node->is_delete || !(node->cmpl_info & UPD_NODE_NO_ORD_CHANGE)) {
    /* Increment row change counter and possibly update statistics when
   this is a DELETE or an indexed column has changed. */
	
    row_update_statistics_if_needed(prebuilt->table, trx);
  }

[30 Sep 2010 12:50] 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/119530

3618 Vasil Dimov	2010-09-30
      Fix Bug#56340 innodb updates index stats too frequently after non-index updates
      
      This is a simple optimization issue. All stats are related to only indexed
      columns, index size or number of rows in the whole table. UPDATEs that touch
      only non-indexed columns cannot affect stats and we can avoid calling the
      function row_update_statistics_if_needed() which may result in unnecessary I/O.
      
      Approved by:	Marko (rb://466)

[6 Oct 2010 17:56] John Russell

Added to change log:

Improved the performance of UPDATE operations on InnoDB tables, when
only non-indexed columns are changed.

[1 Nov 2010 19:02] Bugs System

Pushed into mysql-5.1 5.1.53 (revid:[e-mail]-20101101184443-o2olipi8vkaxzsqk) (version source revid:[e-mail]-20101101184443-o2olipi8vkaxzsqk) (merge vers: 5.1.53) (pib:21)

[9 Nov 2010 19:45] Bugs System

Pushed into mysql-5.5 5.5.7-rc (revid:[e-mail]-20101109182959-otkxq8vo2dcd13la) (version source revid:[e-mail]-20101109182959-otkxq8vo2dcd13la) (merge vers: 5.5.7-rc) (pib:21)

[13 Nov 2010 16:13] Bugs System

Pushed into mysql-trunk 5.6.99-m5 (revid:[e-mail]-20101113155825-czmva9kg4n31anmu) (version source revid:[e-mail]-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)

[13 Nov 2010 16:40] Bugs System

Pushed into mysql-next-mr (revid:[e-mail]-20101113160336-atmtmfb3mzm4pz4i) (version source revid:[e-mail]-20100629074804-359l9m9gniauxr94) (pib:21)