MySQL Telemetry Logging
OpenTelemetry provides a standard for transferring observability data. MySQL has already added support for OpenTelemetry Traces and OpenTelemetry Metrics. MySQL recently added support for OpenTelemetry Logging. OpenTelemetry provides a standardized way to generate, collect, and export telemetry data, including traces, metrics, and logs. By adopting this standard, users of MySQL can gain deeper insights into your databases, connectioning applications, and systems runnning MySQL.
OpenTelemetry: Log
Early in 2024 the APIs for Logging moved from development to stable status. OpenTelemetry log support provides a standardized way to collect and correlate log data across different applications and systems, allowing for easier troubleshooting and improved observability by enabling you to connect logs with traces and metrics, providing a richer context for analyzing system behavior, while using a Cloud Native Compute Foundation (CNCF) standard approach to log collection and export.
MySQL and OpenTelemetry
MySQL has embraced OpenTelemetry, enabling you to collect and correlate traces, metrics, and logs from your database. This integration allows you to:
- Trace Requests: Follow the path of a request through your system, identifying bottlenecks and performance issues.
- Available in 8.4 LTS and 9.x Innovation releases
- Monitor Metrics: Track key performance indicators like query execution time, error rates, and resource utilization.
- Available in 8.4 LTS and 9.x Innovation releases
- Correlate Logs: Connect log messages with specific traces and metrics, providing richer context for troubleshooting.
- Available starting with the 9.1 Innovation release
Correlating Telemetry Data
OpenTelemetry supports the correlation of traces, metrics, and logs using the following attributes:
- Time of Execution: Align data based on the time it was generated.
- Execution Context: Associate data with specific requests or operations using trace and span IDs.
- Origin of Telemetry: Identify the source of the data, such as a specific service or component.
By leveraging these correlation methods, you can gain a comprehensive understanding of your system’s behavior and quickly identify and resolve issues.
Scaling OpenTelemetry with MySQL
OpenTelemetry is particularly well-suited for large-scale deployments of MySQL databases. With a secure push-based model, you can collect telemetry data from thousands of databases without requiring privileged access.
Using OpenTelemetry Collector as a Logging Agent
The OpenTelemetry Collector can efficiently gather and process log data from various sources, including MySQL. By using the OTel standard for logging, you can streamline the collection and export of log data.
Direct-to-Collector Approach
The direct-to-collector approach simplifies log collection by eliminating the need for log agents. MySQL can directly send log data to the OpenTelemetry Collector in a structured format, improving performance and reducing overhead. There are other approachs, but to keep things simple the focus here is on Direct-to-Collector.
Providing:
- Simplified Log Management: No need for complex log parsing or tailing.
- Improved Performance: Reduced overhead from log collection agents.
- Enhanced Security: Direct transmission of logs to a secure collector.
- Increased Flexibility: Customizable data processing and routing with OpenTelemetry pipelines.
Through the implementation of these practices, you can unlock the full potential of OpenTelemetry to optimize your MySQL deployments and make data-driven decisions
OpenTelemetry Collector: Receiver
As MySQL uses the OpenTelemetry APIs, for the YAML receiver configuration – the standard configuration file can be used. Just the default, thus – nothing needs to be added or customized.
receivers:
otlp:
protocols:
http:
OpenTelemetry Collector: Exporters
Next define the target(s) for your telemetry data. This is the details related to the specific data target. Where are we “exporting” the data to. For example an otlp/log endpoint would have
exporters:
endpoint: "<an ip>:443"
headers:
"secret": "YOUR_API_KEY"
"<other headers>": "W"
You obtain these values from the backend application that natively supports OpenTelemetry and can consume the OpenTelemetry data in OTLP format.
For a developer the target could be as simple as a local JSON file for example.
exporters:
file/log:
path: /Users/mfrank/otel/errorlog.json
rotation:
max_megabytes: 10
max_days: 3
max_backups: 3
localtime: true
You can export data to multiple export targets and route through a OpenTelemetry Collector Processor as well.
OpenTelemetry Collector: Pipelines
Finally, you define the service pipeline. Here our log pipeline accepts the incoming log data over OLTP, we use the standard batch processor which helps with efficiency and can be further customized.
service:
pipelines:
logs:
receivers: [otlp]
processors: [batch]
exporters: [file/log]
Installing the MySQL Telemetry Component
Telemetry is not “on” in MySQL Server by default. However, adding the component is simple.
Connect to your MySQL server using a client and execute:
mysql> INSTALL COMPONENT 'file://component_telemetry';
You can check default telemetry trace component settings with:
mysql> SHOW VARIABLES LIKE '%telemetry%';
By default, the installed the Telemetry Log is configured to connect to a local Otel collector.
Running the OtelCollector
If we run an Otel Collector with the above example the data goes to my file/log definition – in this case /Users/mfrank/otel/errorlog.json
./otelcol --config=./configlog.yaml
Then to test, try a login and provide a bad password.
This would generate an error event from the JSON log format with full contextual content.
Video Explanation and Demo
For a video explaining and demoing this click here.
Conclusion
By leveraging OpenTelemetry and its standardized approach to collecting and correlating telemetry data (traces, metrics, and logs), you gain a comprehensive understanding of your MySQL deployments.
This deeper insight empowers you to:
- Optimize Performance: Identify bottlenecks and pinpoint areas for improvement in your MySQL databases.
- Simplify Troubleshooting: Correlate logs with traces and metrics to quickly diagnose and resolve issues.
- Enhance Security: Leverage a secure push-based model for data collection, eliminating the need for privileged access.
- Scale Effectively: Gain valuable insights and manage telemetry data efficiently even in large-scale deployments with thousands of MySQL databases
Additionally, the OpenTelemetry Collector offers flexibility in data processing and routing, further streamlining your observability pipeline.
As always, thank you for using MySQL!
Related Links
- https://blogs.oracle.com/mysql/post/mysql-telemetry-tracing-with-oci-apm
- https://blogs.oracle.com/mysql/post/mysql-telemetry-metrics-with-oci-monitoring-metrics-exporter
- https://dev.mysql.com/doc/refman/9.1/en/telemetry-logging.html
- https://dev.mysql.com/doc/refman/en/telemetry.html
JSON EVENT from Otel Collector File Export
{
"resourceLogs": [
{
"resource": {
"attributes": [
{
"key": "telemetry.sdk.language",
"value": {
"stringValue": "cpp"
}
},
{
"key": "service.namespace",
"value": {
"stringValue": "mysql"
}
},
{
"key": "service.instance.id",
"value": {
"stringValue": "mfrank-mac:28511"
}
},
{
"key": "telemetry.sdk.name",
"value": {
"stringValue": "opentelemetry"
}
},
{
"key": "service.name",
"value": {
"stringValue": "mysqld"
}
},
{
"key": "service.version",
"value": {
"stringValue": "9.1.0"
}
},
{
"key": "telemetry.sdk.version",
"value": {
"stringValue": "1.15.0"
}
}
]
},
"scopeLogs": [
{
"scope": {
"name": "1.0.0"
},
"logRecords": [
{
"timeUnixNano": "1734458944000000000",
"observedTimeUnixNano": "1734458944772071000",
"severityNumber": 9,
"severityText": "INFO",
"body": {
"stringValue": "Access denied for user 'root'@'localhost' (using password: YES)"
},
"attributes": [
{
"key": "mysql.event_name",
"value": {
"stringValue": "logger/error/error_log"
}
},
{
"key": "err_code",
"value": {
"intValue": "10926"
}
},
{
"key": "err_symbol",
"value": {
"stringValue": "ER_ACCESS_DENIED_ERROR_WITH_PASSWORD"
}
},
{
"key": "SQL_state",
"value": {
"stringValue": "HY000"
}
},
{
"key": "thread",
"value": {
"intValue": "12"
}
},
{
"key": "source_file",
"value": {
"stringValue": "sql_authentication.cc"
}
},
{
"key": "source_line",
"value": {
"intValue": "1658"
}
},
{
"key": "function",
"value": {
"stringValue": "login_failed_error"
}
},
{
"key": "component",
"value": {
"stringValue": "sha256_password"
}
},
{
"key": "subsystem",
"value": {
"stringValue": "Server"
}
}
],
"traceId": "",
"spanId": ""
}
]
}
],
"schemaUrl": "http://mysql.com/telemetry/schema/1.0.0"
}
]
}