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 Email Updates:
Status: Closed Impact on me:
None 
Category:MySQL Server: InnoDB Plugin storage engine Severity:S5 (Performance)
Version:5.1.50 OS:Any
Assigned to: Vasil Dimov
Tags: innodb, statistics, UPDATE
Triage: Triaged: D3 (Medium) / R2 (Low) / E2 (Low)

[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:build@mysql.com-20101101184443-o2olipi8vkaxzsqk) (version source revid:build@mysql.com-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:sunanda.menon@sun.com-20101109182959-otkxq8vo2dcd13la) (version source revid:sunanda.menon@sun.com-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:alexander.nozdrin@oracle.com-20101113155825-czmva9kg4n31anmu) (version source revid:alexander.nozdrin@oracle.com-20101113152450-2zzcm50e7i4j35v7) (merge vers: 5.6.1-m4) (pib:21)
[13 Nov 2010 16:40] Bugs System
Pushed into mysql-next-mr (revid:alexander.nozdrin@oracle.com-20101113160336-atmtmfb3mzm4pz4i) (version source revid:vasil.dimov@oracle.com-20100629074804-359l9m9gniauxr94) (pib:21)