Monday, August 25, 2008

Thread Executors in java

With Java 1.4, when we used to write threaded applications, we did not have much options in limiting and reusing the same thread for the available tasks. I had written applications earlier where each process that needed to be executed freely (or a process which can be executed on a thread) used to create its own thread. And once a thread was created and used, it was simply discarded. New threads were created whenever needed.

With java 1.5, there is an executor class which allows you to create controlled thread pools and execute the list of tasks on the same number of threads. If the number of tasks exceeds the number of threads then these extra tasks just wait for a thread to become available and then start their execution.

A simple threaded server using java 1.4 :

1 import java.io.*;
2 import java.net.*;
3 import java.util.*;
4
5 public class testPool implements Runnable
6 {
7   private int port;
8   private int active, total;
9
10   public testPool(int port)
11   {
12     this.port = port;
13     System.out.println("Thread pool constructor - creating thread");
14     new Thread(this).start();
15   }
16
17   public void run()
18   {
19     try
20     {
21       System.out.println("Thread pool - new thread created");
22       ServerSocket ss = new ServerSocket(port);
23       while(true)
24       {
25         Socket s = ss.accept();
26         total++;
27         new Handler(s);
28       }
29     }catch(Exception ex)
30     {
31       ex.printStackTrace();
32     }
33   }
34
35   public class Handler implements Runnable
36   {
37     private Socket socket;
38     public Handler(Socket s)
39     {
40       this.socket = s;
41       System.out.println("Handler constructor - creating thread");
42       new Thread(this).start();
43     }
44     public void run()
45     {
46       System.out.println(Thread.currentThread().getName()+" - Handler - new thread created");
47       active++;
48       boolean loop = true;
49       try
50       {
51         BufferedReader in = new BufferedReader(new InputStreamReader(socket.getInputStream()));
52         DataOutputStream out = new DataOutputStream(socket.getOutputStream());
53         while(loop)
54         {
55           String cmd = in.readLine();
56           if(cmd.equals("QUIT"))
57           {
58             loop = false;
59             socket.close();
60           }
61           else if(cmd.equals("INFO"))
62           {
63             System.out.println("Active Connections : "+active);
64             System.out.println("Total Connections : "+total);
65           }
66           else
67           {
68             System.out.println("Command = "+cmd);
69           }
70         }
71       }catch(Exception ex)
72       {
73         ex.printStackTrace();
74       }
75       active--;
76     }
77   }
78
79   public static void main(String[] args)
80   {
81     System.out.println("Starting Daemon");
82     new testPool(Integer.parseInt(args[0]));
83   }
84 }


Compile and run the program

jayant@jayantbox:~/myprogs/java$ java -cp . testPool 5000
Starting Daemon
Thread pool constructor - creating thread
Thread pool - new thread created


Now, as you keep on connecting to localhost 5000 port, you can see that new threads are created. Even when the old connections are closed, the same thread is not used again but new threads are created.

so, if i do

telnet localhost 5000

from 3 consoles and issue the "INFO" command from 4th console, the output is

jayant@jayantbox:~/myprogs/java$ java -cp . testPool 5000
Starting Daemon
Thread pool constructor - creating thread
Thread pool - new thread created
Handler constructor - creating thread
Thread-1 - Handler - new thread created
Handler constructor - creating thread
Thread-2 - Handler - new thread created
Handler constructor - creating thread
Thread-3 - Handler - new thread created
Handler constructor - creating thread
Thread-4 - Handler - new thread created
Active Connections : 1
Total Connections : 4

4 threads are created. In this case, everytime you connect to the server, a new thread would be created.

Now, lets modify the code to work on java 1.5 using thread Executors...

Add the following code snippets at or after the following line numbers

4 import java.util.concurrent.*;
10  private int pool;
12  this.pool=2;
22  ExecutorService threadExecutor = Executors.newFixedThreadPool(pool);
27  threadExecutor.execute(new Handler(s));

And comment the following 2 lines

27  new Handler(s);
42  new Thread(this).start();


Not lets run the program again on port 5000 and try connecting to it. We have created a pool of 2 threads here. Observer that:


  • pool-1-thread-1 & pool-1-thread-2 are the two threads created.

  • After two connections to the server, the connections are not rejected, but are queued.

  • If a thread becomes available, a connection from the queue is assigned to the thread for processing

  • The same two threads are used again and again. No new threads are created.

  • Connections which are in queue are not able to process any requests unless they are assigned to a worker thread



Process for testing

console 1: telnet localhost 5000
console 2: telnet localhost 5000
console 3: telnet localhost 5000
console 1: from 1
console 2: from 2
console 3: from 3
console 1: INFO
console 1: 1 quitting
console 1: QUIT


Output for the testing process


jjayant@jayantbox:~/myprogs/java$ java -cp . testPool 5000
Starting Daemon
Thread pool constructor - creating thread
Thread pool - new thread created
Handler constructor - creating thread
pool-1-thread-1 - Handler - new thread created
Handler constructor - creating thread
pool-1-thread-2 - Handler - new thread created
Handler constructor - creating thread
Command = from 1
Command = from 2
Active Connections : 2
Total Connections : 3
Command = 1 quitting
pool-1-thread-1 - Handler - new thread created
Command = from 3


