monitoring stats and usage in mySQL

sfullman

Hi There,

I'm creating a pretty heavy duty db application, and I would like to monitor or get back usage information. I'm not sure how many of these questions any one person can answer, but if you know some thanks in advance:

Is it possible to get a number of queries by a mysql user over a time period?
Is it possible to get a total SIZE of queries by a mysql user over a time period? i.e., if they run "SELECT * from MEGA_table limit 10000000000" once, that's a certain query size. If they run, "SELECT tinyfield from tinytable limit 5" lots of times, the query SIZE still might be small.
Is it possible to add a procedure which will store these queries, times, and sizes in a table (say in the mysql db or another db I create)?
Now, suppose that instead of by user, I want to get total queries or query SIZES by database, i.e. how much traffic the database got.
FOR 'EXTRA CREDIT': Some queries are really processor intensive even if they produce just one row. Is there any way to quantify the timeslices or cycles used on a query?

Once again, I appreciate any tips, URL's etc. relating to this.

Gratefully,
Sam Fullman

steadyguy

1, 2, 4, 5: MySQL can have logging enabled with various startup options - the general query log (the --log option) does most of the things you're looking for. You can also specify a slow query log (option: --log-slow-queries).

3: MySQL does not yet support stored procedures. Or triggers, for that matter.