Skip to main content

MariaDB - Classic Collector

Thumbnail icon

The MariaDB app is a unified logs and metrics app that helps you monitor MariaDB database cluster availability, performance, and resource utilization. Pre-configured dashboards and searches provide insight into the health of your database clusters, performance metrics, resource metrics, schema metrics, replication, error logs, slow queries, Innodb operations, failed logins, and error logs.

This app is tested with the following MariaDB versions:

  • Kubernetes: MariaDB - Version 10.5.11
  • Non-Kubernetes: MariaDB - Version 10.7.1

Collecting Logs and Metrics for the MariaDB app​

Configuring log and metric collection for the MariaDB app includes the following tasks.

Step 1: Configure Fields in Sumo Logic​

Create the following fields in Sumo Logic before configuring the collection to ensure that your logs and metrics are tagged with relevant metadata, which is required by the app dashboards. For information on setting up fields, see Sumo Logic Fields.

If you're using MariaDB in a Kubernetes environment, create the fields:

  • pod_labels_component
  • pod_labels_environment
  • pod_labels_db_system
  • pod_labels_db_cluster
  • pod_labels_db_cluster_address
  • pod_labels_db_cluster_port

Step 2: Configure Collection​

Sumo Logic supports the collection of logs and metrics data from MariaDB in both Kubernetes and non-Kubernetes environments. Click on the appropriate links below based on the environment where your MariaDB clusters are hosted.

In Kubernetes environments, we use the Telegraf Operator, which is packaged with our Kubernetes collection. You can learn more about it here. The diagram below illustrates how data is collected from MariaDB in Kubernetes environments. In the architecture shown below, there are four services that make up the metric collection pipeline: Telegraf, Telegraf Operator, Prometheus, and Sumo Logic Distribution for OpenTelemetry Collector.

mariadb

The first service in the metrics pipeline is Telegraf. Telegraf collects metrics from MariaDB. Note that we’re running Telegraf in each pod we want to collect metrics from as a sidecar deployment, that is Telegraf runs in the same pod as the containers it monitors. Telegraf uses the MySQL Input Plugin to obtain metrics. (For simplicity, the diagram doesn’t show the input plugins.) The injection of the Telegraf sidecar container is done by the Telegraf Operator.

Prometheus pulls metrics from Telegraf and sends them to Sumo Logic Distribution for OpenTelemetry Collector which enriches metadata and sends metrics to Sumo Logic.

In the logs pipeline, Sumo Logic Distribution for OpenTelemetry Collector collects logs written to standard out and forwards them to another instance of Sumo Logic Distribution for OpenTelemetry Collector, which enriches metadata and sends logs to Sumo Logic.

Prerequisites

These instructions assume that you are using the latest Helm chart version. If not, upgrade using the instructions here.

Configure Metrics Collection​

This section explains the steps to collect MariaDB metrics from a Kubernetes environment.

  1. Set up Kubernetes Collection with the Telegraf Operator
  2. On your MariaDB Pods, add the following annotations:
annotations:
    telegraf.influxdata.com/class: sumologic-prometheus
    prometheus.io/scrape: "true"
    prometheus.io/port: "9273"
    telegraf.influxdata.com/inputs: |+
