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();