MySQL ALTER TABLE

Here is something interesting that causes a lot of confusion overtime while changing the structure of a database table or adding index. Problem arises when a query is extremely slow due to insufficient or non-existent indexes. The table is heavily used and contains millions of rows. Site has heavy traffic. Adding a required index is only the solution. But a common misconception is during ALTER TABLE to add an index all the selects and writes will be blocked. This is not true. During alter table only writes are blocked for the entire time but selects are allowed for most part of ALTER TABLE except during the RENAME part. Here is the detail of how ALTER TABLE works :

- open and lock the table
- create a copy of the table
- add index to the copied table
- rename the copied table to new table
- drop the old table

During the rename part there is an upgradation to name lock. It waits for all the selects to finish and then renames it. Particular problems are seen when one expects the ALTER TABLE to finish in less than a second but it is taking a lot longer. Seeing at the processlist will show that there are selects going on from the table. The processlist will not show any sign of lock or something but will be stuck at renaming table for the ALTER TABLE query. All the write queries on the table will be in lock state though.

This can be very useful in maintanence of high traffic site in devising the time for updating indexes etc.

Part of History!

Done with my first project in Boston and I am very proud so say that I was a part of change that happened on Nov 4th in USA. Who would have guessed that my first project at ThoughtWorks will be of such huge importance. I cannot blog about my projects but already enough hint is given for the project that I worked on.
From technology point of view, had very good exposure to PHP, MySQL and Cloud Computing (Amazon Web Services). It is great how scalability is becoming relatively easier with Elastic Cloud Computing (EC2) especially for start ups. There was a very good section on The Economist about the cloud computing from corporate point of view and how it is going to change. Well, definitely so much exciting stuff going on that who can stop!
Working on my personal project on Ruby on Rails these days. I was for long trying to create a website for philately enthusiasts, which will try to incorporate a lot of functionality that as a philatelist I always wanted to have in an application. Presently, a very simple site is up and running on my Mac. I will be in touch with fellow philatelist, whom I know, to test the functionality and recommend enhancements and addition to the site. Hopefully, looking for my beta mode in spring of 2009.