[[inputs.mysql]]
  servers = ["user_TO_BE_CHANGED:password_TO_BE_CHANGED@tcp(IP_ADDRESS_MARIADB_TO_BE_CHANGED:PORT_MARIADB_TO_BE_CHANGED)/?tls=false"]
  metric_version = 2
  table_schema_databases = []
  perf_summary_events = []
  gather_table_schema = true
  gather_process_list = true
  gather_info_schema_auto_inc = true
  gather_user_statistics = true
  gather_slave_status = true
  gather_table_io_waits = true
  gather_table_lock_waits = true
  gather_index_io_waits = true
  gather_event_waits = true
  gather_file_events_stats = true
  gather_perf_events_statements = true
  interval_slow = "30m"
  [inputs.mysql.tags]
    environment ="ENV_TO_BE_CHANGED"
    component ="database"
    db_system ="mariadb"
    db_cluster ="ENV_TO_BE_CHANGED"
    db_cluster_address = "ENV_TO_BE_CHANGED"
    db_cluster_port = "ENV_TO_BE_CHANGED" --Enter `default` if you haven’t defined a cluster in MariaDB
  1. Enter in values for the following parameters (marked ENV_TO_BE_CHANGED in the snippet above):

  • telegraf.influxdata.com/inputs. This contains the required configuration for the Telegraf exec Input plugin. Please refer to this doc for more information on configuring the MySQL input plugin for Telegraf. Note: As telegraf will be run as a sidecar, the host should always be localhost.

  • In the input plugins section, that is:

    • servers - The URL of your MariaDB server. For information about additional input plugin configuration options, see the Readme for the MySQL input plugin.

  • In the tags section ([inputs.mysql.tags]):

    • environment. This is the deployment environment where the MariaDB cluster identified by the value of servers resides. For example: dev, prod or qa. While this value is optional we highly recommend setting it.
    • db_cluster. Enter a name to identify this MariaDB cluster. This cluster name will be shown in the Sumo Logic dashboards.
    • db_cluster_address. Enter the cluster hostname or ip address that is used by the application to connect to the database. It could also be the load balancer or proxy endpoint.
    • db_cluster_port. Enter the database port. If not provided, a default port will be used.
    note

    db_cluster_address and db_cluster_port should reflect exact configuration of DB client configuration in your application, especially if you instrument it with OT tracing. The values of these fields should match exactly the connection string used by the database client (reported as values for net.peer.name and net.peer.port metadata fields).

    For example, if your application uses “mariadb-prod.sumologic.com:3306” as the connection string, the field values should be set as follows: db_cluster_address=mariadb-prod.sumologic.com db_cluster_port=3306.

    If your application connects directly to a given sqlserver node, rather than the whole cluster, use the application connection string to override the value of the “host” field in the Telegraf configuration: host=mariadb-prod.sumologic.com

    Pivoting to Tracing data from Entity Inspector is possible only for “MariaDB address” Entities.

  • Do not modify the following values as it will cause the Sumo Logic app to not function correctly.

    • telegraf.influxdata.com/class: sumologic-prometheus. This instructs the Telegraf operator what output to use. This should not be changed.
    • prometheus.io/scrape: "true". This ensures our Prometheus will scrape the metrics.
    • prometheus.io/port: "9273". This tells prometheus what ports to scrape on. This should not be changed.
    • telegraf.influxdata.com/inputs
    • In the tags section ([inputs.mysql.tags]):
      • component: “database” - This value is used by Sumo Logic apps to identify application components.
      • db_system: “mariadb” - This value identifies the database system.
    • See this doc for more parameters that can be configured in the Telegraf agent globally.
  1. Sumo Logic Kubernetes collection will automatically start collecting metrics from the pods having the labels and annotations defined in the previous step.
  2. Verify metrics in Sumo Logic.

Configure Logs Collection​

