Why Use Slow Query Logs When There’s SolarWinds Database Performance Monitor?
May 25, 2016 |
Database
At SolarWinds, we believe that database monitoring can be much, much better than what many people currently settle for. In fact, we know that monitoring needs to be better -- more reliable and more efficient than what many engineering and operations teams currently use. Databases are getting larger and more complex all the time, and older monitoring techniques can’t properly optimize modern systems or keep up with issues when they arise. For instance, we consider slow query logs a prime example of a monitoring tool that’s become virtually obsolete in a world where you can find an alternative such as SolarWinds® Database Performance Monitor (DPM).
We tell people to beware of slow query logs for two principal reasons: they can put a dangerous strain on your system, and, even when used well, they don’t provide nearly enough insight. That means that even if you decide to take the (unnecessary!) risk of running slow query logs, they don’t track metrics such as CPU usage -- they don’t provide adequate information to be a reliable guide for understanding your database. There’s no reason to waste time and effort on such imprecise methods.
Though slow query logs have been a cornerstone of database monitoring for a long time, we frequently see our customers relieved to be able to move on to better strategies and more powerful tools. “Traditionally, the way for us to diagnose and follow up on the performance of any data was to follow the old rule of checking slow query logs,” Sergio Roysen, senior MySQL DBA at Shopify, told us in a recent conversation. Prior to using SolarWinds DPM, Sergio said, “it was painful to turn on the slow query log to capture all traffic and run pt-query-digest afterwards. If you don’t capture all traffic then those tools will show you a biased point of view, because they’re only logging the slow queries, and even then you get too much data. It’s just too slow to analyze -- you can’t just open the dashboard and see which queries are not using indexes.”
Sergio went on to compare other qualities of SolarWinds DPM with what he expects when using more traditional tooling. Sergio said, “[Unlike SolarWinds DPM,] you can’t enable slow query logs to capture all traffic for an extended period of time. It requires so much storage that you can only do it for 5 minutes and even then you need to be very careful.” And that’s true: with a slow query log, you need to be cautious, as you can inadvertently take down your server if you leave the log running for too long. On top of that risk, you’re then left with short slices of time with which to work, to identify the issues causing problems in your system; and you’re missing all the other performance metrics from the system during that time, too.
Beyond that, SolarWinds DPM doesn’t merely do the work of slow query logs better, faster, or more reliably; SolarWinds DPM changes your entire expectations for the sort of information you’ll discover and use. “For instance,” Sergio said, “SolarWinds DPM proved to be a big help when we were diagnosing an issue regarding missing rows in a table that we were planning to use for a new feature with new code. We weren’t sure if those rows had actually never been inserted, or if they had been inserted but then deleted, later on. We realized that we had performed an online schema change in that table recently -- such an operation works by inserting rows in a temporary table with the new schema while using triggers to keep track of the changes taking place at the same time. With any tool other than SolarWinds DPM, it would have been very difficult for us to go beyond a hypothesis for how those rows were lost. But with SolarWinds DPM, we were able to look and say, ‘Guess what? Those inserts that our schema change tool should have run, just didn't happen as they should have.’ SolarWinds DPM helped us find out why. It not only showed us what was there -- it also showed us what was not there and what should have been. After that, we knew we could trust SolarWinds DPM.”
For us, that kind of trust represents more than SolarWinds DPM’s ability to provide better visibility than older tools like slow query logs; it means our customers understand that as their systems and technologies grow, SolarWinds DPM will grow alongside them. As modern databases evolve and make increasing demands on monitoring, SolarWinds DPM will continue to meet them.
