Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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.

share|improve this question

1 Answer 1

up vote 5 down vote accepted

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.

share|improve this answer
1  
Agreed. Plus: you should have weekly or bi-weekly sessions with the dev team to analyse the slow query logs and see what can be done about the queries that feature in there. –  wolfgangsz Jun 9 '11 at 20:21

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.