As seen, processing of connection 3 starts only after connection 1 is closed by the client.

The benefits of using a thread executor instead of a normal thread are

a] Overhead of creating and destroying threads is avoided.
b] A queue of threads is created automatically when the no of tasks exceeds the no of threads in the pool. The tasks in queue are automatically executed when the threads become free.
c] A limit (maximum no of threads) could be imposed thus controlling the available resources for delivering better performance.

Tuesday, August 05, 2008

Berkeley DB

I had a chance to look in BDB-XML some time back. But i did not find it that good in performance. BDB on the other hand promises fast storage and retrieval of key-value pairs. For the new-comers, BDB is a embedded database API which can be used to create your own database and fire a defined set of queries on it. BDB does not provide you with an SQL interface or even a database server. All you have is a set of API using which you can write programs to create your own database, populate data into the database, define your own cache and indexes and then use the database to retrieve values for a particular key using your own programs.

With BDB, there is no sql/query optimizer/query parser layer between you and the database.

There are certain types of BDB engines available:

1.] BDB -> the original BDB with c/c++ api. You write programs in c/c++ to create and access your database.
2.] BDB-JAVA -> The java api to BDB. The java api uses JNI in the backend to communicate with the BDB library (written in c/c++).
3.] BDB-JE -> The java edition of the BDB engine. It is a pure java implementation of the BDB engine. It does not use JNI for communication with the system.
4.] BDB-XML -> This is a very sophesticated version of BDB - where you can store XML documents and retrieve documents using any of the keys in the XML Document. You have an XQuery interface where you can fire XML based queries and retrieve results.

The original BDB is ofcourse the fastest.

For a startup, we will take a look at the DPL API of BDB-JAVA. DPL stands for Direct Persistence Layer and is used generally for storing and managing java objects in the database. DPL works best with a static database schema and requires java 1.5.

To create a database using DPL, you generally require an entity class and then create/open a database environment and insert the entity object into the entity store in the database environment. Sounds greek right ?? Lets see an example

Entity Class

import com.sleepycat.persist.*;
import com.sleepycat.db.*;
import com.sleepycat.persist.model.*;
import static com.sleepycat.persist.model.Relationship.*;

@Entity
public class SimpleEntityClass {

   // Primary key is pKey
   @PrimaryKey
   private String pKey;

   // Secondary key is the sKey
   @SecondaryKey(relate=MANY_TO_ONE)
   private String sKey;

   public SimpleEntityClass(String pk, String sk)
   {
      this.pKey = pk;
      this.sKey = sk;
   }

   public void setpKey(String data)
   {
      pKey = data;
   }

   public void setsKey(String data)
   {
      sKey = data;
   }

   public String getpKey()
   {
      return pKey;
   }

   public String getsKey()
   {
      return sKey;
   }
}


Then create a Database Access class which can insert and retrieve Entity objects from the database.

import java.io.*;
import com.sleepycat.db.*;
import com.sleepycat.persist.*;

public class SimpleDA
{
   PrimaryIndex pIdx;
   SecondaryIndex sIdx;

   EntityCursor pcursor;
   EntityCursor scursor;

   public SimpleDA(EntityStore store) throws Exception
   {
      pIdx = store.getPrimaryIndex(String.class, SimpleEntityClass.class);
      sIdx = store.getSecondaryIndex(pIdx, String.class, "sKey");
   }

   public void addEntry(String pk, String sk) throws DatabaseException
   {
      pIdx.put(new SimpleEntityClass(pk, sk));
   }

   public SimpleEntityClass findByPk(String pk) throws DatabaseException
   {
      SimpleEntityClass found = pIdx.get(pk);
      return found;
   }

   public ArrayList findBySk(String sk) throws DatabaseException
   {
      ArrayList ret = new ArrayList();
      scursor = sIdx.subIndex(sk).entities();
      for(SimpleEntityClass sec1 = scursor.first(); sec1!=null; sec1 = scursor.next())
      {
         ret.add(sec1);
      }
      scursor.close();
      return ret;
   }
}


Create/open the environment to put and retrieve records from the database

import java.io.*;
import com.sleepycat.db.*;
import com.sleepycat.persist.*;

public class SimpleStore
{
   private static File envHome = new File("./bdbjava");
   private Environment env;
   private EntityStore store;
   private SimpleDA sda;

   public void setup() throws DatabaseException
   {
   // put all config options here.
      EnvironmentConfig envConfig = new EnvironmentConfig();
      envConfig.setAllowCreate(true);
      envConfig.setCacheSize(536870912); //512 MB
      envConfig.setCacheCount(2); // 2 caches of 256 MB each
      envConfig.setTxnWriteNoSync(true);
      envConfig.setInitializeCache(true);
      envConfig.setThreaded(true);
      envConfig.setInitializeLogging(true);
      envConfig.setTransactional(true);

      StoreConfig sConfig = new StoreConfig();
      sConfig.setAllowCreate(true);

      try
      {
         env = new Environment(envHome, envConfig);
         store = new EntityStore(env, "MyDatabaseName", sConfig);
      }catch(Exception ex)
      {
         ex.printStackTrace();
         System.exit(-1);
      }
   }

