Tuesday, May 23, 2017

MySql configuration for Production Environment

Mysql Configurations for High Volumn of Txns:

Developers knows how to tune Mysql during run time. However some require restart, some not.
The most used commands are (total around 500), samples are given below:

show variables;
show variables like '%tx_isolation%';

mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 151   |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)

mysql> set global max_connections=200;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like '%connections%';
+----------------------+-------+
| Variable_name        | Value |
+----------------------+-------+
| max_connections      | 200   |
| max_user_connections | 0     |
+----------------------+-------+
2 rows in set (0.00 sec)


1) SET GLOBAL
2) innodb_buffer_pool_size: This is the first setting to look for InnoDB. The buffer pool is where data and indexes are cached.
    Making it large as much possible will ensure you use memory and not disks for most read operations. 
    Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM).
3) innodb_log_file_size: Size of the redo logs. The redo logs are used to make sure writes are fast and durable and also 
    during crash recovery. Make it 1 Gb for better use. These are two files. innodb_log_file_size = 512M (giving 1GB of redo logs).
4) max_connections: If you are often facing the ‘Too many connections’ error, max_connections is too low. Using a connection pool 
    at the application level or a thread pool at the MySQL level can help here.
    
5) innodb_file_per_table: This setting will tell InnoDB if it should store data and indexes in the shared tablespace 
    (innodb_file_per_table = OFF). Or store in a separate .ibd file for each table (innodb_file_per_table= ON). Having a file per table 
    allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as 
    compression. However it does not provide any performance benefit.    

6) innodb_flush_log_at_trx_commit: the default setting of 1 means that InnoDB is fully ACID compliant. It is the best value when your 
    primary concern is data safety.
7) query_cache_size: The query cache is a well known bottleneck that can be seen even when concurrency is moderate. The best option is 
    to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6).
8) For Enabling Logs in Mysql:

    set global general_log=1;
    show variables like '%SQL_LOG%';       //SQL_LOG_OFF should be ON
    set global general_log=ON;             //1 or ON both are same
    show variables like 'GENERAL_LOG%';    //GENERAL_LOG should be ON
    show variables like '%long_query_time%';  
    set @@GLOBAL.long_query_time=1;
    show global variables like '%long_query_time%';
    show session variables like '%long_query_time%';  //Will show the older value. 
                        //Exit Mysql and Re-login and fire the same query.
                        
9) Make sure the database tables are using InnoDB storage engine and READ-COMMITTED transaction isolation level.
   show variables like '%tx_isolation%';   //REPEATABLE-READ becomes slow for insertion, selection.

10) Increase the database server innodb_lock_wait_timeout variable to 500.

Wednesday, May 17, 2017

Binary Tree in Java

Binary Tree or Binary Search Tree
Trees: Unlike Arrays, Linked Lists, Stack and queues, which are linear data structures, trees are hierarchical 
data structures. Elements with no children are called leaves.

package datastructure.tree;
public class BinaryTreeTest {
    static class Node {
        Node left;
        Node right;
        int data;
        public Node(int d) {
            this.data = d;
        }
    }

    static Node root = null;
    public static void main(String[] args) {
        root = new Node(50); //Making 5 as Root node    

        BinaryTreeTest object = new BinaryTreeTest();
        object.startFunctions();
        System.out.println("Total Node Count: "+object.countNodes(root));
        System.out.println("Search Node 60: "+object.search(root, 60));
    }

    public void startFunctions() {        
        System.out.println("Building tree with root value " + root.data);
        insert(root, 30);
        insert(root, 20);
        insert(root, 40);
        insert(root, 70);
        insert(root, 60);
        insert(root, 80);
        System.out.println("Traversing Tree InOrder");
        printInOrder(root);        
        System.out.println("Traversing Tree PreOrder");
        printPreOrder(root);        
        System.out.println("Traversing Tree PostOrder");
        printPostOrder(root);
    }

    public void insert(Node node, int value) {
        if(value<node.data) { //To go to left side
            if(node.left == null) {
                node.left = new Node(value);
                System.out.println("  Inserted " + value + " to left of " + node.data);
            }
            else {
                insert(node.left, value);
            }
        } 
        else if(value>node.data) { //To go to right side
            if(node.right == null) {
                node.right = new Node(value);
                System.out.println("  Inserted " + value + " to right of "+ node.data);
            } else {
                insert(node.right, value);
            }
        }
    }
    public void printInOrder(Node node) {
        if(node != null) {
            printInOrder(node.left);
            System.out.println("  InOrder Traversed " + node.data);
            printInOrder(node.right);
        }
    }
    public void printPreOrder(Node node) {
        if(node != null) {
            System.out.println("  PreOrder Traversed " + node.data);
            printPreOrder(node.left);
            printPreOrder(node.right);
        }
    }
    public void printPostOrder(Node node) {
        if(node != null) {
            printPostOrder(node.left);
            printPostOrder(node.right);
            System.out.println("  PostOrder Traversed " + node.data);
        }
    }
    private int countNodes(Node r) {
        if(r == null)
            return 0;
        else {
            int count = 1;
            count += countNodes(r.left);
            count += countNodes(r.right);
            return count;
        }
    }
    private boolean search(Node r, int val) {
        if(r.data == val)
            return true;
        if(r.left != null){
            if(search(r.left, val))
                return true;
        }
        if(r.right != null){
            if(search(r.right, val))
                return true;
        }
        return false;         
    }