This section explains the steps to collect MariaDB logs from a Kubernetes environment.

  1. (Recommended Method) Add labels on your MariaDB pods to capture logs from standard output. Make sure that the logs from MariaDB are sent to stdout. Follow the instructions below to capture MariaDB logs from stdout on Kubernetes.
    1. Apply following labels to the MariaDB pod:
    environment: "prod_ENV_TO_BE_CHANGED"
    component: "database"
    db_system: "mariadb"
    db_cluster "Cluster_ENV_TO_BE_CHANGED"
    db_cluster_address = "ENV_TO_BE_CHANGED"
    db_cluster_port = "ENV_TO_BE_CHANGED"
    1. Enter in values for the following parameters (marked in "ENV_TO_BE_CHANGED" above):
    • environment. This is the deployment environment where the MariaDB cluster identified by the value of servers resides. For example: dev, prod, or QA. While this value is optional, we highly recommend setting it.
    • db_cluster. Enter a name to identify this MariaDB cluster. This cluster name will be shown in the Sumo Logic dashboards. If you haven’t defined a cluster in MariaDB, then enter default for db_cluster.
    • Do not modify the following values as it will cause the Sumo Logic apps to not function correctly.
      • component: “database” - This value is used by Sumo Logic apps to identify application components.
      • db_system: “mariadb” - This value identifies the database system.
    • See this doc for more parameters that can be configured in the Telegraf agent globally.
    1. The Sumologic-Kubernetes-Collection will automatically capture the logs from stdout and will send the logs to Sumologic. For more information on deploying Sumologic-Kubernetes-Collection, visit here.
    2. Verify logs in Sumo Logic.
  2. (Optional) Collecting MariaDB Logs from a Log File. Follow the steps below to capture MariaDB logs from a log file on Kubernetes.
    1. Determine the location of the MariaDB log file on Kubernetes. This can be determined from the server.conf for your MariaDB cluster along with the mounts on the MariaDB pods.
    2. Install the Sumo Logic tailing sidecar operator.
    3. Add the following annotation in addition to the existing annotations.
    annotations:
      tailing-sidecar: sidecarconfig;<mount>:<path_of_MariaDB_log_file>/<MariaDB_log_file_name>
    Example: 
    annotations:
      tailing-sidecar: sidecarconfig;data:/var/opt/MariaDB/errorlog
    1. Make sure that the MariaDB pods are running and annotations are applied by using the command:
    kubectl describe pod <MariaDB_pod_name>
    1. Sumo Logic Kubernetes collection will automatically start collecting logs from the pods having the annotations defined above.
    2. Verify logs in Sumo Logic.
  3. Add an FER to normalize the fields in Kubernetes environments. This step is not needed if using application components solution terraform script. Labels created in Kubernetes environments automatically are prefixed with pod_labels. To normalize these for our app to work, we need to create a Field Extraction Rule if not already created for Proxy Application Components:
    1. Go to Manage Data > Logs > Field Extraction Rules.
    2. Click the + Add button on the top right of the table.
    3. The Add Field Extraction Rule form will appear:
    4. Enter the following options:
      • Rule Name. Enter the name as App Observability - database.
      • Applied At. Choose Ingest Time
      • Scope. Select Specific Data
      • Scope. Enter the following keyword search expression:
      pod_labels_environment=* pod_labels_component=database
      pod_labels_db_cluster=* pod_labels_db_system=*
      • Parse Expression. Enter the following parse expression:
      if (!isEmpty(pod_labels_environment), pod_labels_environment, "") as environment
       | pod_labels_component as component
       | pod_labels_db_system as db_system
       | if (!isEmpty(pod_labels_db_cluster), pod_labels_db_cluster, null) as db_cluster
    5. Click Save to create the rule.

Installing the MariaDB Monitors​

The next few sections provide instructions for installing the MariaDB Monitors app, as well as examples of each of the app dashboards. These instructions assume you have already set up the collection as described in the Collect Logs and Metrics for the MariaDB app page.

Pre-Packaged Alerts​

Sumo Logic has provided out-of-the-box alerts available through Sumo Logic monitors to help you monitor your MariaDB clusters. These alerts are built based on metrics and logs datasets and include preset thresholds based on industry best practices and recommendations. See Alerts for more information.

  • To install these alerts, you need to have the Manage Monitors role capability.
  • Alerts can be installed by either importing a JSON file or a Terraform script.
  • There are limits to how many alerts can be enabled - see the Alerts FAQ for details.

Method A: Importing a JSON file​

  1. Download the JSON file that describes the monitors.
  2. The JSON contains the alerts that are based on Sumo Logic searches that do not have any scope filters and therefore will be applicable to all MariaDB clusters, the data for which has been collected via the instructions in the previous sections. However, if you would like to restrict these alerts to specific clusters or environments, update the JSON file by replacing the text db_system=mariadb with <Your Custom Filter>. Custom filter examples:
    • For alerts applicable only to a specific cluster, your custom filter would be db_cluster=mariadb-prod.01.
    • For alerts applicable to all clusters that start with Kafka-prod, your custom filter would be db_cluster=mariadb-prod*.
    • For alerts applicable to a specific cluster within a production environment, your custom filter would be db_cluster=mariadb-1 and environment=prod. This assumes you have set the optional environment tag while configuring collection.
  3. Go to Manage Data > Alerts > Monitors.
  4. Click Add.
  5. Click Import and then copy-paste the above JSON to import monitors.
  6. The monitors are disabled by default. Once you have installed the alerts using this method, navigate to the MariaDB folder under Monitors to configure them. See this document to enable monitors to send notifications to teams or connections. See the instructions detailed in Add a Monitor.