   public SimpleStore()
   {
      setup();
   }

   public void putData(String pk, String sk) throws Exception
   {
      sda = new SimpleDA(store);
      sda.addEntry(pk, sk);
   }

   public void getDataPk(String pk) throws Exception
   {
      sda = new SimpleDA(store);
      SimpleEntityClass sec = sda.findByPk(pk);
      System.out.println("pk = "+sec.getpKey()+", sk = "+sec.getsKey());
   }

   public void getDataSk(String sk) throws Exception
   {
      sda = new SimpleDA(store);
      ArrayList data = sda.findBySk(sk);
      for(int x=0; x<data.size(); x++)
      {
         SimpleEntityClass sec = data.get(x);
         System.out.println("pk = "+sec.getpKey()+", sk = "+sec.getsKey());
      }
   }

   public void closeAll() throws Exception
   {
      store.close();
      env.close();
   }

   public static void main(String[] args)
   {
      SimpleStore ss = new SimpleStore();
      ss.putData("pk1","sk1");
      ss.putData("pk2","sk2");
      ss.putData("pk3","sk1");
      ss.putData("pk4","sk3");

      ss.getDataPk("pk1");

      ss.getDataSk("sk1");

      ss.closeAll();
   }
}


So, now you have your own program for creating entities of the type SimpleEntityClass and store them in the database in serialized form. These objects cab be retrieved using primary or secondary keys.

For the relationship between primary and secondary keys please refer to http://www.oracle.com/technology/documentation/berkeley-db/db/java/com/sleepycat/persist/model/Relationship.html

Since you would be storing complete objects instead of just the required data sets, the database size would be relatively high and that would slow down things a bit.

Friday, August 01, 2008

The Other World...

There is this world where you buy your tickets (be it movie tickets or travel tickets) online. Where you go to the big baazaar an swipe your credit card to make the payment. Where you call up for your car service and the service guys take the car from your place, service it and give it back to you with a bill. You use your credit card for most of the transactions and you rarely deal with cash. You sit in an air-conditioned office and punch a few keys on your computer and you get paid heavily for it. The world of a software related person.

And then there is this other world where everything is done through cash. You rely on the travel agent to get your rail and air tickets. And you get your movie tickets from the movie hall ticket window. You take your car to the service station yourself and stand there and get the service done in front of your eyes. You pay cash all the time and try to avoid getting bills to avoid paying the 12.XX% tax on the total amount. You are not eligible for a credit card cause you dont have any asset to show to the banks.

I had an encounter with the "other world" when i got wood work and painting done in my new flat. I came to know that the margin of profit could be increased for 10% to 60% by simply twisting the quality of materials. I also came to know the fact that the MRP quoted on most materials includes a huge profit margin and that you can actually bargain on the MRP.

When i went to get the first lot of wood i came to know that wood comes from 30Rs/sq ft to more than 100Rs/sq ft. And a increase of 5 Rs/sq ft can make a huge difference in the final cost. Dealing with the workers is a pain. For them you are a fresh,delicious and juicy piece of chicken almost ready for being chopped. And they would love to chop you irrespective of what you think. If you leave them to their will, they would chop you and fight over your pieces. For them even 5 Rs has a meaning. And then there are these people in the local market who only understand cash. If you look at their shop, you wont believe that the shop can have lakhs of rs in cash within itself. There are no guards to guard the shop. And they still have lakhs of cash with them and crores of raw materials just lying there. They dont have a credit card machine cause the bank wont give them any. They dont have credit cards themselves cause they dont have any proof of their huge income. They make lakhs in a month and dont file any income tax returns. They hardly pay any tax. And the fact may be that though they may be serving you, but they may be having more wealth and property than you do.

When i made my first payment of 46000 (cash) to the wood supplier, i filled up my office bag with rupees. I was scared of driving on the road and my hands were literally trembling when i was counting the amount i had to pay. But the shop owner was calm and composed as if it was his daily job. These guys dont know what the internet is or how to use it. All they know is how to make money. It is the other world - the actual world. For us this world is hidden behind a layer of service guys and internet.

Tuesday, July 15, 2008

murphy's law

Murphy's law states that "If anything can go wrong, it will".

http://en.wikipedia.org/wiki/Murphy%27s_law
http://www.murphys-laws.com/murphy/murphy-laws.html

I first encountered the murphy's law when i was in school. One of my friends mentioned it and we had a bet that there was no such law. And that friend of mine bought a big thick book on murphy's law to prove that he was right and there is actually such a law.

There has been numerous instances throughout my life which are in line with the murphy's law. But the most recent one was the best.

