Updating a counter in different ways

Tweet In MySQL Cluster a statement like

update t1 set counter=counter+1 where pk=1

can be executed in a few different ways with great difference in performance.

If we do it from the MySQL server then the update is implemented as:

read_ex(counter) ; //one round trip to MySQL Cluster, read with exclusive lock
counter++
update(counter); //one round trip to MySQL Cluster, update - write back


If we are using the NDBAPI (Cluster Direct API) we could implement it as above as well, but for the operation above, the NDBAPI offers a way of performing the update in just one round trip! This can be done using Interpreted Programs, which are instructions defined in your NDBAPI application and upon execute, sent to the ndbd kernel and executed there. Cool. What I will show you here is how to do an update using the interpreted program, which is called an interpreted update.

In order to illustrate the impact of round-trips (and the mysql server) I did a simple test on my laptop.

The statement

update t1 set counter=counter+1 where pk=X

was implemented in

1. Java using Connector/J to talk to the MySQL server
2. NDBAPI as a read_ex(counter), counter++, update(counter)
3. NDBAPI as an interpreted update
   

I setup a two node cluster and created a table:

create table t1(pk integer primary key, counter integer not null) engine=ndb;

and inserted some tuples.

Thereafter I ran the three tests above by looping 100 000 times updating the counter of a random tuple. The diagram below show the average time for performing an update in each of the three cases:

So, there are many micro seconds to gain and if we can avoid unnecessary round trips, let's do so!

Here is the code snippet for doing an interpreted update (error handling is intentionally left out):


unsigned int inc=1;
unsigned int pk=1;

NdbTransaction * t = ndb->startTransaction();

NdbOperation * op = t->getNdbOperation("t1");
//define the operation to be an interpreted update
op->interpretedUpdateTuple();

op->equal("pk",(const char*)pk);
// increment the value of counter with inc
op->incValue("counter", inc);

t->execute( NdbTransaction::Commit, NdbTransaction::AbortOnError, 1);
t->close();


NdbScanFilter

Tweet Sometimes the NDBAPI is not perhaps the most straightforward. This might be useful if you are struggling with scan filters (filtering tuples in the ndbd kernel, like engine-condition-pushdown) in combination with strings. Please have a look at my previous blog about how to use strings in the NDBAPI.

If you are using string daratypes with the NdbScanFilter::COND_EQ then the following rules applies:

• VARCHAR and VARBINARY strings must have their length prepended (1 or 2 bytes)
• CHAR must be space padded
• BINARY must be null byte padded
  

However, for NdbScanFilter::COND_LIKE the following applies:

• VARCHAR and VARBINARY must NOThave the lenght prepended!
• CHAR must NOT be space padded
• BINARY must NOT be null byte padded

Many thanks to Pekka (MySQL) and Jim Dowling (MySQL) on this subject.