Bug #39268 No transaction hints used to update SYSTAB_0 for autoincrement and PK-less table
Submitted: 5 Sep 2008 9:05 Modified: 22 Sep 16:18
Reporter: Mikael Ronstrom
Status: Closed
Category:Server: Cluster Severity:S5 (Performance)
Version:mysql-5.1-telco-7.0 OS:Any
Assigned to: Martin Skold Target Version:
Tags: 16), (5.1.27-ndb-6.3
Triage: Needs Triage: D5 (Feature request)

[5 Sep 2008 9:05] Mikael Ronstrom 
Description:
When running the DBT2 benchmark I had severe scalability issues.
After investigating the issue I discovered that the HISTORY table
had no primary key and that there were transactions towards nodes
that should in reality be idle. This was due to two bugs, the first
being that updates of SYSTAB_0 to get a unique identifier didn't
use transaction hints and that the cache_size used was only 1, thus
each insert into the HISTORY table had to also update the SYSTAB_0
record for the HISTORY table. This meant that this record became a
"hotspot" in the DBT2 benchmark.

How to repeat:
Run inserts into a table with autoincrement which has only one partition in
nodegroup 0. Watch the load in NG1 how it goes up as more and
more although it should not need.

Suggested fix:
In the method Ndb::opTupleIdOnNdb there is a call to
startTransaction without parameters for transaction hints.
Change this to use the transaction hint variant of the
startTransaction-call. This means we will get the transaction
sent to the proper node and thus decrease the impact of
any hotspots due to creating key numbers in steps of 1
on autoincrement tables.
[4 Feb 15:08] Martin Skold 
http://lists.mysql.com/commits/65174
[10 Feb 17:31] 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/65775

2836 Martin Skold	2009-02-10 [merge]
      Merge
[11 Feb 16:17] 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/65920

3252 Martin Skold	2009-02-11 [merge]
      Merge
[11 Feb 21:45] 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/65957

3257 Martin Skold	2009-02-11 [merge]
      Merge
[11 Feb 22:04] Bugs System 
Pushed into 5.1.31-ndb-6.4.3
(revid:martin.skold@mysql.com-20090211204523-03nx13fjekybwez2) (version source
revid:martin.skold@mysql.com-20090211204523-03nx13fjekybwez2) (merge vers:
5.1.31-ndb-6.4.3) (pib:6)
[11 Feb 22:06] Bugs System 
Pushed into 5.1.31-ndb-6.2.17
(revid:martin.skold@mysql.com-20090210162313-g4ou3ovzoyoljaej) (version source
revid:martin.skold@mysql.com-20090204135340-m9u7dfp2g3ul8ass) (merge vers:
5.1.31-ndb-6.2.17) (pib:6)
[11 Feb 22:07] Bugs System 
Pushed into 5.1.31-ndb-6.3.23
(revid:martin.skold@mysql.com-20090210163054-bcupy4stktagp5rt) (version source
revid:martin.skold@mysql.com-20090210163054-bcupy4stktagp5rt) (merge vers:
5.1.31-ndb-6.3.23) (pib:6)
[18 Sep 15:58] 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/83739

2998 Martin Skold	2009-09-18
      bug#39268  No transaction hints used to update SYSTAB_0 for autoincrement and
PK-less table
      modified:
        storage/ndb/src/ndbapi/Ndb.cpp
[18 Sep 16:00] Martin Skold 
First patch is incorrect, hint should be against table SYSTAB_0
[21 Sep 13:44] 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/83902

3053 Martin Skold	2009-09-21 [merge]
      Merge
      modified:
        mysql-test/include/restart_mysqld.inc
        storage/ndb/src/ndbapi/Ndb.cpp
[21 Sep 13:46] 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/83906

3012 Martin Skold	2009-09-21 [merge]
      Merge
      modified:
        storage/ndb/src/ndbapi/Ndb.cpp
[21 Sep 14:26] 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/83920

3009 Martin Skold	2009-09-21 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_multi.result
        mysql-test/suite/ndb/t/ndb_multi.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster_binlog.cc
        sql/ha_ndbcluster_binlog.h
        storage/ndb/src/ndbapi/Ndb.cpp
[21 Sep 15:52] 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/83946

3057 Martin Skold	2009-09-21 [merge]
      Merge
      modified:
        mysql-test/include/restart_mysqld.inc
        mysql-test/suite/ndb/r/ndb_multi.result
        mysql-test/suite/ndb/t/ndb_multi.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster_binlog.cc
        sql/ha_ndbcluster_binlog.h
        storage/ndb/src/ndbapi/Ndb.cpp
[21 Sep 16:23] 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/83957

3024 Martin Skold	2009-09-21 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_multi.result
        mysql-test/suite/ndb/t/ndb_multi.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster_binlog.cc
        sql/ha_ndbcluster_binlog.h
        storage/ndb/src/ndbapi/Ndb.cpp
[22 Sep 8:18] 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/84025

3020 Martin Skold	2009-09-22 [merge]
      Merge
      modified:
        mysql-test/suite/ndb/r/ndb_multi.result
        mysql-test/suite/ndb/t/ndb_multi.test
        sql/ha_ndbcluster.cc
        sql/ha_ndbcluster_binlog.cc
        sql/ha_ndbcluster_binlog.h
        storage/ndb/src/ndbapi/Ndb.cpp
[22 Sep 16:18] Jon Stephens 
Documented bugfix in the NDB-6.2.17, 6.3.23, and 6.4.3 changelogs, as follows:

        Updates of the SYSTAB_0 system table to obtain a unique
        identifier did not use transaction hints for tables having no
        primary key. In such cases the NDB kernel used a cache size of
        1. This meant that each insert into a table not having a primary
        key required an update of the corresponding SYSTAB_0 entry,
        creating a potential performance bottleneck.

        With this fix, inserts on NDB tables without primary keys can be
        under some conditions be performed up to 100% faster than 
        previously.

Closed.

(Note: Changelog entry appearing for 6.4.3 means that the fix also appears in NDB 7.0.4
and later releases.)
[30 Sep 10:13] Bugs System 
Pushed into 5.1.37-ndb-6.2.19 (revid:frazer@mysql.com-20090929142503-sst6g3fs0vx9fgil)
(version source revid:martin.skold@mysql.com-20090918135924-dr4wa8cz36yszxes) (merge
vers: 5.1.37-ndb-6.2.19) (pib:11)
[30 Sep 10:13] Bugs System 
Pushed into 5.1.37-ndb-6.3.28 (revid:jonas@mysql.com-20090930070741-13u316s7s2l7e1ej)
(version source revid:martin.skold@mysql.com-20090921135309-q77376ub6jvk10mg) (merge
vers: 5.1.37-ndb-6.3.27) (pib:11)
[30 Sep 10:14] Bugs System 
Pushed into 5.1.37-ndb-7.0.9 (revid:jonas@mysql.com-20090930075942-1q6asjcp0gaeynmj)
(version source revid:martin.skold@mysql.com-20090921142346-kg9qrq7flqgd6wum) (merge
vers: 5.1.37-ndb-7.0.8) (pib:11)
[30 Sep 10:15] Bugs System 
Pushed into 5.1.35-ndb-7.1.0 (revid:jonas@mysql.com-20090930080049-1c8a8cio9qgvhq35)
(version source revid:martin.skold@mysql.com-20090922061929-qjq5pztm76lwa7ri) (merge
vers: 5.1.35-ndb-7.1.0) (pib:11)