    boolean identicalTrees(Node root1, Node root2) {        
        if(root1 == null && root2 == null)
            return true;

        if(root1 != null && root2 != null) 
            return (
                    root1.data == root2.data && 
                    identicalTrees(root1.left, root2.left) && 
                    identicalTrees(root1.right, root2.right)
                 );        
        return false;
    }
}

//Output
Building tree with root value 50
  Inserted 30 to left of 50
  Inserted 20 to left of 30
  Inserted 40 to right of 30
  Inserted 70 to right of 50
  Inserted 60 to left of 70
  Inserted 80 to right of 70

Traversing Tree InOrder
  InOrder Traversed 20
  InOrder Traversed 30
  InOrder Traversed 40
  InOrder Traversed 50
  InOrder Traversed 60
  InOrder Traversed 70
  InOrder Traversed 80

Traversing Tree PreOrder
  PreOrder Traversed 50
  PreOrder Traversed 30
  PreOrder Traversed 20
  PreOrder Traversed 40
  PreOrder Traversed 70
  PreOrder Traversed 60
  PreOrder Traversed 80

Traversing Tree PostOrder
  PostOrder Traversed 20
  PostOrder Traversed 40
  PostOrder Traversed 30
  PostOrder Traversed 60
  PostOrder Traversed 80
  PostOrder Traversed 70
  PostOrder Traversed 50

Total Node Count: 7
Search Node 60: true


Properties:
1) The maximum number of nodes at level ‘L’ of a binary tree is 2 Power(L-1). Root is always at Level 1. Hence only 1 Node at Root. 
Max Node at Level 3 are 4. Here 20, 40, 60, 80 are at level 3. Here level is number of nodes on path from root to the node (including root and node). 

2) Maximum number of nodes in a binary tree of height ‘h’ is 2h – 1. Here height of a tree is maximum number of nodes on root to leaf path. 
Height of a leaf node is considered as 1.

Full Binary Tree A Binary Tree is full if every node has 0 or 2 children. Above all images are full binary tree.
A degenerate (or pathological) tree: A Tree where every internal node has one child. Such trees are performance-wise same as linked list. 

MySQLTimeoutException: Statement cancelled due to timeout or client request

Dear Reader,
Recently I was facing this error in Production when my application was connecting to DB.

Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request

I had set Statment.setQueryTimeout(15);  //15 Seconds.
Hence after 15 Seconds, the Application was cancelling the request if the record was not getting inserted in DB.

I tried lot to re-produce this issue as DBA was adamant to accept this as a problem. Finally after lot of R&D I did below.

Complete Error Message:    
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1757)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2022)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1940)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1925)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:105)
    at com.enstage.DEEPAK_KUMAR_MODI_JAVA_API_EguardSummary.saveSummary(EguardSummary.java:412)
    ... 5 more

