InnoDB per-table tablespaces – file for each innodb table

May 7th, 2009 6 Comments   Posted in databases, mysql

Lot has been discussed over the usefulness and power of various mysql engines. (Viz. MyISAM or InnoDB or Archive)

In basic format, MyISAM is faster in SELECT operations and running `fulltext indexes/searches`.

Whereas InnoDB has transaction support and ROW-Level locking which helps in database locking issue.

MyISAM engine stores data and indexes for each table in separate files as follows :
MYI = index file, MYD = data file, FRM = format (schema) file

Whereas default installation of InnoDB engine stores most of the information in single file called ibdata1. (usually /var/lib/mysql/ibdata1 )

This default behaviour of InnoDB engine has few drawbacks: (Its a well known fact that InnoDB needs good tuning to give much solid performance.)
1) Single large file
2) Even if the tables/data is deleted from InnoDB tables, the size taken by ibdata1 does not shrink  (eventually space crunch on server)
3) Fragmentation

To avoid above situations, it is always wise to add per-table tablespace option in InnoDB config parameters in /etc/my.cnf
Add following parameter to conf file :


Read here about the innodb_file_per_table config parameter.

What this parameter will ensure that all new tables (Please note: Only new InnoDB tables) will have individual file for data instead of single large file for all innodb tables.

Now real question is what will happen to existing tables which are already InnoDB/MyISAM tables.

Applying anyone of the following method will ensure conversion of existing InnoDB tables in using multiple tablespaces.

1) Export all databases, delete ibdata1 and import everything back
This is sure way of converting everything to per-table tablespaces along with fresh ibdata1 file

2) Export only InnoDB tables, drop them, delete ibdata1 and import InnoDB tables back into respective databases
If there are lots of databases and dumping all DB is not an option then this step can be performed.
Note: Please ensure backup at every stage

3)     run  following command in your shell

mysqlcheck –optimize –all-databases

4) Convert all InnoDB tables to MyISAM using ALTER TABLE command
for each InnoDB table run following command at mysql prompt

mysql>ALTER TABLE tablename ENGINE=InnoDB;

However STEP 3 and 4 does not shrink the exising size of ibdata1 file.  To regain the space used earlier by ibdata1, please use step1 or step2

And most important after every mysql installation pls add the ‘innodb_file_per_table’ parameter in your /etc/my.cnf


Install fortune cookies on Ubuntu

May 5th, 2009 1 Comment   Posted in linux, tips, ubuntu

I’ve just finished installation of Ubuntu 9.04 (Jaunty) 64-bit on my inspiron 1520.
And let me tell you ext4 is fast … and jaunty is very sleek and nice looking and fast compared to earlier versions or others.

Do you love to get fortune cookies in your linux ?
Adding them as signature to your emails ?
Most of the times the signatures/cookies provided are absolutely awesome and fit perfectly for the situation.
(Or you can obviously try again to get more relevant quote/cookie)

The easiest way to install fortune cookie on ubuntu is as follows :

sudo apt-get install fortune-mod fortunes

It will install many fortune cookies at location : /usr/share/games/fortunes
The above two packages contain enough cookies which should be sufficient for most of our needs.

The usage is as follows :

mayavi@mayavi-laptop:~$ /usr/games/fortune
Talent does what it can.
Genius does what it must.
You do what you get paid to do.

You can get specific topic fortune using following command :

mayavi@mayavi-laptop:~$ /usr/games/fortune linux
If Bill Gates is the Devil then Linus Torvalds must be the Messiah.
– Unknown source

You can combine two or more categories in the above command to get more diverse options.

You can create your own fortune cookies or add to existing ones.

For e.g. to add new quote to Linux, open up a file   /usr/share/games/fortunes/linux

and add your quote in this file between %

I’ve added Next  quote :     %  “1f u c4n r34d th1s u r34lly n33d t0 g37 l41d” %

Post this run following command :

root@mayavi-laptop:/usr/share/games/fortunes# strfile linux
“linux.dat” created
There were 336 strings
Longest string: 1177 bytes
Shortest string: 39 bytes

And you are ready to use your new quotes/files

Add this to you signature in evolution/thunderbird :-)