I was coming back from my in-laws place - chattarpur to delhi. The journey is divided into two parts - part 1 from chattarpur to jhansi by bus. And part 2 from jhansi to delhi by train. Part-1 has to be covered by bus only - and the number of government buses are very low - they are rarely seen. Major bus operators are private who want to make the most by filling up as many passengers as possible per trip. The distance from jhansi to chattarpur is 135 kms. And it takes approx 3 hours by bus. Imagine standing in a very crowded bus for 3 hours without voluntarily movine a muscle. All muscle movements are involuntary - inspired by the movements of the bus and the pot-holes on the road.

So, the story goes that my train was at 6:11 pm and so, taking a buffer of 2 hours, i left from home at 1 pm and caught the 1:30 bus which should have left me at jhansi by 4:30 - max 5:00 pm - which leaves me enough time to go and catch the train. The bus i sat in ran at very slow speed - stopping at all petty stops and even on the road to pick up & drop passengers here and there. I realized that we were running very slow when we covered half the distance in 2.5 hours. So by 4:00 pm we were still 75 kms away from Jhansi. It seems that the bus driver and the passengers both realized this fact at the same time as well - so the driver started over speeding.

I got some hope that we will reach by 5:30 max - still giving me half and hour to catch my train. But then the inevitable happened - a railway crossing and a jam. The train crossed 15 minutes later and it took 15 minutes to cross the jam. Now I was sure that i will miss my train. But the bus driver started speeding. I think its max speed was 65 kmph on which it ran most of the way. Still stopping here and there to pick up and drop passengers.

I started counting milestones. When i saw that we were 20 kms away from jhansi and it was 5:30, i knew that i will either miss my train or as usual the train would be 15 minutes late and i will be lucky enough to catch it. As soon as i saw the jhansi border i jumped off the bus and ran looking for an auto. It was 5:50 and i was desperately hoping that the train would be late.

I flagged down an auto which was carrying 4 passengers and asked him how much time would it take to go to the station. He told me 40 Rs and 10 minutes - by the watch. I told him - i will pay him 50 if he makes it in 10 minutes. All 4 passengers were dropped at the nearby bus stop and the race started. The auto-driver, an old guy tried his best to overtake other vehicles and ride ahead but it seems that autos are not made to race. They are steady means of transport and cannot go beyond 30 kmph.

I was getting nervous and at 6:00, i asked him how much time - he said 2 minutes more. And then out of no-where we were in front of a red light and there was a traffic cop asking all vehicles to stop. I never expected this to happen. It meant that due to this red-light i will be missing my train. My mind went back to school and i recalled my friend who had taught me the murphy's law. And i made up my mind that - as per murphy's law, i wont be able to catch the train. I will miss the train by seconds. But i may as well try to catch it. Maybe my luck would work out and the train would be late.

So, after 5 minutes instead of 2 the auto left me at the station. I handed him 50 rs and ran. Running all the way shouting - "bhaiya hatna" / "jane dena". I saw people moving out of my way - looking at me with an expression of "WTF" in their eyes. I ran up the stairs and towards the 5th platform on which the train generally is. But reaching there, i saw there was no train. I was going to give up when i thought why not get an idea - by how much time i missed the train. And so, i asked a passerby. And he told me that the train was a bit ahead on the platform.

My heart leaped. And i jumped down the stairs. There was a cop standing there and he looked at me with tons of suspicion in his eyes. I asked him the bogie no - "C8" and he realized that i have come late. He told me go straight ahead and I again ran. It has been ages since i had done any physical exercise. And i was out of my breath. I had a heavy bag on by back. And i was sweating like anything. My whole body was paining and asking me to relax & slow down. But i still continued and finally jumped into the C8 coach.

Rushed inside and found my seat. The train whistled and started moving. I looked at my watch and it was 06:12. The trains are generally late by 10-15 minutes. But this time, as per murphy's law, he train was exactly on time.

If anything can go wrong, it will...

Thursday, June 26, 2008

GTA IV

Yes, i have GTA 4. I have been playing on xbox for some time. Halo 3 was good. Awesome visuals - though my TV is not a HDTV and is just 20 inches - around 6 years old cheap one. But still the thrill of playing Halo 3, watching the visuals was good.

Mass Effect was still better. The amount of information associated with each character. How each character reacts to which situation. The power to choose between two different decisions. It was good in its own way.

And now it is GTA 4. I had played the original gta which was a 2d game on my home pc. But that time i was in college and the game was a simple demo. So, though i enjoyed it a lot, but still i wanted more. But, at that time purchasing a game was like a throwing money away. So, i simply enjoyed the 15 minute demo version of GTA.

And then on this sunday, i got the GTA-IV to run on my xbox. I was looking for Gears Of War. But it was not available. The shop keeper instead shoved a GTA-4 under my nose. And i was surprised to see a copy of the most wanted game available so easily. I jumped on it. Checked the seals to ensure that it was orignial and then got it for a hefty 2500/-. That is 2 months salary to my maid. Or 20-25 movies. Or a 80 GB HDD. And instead i spent so much on a single DVD of game.

Anyways, playing GTA is a good experience. In addition to stealing cars and beating up people for money, i could call and fix up meetings and dates with my friend and girl-friend. I could go to strip clubs and play pool. I could go on the internet inside GTA and search for information online. It is like a city where i could do whatever i want.

