Detect how much stress the mysql database is currently under with PHP

Question

I am developing a large web app and want it to alter itself dependent on a factor that relates to the stress the database is currently under.

I am not sure what would me most accurate/effective/easiest. I am considering maybe the number of current connections or server response time or CPU useage?

What would be best suited and possible?

Thanks

Answer 1

The MySQL Query Profiler does what you are looking for.

http://dev.mysql.com/tech-resources/articles/using-new-query-profiler.html

If you would rather pay money to get a graphical profiler then try this out:

http://www.jetprofiler.com/

Answer 2

The amount of "stress" the database is under is not very real metric. The important thing is to identify how scalable the application is, and the extenet to which the database is contributing to unacceptable performance. This sounds like a bit of a get-out but there's not much point in spending time and effort on something without a clear objective of what you intend to achieve.

Important things are to start recording microsecond level response times in your webserver logs and enable slow query logging in mysql. Then test your DBMS to see what's slow, what's slow AND getting hit often, and what slows down as demand increases.

Certainly if you have performance problems then by all means start looking at CPU, memory usage and I/O but these are primarily symptoms of a performance problem - not true indicators. You might have 10% CPU usage and your system could be running like a dog, or 95% usage and running like a greyhound ;).

System load (i.e. the average length of the run queue is a better indicator than CPU - but still measuring a symtpom. In general database related slowness is usually primarily about I/O issues, and usually resolved by SQL tuning.

C.