April 2016 Summaries

This article discusses two approaches to collecting performance metrics from MySQL databases: querying internal server status variables and querying the performance schema and sys schema. It provides examples of how to use these methods to retrieve high-level summary statistics or more granular, low-level data. The article also explains how to view these metrics in the free MySQL Workbench GUI or a full-featured monitoring system like Datadog. By using these approaches, users can gain insights into their MySQL databases' usage patterns and performance.

MySQL, a widely-used open-source relational database server owned by Oracle, offers both a free Community Edition and commercial versions with additional features. Monitoring MySQL's performance is crucial, as slow database performance can affect all dependent systems. Essential performance metrics include query throughput, latency, errors, and resource utilization within the InnoDB buffer pool. MySQL's performance schema and sys schema provide comprehensive tools for analyzing these metrics, aiding in the detection of potential issues such as CPU saturation or disk I/O bottlenecks. Key metrics like "Questions" for query execution and "Slow_queries" for identifying slow queries are vital for maintaining database health. Monitoring connection utilization is also essential, as exceeding the connection limit can lead to refused connections. Adjusting settings like the "long_query_time" parameter and the InnoDB buffer pool size can enhance performance, while the sys schema simplifies metric retrieval. Understanding these metrics aids in identifying performance constraints and informs decisions on scaling or optimizing database resources. Part 2 of the series will delve into the collection and monitoring of these metrics.

Datadog provides a comprehensive solution for MySQL monitoring by integrating the Datadog Agent on database servers to collect metrics and performance data. The integration allows users to view historical query performance metrics, explain plans, host-level metrics, and more through Datadog Database Monitoring. This enables users to identify long-term performance trends, recent issues, and optimize their databases for better performance. Additionally, Datadog supports auto-instrumentation for popular languages, libraries, and frameworks, allowing users to start tracing with minimal configuration. The platform also provides logs and distributed traces to troubleshoot performance issues with greater context, giving users the deep visibility needed to resolve any issues that arise.

This post is part 2 of a 3-part series on MySQL monitoring. It explains how to collect metrics from MySQL using two approaches: querying internal server status variables and the performance schema and sys schema. The performance schema stores performance metrics about individual SQL statements, while the sys schema provides easily interpretable tables for inspecting performance data. The post also discusses how to use a GUI, such as MySQL Workbench, to view performance metrics and a full-featured monitoring tool, like Datadog, to implement ongoing monitoring of a production MySQL database.

Kafka is a distributed, partitioned, replicated log service developed by LinkedIn and open sourced in 2011. It's designed for handling real-time data feeds of large companies. Kafka has several key differences from other message queue systems like RabbitMQ, ActiveMQ, or Redis's Pub/Sub. These include being a replicated log service, using a custom binary TCP-based protocol, being very fast even with small clusters, having strong ordering semantics and durability guarantees. Many organizations use Kafka, including LinkedIn, Pinterest, Twitter, and Datadog. The latest release is version 2.4.1. A Kafka deployment consists of brokers that act as intermediaries between producer applications and consumer applications. Producers push messages to brokers in batches, while consumers pull messages from the log at their own rate. Messages are organized into topics, which store related messages, and partitions are assigned to brokers. The greater the number of partitions, the more concurrent consumers a topic can support. Kafka's replication feature provides high availability by persisting each partition on multiple brokers. ZooKeeper is used in Kafka deployments for maintaining information about Kafka's brokers and topics, applying quotas to govern traffic, and storing replicas. Monitoring ZooKeeper metrics is key to maintaining a healthy Kafka cluster. Key metrics include outstanding requests, average latency, number of alive connections, pending syncs, bytes sent/received, usable memory, swap usage, and disk latency. A properly functioning Kafka cluster can handle significant amounts of data, but monitoring health and performance is crucial for reliable performance from dependent applications.

You can monitor your Kafka deployment with Datadog to gain visibility into key metrics, logs, and traces. To implement ongoing monitoring, you will need a platform where you can collect and analyze your Kafka metrics, logs, and distributed request traces alongside monitoring data from the rest of your infrastructure. Datadog automatically collects many of the key metrics discussed in Part 1 of this series and makes them available in a template dashboard. You can install the Datadog Agent to collect metrics, logs, and traces from your Kafka deployment. Before you begin, you must verify that Kafka is configured to report metrics via JMX and check that ZooKeeper is sending JMX data to its designated port. The Datadog Agent collects metrics, logs, and distributed request traces from your hosts so that you can view and monitor them in Datadog. You will need to create Agent configuration files for both Kafka and ZooKeeper and modify the Agent's configuration file to set `logs_enabled: true` to collect logs from Kafka and ZooKeeper. Once the Agent begins reporting metrics from your deployment, you will see a comprehensive Kafka dashboard among your list of available dashboards in Datadog. You can easily create a more comprehensive dashboard to monitor your entire web stack by adding additional graphs and metrics from other systems. With Datadog, you can collect distributed traces to measure request volume and latency, set up alerts to be automatically notified of potential issues, and use Data Streams Monitoring to track and improve the performance of streaming data pipelines and event-driven applications that use Kafka.

Metric tags are crucial for effective monitoring of large-scale and dynamic infrastructure, as they allow for flexible aggregation and observation of metrics across various levels, such as hardware profiles, software versions, and availability zones. In systems like Datadog, tags can be added to metrics on the fly, allowing for real-time updates and automatic metadata detection, which simplifies the process of identifying and responding to changes in infrastructure without needing to modify the data collection process. By using key:value tags, users can create new dimensions and attributes, which enables more detailed and customizable monitoring, making it easier to track performance issues at different abstraction levels. This tagging system eliminates the need for manual aggregation and complex naming conventions, providing a scalable solution suitable for the dynamic nature of modern cloud environments. Datadog also offers the ability to add custom tags programmatically, enhancing the flexibility and adaptability of its monitoring capabilities.