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)