Method B: Using a Terraform script​

  1. Generate a Sumo Logic access key and ID. Generate an access key and access ID for a user that has the Manage Monitors role capability in Sumo Logic using these instructions. Identify which deployment your Sumo Logic account is in, using this link
  2. Download and install Terraform 0.13 or later.
  3. Download the Sumo Logic Terraform package for MariaDB alerts. The alerts package is available in the Sumo Logic GitHub repository. You can either download it through the “git clone” command or as a zip file.
  4. Alert Configuration. After the package has been extracted, navigate to the package directory terraform-sumologic-sumo-logic-monitor/monitor_packages/MariaDB/. Edit the MariaDB.auto.tfvars file and add the Sumo Logic Access Key, Access Id, and Deployment from Step 1.
access_id   = "<SUMOLOGIC ACCESS ID>"
access_key  = "<SUMOLOGIC ACCESS KEY>"
environment = "<SUMOLOGIC DEPLOYMENT>"

The Terraform script installs the alerts without any scope filters, if you would like to restrict the alerts to specific clusters or environments, update the variable mariadb_data_source. Custom filter examples:

  • For a specific cluster, your custom filter would be db_cluster=mariadb.prod.01
  • For all clusters in an environment, your custom filter would be environment=prod
  • For alerts applicable to all clusters that start with mariadb-prod, your custom filter would be db_cluster=mariadb-prod*
  • For alerts applicable to a specific cluster within a production environment, your custom filter would be db_cluster=mariadb-1 and environment=prod. This assumes you have set the optional environment tag while configuring collection.

All monitors are disabled by default on installation. If you would like to enable all the monitors, set the parameter monitors_disabled to false in this file.

By default, the monitors are configured in a monitor folder called “MariaDB”. If you would like to change the name of the folder, update the monitor folder name in “folder” key at MariaDB.auto.tfvars file. If you would like the alerts to send email or connection notifications, configure these in the file MariaDB_notifications.auto.tfvars. For configuration examples, refer to the next section.

  1. Email and Connection Notification Configuration Examples. Modify the file MariaDB_notifications.auto.tfvars and populate connection_notifications and email_notifications as per below examples.
Pagerduty Connection Example
connection_notifications = [
    {
      connection_type       = "PagerDuty",
      connection_id         = "<CONNECTION_ID>",
      payload_override      = "{\"service_key\": \"your_pagerduty_api_integration_key\",\"event_type\": \"trigger\",\"description\": \"Alert: Triggered {{TriggerType}} for Monitor {{Name}}\",\"client\": \"Sumo Logic\",\"client_url\": \"{{QueryUrl}}\"}",
      run_for_trigger_types = ["Critical", "ResolvedCritical"]
    },
    {
      connection_type       = "Webhook",
      connection_id         = "<CONNECTION_ID>",
      payload_override      = "",
      run_for_trigger_types = ["Critical", "ResolvedCritical"]
    }
  ]

Replace <CONNECTION_ID> with the connection id of the webhook connection. The webhook connection id can be retrieved by calling the Monitors API.

For overriding payload for different connection types, refer to this document.

