MySQL InnoDB Table Optimization Performance

The InnoDB that comes with MySQL as of 5.6.11 has a serious flaw when it comes to utilizing the Fast Index Creation offered by MySQL when running optimize table queries. This is a verified bug which was reported a long time ago and the link to the bug is here. The reason why InnoDB still doesn't have this feature is because I guess Oracle thinks that this is more a feature than a bug. Percona Server has resolved this issue in their XtraDB storage engine but it comes at a cost :)

So i have a table with one primary key and one secondary key and the definition of the table is below. The table is populated with 240753 records. The information schema tells that the data length of the table is 22593536 and the innodb_buffer_pool_size is 5242880. So the table size is approximately 4 times the buffer size. 

CREATE TABLE test (

  id int(10) unsigned NOT NULL AUTO_INCREMENT,

  md5hash char(64) DEFAULT NULL,

  PRIMARY KEY (id),

  KEY mykey (md5hash)

) ENGINE=InnoDB AUTO_INCREMENT=240573 DEFAULT CHARSET=latin1;

Self referencing an EJB to start a new transaction with Container Managed Transactions

Let us take an example of a Stateless EJB that has transaction attribute as REQUIRED. This means that if a client invokes the bean's method while the client is associated with a transaction context then the container invokes the bean's method in the client's transaction context. If the client invokes the bean's method while the client is not associated with a transaction context then the container starts a new transaction before it invokes the beans's method.

@Stateless
@TransactionAttribute( value = TransactionAttributeType.REQUIRED )
class EJB1 implements EJB1Local{
   
    public void method1( ){ }

   @TransactionAttribute( value = TransactionAttributeType.REQUIRES_NEW )
    public void method2( ){ }

}

Overriding default transaction control in JBoss

As of JBoss 5.0.1.GA the transaction time out attribute can be set explicitly in the configuration file named transaction-jboss-beans.xml. In JBoss CMT ( Container Managed Transactions ) if a transaction takes more time than the time specified in the configuration file then an Exception is thrown.

Sometimes purging a database table and optimizing it might be in the same transaction. Optimizing a table need not be a transaction whereas purging a table definitely has to be a part of the transaction as it leads to inconsistencies if not committed. Optimizing a table might take a lot of time if the DB is large. It is definitely not a good idea to combine purging and optimizing in one transaction because this might lead to a transaction timeout.

SAN vs. NAS

Have you been looking for the differences between a SAN and NAS ? Or are you tired of reading huge pages of definitions of SAN and NAS ? Here is an 8 minute video I found in YouTube by an employee of an IT services company. He explains the key differences between SAN and NAS and also tells us what iSCSI and Fiber Channels are. Take a look at it :-)

Java wrapper classes unusual behavior

What do you think the output of the following piece of code would be ?

 public void unusualBehavior(){
  Integer i1 = 1000;
  Integer i2 = 1000;
  System.out.println("i1 & i2 " + (i1 != i2));
  Integer i3 = 10;
  Integer i4 = 10;
  System.out.println("i3 & i4 " + (i3 == i4));
  Integer i5 = new Integer(10);
  Integer i6 = new Integer(10);
  System.out.println("i5 & i6 " + (i5 != i6));
 }

Producer and Consumer problem using Java wait( ) and notify( )

A producer is a thread that keeps writing elements/jobs into the job queue and a consumer keeps consuming jobs from the beginning of the queue. The job queue is synchronized since it is shared between the producer and the consumer. Wait() and Notify() are methods that are defined in the class "Object" in Java. If a thread has to call any one of these methods on an object then it has to have acquired a lock on this object. A lock can be acquired by using the synchronized keyword in Java.

In this example the producer acquires the lock on the job queue (which is a subclass of Object and hence inherits Wait() and Notify() methods from it) and adds an element/job into it and calls the notify() method on the job queue. This awakens the consumer thread that is waiting on the job queue. As said the Consumer thread acquires a lock on the job queue and waits on it only if it finds that the job queue is empty. Now remember that when a thread calls wait method on an object then the lock that was acquired on that object is released; after which the thread goes back to the RUNNABLE (stops running) state. It transitions to the RUNNING (starts running) state only when another thread calls notify on the same object.

It is best to put the call to method wait() inside a loop because we are not guaranteed that a thread that is waiting on an Object is awakened only by some other thread that calls the notify method on the same object. This is called spontaneous wake up - a thread may wake up even though no code has called notify() or notifyall().

Java class level vs. instance level locking

In this post I am going to explain the difference between the 2 following method definitions

public static void synchronized f() {… } and

public void synchronized f() {… }

The first method is an example of class level locking whereas the second one is an example of instance level locking. Suppose you have a class like the following: