Voice of the DBA

Tag: monitoring

  • What is the Future of Monitoring?

    I read Tom LaRock’s post on the future of monitoring, looking at the rise of machine learning and complex algorithms to evaluate systems. Tom notes that the data input into a system is crucial in determining whether the system can produce a prediction or output that has value. Since the humans that decide what data is included may not choose wisely, the system won’t necessarily reduce the time required to manage a system.

    I tend to agree. From my conversations with those people having success with machine learning systems, the data preparation is the most critical (and difficult) step. Shortcut the cleansing and organizing, or make poor choices of what data to include and you won’t likely get actionable results.

    However, I have a different view of monitoring. After my watching of the Darpa Grand Challenge (editorial on this recently), I think the future of monitoring is perhaps actually making changes to code in-line. Perhaps with approval from humans, but I suspect that we can train some process to understand how particular batches can cause issues. We can certainly set filters that might note index changes on large tables could be problematic and should be investigated before an issue arises. We can teach a system to recognize code deployments, perhaps even roll back certain changes if the application fails. We certainly could have a machine learning system watching index usage and query plans to recommend indexes in a real time manner, perhaps even turning on and off on-line rebuilds.

    I think there is possibility, but where I’d really like to see advanced monitoring is not in production environments. I’d like to see better systems that can watch development, helping suggest or rewrite SQL before it’s deployed. Perhaps applications can warning developers of potential performance or security issues. Such systems could help us in building more consistency into our applications. We can have systems that help our developers code better.

    As our databases and software become more critical to the functioning of most of our businesses, we certainly need more reliable and robust development practices.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 3.6MB) podcast or subscribe to the feed at iTunes and Libsyn

  • Monitoring and Alerting

    Monitoring your systems is important. It’s not just me that thinks so, as plenty of experienced DBAs and developers know the value of monitoring. Heck, most people have learned to build some sort of metric collection into their software. Azure makes it easy to instrument your application and gather lots of data on how well things are working. Perhaps too easy to gather too much data and then you pay for it, or can’t find time to analyze it. High performing software development shops use monitoring in their Continuous Integration (CI) and Continuous Delivery (CD) pipelines to better understand the health of their code and speed of their workflow, in addition to instrumenting the actual application.

    For those of us that need to ensure our database servers are running well, we not only need monitoring, but also alerting. I ran across a couple articles that have thoughts about monitoring and the difference between monitoring and alerting. While I don’t completely agree with all the items in the second piece, I do think that it’s important that you get alerting working well.

    I’ve had more than my share of un-actionable alerts, or even unnecessary alerts in my career. These days I’ve learned to better classify those items that matter to me. Most of the time what I find myself doing is downgrading most alerts because very few are actually mission critical. Far too often I’ve worried about 100% CPU or slow log writes or even zero sales in an hour or some other metric that “seems” critical. However, since few of these alerts stop business from flowing, I’ve learned to lower their priority or just remove them as alerts and allowing monitoring to track the values. I do need to watch the monitoring and fix issues, but I don’t need to get up at 3am.

    The other thing I’ve worked to do is automate responses to problems. If I know there are ways a computer can respond, let it. Don’t get a human involved if the system can manage itself. Certainly the automated solutions don’t always work, but have some escalation built in that only alerts a human after the system has exhausted its own responses. After all, we don’t want to exhaust humans if we don’t need to do so.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.4MB) podcast or subscribe to the feed at iTunes and LibSyn.

  • Gauges and Alarms

    I was looking through the news this week and found a piece by Michael J. Swart that I really enjoyed. It talks about different types of metrics that he has used when monitoring and alerting on his database servers. Michaal talks about the difference between alarms and gauges, which are usually base on the time scale in which a DBA or administrator needs to react to a situation. Alarms are created when something grows in a short term and causes an issue. The gauges tend to be longer term items that eventually cause a problem.

    I’ve had a similar view in the past. However I look at these types of metrics from the standpoint of how I tackle my job as a DBA. I also try to think about these issues as a developer, ensuring that telemetry helps track issues that occur over time and acute issues cause a graceful degredation (or exit) and not an unexpected one.

    When I’ve needed to manage systems, I ensure that I have alarms set up. I need these for the situations where something goes wrong in a short period of time. However I never want alarms to be based on a lack of visibility into the system over time. I want to try and eliminate as many alarms as possible with better long term monitoring that looks for indicators that the system might have issues. I’ve found that many (not all) of the alarms that I receive could have been prevented if I’d been paying close attention.

    In other words, I’m a proactive DBA, looking at gauges (using MIchael’s terms), not a reactive one counting on alarms.

    I can’t predict everything, but when I find that looking back at data is helpful. If I find a trend that could have alerted me, I’ll evolve my monitoring system to try and track those potential issues in the future. I’ve written my own custom metrics in the past, which track data, either PerfMon type counters or business data, and then let me know when the growth, decline, or change is unexpected. In this way, I tend to find I can often proactively prevent issues. If I can use my data to “predict” an issue my boss doesn’t believe in, I’ve also found my budget for resources may increase a bit, allowing me to manage systems even better in the future.

    Steve Jones

  • Finding Production Drift

    A few years ago when I started delivering presentations about automating database delivery with Grant Fritchey, I was surprised by something. We were rehearsing a talk and Grant mentioned that there were really only two places where we knew the state of our code: in a VCS and in production. I hadn’t really thought about that before, but realized it was true.

    While the development environments are always in flux, by using a VCS, you can determine the state of your code at any point in time. However since changes can occur in production (DBAs adding indexes, changing security, etc), you can’t count on a release branch of some other designation as being a guarantee of the state of your live environment. Production is always its own version of the truth.

    Ideally all of the changes being made on your production system are tested in development, QA, and pre-production environments before being deployed, but very few companies have a mature enough deployment pipeline to allow them to push hot fixes and patches rapidly enough, especially when they need to be made in an emergency.

    More than likely some changes will always be made in production directly, especially small ones that affect data. You can’t prevent this, but you should be able to deal with the aftermath and ensure that your system doesn’t destabilize from the enhancements. Without a doubt DBAs should log any scripts they run, but it’s easy to forget things, especially when changes are made in firefighting mode or at 3am.

    That’s why having some sort of monitoring system to watch production for any changes becomes an important logging mechanism for DBAs. Whether you build one yourself, or buy some software, you use the native tools or bolt something onto SQL Server, make sure that you put something in place. Work to push your ad hoc changes back to development, and ensure they get into a VCS at some point.

    Steve Jones

    The Voice of the DBA Podcast

    Listen to the MP3 Audio ( 2.5MB) podcast or subscribe to the feed at iTunes and LibSyn.