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
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:
Interesting question. What you REALLY want is a way for PHP to ask the mySQL server two questions:
server, are you using almost all your cpu capacity?
server, are you using almost all your disk IO capacity?
Based on the answers, I suppose you want to simplify the work your PHP web app does ... perhaps by eliminating some kind of search capability, or caching some data more aggressively.
If you have a shell to your (linux or bsd) mysql server, your two questions can be answered by eyeballing the output from these two commands.
sar -u 1 10 # the %idle column tells you about unused cpu cycles
sar -d 1 10 # the %util column tells you which disks are busy and how busy.
But, there's no sweet little query which fetches this data from mySQL to your app.
Edit: one possibility is to write a little PERL hack or other simple program that runs on your server, connects to the local data base, and once every so often (once a minute, maybe) determines %idle and %util, and updates a little one-row table in your data base. You could, without too much trouble, also add stuff like how full your disks are, to this table, if you care. Then your PHP app can query this table. This is an ideal use of the MEMORY access method. At any rate, keep it simple: you don't want your monitoring to weigh down your server.
A second-best trick, that you CAN do from your client.
Issue the command SHOW PROCESSLIST FULL, count the number of rows (mySQL processes) for which the Command is "Query", and if you have a lot of them consider it to be a high workload.
You might also add up the Time values for the processes which have status Query, and use a high value of that time as a threshold.
EDIT: if you're running on a mySQL 5 server, and your server account has access to the mySql-furnished information_schema, you can use a query directly to get the process data I mentioned:
SELECT (COUNT(*)-1) P.QUERYCOUNT, SUM(P.TIME) QUERYTIME
FROM information_schema.PROCESSLIST P
WHERE P.COMMAND = 'Query'
COUNT(*) - 1: because the above query itself counts as a query.
You will need to fiddle with the threshold values to make this work right in production.
It's a good idea to have your PHP web app shed load when the data base server can't keep up. Still, a better idea is to identify your long-running queries and optimize them.
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.