I have just completed 3-4 missions and am looking forward to more time to spend on it.

Few intersting links:

Missions : http://www.gta4.net/missions/
Official Cheats : http://www.gta4.net/cheats/
Other cheats : http://www.cheatcc.com/xbox360/grandtheftauto4cheatscodes.html

Wednesday, June 18, 2008

QA Tester Versus Developer

How Roshan D'Mello (QA Tester) frustrates developer (Mukesh Thakur)

Roshan D'Mello: Hey Mukesh, there is a bug in your code. Type a text in user name text box and press enter. Beep sound doesn't appear.



Mukesh Thakur: How can that be a bug? There is no requirement that beep sound should come. Anyway, I will assign it to offshore and get it fixed.



After 2 days,

Mukesh Thakur: Roshan, bug is fixed. Please verify.



After another 2 days,

Roshan D'Mello: I have re-opened the bug because sound is not coming in some PCs. Sound is coming in my machine, but my colleague Rajat Choudhry is not getting the sound.



After another 2 days,

Mukesh Thakur: Not a bug. I observed that your friend Rajat Choudhry has old IBM machine. Unlike your DELL machine, IBM machines do not have inbuilt speakers. So, to hear the sound in Rajat Choudhry's machine, please use head phones and then get the bug closed soon.



Another 2 days,

Roshan D'Mello: I have re-opened the bug because sound tone is different across different machines. Sound is coming as 'BEEP' in my machine, but my colleague Rajat Choudhry who is having IBM machine is getting the sound as 'TONG'.

Mukesh Thakur: Not a bug. Get lost man. What can we do for the bug? The two machines are built in such a way that they produce different sounds. Do you expect the developers to rebuild the IBM processors to make them uniform? Please close it.



Another 2 days,

Roshan D'Mello: I have re-opened the bug because intensity of beep sound produced on 2 different DELL machines is different. My machine produces beep sound of intensity 10 decibels whereas my friend's machine produces sound worth 20 decibels. Fix your code to make the sound uniform across all machines.



Another 2 days later,

Mukesh Thakur: Once again it is not a bug. I have noticed that the volume set is different on the two machines. Ensure that volume is same in both the machines before I get mad and then close the bug.



Another 2 days,

Roshan D'Mello: I have re-opened the bug.

Mukesh Thakur: What ?? Why? What more stupid reasons can be there for re-opening?

Roshan D'Mello: Sound intensity is different for machines placed at different locations (different buildings). So, I have re-opened it.



After 2 days,

Mukesh Thakur: I have made some scientists do an acoustical analysis of the two buildings you used for testing. They have observed that the acoustics in the two buildings varies to a large extent. That is why sound intensity is different across the 2 buildings. So, I beg you to please close the bugs.



After 1 year

Roshan D'Mello: I am re-opeing the bug. During the year, I requested the clients to arrange architects to build two buildings with same acoustical features, so that I can test it again. Now, when I tested, I found that intensity of sound still varying. So, I am re-opening the defect.

Mukesh Thakur: GROWLLLL.....I am really mad now. I am sure that the sound waves of the two buildings are getting distorted due to some background noise or something. Now I need to waste time to prove that it is because of background noise.



Roshan D'Mello: No need for that. We will put the machines and run them in vacuum and see.

Mukesh Thakur: (not alive)

Friday, June 13, 2008

vim configuration basic

Have you ever thought about configuring vim? The same vim that you use for editing your files (you should be very familiar with vi editor if you are using unix-like systems). Yes, the editor is highly configurable.

Here are some of the basic tips for vim - Vi Improved 7.1

The global configuration file resides at /etc/vim/vimrc. And the local configuration file resides at your home folder. So if you are logged in as jayant and your home folder is /home/jayant, then your local configuration file would be /home/jayant/.vimrc. If you do not see color in your vi editor, you can do the following

syntax on
set background = dark


I have set the following as my default configuration

jayant@jayantbox:~$ cat .vimrc
set autoindent
set cmdheight=2 "command bar is 2 high
set backspace=indent,eol,start "set backspace function
set hlsearch "highlight searched things
set incsearch "incremental search
set ignorecase "ignore case
set textwidth=0
set autoread "auto read when file is changed from outside
set ruler "show current position
" set nu "show line number
set showmatch "show maching braces
set shiftwidth=2
set tabstop=2
set gfn=Courier\ 12
set t_Co=256
colorscheme oceandeep

This sets the tab width to 2 chars instead of the default 8. Color Scheme is changed to oceandeep. You can get Color schemes for vim from http://www.vim.org/scripts/script_search_results.php?keywords=&script_type=color+scheme&order_by=creation_date&direction=descending&search=search. Your color schemes have to be put in <home>/.vim/colors folder. Auto indenting has been turned on, so you dont need to press tab to indent your code.



Check out my vim using the oceandeep color scheme

Wednesday, June 04, 2008

MySQL versus PostgreSQL - part II

