Category Archives: Optimization

MySQL: Changing runtime variables with out restarting MySQL server.

14 Replies

I have run in to the issue where I wanted to change some configuration on our production MySQL server but did not want to restart MySQL since its in production.  One of the parameters we like to change time to time is the “log_slow_queries” so we can see what queries are slow in production environment.  But obviously we don’t want to restart mysql server so we start up mysql client and type: 

mysql> set @@long_query_time=3;
Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 3 |
+-----------------+-------+
1 row in set (0.00 sec)

Once you are done checking for slow queries, you can reset it back to whatever value you want by repeating the command with higher number (default is 10).

mysql> set @@long_query_time=10;
Query OK, 0 rows affected (0.00 sec
mysql> show variables like 'long%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| long_query_time | 10 |
+-----------------+-------+
1 row in set (0.00 sec)

There are many more variables you can change.  Type show variables in the mysql client to see the list and what all those variables are set to.

What is this “load average” I keep hearing about?

8 Replies

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 usage but low # for load average because things didn’t get queued much and cpu just spiked a bit at the time you looked at it. You can also have slow responding server with high cpu % and low load average.

So what is ok and what is not ok # to see in load average? This is actually simpler to answer than explaining what is what. For each cpu you add, you add 1 to your high #. For example, if you dual cpu, its ok to see load upto 2. Which basically says both of the cpus were doing 100% of work and its ok. If that # is above 2, lets say 4, that means your system is working twice as hard as it should. So lets say you have dual cpu with hyperthreading, what is the optimial number to see in load average? If you said 4, you are correct!

So now you know! Here are couple other commands which will show you load averages:

w
18:58:26 up 438 days, 13:32, 1 user, load average: 1.83, 2.26, 2.24

uptime
18:58:44 up 438 days, 13:33, 1 user, load average: 1.59, 2.18, 2.21

To learn about the commands used in this post, see man w, man uptime, man top

—————

DISCLAIMER: As always, if you find any inaccurate information, please comment and let me know. When you do comment, make sure you give me some references to confirm.

Improve page load time and increase server capacity by doing simple DNS and server changes

21 Replies

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 servers at the same location for doing other tasks. So I decided to use one of those servers to offload some of the http processing. I setup our dns to point js.domain.com and css.domain.com to go to this spare server which reduced load on our main server quite a bit. We have a lot of js and css content which is loaded every time user hits a page. Our development team took care of the including the domain in the part where it loads js/css on the page. Another benefit of doing this was to allow users’ browsers to simultaneously download from two servers at a time which in turn equates to load time decrease. Browsers only do certain # of connection per server and wait until those requests are completed before making another request. I believe both FF and IE have limit of 2 per domain.
  • Second thing I did was to turn on compression at Apache level to decrease the amount of traffic sent to users. As long as users browsers support compression, apache would server compressed content. Doing this more than halved our bandwidth usage. We went from doing about 20-25 gigs/day to about 11-13 gigs.

As of now our load has been low even during high traffic times. Our bandwidth usage is low and going up with traffic as expected. Users have to wait 1-2 secs during high traffic times compared to 2-5 secs before tweaking. And during normal usage, we barely see 2 sec page load time instead its mostly only 1 sec. As of writing of this blog, we currently have 12781 users on-line with 90 sql queries per sec on average.