My experience resolving slowness of PHP/MySQ

A client called me several times complaining random slowness of his site which is running a fairly old Fedora 2 with PHP 4 and MySQL 3.

The problem he had is when he access a particular page, sometime, it goes super fast, sometime, it could take over 5 minutes or more to get the result back. There're only around less than 50 people using the site at the same time. Challenge is there's no fix pattern to replicate the issue. And I personally played with the page for over 20 minutes, and never was able to replicate the issue.

With lots of frustration, I started my journey to resolve the issue.

1. My friend google.com
Searched keywords like: slow php mysql linux
Found some hints on how to optimize mySQL and PHP. Nothing really help. However, I did find a debug tool to log the slow queries.

2. Log slow queries
Then I changed my.cnf and added:

set-variable = long_query_time=3
log-slow-queries=/var/log/mysql/slow-query

Please be advised, if you do not put set-variable = in front of long_query_time=3, it will not work, and mySQL will not start.

So I got a nice report of slow queries. From there, I found out some queries without sufficient index. After adding indices, I'm sure lots of queries got improved, however, the particular issue is still there. The big random slowness still happen.

4. mytop
Found a nice tool to monitor mySQL:
http://jeremy.zawodny.com/mysql/mytop/

Installation was easy. It requires PERL and some extra modlues. CPAN did a nice work to help me install them, although it took around half hours to do the upgrading, downloading and installation. Good things is I got lots of things to do while waiting.

In order to make it easy to run mytop, need to create a config file:
~/.mytop

user=<your user name here, I used admin>
pass=<your password here>
host=localhost
db=<your DB name here>
delay=5
port=3306
socket=
batchmode=0
header=1
color=1
1dle=1

Run mytop helped me verified my optimization of queries were working. Again, the issue is still there.

3. Check error log of PHP
Went to check error log of Apache/PHP. WOW! the error log file and access log reached over 600M each!!!
That might explain why it goes so slow. When multiple requests sent to server, to handle each request, server need to log something. To append text at end of a 600M file cannot be fast, not to mention having multiple threads/process racing for the change.
So backed up the log, restarted apache. Looks like things are getting better!

4. Restart Server
Some how found the server has over 2800 tasks. Most of them are related to rpm, awk, etc.
Something like below:

4 0 13076 12539 16 0 2456 264 pipe_w S ? 0:00 awk -v progname=/etc/cron.daily/rpm progname {????? print progname ":\n"????? progname="";???? }???? { print; }
4 0 13077 13075 16 0 6280 432 - S ? 0:00 /usr/lib/rpm/rpmq -q --all --qf %{name}-%{version}-%{release}.%{arch}.rpm\n
4 0 13078 13075 18 0 28856 336 pipe_w S ? 0:00 sort
5 0 19864 1 15 0 10708 1340 pipe_w S ? 0:00 /usr/libexec/webmin/shell/index.cgi


Not sure what they are. So I decided to reboot server. Task number dropped to less than 100.

After the journey, server is working OK so far. No report of slowness yet. But I will give it a couple of more days to confirm.

My channel on Justin.tv

My test TV channel on Justin.tv

Watch live video from lima's channel on Justin.tv

Story on nagios setup and my web server

I tried to installed Nagios on my web server today, hoping it could help me monitoring the server and report any problem it can detect.

After I followed instruction on:

http://nagios.sourceforge.net/docs/3_0/quickstart-fedora.html

/usr/sbin/usermod -G nagcmd apache

to change group of apache, my server went completely down! Apache refused me from accessing any pages on mysite.

I initially thought it was related to SELinux setting, but later I figured, my SELinux is not enabled.

After reviewing secure log, I noticed apache was removed from the following two groups:
apache
psaserv

By adding it back to those groups, my sever is running OK again.

Well, be careful when you change any group setting :(

How to keep program running when ssh session is closed or network is disconnected

I guess most of sys admin or unix server had same experience as I had. Very often, I start a long task, such as disk backup, in a remove ssh terminal. After leaving it running for so many hours, suddenly a quick network disconnection will ruin all the work done before completely! A very quick network instability could drop the ssh session, and all the running work in the session simply got dropped! This is too annoying.

Today, I finally found a great tool to resolve the issue: GNU Screen.

I found a bunch of good tutorials about the great tool. One of my favorite is:
http://www.kuro5hin.org/story/2004/3/9/16838/14935

The tool can be found at:
http://www.gnu.org/software/screen/

Most likely, you probably already have it in your installation, or can simply run:

apt-get install screen
or
yum install screen

to get it.

The idea of screen, I guess, is to create a bunch of local session to terminal services. What your SSH will only connect to the local session, and the real work will be done in the local session. So even you lose connection to screen, your work is still running. When your network is back, you simply attach to the screen session, and you can get everything back. The idea is really similar to VNC or other remote control tools.

For more information about the tool, please check the links I provided above. Hopefully it can resolve lots of headache you already had.

How to config apache to set bandwidth limitation

Here's a nice document about how to set bandwidth limitation on Apache 2 with a module called mod_cband.

http://www.howtoforge.com/mod_cband_apache2_bandwidth_quota_throttling

Download older version of Oracle products

Oracle only put the latest supported product on their site for download. If by any chance an older version is required, you can go to the following site to see if you can find the old version you need:

http://edelivery.oracle.com/

I was looking for Oracle client 10.1.0.3.0 when Oracle only had 10.2.0.4.0 for download. After about 1 month of search, I finally found 10.1.xxx from that site.

Fix display problem when running Far Manager in Chinese environment

Far Manager is the greatest file manager I have been using over the longest time and cannot live without.

Besides of all the great features and plugins, I do keep running into an annoying issue on my Windows with region set to Chinese. The problem is in Chinese environment, FAR window will be screwed with panel borders shown as garbage characters like below :


I have tried many ways to resolve the issue.
On Win95 through XP, I was able to create a batch file just to start Far, create a shortcut to the batch and set Code Page to English. Although I will not be able to see any Chinese file names, the display looks good.

But in Vista, somehow the code page dropdown turned to be empty. So I had to search for other solutions. Luckily, I do find something very handy which I will share below. Basically you can create a shortcut, and enter the following command into it:

C:\Windows\System32\cmd.exe /c chcp 437&&"C:\Program Files (x86)\Far\far.exe"

The idea is simple, it set current code page to 437 by "chcp 437". 437 is English in Windows.
Then start Far.

Please be advised, I'm running Vista 64bit. If you run in 32bit OS, the path is different. But it does not matter.

I also often change layout of shortcut to: 100 x 50:


Click on the shortcut, you will see a nice Far window:



I think the trick also works on other locales in case you run into the same issue.

Just heard Far goes open source! What a great news! Wow!!!