My earlier post mysql versus postgresql brought me lots of negative comments - that i did not compare the transactional database of pgsql with the transactional engine (innodb) of mysql. The main reason why i did not do that was because i had found InnoDB to be very slow as compared to MyISAM.

But after all those comments i ran the benchmarks again using the same scripts and the same technology on the same machine (my laptop) and here are the results. I created a new table in both Mysql (using InnoDB engine) and pgsql. And i disabled the binary logging in mysql to speed up insert/update/delete queries. Please refer to the earlier post for the setup information.

Following notification would be used :

<operation(select/insert/update/delete)> : <no_of_threads> X <operations_per_thread>


  • Firstly i ran single thread with inserts both before and after disabling binary logging in mysql
    Mysql Insert : 1 X 100000
    Time : 65.22 Sec (binary logging enabled)
    Time : 32.62 Sec (binary logging disabled)
    So disabling binary logging in mysql would make your insert/update/delete queries take half the time.
    Pgsql Insert : 1 X 100000
    Time : 53.07 Sec
    Inserts in mysql are very fast.

  • Selects : 2 X 100000
    Mysql time : 30.1 Sec
    Pgsql time : 29.92 Sec
    Both are same

  • Updates : 2 X 50000
    Mysql time : 29.38 Sec
    Pgsql time : 36.98 Sec
    Mysql updates are faster

  • Ran 4 threads with different no_of_operations/thread
    Run 1 [Select : 1 X 100000, Insert : 1 X 50000, Update : 1 X 50000, Delete : 1 X 20000]
    Mysql time : 40.86 Sec
    Pgsql time : 45.03 Sec
    Run 2 [Select : 1 X 100000, Insert : 1 X 100000, Update : 1 X 50000, Delete : 1 X 10000]
    Mysql time : 49.91 Sec
    Pgsql time : 63.38 Sec
    Run 3 [Select : 1 X 100000, Insert : 1 X 20000, Update : 1 X 20000, Delete : 1 X 1000]
    Mysql time : 29.83 Sec
    Pgsql time : 29.3 Sec
    It could be seen that increasing the amount of insert/update/delete queries affects the performance of pgsql. Pgsql would perform better if number of selects are very high. Whereas mysql-innodb performs better in all cases

  • Had 4 runs with different no of threads.
    Run 1: 12 threads [Select : 2X30000 + 3X20000, Insert : 1X20000 + 2X10000, Update : 2X10000, Delete : 2X1000]
    Mysql time : 31.16 Sec
    Pgsql time : 30.46 Sec
    Run 2: 12 threads [Select : 2X50000 + 2X40000 + 1X30000, Insert : 1X20000 + 2X15000, Update : 2X15000, Delete : 2X2000]
    Mysql time : 52.25 Sec
    Pgsql time : 53.03 Sec
    Run 3: 20 Threads [Select : 4X50000 + 4X40000 + 2X30000, Insert : 2X20000 + 3X15000, Update : 2X20000 + 1X15000, Delete : 2X5000]
    Mysql time : 169.81 Sec
    Pgsql time : 136.04 Sec
    Run 4: 30 Threads [Select : 2X50000 + 3X40000 + 3X30000 + 3X20000 + 4X10000, Insert : 1X30000 + 2X20000 + 3X10000, Update : 3X20000 + 3X10000, Delete : 1X10000 + 2X5000]
    Mysql time : 200.25 Sec
    Pgsql time : 156.9 Sec
    So, it can be said that for a small system with less concurrency, mysql would perform better. But as concurrency increases, pgsql would perform better. I also saw that while running the pgsql benchmark, the system load was twice than while running mysql benchmark.


Enabling mysql binary logging for replication would ofcourse add an over head. Similarly enabling trigger based replication in pgsql would be another overhead. The fact that replication in mysql is very closely linked with the database server helps in making a high availability system easier. Whereas creating slaves using replication in pgsql is not that easy. All available products for replication in pgsql are external - 3rd party softwares. Still, for a high concurrency system pgsql would be a better choice.

Tuesday, June 03, 2008

MySQL versus PostgreSQL

I created and ran some simple tests on mysql and postgresql to figure out which one is faster. It is already known that postgresql is more stable and reliable than mysql. pgsql has a rich set of features. It is a complete RDBMS and also supports fulltext search.

All benchmarks were done on my laptop - Intel core 2 duo (2.0 GHz) with 4MB L2 cache & 2 GB ram. I have 64 Bit ubuntu system loaded with MySQL 5.1.24-rc (64 bit binary) and PostgreSQL 8.3.1 (compiled from source).

I used python as a scripting language for writing down my benchmark scripts. I used psycopg2 as a connector from python to postgres and mysql-python as a connector from python to mysql.

The benchmarking was done in phases. Firstly simple Insert, update and select queries were run to check the raw speed of these queries. Then threads were created to run simultaneous insert, update, select and delete queries. I checked the benchmark times for different number of concurrent threads.

I created a simple table on both mysql and pgsql. I used the MyISAM database engine to create table in mysql. :

ABC(id int not null auto_increment primary key, value varchar(250));

Queries that were run are:

