Bug #55716 CHECK TABLES should not perform check on entire adaptive hash index
Submitted: 3 Aug 2010 15:02 Modified: 15 Oct 2010 14:10
Reporter: Inaam Rana Email Updates:
Status: Closed Impact on me:
Category:MySQL Server: InnoDB storage engine Severity:S5 (Performance)
Version: OS:Any
Assigned to: Inaam Rana CPU Architecture:Any
Triage: Needs Triage: R1 (None/Negligible) / E2 (Low)

[3 Aug 2010 15:02] Inaam Rana
Currently we do a full validation of AHI whenever check tables is called on any table. This can lead to unacceptable performance of CHECK TABLES.

How to repeat:
Do CHECK TABLE on a small table while there is a very large adaptive hash index.

Suggested fix:
Do full validation of AHI only in debug builds.
[25 Aug 2010 9:23] Bugs System
Pushed into mysql-5.5 5.5.6-m3 (revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (version source revid:alik@ibmvm-20100825092002-2yvkb3iwu43ycpnm) (merge vers: 5.5.6-m3) (pib:20)
[30 Aug 2010 8:31] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (version source revid:alik@sun.com-20100830082732-n2eyijnv86exc5ci) (merge vers: 5.6.1-m4) (pib:21)
[30 Aug 2010 8:34] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (version source revid:alik@sun.com-20100830082745-n6sh01wlwh3itasv) (pib:21)
[13 Sep 2010 23:00] Bugs System
No feedback was provided for this bug for over a month, so it is
being suspended automatically. If you are able to provide the
information that was originally requested, please do so and change
the status of the bug back to "Open".
[13 Sep 2010 23:05] Calvin Sun
This issue is fixed, and no documentation needed given it applies to debug binary only.
[14 Sep 2010 7:06] Marko Mäkelä
Calvin, I think that this fix must be documented. Before this fix, CHECK TABLE triggered not only a consistency check of all indexes of the given tables, but also a consistency check of the adaptive hash index data structures, covering all pages in the buffer pool. After this fix, the consistency check of the adaptive hash index is only enabled in debug binaries.
[14 Sep 2010 18:36] John Russell
Added to change log:

The CHECK TABLE command could cause a time-consuming verification of
the InnoDB adaptive hash index memory structure. Now this extra
checking is only performed in binaries built for debugging.
[28 Sep 2010 8:46] Bugs System
Pushed into mysql-5.1 5.1.52 (revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (version source revid:sunanda.menon@sun.com-20100928083322-wangbv97uobu7g66) (merge vers: 5.1.52) (pib:21)
[28 Sep 2010 15:39] Bugs System
Pushed into mysql-trunk 5.6.1-m4 (revid:alik@sun.com-20100928153607-tdsxkdm5cmuym5sq) (version source revid:alik@sun.com-20100928153508-0saa6v93dinqx1u7) (merge vers: 5.6.1-m4) (pib:21)
[28 Sep 2010 15:41] Bugs System
Pushed into mysql-next-mr (revid:alik@sun.com-20100928153646-pqp8o1a92mxtuj3h) (version source revid:alik@sun.com-20100928153532-lr3gtvnyp2en4y75) (pib:21)
[28 Sep 2010 15:43] Bugs System
Pushed into mysql-5.5 5.5.7-rc (revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (version source revid:alik@sun.com-20100928153459-4nudf4zgzlou4s7q) (merge vers: 5.5.7-rc) (pib:21)
[14 Oct 2010 8:32] Bugs System
Pushed into mysql-5.1-telco-7.0 5.1.51-ndb-7.0.20 (revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (version source revid:martin.skold@mysql.com-20101014082627-jrmy9xbfbtrebw3c) (merge vers: 5.1.51-ndb-7.0.20) (pib:21)
[14 Oct 2010 8:48] Bugs System
Pushed into mysql-5.1-telco-6.3 5.1.51-ndb-6.3.39 (revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (version source revid:martin.skold@mysql.com-20101014083757-5qo48b86d69zjvzj) (merge vers: 5.1.51-ndb-6.3.39) (pib:21)
[14 Oct 2010 9:02] Bugs System
Pushed into mysql-5.1-telco-6.2 5.1.51-ndb-6.2.19 (revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (version source revid:martin.skold@mysql.com-20101014084420-y54ecj85j5we27oa) (merge vers: 5.1.51-ndb-6.2.19) (pib:21)
[15 Oct 2010 14:10] Jon Stephens
5.1 fix appeared in 5.1.51, not 5.1.52 -- see comment from [1 Sep 11:47].

Moved changelog entry to 5.1.51 changelog, closed.

(No additional changelog entries needed for pushes to telco trees.)