Bug #39267 No caching of unique identifiers in PK-less tables

• Submitted: 5 Sep 2008 8:58
• Modified: 3 Oct 2008 12:56
• Reporter: Mikael Ronstrom
• Status: Closed
• Category: Server: Cluster
• Severity: S3 (Non-critical)
• Version: 5.1.27-ndb-6.3,16
• OS: Any
• Assigned to: Mikael Ronstrom
• Triage: Needs Triage: D5 (Feature request)

[5 Sep 2008 8:58] 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 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.

[2 Oct 2008 23:31] Mikael Ronstrom

Pushed to CGE 6.3.18

[3 Oct 2008 12:56] Jon Stephens

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%.

[8 Oct 2008 14:24] Jon Stephens

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

[13 Dec 2008 0:25] Bugs System

Pushed into 6.0.7-alpha  (revid:[e-mail]-20081002125030-dddij7znq00f7nwt) (version
source revid:[e-mail]-20081015121427-mqe9vyomt1jroewp) (pib:5)