Hello,

Could you pls help me with the following prob that we have been facing since long...

We work on a LAMP architecture. Our server is quite powerful with two processors and 4GB RAM. we have 80 GB of our Hard disk dedicated to Mysql. Still during peak hours we face a server choke. running mytop shows nearly 60 - 70 mysql threads running. queries which normally take around 5 secs takes around 100 secs to complete. When we see the apache server-status, there are many httpd requests in W state.

Running top shows that there is much IO WAIT (around 60%) and CPU idle is almost 0%. We really dont know what is initiating the problem. Is it apache or is it mysql.

I can post the conf details of mysql and httpd if required..
Kindly chk for a mytop attchmnt.

Thanks

Ratheesh

Please login to ssh and provide the output of following command:

Hello,
I m not permitted to give the Host name here. So it is modified. The rest of info is correct

#uname -a
Linux xxx.yyysolutions.com 2.4.21-4.ELsmp #1 SMP Fri Oct 3 17:52:56 EDT 2003 i686 i686 i386 GNU/Linux

I suspected it could be due the kernel version but that's alright. Please post output of:

and output of

Also, please let me know the number and kind of disks you're using. With that powerful server, I think the resource bottleneck is the hard drive. You may need to upgrade to SCSI but let's see the output of those commands first.

Hlo,

Pls find attached the result of top and ps.

Yes, it's MySQL overloading the server. Please post your my.cnf data so that I can help your tweak few mysql variables. Also let me know your PHP and MySQL version. It won't help you a lot, but it should help you a bit.

Do you already have MySQL on a separate disk?

Helo,

I forgot to mention u that the result of top and ps
was taken at a period when there was no load on the server yesterday. When the scenario repeats I will provide the same again for better analysis.

For the time being i am attaching the ps, apache server status, and  mysql processlist results that were taken when the prob occured on 23 June 2006

The my.cnf that you asked has also been attached.

we are running MySQL 4.1.11, PHP 5.0.2.

What problem is MySQL causing? what is initiating the prob?

PS : note the 150 apache requests in W state in the Apache server status. What are ur comments on this?

Do you do hosting on this server? Or is that your site? I can see a number of instances of 'yyyLicense.php' in apache. What the php files does? If that's your website, and you got those files custom programmed, you might want to ask the programmer to optimize the scripts for heavy access.

I forgot to ask, are there MySQL databases on the server using InnoDB engine or all/most are using MyISAM?

It's tough to say anything at the moment. But it could be because of bad queries, too many queries or something similar. If it's because of too many queries / second, it's quite possible that you're hard drive isn't able to handle that many accesses. Did you read my article on server overloading explained ? It might help you understand your loads and find the resource bottleneck.

Please use the attached stat.php file (modify the USERNAME and PASSWORD string in the file before uploading) to get few MySQL stats and data like queries / second etc. If possible, give me a link to the stats file after you're done. Also this output should be helpful:

This post was last modified: 09-07-2006 10:24 AM by Pacifier.

yeah we too were worried about the yyyLicense.php scripts. Its like whenever we encountered there were around 50 - 60 of License requests hanging around in W state until they were timed out.

We checked the script and found nothing fishy. The only thing that we found was that there was a functional semaphore being used in the script. We thought that most probably this might be causing the prob and we wrote a new nonblocking semaphore acquire func in php as an ext ( as sem acquire is always blocking in php ). So now in the nonblocking implementation a request for License will wait only till 12 secs. If it cannot acquire the sem within 12 secs then it exits.

Is the my.cnf ok??

ALL our tables are INNODB type. There are no MyISAM tables at all.

I have updated my previous post to include stat.php. Sorry forgot to include ago. The my.cnf looks okay otherwise. It's probably because of number of requests / queries.

Which version of apache you're running? Can you post your key variables such as MaxClients, TimeOut, MaxSpareServers, MinSpareServers, KeepAlive, KeepAliveTimeout etc..

You may need to disable your Apache logs to decrease some I/O usage or upgrading to SCSI drives (or SATA II at least) is another option. Ohh, and before i justify my opinion, please post the output of "free -m" ..