Query-level metrics for PostgreSQL/MySQL in Kubernetes with Packetbeat

MySQL and PostgreSQL can be a bit of a black box when running if you don’t take the time to configure metrics. How do you identify which queries are slow and need to be optimized? MySQL has the slow log, but that requires a time threshold to log queries that run for longer than >N seconds. What if you want to identify the most common queries even if they are fast?

Elastic.co has a neat product called beats that run small processes that collect information and send them to Elasticsearch and can view them in Kibana. As of now, they have a few different agents:

• Filebeat – Reads log files and sends to Elasticsearch. Very similar to Logstash
• Metricbeat – CPU, memory, application usage, etc.
• Packetbeat – Captures network packets and extracts statistics. This is one I’m going to use
• Winlogbeat – Windows events
• Auditbeat – Linux audit log

Packetbeat attaches to a network interface, captures all the packets, and for certain supported protocols it can analyze the packets to extract application-level insights. For MySQL and PostgreSQL, it will include:

• The full query (SELECT * FROM foo WHERE baz …)
• Query run time
• Client IP address
• # of rows
• Did the query succeed or fail?
• And a few other attributes

With this information in Elasticsearch, you can build dashboards and perform analytics.

Getting it working

If you’re already running your database server in Kubernetes, then it’s pretty easy to add Packetbeat as a sidecar pod. A sidecar pod is effectively a second Docker container that runs with the same network interface as the primary container. We can use this to our advantage.

MySQL/Postgres

spec:
  containers:
  - image: percona:5.7.14
    imagePullPolicy: Always
    name: mysql
    [...]
  - image: docker.elastic.co/beats/packetbeat:6.5.4
    imagePullPolicy: Always
    name: packetbeat
    securityContext:
      allowPrivilegeEscalation: true
      capabilities:
        add:
        - NET_ADMIN
      privileged: false
      procMount: Default
      readOnlyRootFilesystem: false
      runAsNonRoot: false
    volumeMounts:
    - mountPath: /usr/share/packetbeat/packetbeat.yml
        name: pbconfig
        readOnly: true
        subPath: packetbeat.yml
    - mountPath: /usr/share/packetbeat/data/
        name: state
    volumes:
      - configMap:
          defaultMode: 292
          name: mysqlpacketbeat
          optional: false
        name: beatconfig
Code language: JavaScript (javascript)

ConfigMap

apiVersion: v1
data:
  packetbeat.yml: |-
    packetbeat.interfaces.device: eth0
    packetbeat.protocols:
    - type: mysql
      ports: [3306]

    output.elasticsearch:
      hosts:
      - http://elasticsearch.elasticsearch.svc.cluster.local:9200
      index: packetbeat-%{+yyyy.MM}

    setup.kibana:
      host: "kibana.elasticsearch.svc.cluster.local:5601"
    setup.template:
      name: packetbeat
      pattern: packetbeat-*
kind: ConfigMap
metadata:
  name: mysqlpacketbeat
Code language: JavaScript (javascript)