Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I have multiple scripts under PHP execution on linux server, which do constant operations on MySQL residing on the same server. I observed that many 'deadlocks' and 'longer wait timeout exceeded' MYSQL ERRORS while the scripts keep on executing for insert, update & delete on tables having upto 8Lakh+ records, at one particular point of time the scripts complete faster and at some times the scripts take time to complete. My doubt is how can I analyze the load of the server and the load currently made on the MySQL and which script is making the load.

share|improve this question

2 Answers 2

Short answer is that its very difficult to reconcile what's happenning on the DB with what's happenning in PHP land.

Certainly you should set up your slow query log to record just about everything, there's a script here which makes analysin the data a lot easier - what it doesn't so - and what you need to look at is the list of queries sorted by the product of the frequency and run time - the ones with largest values are those you'll get the most benefit from improving.

As to tying it back to the PHP scripts - the only sensible way to do this is by modifying the SQL in the scripts to inject an identifier - either as a comment or as a neutral label, e.g.

 $qry="SELECT '" 
     . mysql_real_escape_string($_SERVER[’SCRIPT_NAME’])
     . "' as executing_script, * FROM mytable WHERE id=$x";
share|improve this answer

You should be able to use the mysql command 'show processlist' to see what the MySQL server is working on at the time of one of these issues. From there, you'll be able to tell SQL statements are currently running or are 'Locked'.

It's hard to tell from your description, however, I'm guessing you're probably running MyISAM tables, which only support table level locking. This means only one command at a time can make changes to each MySQL table. If you have an application that has a large number of rows that you both read and write to regularly, you can create table locks. They will appear in the process list as many 'LOCKED' SQL statements.

You should be able to run 'show processlist' by using the mysql command line client OR using the mysqladmin client. eg.

mysqladmin processlist

see:

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.