Update and upsert speedup

Fast updates and upserts

TokuDB allows certain types of update and upsert statements to be executed by the storage engine rather than be executed as a read modify write sequence of operations by MySQL. The idea is to avoid the read to improve the performance of random point updates and upserts. We tag the update and upsert statements with a NOAR keyword that is a hint to TokuDB to handle the execution of the update and upsert statements.

NOAR keyword

NOAR = No affected rows.

Keyword added to the MySQL parser to allow update and upsert statements to hint that affected rows need NOT be computed for the statement. This enables fractal tree optimizations to be used for certain update and upsert statements.

Fast update status

• Shipped with Tokutek’s MySQL and MariaDB 5.5 products.
• Not supported on Percona Server because we did not agree to extend the SQL parser with the NOAR keyword. There are other ways to express this functionality that could be used.

Fast update restrictions

• No triggers
• Statement or mixed replication
• Primary key must be defined
• Simple and compound primary key
• Int, char and varchar primary key types
• No updates on fields that are part of any key
• No clustering keys
• Int, char, varchar, and text field updates

Fast update expressions

• x = constant
• x = x + constant
• x = x - constant
• x = if (x=0,0,x-1)
• x = x + values(x)

Fast Updates

• Check if the update statement is a point update.
• Check that the table is simple enough.
• Check that the update expressions are simple enough.
• Compile the update expressions into a row update function.
• Send the row update function into the fractal tree with an update message directed at the primary key.
• Execute the row update function when the update message is applied to a leaf entry.

Fast Upserts

• Check if the upsert statement is a point upsert.
• Check that the table is simple enough.
• Check that the upsert expressions are simple enough.
• Compile the upsert expressions into a row upsert function.
• Send the row upsert function into the fractal tree with an update message directed at the primary key.
• Execute the row upsert function when the update message is applied to a leaf entry. If the row does not exist, then insert a new row.