1st Reproduction of Issue:
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("URL");
            st = con.createStatement();            
            
            //Either Throw the exception Manually
            throw new java.lang.Exception(new com.mysql.jdbc.exceptions.MySQLTimeoutException());
        }
        catch (Exception e) {
            e.printStackTrace();
            try {
                con.close();
                st.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        finally{
            System.out.println("Finally");
        }

//Output
java.lang.Exception: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    at MySqlTester.main(MySqlTester.java:32)
Caused by: com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
    ... 1 more
Finally



2nd Reproduction of Issue:
Use "SELECT SLEEP(10)"
See below entire program:

//MySqlTester.java
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
/*@author Deepak Kumar Modi
*/
public class MySqlTester {
    public static void main(String[] args) {
        Connection con = null;
        Statement st = null;
        ResultSet rs = null;
        //SELECT * FROM ACTOR
        try{
            Class.forName("com.mysql.jdbc.Driver");
            con = DriverManager.getConnection("jdbc:mysql://X.X.1.54:3306/deepak_modi?user=root&password=deepakmodi");
            st = con.createStatement();
            System.out.println("Start Date: "+new java.util.Date());
            st.setQueryTimeout(2);    //Max wait is set for 2 Seconds            
            rs = st.executeQuery("SELECT SLEEP(10)");  //DB will wait for 10 Seconds
            int count=0;
            while(rs.next()) {                
                System.out.print("ID: " + rs.getString("actor_id"));
                System.out.print(", FN: " + rs.getString("first_name"));
                System.out.print(", LN: " + rs.getString("last_name"));
                System.out.println();
                count++;
                if(count==5)
                    break;                 
            }
            //Either Throw the exception Manually
            //throw new java.lang.Exception(new com.mysql.jdbc.exceptions.MySQLTimeoutException());
        }
        catch (Exception e) {
            System.out.println("End Date: "+new java.util.Date());
            e.printStackTrace();
            try {
                con.close();
                st.close();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        }
        finally{
            System.out.println("Finally");
        }
    }
}

//Output (See the exception is thrown just after 2 seconds. Application cancelled the request.)
Start Date: Wed May 17 14:34:11 IST 2017
End Date:   Wed May 17 14:34:13 IST 2017 
com.mysql.jdbc.exceptions.MySQLTimeoutException: Statement cancelled due to timeout or client request
Finally
    at com.mysql.jdbc.StatementImpl.executeQuery(StatementImpl.java:1622)
    at MySqlTester.main(MySqlTester.java:19)

    
Fix of this issue: Get HOLD of DBA. or Modify Mysql Configuration.    

Friday, May 5, 2017

Lock and ReentrantLock in Java Example

package concurrency;
import java.util.concurrent.locks.Lock;
import java.util.concurrent.locks.ReentrantLock;

public class LockCounter {
    private Lock lock = new ReentrantLock();
    private static int count = 0;

    public int increment(){
        lock.lock();  //Comment this Line to see abnormal multithreading behavior
        count++;
        System.out.println("In: "+count);
        lock.unlock();  //Comment this Line to see abnormal multithreading behavior
        return count;
    }
    public int decrement(){
        lock.lock();  //Comment this Line to see abnormal multithreading behavior
        count--;
        System.out.println("De: "+count);
        lock.unlock();  //Comment this Line to see abnormal multithreading behavior
        return count;
    }
    public static void main(String[] args){
        new Thread(new Runnable(){public void run(){
            LockCounter obj = new LockCounter();
            while(true){
                obj.increment();
                try {
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
            }}).start();
        new Thread(new Runnable(){public void run(){
            LockCounter obj = new LockCounter();
            while(true){
                obj.decrement();
                try {
                    Thread.sleep(1000);
                } catch (InterruptedException e) {
                    e.printStackTrace();
                }
            }
            }}).start();
    }
}

//Output: We have disabled Lock API. lock() and unlock() were commented here.
In: 1
De: 0
In: 1
De: 1
De: 1
In: 1
De: 1
In: 1
De: 1
In: 1
In: 0
De: 0
In: 0
De: 0
De: 0
In: 0
In: 0
De: 0
In: 0
De: 0
De: 0
In: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
De: -1
In: 0
De: -1
In: 0
In: 1
De: 0
De: 0
In: 0
In: 1
De: 1
De: 1
In: 1
In: 1
De: 1

//After Implementing Lock and ReentrantLock API
//Output: We have ENABLED Lock API. lock() and unlock() were UN-Commented here.
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0
In: 1
De: 0

Sensitive Password Entry in Console using Java

Dear Reader,
When you need an application where some Password is required to start the application and that need 
to be entered in Console (Unix OS/Windows OS) manually Eg: Starting Master HSM server for Card Encryption/Decryption, 
Starting Unix based critical applications, you don't want to show either * or text while typing password. This may 
cause a shoulder surfing.

In Java you can do this using Below API, I have tested this Windows and UNIX. Running this in Eclipse will throw Exception.
Hence Run in Command Prompt.

import java.io.Console;
public class HSMPassword {
    public static void main(String[] args) {
        char[] pass ;
        Console console = System.console();
        System.out.println("Reading Password From Console, Please Press Enter after typing your password.");
        pass = console.readPassword("");
        String slotPIN = new String(pass);
        System.out.println("Password Entered is: "+slotPIN);
    }
}
--------------Starting in Console--------------------
Microsoft Windows [Version 6.1.7601]
Copyright (c) 2009 Microsoft Corporation.  All rights reserved.
E:\MyDiary>javac HSMPassword.java
E:\MyDiary>java HSMPassword
Reading Password From Console, Please Press Enter after typing your password.

Password Entered is: deepak

E:\MyDiary>java HSMPassword
Reading Password From Console, Please Press Enter after typing your password.

Password Entered is: hello
E:\MyDiary>