MySQL Optimization Issue...

I have optimization problems on my web site (URL).

The configuration of the server is as follows:
I have one box running on Linux (RH7.3)/Apache (1.3.26)/PHP (4.2.2) acting as my Web server and a separate box running on Linux (RH7.1)/MySQL (3.23.49a) acting as my database server. Both these are connected using a VPN.

The problem we face is that the site is intermittently slow (comes to almost a crawl at times). The same page that loads sometimes in 5 seconds takes as much as 400 seconds. I checked the traffic stats on the site and noticed that the traffic is almost similar, averaging at about 600 page views in an hour.

To try to debug the problem further, I tried to run a small test on a local server (intranet) in a controlled environment to see some astonishing results:
We created a file with 1 select query (basically selects some rows from the database and displays them in a table). We had 1 user access this file. We noticed that the page loaded in less than a second (expected). We then asked 10 people to access the same page simultaneously. To our surprise the page loaded in 6 seconds for ALL 10 users. If we increased the number of simultaneous users, the load time increased for EVERYBODY.

I am not sure if this is an issue with locks in MySQL OR is this a Apache/PHP related issue.

Any help would be highly appreciated.

Hello,

I think this problem may have something to do with the connection from the database server to the web server. (The webserver processes all requests before outputting results and perhaps have to wait for all results to be provided by the database server.) Is it possible to test the site when you have the database and webservers on one physical place?

(Please keep in mind that this post is merely a brainstorm. I have no experience with MySql... yet)

Perhaps also usefull is an quote posted by someone on another forum..

http://www.experts-exchange.com/Dat...Q_20242198.html

I have to agree with the statament. Poor database design will hang your database with even a couple of users because processing time takes up a lot of time.

have you tried benchmarking your application? with PEAR's benchmark class you can split up your application in different parts, and see how long each of them takes to execute.. by using that class you'll be able to see what you should optimize

Hi,

Thanks for your posts. I have spent a lot of time trying to find the root cause of the problem and came up with a lot of revelations. There were certain system jobs being fired at the same time when we were firing some cron jobs for batch processing. This used to bring the server down to its knees. We also identified a hardware problem with the ethernet card on the web server. We did further optimization of database tables on mysql. All of this has improved the performance appreciably. An interesting thing that I learnt was about how "resource intensive" multiple echo statements in PHP can be.

Regarding PEAR's benchmarking class, how do I use this? Do I need to install anything on the server (or does it come along with the out-of-the-box install of PHP)? Any instructions of how to use would be great.

Thanks once more.

I actually not sure.. I've never used it before, I have simply heard others say it is good..

http://pear.php.net/packages.php

Benchmark Class: http://pear.php.net/package-info.php?pacid=53
PEAR Base Class: http://pear.php.net/package-info.php?pacid=14

Good luck!