MySQL Aurora CPU spikes

Recently we encountered an issue with aurora, where our cpu started spiking every X minutes. (X < 30)

Screen Shot 2015-08-27 at 3.02.27 pm

we had like 20-30 connections, out of which majority were inserts and a few selects.

every time the spike occurred our inserts would start waiting and waiting….

here is innotop output


we checked our code. nothing major had changed.

The mysql error log had this mysterious message which i could not trace in the mysql/percona/maria source.

“Innodb: Retracted crabbing in btr_cur_search_to_nth_level 33001 times since restart”

if you get to know the meaning of this – please do let me know – would love to understand this.

crab_Screen Shot 2015-09-17 at 9.29.17 pm

Finally we contacted Aurora support team, after an investigation by them, it turned out to be a issue in aurora.

I am not too sure whether the above ‘error log message’  had any impact or not on this case.

PS: if you are wondering , that this post ended abruptly, even i am a little surprised by the ending too.

Screen Shot 2015-08-24 at 7.47.15 pm

Should I worry about the Query Cache in Aurora ?

There are a lot of blog posts on the internet which warn you about using the Query Cache in MySQL.

I was surprised to see that the query cache was enabled in Aurora.

Screen Shot 2015-08-24 at 7.25.02 pm

This was the size on a ‘db.r3.large’ instance.

On a ‘db.r3.2xlarge’  instance, it was set to 2460900352 i.e. 2.4GB

I am not sure, if amazon has done something to improve the query cache.

So, do run tests with Aurora and see if the cache suits you.

Screen Shot 2015-08-24 at 7.47.15 pm

Storm Spout Co-ordinator – llegal State Exception – Deactivate / Activate Topology

Recently while working with Transactional Topologies storm 0.9.4 we came across this error in the spout

Expecting previous txid state to be the previous transaction

We were a little confused as to why this happened, as the code change was minimal.

Root Cause Analysis:

Normally we make method ‘isReady()’ of Coordinator return true. We made changes for it to return false, i.e we wanted to

implement deactivation/activation of the topology instead of killing it cold i.e. a graceful stop.

In addition to that, we had for our topology

conf.put(Config.TOPOLOGY_MAX_SPOUT_PENDING, 10);

i.e. the number of parallel in flight transactions was set to 10.

What happens when ‘isReady()’ returns false ?

As per Coordinator Java doc,  the next transaction id’s  are skipped,

i.e. will not be used. refer

if(_activeTx.size() < _maxTransactionActive) {
    BigInteger curr = _currTransaction;
    for(int i=0; i<_maxTransactionActive; i++) {
        if((_coordinatorState.hasCache(curr) || _coordinator.isReady())
                && !_activeTx.containsKey(curr)) {
          Object state = _coordinatorState.getState(curr, _initializer);
        curr = nextTransactionId(curr);   //      <------- txn++ ----------------

What happens when ‘isReady()’ returns true .i.e. we activate topology again ?

The same logic from above runs i.e.

Object state = _coordinatorState.getState(curr, _initializer);

because we have below

conf.put(Config.TOPOLOGY_MAX_SPOUT_PENDING, 10); 
// hence it enters the for loop above

Now if we go into the getState() method

if(_strictOrder) {
    if(prev!=null && !prev.equals(txid.subtract(BigInteger.ONE))) {
        throw new IllegalStateException("Expecting previous txid state to be the previous transaction");

Lets say we deactivate the topology, making isReady() return FALSE.

Assuming the previous committed txn is 3 and current is say 6 after some skipping.

From the above, the 6 – 1 is not equal to 3. i.e. STRICT order needs to be maintained.

Storm expects the next txn to be 4 as 4 – 1 == 3.  but do to skipping, this is violated.

Hence the exception as we have set

conf.put(Config.TOPOLOGY_MAX_SPOUT_PENDING, 10);

if this is set to 1, this will not happen as it will never enter the for loop.

I guess this is a bug.

Work Around:

Deactivate your topology by making isReturn() return False, wait for data processed to reach Zero.

and then kill your topology.

or set

conf.put(Config.TOPOLOGY_MAX_SPOUT_PENDING, 01);

Hope this helps.

a wild Supposition: can MySQL be Kafka ?

This is an idea which i presented at percona live 2015.

Is MySQL an avatar of Apache Kafka ?

Can it be Kafka ?

Yes, it can.

This talk takes a shot at modeling MySQL as Kafka.

PS: it’s unconventional, hence a WILD supposition :)

slides @


MySQL Cluster – Java Connector / Bindings

While working with MySQL Cluster, i was looking for a monitoring framework for the cluster.

i came across a library @ – which had java and other connectors to NDB, the library was a wrapper of the existing C++ NDB Api.

This library allowed me to connect to the management node , get the state of the cluster and get real time notifications about heartbeat misses/node disconnections.

The library error-ed out on some conditions, with a small fix, it can work with MySQL Cluster 7.3.

I have listed down steps for compilation and running a sample program at github