optimizing mysql guide

I had written about this but mostly in parts. Here is a step by step guide to optimizing mysql. Following variables should be looked into for optimizing mysql.

• Memory consumption: Total memory consumption should be less than the available RAM. You should also leave a small amount of memory for the OS. Total memory consumption can be calculated using this formula (for myisam tables):
  Key_buffer + (read_buffer + sort_buffer)*max_connections
  For innodb tables, you should also incorporate innodb_buffer_pool in the formula
  And if you have enabled query cache - add query_cache_size to the formula


• Table cache: monitor your mysql server using "show status like '%tables%'". If your opened_tables keeps on increasing continuously, increase the table_cache size


• Key_buffer: calculate the key_buffer hit ratio using key_reads & key_read_requests:
  cache_hit_ratio = key_reads/key_read_requests
  If cache_hit_ratio > 0.01, then you have some scope of optimization. cache_hit_ratio should ideally be less than 0.01. Increate the key buffer if ratio is more.


• Thread cache: Monitor mysql and see the status of threads_created using "show status like '%thread%'". If threads_created is huge and keeps on increasing continuously, then you should increase the thread_cache_size.
  Thread_cache_hit_ratio = threads_created/Connections


• wait_timeout : If you see a lot of sleeping mysql processes, decrease the wait_timeout. This would terminate sleeping mysql threads after wait_timeout seconds;


• tmp_table_size : if you are using lots of "sort by", "distinct" or "order by" clauses, you should be creating temp tables. Check the status variables using query "show status like '%tmp%'". if the value of created_tmp_disk_tables is large, simply increase the value of tmp_table_size so that the tables are created in memory than on disk.

For query cache optimization pls check http://jayant7k.blogspot.com/2007/07/mysql-query-cache.html

The basic funda is that the more memory you give to mysql, the better it would perform.

These steps should optimize most basic mysql installations.

Trying out windows 7

So, you have windows xp and linux (preferably ubuntu) installed on your system. And you did not install windows vista because it has issues with running some games and also because it is a resource hog. It chews on your resources until none is left. Well, you saw windows 7, you read its reviews and you figured out that maybe you should try windows 7. Here is a how-to of how i went about installing windows 7 and the moments of despair and surprise associated with it.

First of all, you have to download windows 7 from the microsoft's site. I was surprised that microsoft - for the first time in the history of mankind is giving out windows 7 OS for free (even if it is for a year only). Go ahead and download Windows 7. Select the version you want to download 32 bit or 64 bit. Preferably you should go with 32 bit if dont want to worry much about compatibility issues. But if you feel very adventurous, you can also try out the 64 bit version. The 32 bit is of around 2.5 GB and the 64 bit is of around 3 GB. I was surprised by the download speed. I was ablt to download it in an hour or so.

Next, get a DVD-R, and burn the image on DVD-R. But before you proceed to installing windows 7, you need to create some space on your already filled up drive. I have a 500 GB drive with 250 GB dedicated to dumps of games and movies and songs and pics. Out of the remaining 250 GB, i had 100 GB dedicated to windows xp and 150 GB dedicated to linux (2 drives - 100 GB /home and 50 GB /). So, to create more space i decided to shrink my linux drives to 70 GB /home and 30 GB / making around 50 GB free space for windows 7.

To go about resizing partitions, i first decided to use partition magic. But then the geek within me protested. Why should i go ahead and find a crack for a paid partition editor when i can use something like gparted (in linux) to do the same work. I popped in a live cd of kubuntu 8.10 (thinking about it now - i should have created a live cd of ubuntu 9.04). After boot up, I found out that kubuntu does not ship with gparted installed. Hell, so i did a "sudo apt-get install gparted" to install gparted on the virtual drive. It took around 10-15 minutes on my 256 KB connection.

After opening up gparted i simply resized the required partitions and applied the changed. Resizing a partition is an easy job. But moving partitions around is a heavy job. So make changes in such a manner that moving parititons around should not be necessary. I made the mistake of moving my bigger partition (70 GB) and i realized it later. It took around 1.5 hours to do this. And it is always advisable to backup your important data on an external drive before doing this. In case you screw up.

By luck every thing went well and later i was able to boot up both in windows xp & ubuntu.

Now was the time to start installation of windows 7. I popped in the DVD and the main screen came up. Remember to write down the product key that you got during downloading the iso. Windows still punishes you severely if you dont write down the product key. It asks the product key in the middle of installation and if you have forgotten to note down the product key, you will have to do all the work again.

The installation of windows 7 took around 45 minutes. The bad thing is that at times there is no activity and I was confused whether anything is happening or not. Remember to wait and watch. Basically there are 3 steps in installation. Remember to choose custom installation and the free space on your drive for installation. I was surprised that installation started in a flash after i selected the drive. I was hoping to get an installation summary and an option of correcting any mistake i had done. So be-ware, a wrong click and kill your system. Remember to remove the DVD during the first boot - it does not prompt you to do so, but if you dont, you would be starting the installation again. After 2-3 reboots your system is ready and you will get a boot screen (black and white) with option for "earlier version of windows" or "windows 7".

Now all you need to do is restore your grub boot menu. Again pop in your ubuntu live cd and boot up ubuntu. Open a terminal and type.

sudo grub
grub> find /boot/grub/stage1
If you get error 15 "file not found" try
grub> find /grub/stage1
You will get something like (hdX,Y). Do
grub> root (hdX,Y)
grub> setup (hd0)
grub> quit

Now simply reboot and you should be getting your grub boot menu back. Select windows and get the boot menu for windows xp / windows 7.

Whew!!!!