Log SQL queries in production?

Question

I'm having a dilemma on whether or not to log SQL queries in production as well.

I don't know how slow writing files is in PHP. Probably some benchmarks could give some answers, but I wanted to see what you guys think before.

What does or does not make the process slow? Or what things could it depend on?

Answer 1

You have a couple options:

• have your database log the queries
• Create a logger class with a static method that uses a cached file handle to write to.. this is pretty fast. Also, you can set this class up to look at a log variable in config to just ignore the incoming sql query or log it to file. Assuming you are using a database API, you can just extend the query function to include this extra line of code for (optional) logging

Answer 2

Well, the number 1 thing that would be slow would be disk IO via hitting the db. The best answer is for you to try it in some non-trivial cases (remember, everything is fast for small n) and ask some stakeholders if the performance is acceptable. It might not be the answer you are after, but it's really the best answer.

Answer 3

Most databases have built-in options for logging queries and slow queries, so you shouldn't need log through PHP. You should not log all queries in production unless you are having problems and it's part of a troubleshooting process. You can and should log slow queries so you can see what may be slowing down your production site.

If you framework supports it, you can log queries only if the page took a certain amount of time to generate (this is what I do). Then you are logging conditionally and may discover an excessive number of queries being run.

Answer 4

For the record (you didn't specify your DB), Postgresql has a bunch of options related with logging. Among them, I use log_min_duration_statement to log the queries that ran for more than N seconds. Useful for profiling, without filling your log files and interfering with performance. I'd bet most databases have something similar.