MySQL Logging - Is it worth it?

Question

If I were to enable MySQL logging by doing the following, what are the advantages and disadvantages? What do you do personally?

general_log_file        = /var/log/mysql/mysql.log
general_log             = 1
log_slow_queries        = /var/log/mysql/mysql-slow.log
long_query_time         = 2
log-queries-not-using-indexes

On one hand it is good for security. However, I've heard that it slows down MySQL a lot and has performance impacts. It also causes a lot of disk IO which further slows the server down.

Answer 1

Slow queries are pretty much necessary for performance analysis in a production environment.

Generally, the advice is that you'd have separate disks that you'd use for /var/log on a SQL server that would not impact the disks that handle the actual SQL database itself.

general_log I find less useful except for troubleshooting (client errors etc.) as you should be taking regular backups with mysqldump. I'd only be inclined to turn on general_log when there's something specific you need to troubleshoot.

I'd be more inclined to use binary logs for backups, as the query log will contain select statements that aren't needed for recreation of data. Binary logs are the business for this and, generally quicker to restore if you need to.