MySQL Bugs: #39267: No caching of unique identifiers in PK-less tables

Bug #39267	No caching of unique identifiers in PK-less tables
Submitted:	5 Sep 2008 6:58	Modified:	3 Oct 2008 10:56
Reporter:	Mikael Ronström	Email Updates:
Status:	Closed	Impact on me:	None
Category:	MySQL Cluster: Cluster (NDB) storage engine	Severity:	S3 (Non-critical)
Version:	5.1.27-ndb-6.3,16	OS:	Any
Assigned to:	Mikael Ronström	CPU Architecture:	Any

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 DBT2 at 8 nodes with and without patch below.
With patch gives result 158k TPM, without patch gives 106k TPM.
Even simpler way to repeat is to create a PK-less table and check
how many inserts it can handle compared to a PK table. Using the
NDB API the difference will easily be 10x.

Suggested fix:
In ndb_write_row there is a call to getAutoIncrement for
PK-less tables (table_share->primary_key == MAX_KEY).
Here change 1 (==cache_size) to 1000. This means every
1000th insert will turn into a update on the SYSTAB_0 record.
Even with 1 million inserts per second this becomes 1000
updates per second and thus this record should not be able
to become a bottleneck for any realistic scenarios. It's
probably ok to even set it to 10000 to give even more leeway
here, the number is 64 bits and thus there is sufficient
amount of numbers.

Pushed to CGE 6.3.18

Documented in the 5.1.28-ndb-6.3.18 changelog as follows:

        Unique identifiers in tables having no primary key were not cached. This
        fix has been observed to increase the efficiency of INSERT operations on
        such tables by as much as 50%.

Also merged to NDB-6.2.16; also documented in 6.2.16 changelog.

Pushed into 6.0.7-alpha  (revid:mikael@mysql.com-20081002125030-dddij7znq00f7nwt) (version source revid:tomas.ulin@sun.com-20081015121427-mqe9vyomt1jroewp) (pib:5)