Insert(I) : Insert ignore into ABC (id, value) ...(For pgsql, a rule has to be created to ignore duplicate inserts)
Update(U) : Update ABC set value=<something> where id=<random_id>
Select(S) : Select * from ABC where id=<random_id>
Delete(D) : Delete from ABC where id=<random_id>



  • Insert - 100000 rows in 1 thread
    Time taken for Mysql : 20.8 seconds
    Time taken for Pgsql : 58.1 seconds
    So, raw insert speed of mysql is much better as compared to pgsql

  • 100000 selects in 1 thread
    Time taken for Mysql : 21.76 seconds
    Time taken for Pgsql : 20.15 seconds
    Raw selects are better in pgsql as compared to mysql

  • Selects - 2 threads of 100000 selects
    Time taken for Mysql : 40.46 seconds
    Time taken for Pgsql : 27.38 seconds
    So, if i increase the concurrency of selects, pgsql perfors much than mysql

  • Update - 2 threads of 50000
    Time taken for Mysql : 23.97 seconds
    Time taken for Pgsql : 34.03 seconds
    Mysql looks better in handling updates here.

  • 4 Threads
    Run 1 : [100000 Selects, 50000 Inserts, 50000 Updates, 20000 Deletes]
    Time taken for Mysql : 45.25 seconds
    Time taken for Pgsql : 54.58 seconds
    Run 2 : [100000 Selects, 100000 Inserts, 50000 Updates, 10000 Deletes]
    Time taken for Mysql : 59.05 seconds
    Time taken for Pgsql : 69.38 seconds
    Run 3 : [100000 Selects, 20000 Inserts, 20000 Updates, 1000 Deletes]
    Time taken for Mysql : 35.54 seconds
    Time taken for Pgsql : 31.23 seconds
    These runs show that Mysql is good when you have very large no of inserts/updates/deletes as compared to selects. But pgsql's performance surpasses that of mysql when the number of selects are much higher.

  • Finally, lets approach the real life scenario where generally the number of selects are much more than the number of inserts and there are multiple threads performing selects and inserts.
    I will use the following notification here - <no_of_threads> X <no_of_operations(select/insert/update/delete)_per_thread>
    So, for example 3 X 20 Selects = 3 threads of 20 Selects in each thread

    Run 1 : [2 X 30000 selects, 3 X 20000 selects, 1 X 20000 inserts, 2 X 10000 inserts, 2 X 100000 updates, 2 X 1000 deletes] Total - 12 threads
    Time taken for Mysql : 42.28 seconds
    Time taken for Pgsql : 44.28 seconds
    Both Mysql and Pgsql are almost at par.

    Run 2 : [2 X 50000 selects, 2 X 40000 selects, 1 X 30000 selects, 1 X 20000 inserts, 2 X 15000 inserts, 2 X 15000 updates, 2 X 2000 deletes] Total - 12 threads but number of selects are quite high
    Time taken for Mysql : 61.02 seconds
    Time taken for Pgsql : 48.60 seconds
    So, as we increase the number of operations (specially selects) mysql's performance degrades, whereas pgsql's performance remains almost the same

    Run 3 : [4 X 50000 selects, 4 X 40000 selects, 2 X 30000 selects, 2 X 20000 inserts, 3 X 15000 inserts, 3 X 15000 updates, 2 X 3000 deletes] Total - 20 threads (10 threads for select, 5 for insert, 3 for update and 2 for delete) Which is the normal trend in database servers.
    Time taken for Mysql : 169.31 seconds
    Time taken for Pgsql : 128.7 seconds
    Bingo, so as concurrency increases pgsql becomes faster than mysql.



My earlier benchmarks with pgsql 7.x was not as good as this one. With postgresql 8.3.1, the speed of serving concurrent requests has increased a lot. So, in a high concurrency environment, i would generally recommend to go ahead with using postgresql rather than mysql.

Please check the comments section. We have some really interesting comments there...

Saturday, May 31, 2008

From MySQL to PostgreSQL

Why? In brief, it is said that mysql is not as stable as postgresql. Postgresql or pgsql focuses on a single database engine as compared to mysql which has a pluggable engine architecture and has multiple engines. Also postgresql is well designed as compared to mysql. psql console is much better than mysql console (you will realize it when you use it). It is supposed to be much more scalable and have better performance than mysql. Pgsql uses a more standard sql language as compared to mysql.

Both have fulltext search capabilities. Though it is said that the fulltext search of pgsql is better than mysql, but i still have to look into it. And the most important of all - pgsql is a full fledged RDBMS, whereas mysql (using the default MyISAM engine) is a DBMS. Data integrity is better in pgsql as compared to mysql. Also i have seen lots of corrupt tables in mysql(might be because i have used mysql throughout my career), and have heard(on the web) that mysql is more crash prone than pgsql.

The final question then comes down to speed. Which one is faster? Logically, since mysql is a DBMS and it does not maintain foreign key relations, it should be faster than pgsql. I will benchmark and blog the results sometime later.

