MySql Optimization and Good Programming Skills

Why we do optimization: A little time can save thousands in hardware, As you data grows, you’ll see that your application Performance will degrade over time and you’re probably not monitoring it anyway. It is easier than re-coding you applications.
So I am describing the ways to use for optimization of your database (MySql):
1. Benchmarking
2. Profiling
1. Benchmarking: - It make simple performance comparisons, Determine load limits, Test your application’s ability to deal with change. here are following benchmarking tools that are available:

• Mysql Benchmarking suite
  
• Mysql super smack
• MyBench
• ApacheBench ( It comes installed on almost any Unix/Linux distribution with the Apache web server installed, to use it you have to run following command like: > ab -n 100 -c 10 http://127.0.0.1/finduser1.php where 100 is the total no of connections and 10 the total concurrent requsts send at a time. )
  
• Httperf
  

Load generators: Contrieved Load Generators (Apache Bench) - For a particular web page, Realistic Load generators (Httperf) - For generating actual traffic.

2. Profiling: This enable you to procure information about - memory consumption, response times, locking, process counts from the engines that execute your SQL scripts and application code. There are a lot of profiling tools are available:

• The SHOW FULL PROCESSLIST and SHOW STATUS command ( SHOW FULL PROCESSLIST example: mysql> SHOW FULL PROCESSLIST; run this command and then see the result. SHOW STATUS Command Example:
  mysql> SHOW STATUS LIKE 'Qcache%';)
  
• The EXPLAIN command (Run Query with using EXPLAIN Command, it will give you the whole process of a query such as which column is used as index and how many rows are coming as a result and type of query.)
  
• SLOW QUERY and GENERAL QUERY LOG (This you can use by adding some lines in your mysql conf file (my.cnf): log-slow-queries = /var/log/mysql/mysql-slow.log long_query_time = 1 here /var/log/mysql/mysql-slow.log is the path of your log file by default this will create a file at /var/lib/mysql with a suffix of -slow.log and long_query_time is the time which is the longest time a query can take and its lowest value is 1 and DEFAULT value is 10)
  
• MyTop (It summarizes the SHOW FULL PROCESSLIST and various SHOW STATUS statements. Practicallu used it, its a good tool for profiling, but you need to install it from http://www.cpan.org/modules/by-module/Term/ )
  
• The Zend Advanced PHP Debugger extension (APD) - It is very good Profiling tool for profiling PHP as it does function call traces for your pages, memory consumption, execution time etc.
  

Practically, I have used AB (ApacheBench) for generating load for a single page of the site. It was giving the whole process time for a page when 10 (I gave 10) requests were concurrently processing for the page for total no of 100 connections, Then I checked my slow query log file which were having the list of all queries taking long time than given in mysql configuration file. I used mysqldumpslow (in command prompt go to the log file directory and do :> mysqldumpslow slowlogfilename) for checking this log file, mysqldumpslow will give how many times each query run and its process time. And I got that a lot of queries were repeated for a same row, So I changed my API in the form so that it will fetch the data from same table for same WHERE clause in a single database hit then I process those values further in my API. So I suggest that for a good programmer, he/she must design his/her API in such a way so that lowest data base hit will occur, and only select those column which are necessary at that time as use of SELECT * FROM... is much more costly than SELECT column_name1 FROM ...

I also used indexing for the tables in which SELECT queries were more called more that INSERT, UPDATE or DELETE. (I suggest to use index for a table for only that column which is called many time in WHERE clause of query and also make indexing for a single column when you are using AB so that you will get to know easily for which column index your query processing time is lower.)

I also suggest to INSERT or SELECT data in BUNDLE. (So that less data base hit will occur)

Nine Rules for Web Startups

I am describing nine rules for website designing, Hope these will be useful for all web developers.

1: Be Narrow: Most website try to do too many things that it become too difficult to manage and understand, so always try to do smallest things that are potentially useful.
2: Be Different: There are a lot of people who will be thinking same as you are, so always try to do different from others.
3: Be Casual: Flickr enables personal publishing among millions of folks who would never consider themselves personal publishers—they're just sharing pictures with friends and family, a casual activity. Casual games are huge (Now this is not the era of hobbyist web or the professional web) even Skype also provide casual conversation.
4: Be Picky: You must give space to others if something is not going right, false negatives are usually better than false positives. One of Google's biggest strengths—and sources of frustration for outsiders.
5: Be User-Centric: User experience is everything.
6: Be Self Centered: Create something does not exist in this world, be a user o0f your own product.
7: Be Greedy It's always good to have options. One of the best ways to do that is to have income.
8: Be Tiny: Acquisitions are much easier if they're small. And small acquisitions are possible if valuations are kept low from the get go.
9: Be Balanced: You must be balanced in each and every situation as you have to give a lot of commitment to acquire something, but this will not be for always.

Linux Packaging..

There are a lot of problems about linux packaging, like why they dont have a single MSI (this is Microsoft Windows Installer which is having the collection of all the dependencies file in one .msi package) in which all of its dependencies are collected and they just ship them with their installation package. So I want to tell the benefits of linux packaging system.
1. Linux is the world best shared library (dynamic link Library DLL) system: Linux have one of the best shared library system, even you can get more version of same library.
2. linux automatic dependencies system is good: Dependencies are the third party software that are installed to support another software. In Windows when you installed any updation for software then in windows this new DLL overwrite the old DLL, and this creates the registry corrupt. But in linux it save the new DLLs in a new directory under file system (/usr/local/..). And we can switch in between new and old DLLs as needed.
In Linux if the linux is installed properly, then the possibility to get dependencies files will be more. So if any new person is installing linux first time then he/she must choose its full version.

Installation experience

Today I have installed profiling software for my project that will catch the time for all functions running on a page. And a lot more about the classes we are using on that page.
I have also installed Wiki (an open web page), in which any user can change any thing on that page himself. This was quite intresting as I am knowing a lot about Apache installation. It is very necessary for a developer to know not atleast about his/her programming language but also about the enviornment in which his/her is working.