Email Notifications Example
email_notifications = [
    {
      connection_type       = "Email",
      recipients            = ["abc@example.com"],
      subject               = "Monitor Alert: {{TriggerType}} on {{Name}}",
      time_zone             = "PST",
      message_body          = "Triggered {{TriggerType}} Alert on {{Name}}: {{QueryURL}}",
      run_for_trigger_types = ["Critical", "ResolvedCritical"]
    }
  ]
  1. Install the Alerts. Navigate to the package directory terraform-sumologic-sumo-logic-monitor/monitor_packages/MariaDB and run terraform init. This will initialize Terraform and will download the required components.
    1. Run terraform plan to view the monitors which will be created/modified by Terraform.
    2. Run terraform apply.
  2. Post Installation. If you haven’t enabled alerts and/or configured notifications through the Terraform procedure outlined above, we highly recommend enabling alerts of interest and configuring each enabled alert to send notifications to other users or services. This is detailed in Step 4 of this document.

Installing the MariaDB app​

This section demonstrates how to install the MariaDB app. To install the app:

Locate and install the app you need from the App Catalog. If you want to see a preview of the dashboards included with the app before installing, click Preview Dashboards.

  1. From the App Catalog, search for and select the app.
  2. Select the version of the service you're using and click Add to Library.
note

Version selection is not available for all apps.

  1. To install the app, complete the following fields.
    1. App Name. You can retain the existing name, or enter a name of your choice for the app.

    2. Data Source.
      • Choose Enter a Custom Data Filter, and enter a custom MariaDB cluster filter. Examples;
        1. For all MariaDB clusters, db_cluster=*.
        2. For a specific cluster, db_cluster=mariadb.dev.01.
        3. Clusters within a specific environment db_cluster=mariadb.dev.01 and environment=prod. This assumes you have set the optional environment tag while configuring collection.
    3. Advanced. Select the Location in Library (the default is the Personal folder in the library), or click New Folder to add a new folder.
  2. Click Add to Library.

Once an app is installed, it will appear in your Personal folder, or another folder that you specified. From here, you can share it with your organization.

Panels will start to fill automatically. It's important to note that each panel slowly fills with data matching the time range query and received since the panel was created. Results won't immediately be available, but with a bit of time, you'll see full graphs and maps.

Viewing MariaDB Dashboards​

Filter with template variables

Template variables provide dynamic dashboards that can rescope data on the fly. As you apply variables to troubleshoot through your dashboard, you view dynamic changes to the data for a quicker resolution to the root cause. You can use template variables to drill down and examine the data on a granular level. For more information, see Filter with template variables.

Overview​

The MariaDB - Overview dashboard gives you an at-a-glance view of the state of your database clusters by monitoring key cluster information such as errors, failed logins, errors, queries executed, slow queries, lock waits, uptime, and more.

Use this dashboard to:

  • Quickly identify the state of a given database cluster
mariadb dashboards

Error Logs​

The MariaDB - Error Logs dashboard provides insight into database error logs by specifically monitoring database shutdown/start events, errors over time, errors, warnings, and crash recovery attempts.

Use this dashboard to:

  • Quickly identify errors and patterns in logs for troubleshooting.
  • Monitor trends in the error log and identify outliers.
  • Ensure that server start, server stop, and crash recovery events are in line with expectations.
  • Dashboard filters allow you to narrow a search for the database clusters..
mariadb dashboards

Failed Logins​

The MariaDB - Failed Logins dashboard provides insights into all failed login attempts by location, users and hosts.

Use this dashboard to:

  • Monitor all failed login attempts and identify any unusual or suspicious activity.
mariadb dashboards

Replication​

The MariaDB - Replication dashboard provides insights into the state of database replication.

Use this dashboard to:

  • Quickly determine reasons for replication failures.
  • Monitor replication status trends.
mariadb dashboards

Slow Queries​

The MariaDB - Slow Queries dashboard provides insights into all slow queries executed on the database.

mariadb dashboards

Slow queries are queries that take 10 seconds or more to execute (default value is 10 seconds as per MariaDB configuration which can be altered) and excessive slow queries are those that take 15 seconds or more to execute.

Use this dashboard to:

  • Identify all slow queries.
  • Quickly determine which queries have been identified as slow or excessive slow queries.
  • Monitor users and hosts running slow queries.
  • Determine which SQL commands are slower than others.
  • Examine slow query trends to determine if there are periodic performance bottlenecks in your database clusters.

Performance and Resource Metrics​

