Logging mysql queries

Question

Hi guys,

I am about to begin developing a logging system for future implementation in a current PHP application to get load and usage statistics from a MYSQL database.

The statistic will later on be used to get info about database calls per second, query times etc.

Of course, this will only be used when the app is in testing stage, since It will most certainly cause a bit of additional load itself.

However, my biggest questionmark right now is if i should use MYSQL to log the queries, or go for a file-based system. I'll guess that it would be a bit of a headache to create something that would allow writings from multiple locations when using a file based system to handle the logs?

How would you do it?

Answer 1

If you are doing tests on MySQL you should store the results in a different database such as Postgres, this way you won't increase the load with your operations.

Answer 2

If all you are interested in is longer-term, non-real time analysis, turn on MySQL's regular query logging. There are tons of tools for doing analysis on the query-logs (both regular and slow-query), giving you information about the run-times, average rows returned, etc. Seems to be what you are looking for.

Answer 3

Use the general log, which will show client activity, including all the queries:

http://dev.mysql.com/doc/refman/5.1/en/query-log.html

If you need very detailed statistics on how long each query is taking, use the slow log with a long_query_time of 0 (or some other sufficiently short time):

http://dev.mysql.com/doc/refman/5.1/en/slow-query-log.html

Then use http://www.maatkit.org/ to analyze the logs as needed.

Answer 4

MySQL already had logging built in- Chapter 5.2 of the manual describes these. You'll probably be interested in The General Query Log (all queries), the Binary Query Log (queries that change data) and the Slow log (queries that take too long, or don't use indexes).

If you insist on using your own solution, you will want to write a database middle layer that all your DB calls go through, which can handle the timing aspects. As to where you write them, if you're in devel, it doesn't matter too much, but the idea of using a second db isn't bad. You don't need to use an entirely separate DB, just as far as using a different instance of MySQL (on a different machine, or just a different instance using a different port). I'd go for using a second MySQL instance instead of the filesystem- you'll get all your good SQL functions like SUM and AVG to parse your data.

Answer 5

I agree with macabail but would only add that you could couple this with a cron job and a simple script to extract and generate any statistics you might want.