0

I am looking for a solution to monitor very large production database (many replicas, over 200 tables, some of tables have 10-50 GB of data) with a lot of traffic (a wide set of query complexity).

The main goal is to keep an eye on heavy queries after application code change. We have experienced that new code introduced non-optimal sql queries.

The database dump is very huge so we use a sample on staging environments only. It is almost impossible to detect performance issue on staging in this case.

I thought about using something like New Relic but for such big infrastructure costs are too high and I am not sure it affects performance in a negative way. On the other I can use MySQL slow log. I enabled it on some replicas with a log rate limit. After one month I do not see I/O speed impact but I am not sure about enabling it for all replicas and the master database. I addition I found this article http://blog.symedia.pl/2016/10/performance-impact-general-slow-query-log.html and it says something different that I read in the past.

What would you do to monitor large database?

2
  • there is already a slow query log that you can enable, where you an defne, what you consider slw, there is a log that should be controlled regularlraly
    – nbk
    Commented Jul 8, 2020 at 21:18
  • Check out Percona Monitoring and Management platform or PMM for short. It’s free! It includes query analytics and collects other useful metrics. percona.com/doc/percona-monitoring-and-management/index.html Commented Jul 10, 2020 at 2:31

2 Answers 2

1

You can use pt-query-digest --processlist

While there are four different ways to use it, IMHO the best way to use it is to poll the processlist and collect metrics every hour. This method does not need a slow query log.

I have suggested this before (See mysql 5.7 general_log table has user_host but the file does not)

I have promoted this idea many times over the years.

2
  • Thank you for this recommendation. I didn't know this tool. However can you share some experience on running it frequently on large databases (for example every 15 minutes)? Do you have some suggestions about values for --limit, --interval and --iterations options? I see most people use it to analyze existing slow logs. In addition, did you try to send output from this tool to a logging service like Graylog?
    – deem
    Commented Jul 9, 2020 at 10:11
  • I usually run this every hour using crontab. You can redirect output to a text file like I usually do or to anything else you need that uses stdin Commented Jul 9, 2020 at 19:00
1

At the last large place I worked, I kept the slowlog going on over 100 production servers -- masters and replicas, some large, some small. There was essentially no downside.

long_query_time = 1 is probably a good compromise -- it catches long-running queries, plus some of the frequently-run-but-slow queries. Meanwhile, it does not flood the log file.

pt-query-digest is a good way to find the "worst" queries. I would focus on those.

More info: http://mysql.rjweb.org/doc.php/mysql_analysis#slow_queries_and_slowlog It includes suggested settings.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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