Basically, pgsql is more stable & reliable than mysql. Pgsql has a rich set of features and data integrity is well maintained in pgsql.

Lets look at some of the steps you need to follow if you decide to switch to pgsql from mysql:


  • First of all, down the database engine from www.postgresql.org. Untar it and compile it.

    $ tar -xvjf postgresql-8.3.1.tar.bz2
    $ cd postgresql-8.3.1
    $ ./configure --with-openssl --enable-thread-safety --with-libxml
    (check out the options that you need using ./configure --help)
    $ make
    $ sudo make install

    By default this will install pgsql in /usr/local directory

  • Next, create a user postgres and the database directory for pgsql
    $ adduser postgres
    $ mkdir /usr/local/pgsql/data
    $ chown postgres.postgres /usr/local/pgsql/data

  • Create the pgsql database:
    Firstly, log into mysql and do
    mysql> show variables like '%character';
    +--------------------------+-----------------------------------------------------------------+
    | Variable_name | Value |
    +--------------------------+-----------------------------------------------------------------+
    | character_set_client | latin1 |
    | character_set_connection | latin1 |
    | character_set_database | latin1 |
    | character_set_filesystem | binary |
    | character_set_results | latin1 |
    | character_set_server | latin1 |
    | character_set_system | utf8 |
    | character_sets_dir | /usr/local/mysql-5.1.24-rc-linux-x86_64-glibc23/share/charsets/ |
    +--------------------------+-----------------------------------------------------------------+

    You will see here that the character set for the database & server is latin1. Even though the default database is utf8. If you create the pgsql database using the default command, it will create a utf8 database and you will face problems importing your data into it.

    So, create a latin1 database for pgsql
    $ su - postgres
    $ initdb -D /usr/local/pgsql/data -E latin1 --locale=C

  • Now you have postgresql installed and you can create your own databases and tables. But before proceeding forward, do psql from user postgres's shell.

    start pgsql:

    $ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >~/logfile 2>&1 &

    $ psql
    Welcome to psql 8.3.1, the PostgreSQL interactive terminal.

    Type: \copyright for distribution terms
    \h for help with SQL commands
    \? for help with psql commands
    \g or terminate with semicolon to execute query
    \q to quit

    postgres=#


    The =# says that you are logged in as a super user. To login as another user, create another user and give him required privileges. And then su to the user and login to pgsql from that user.

    postgres=# CREATE USER jayant WITH CREATEDB INHERIT LOGIN;
    postgres=# \q
    $ su - jayant
    $ createdb test
    $ psql test
    test=>


    Here => says that i am not a super user. Now i can create my tables in the test database or create more databases.

    For help on how to create users do

    test=> \h CREATE USER

    And it will give you the syntax for creating a user in postgres

  • To convert your table from mysql format to pgsql format, just dump the table and data separately from mysql using the mysqldump --no-data and mysqldump --no-create-info respectively.

    And you will get two files <mysql_table>.sql & <mysql_table_data>.sql

    download a perl script mysql2pgsql.perl which will be able to convert the sql for your table into appropriate format for pgsql.

  • Now load the table and data into pgsql

    $ psql -f <mysql_table>.sql
    $ psql -f <mysql_table_data>.sql



It looks simple, but it is not that simple. Some points that need to be remembered while using pgsql


  • auto_increment column is defined using the SERIAL word. So, to create an auto_increment primary key column the syntax would be

    CREATE TABLE MYTABLE(ID SERIAL NOT NULL PRIMARY KEY, ....);

    This would also create a sequence mytable_id_seq.

  • While importing data from the mysql dump you may get some warning about backslash escapes in your sql. To remove this warning you will have to set the escape_string_warning to off.

    edit your postgresql.conf file which can be found in the /usr/local/pgsql/data directory and change the variable to off. Now restart pgsql.

  • To start and stop pgsql following commands can be used

    start pgsql
    $ /usr/local/pgsql/bin/postgres -D /usr/local/pgsql/data >~/logfile 2>&1 &

    stop pgsql
    $ /usr/local/pgsql/bin/pg_ctl stop -D /usr/local/pgsql/data

  • Another thing to note is that you dont have custom queries like 'insert ignore into', 'replace into' or 'insert into ... on duplicate key...' in pgsql. You will need to create rules on the table to handle these cases.

    test=> \h create rule
    Command: CREATE RULE
    Description: define a new rewrite rule
    Syntax:
    CREATE [ OR REPLACE ] RULE name AS ON event
    TO table [ WHERE condition ]
    DO [ ALSO | INSTEAD ] { NOTHING | command | ( command ; command ... ) }


    So for exampe to create an insert ignore into type of rule, the following rule needs to be created.

    test=> CREATE OR REPLACE RULE "insert_ignore_mytable" AS ON INSERT TO "mytable" WHERE EXISTS (SELECT 1 FROM "mytable" WHERE id = NEW.id) DO INSTEAD NOTHING;

  • Remember SHOW PROCESSLIST of mysql which used to list down all the processes.

    To list down all the processes for pgsql following command needs to be run

    test=> select * from pg_stat_activity;



We will looking more and more into pgsql.