I have been asked numerous times what does “load average” means in top. If you don’t know what top is and you have access to linux machine, go type top now and see what it shows.

load average: 2.05, 2.17, 1.93

Quick answer is: first number (2.05) is 1 minute avg, second number (2.17) is 5 minute avg, third number (1.93) is 15 min avg. Generally system admins look at these #’s to see how is their server is doing. But now you wonder, if this is the #’s you look at, why is there cpu %? Isn’t that computer load also? Ofcourse it is. BUT, meaning of cpu % shown in [ Cpu(s): 14.2% us, 1.7% sy, 0.0% ni, 80.7% id, 3.1% wa, 0.0% hi, 0.3% si, 0.0% st ] actually just means how much % of time was spent doing stuff on cpu. On the other hand, load average takes other things such as how much cpu’s were being used and how many process had to wait for their turn to use cpu, etc. Thats why sometimes you will see high % for Cpu …

Problem:

One of the sites I maintain has been getting more and more traffic everyday. A very good thing for the site, not so good for the solo server which is serving those pages. The site is VERY dynamic with LAMP setup. We only have one server serving our web pages to our users. Since its a dynamic site with PHP and MySQL, it has a lot of load during peak times. Average load time of a page is between 1-2 secs during normal usage, 2-5 secs under average to heavy load.

During heavy load, we started to see our mysql stop responding to requests which is a big concern for us since we don’t show content if there is no db connection. We had to come with a solution, fast, to prevent this issue from appearing.

Solution:

So here are couple things I ended up doing on the server side to reduce load on this server without having to do much code change.

• We have couple other …

Every programmer loves to optimize, even when we know we shouldn't. To satisfy your cravings MySQL has several keywords that can be placed in your SQL statement to give the database server an explicit optimization instruction.

I should point out that using the hints incorrectly will most likley cause your queries to perform worse, so be sure that it actually makes sense to use them before you go nuts. This means use EXPLAIN and read the documentation on each hint before using.

It's also a good idea to enclose the hints within a SQL comment, for example SELECT /*! SQL_NO_CACHE */ columns FROM table. This can help to make your application a bit more portable.

Let's take a look at some MySQL Optimization Hints:

SQL_NO_CACHE

The SQL_NO_CACHE hint turns off MySQL's …