My task is to check the performance of MySql DB server through java code(api).Requirement is to check after every 5 sec: 1) DB is is up and running? 2) Response time of queries is as per acceptable standards. 3) Alert on critical situations like DB down,situatuon of deadlock etc.... What i thought is to run a thread after every 5 sec that will check the db status by firing a simple select query....but i am still wondering the way to find the response time as it depends entirely on the type of queries.
If anybody can help me on this...it will be a great help...
I use Nagios to check server-up status. As for doing the other things via an external Java program, I'm very doubtful. You get sort of a Heisenberg effect there, plus I don't know if the information's available through that channel.
Some DBMS's do keep logs and statistical/performance tables, and they can be queried, but I don't recall offhand to what degree that applies to MySQL. Also, this is the kind of thing you're more likely to find in the commercial version of MySQL rather than the free one.
I recommend that you check the MySQL and MySQL Enterprise documentation and see what they say.
An IDE is no substitute for an Intelligent Developer.
You could use jamon to track performance of all your queries. It is easy to setup and works on any database including mysql. See the demo below. jamon also tracks exceptions so you can also see when errors are occuring on your database. You can also get data out of jamon with an SQL like language, and so you could query this on a regular basis to see if queries are running slow, a large ammount of queries are currently running, connections are passing a threshold, or a large number of exceptions are being thrown.
Thanks Tim and Steve for your replies...... I am amazed to see the JAMon capabilities.....I really need to dig into it to understand it completely...Out of several features as of now i just need the DB monitoring part of it......I think now my problem will be solved!