Quote
Is there any downside of enabling slow_query_log in mysql in production?
No real downside and numerous upsides.
Obviously it will point out particular, slow queries but it can also be monitored
over time to spot new, slow queries emerging and to spot long-term loading patterns (say, more slow queries are seen on particular days of the week/month).
Quote
... to what value we should be setting it ...
That we
cannot tell you.
Only
you know the workload of your particular system and, therefore, what constitutes "slow".
Some sites can enforce 1 second limits. Others might have to allow 30 seconds to allow "most" queries through. The trick is to have the Slow Log catch the
outliers, without being "swamped" by all the Good Work that your database is doing.
Regards, Phill W.