The MariaDB - Performance and Resource Metrics dashboard allows you to monitor the performance and resource usage of your database clusters.

Use this dashboard to:

  • Understand the behavior and performance of your database clusters.
  • Monitor key operational metrics around connections, network traffic, threads running, innodb waits, and locks.
  • Monitor query execution trends to ensure they match up with expectations.
  • Dashboard filters allow you to narrow a search for a specific database cluster.
mariadb dashboards

Performance Schema Metrics​

The MariaDB - Performance Schema Metrics Dashboard provides insights into the metrics provided by the MariaDB Performance Schema, which is a feature for monitoring MariaDB Server execution at a low level.

Use this dashboard to:

  • Monitor errors and warning for SQL statements.
  • Monitor statements running without use of index columns.
  • Monitor statistics such as Table and Index waits and read and write lock waits to optimize the performance of your database.
mariadb dashboards

Replication Metrics​

The MariaDB - Replication Metrics dashboard shows replication events, errors, warnings, and nodes.

mariadb dashboards

InnoDB Metrics​

The MariaDB - InnoDB Metrics dashboard shows replication events, errors, warnings, and nodes.

mariadb dashboards

Table Performance Metrics​

The MariaDB - Table Performance dashboard provides insights into performance like table i/o wait and table lock waits.

Use this dashboard to:

  • Identify root cause of slow queries performed on database table.
  • Compare read and write lock waits time with timeshift operator to compare baseline and current trend.
mariadb dashboards

MariaDB Alerts​

Sumo Logic has provided out-of-the-box alerts available through Sumo Logic monitors to help you quickly determine if the MariaDB Database are available and performing as expected. These alerts are built based on logs and metrics datasets and have preset thresholds based on industry best practices and recommendations.

Sumo Logic provides the following out-of-the-box alerts:

Alert Type (Metrics/Logs)Alert NameAlert DescriptionTrigger Type (Critical / Warning)Alert ConditionRecover Condition
LogsMariaDB - Excessive Slow Query DetectedThis alert fires when the average time to execute a query is more than 15 seconds for a 5 minute time interval.Critical>=1<1
LogsMariaDB - Instance downThis alert fires when we detect that a MariaDB instance is downCritical>=1<1
MetricsMariaDB - Connection refusedThis alert fires when connections are refused when the limit of maximum connections is reached.Critical>=1<1
MetricsMariaDB - Follower replication lag detectedThis alert fires when we detect that the average replication lag within a 5 minute time interval is greater than or equal to 900 seconds .Critical>=900<900
MetricsMariaDB - High average query run timeThis alert fires when the average run time of MariaDB queries within a 5 minute time interval for a given schema is greater than or equal to one second.Critical>=1<1
MetricsMariaDB - High Innodb buffer pool utilizationThis alert fires when the InnoDB buffer pool utilization is high (>=90%) within a 5 minute time interval.Critical>=90<90
MetricsMariaDB - Large number of aborted connectionsThis alert fires when there are 5 or more aborted connections detected within a 5 minute time interval.Critical>=5<5
MetricsMariaDB - Large number of internal connection errorsThis alert fires when there are 5 or more internal connection errors within a 5 minute time interval.Critical>=5<5
MetricsMariaDB - Large number of slow queriesThis alert fires when there are 5 or more slow queries within a 5 minute time interval.Critical>=5<5
MetricsMariaDB - Large number of statement errorsThis alert fires when there are 5 or more statement errors within a 5 minute time interval.Critical>=5<5
MetricsMariaDB - Large number of statement warningsThis alert fires when there are 20 or more statement warnings within a 5 minute time interval.Critical>=20<20
MetricsMariaDB - No index used in the SQL statementsThis alert fires when there are 5 or more statements not using an index in the SQL query within a 5 minute time interval.Critical>=5<5
MetricsMariaDB - Slave Server ErrorThis alert fires when there are slave server errors within a 5 minute time interval.Critical>0<=0
Status
Legal
Privacy Statement
Terms of Use

Copyright © 2024 by Sumo Logic, Inc.