云原生监控系统Prometheus——Exporter(MySQL数据库监控)


Exporter(MySQL数据库监控)

  数据是企业的核心价值,数据库管理员对数据库进行监控,以便随时掌握数据库运行状况,使数据库稳定运行,更好地优化提升其性能,且在排查问题时有据可依。

  目前互联网中,最常见的数据库模型主要有两种:关系型数据库和非关系型数据库。

  这里我们先介绍 mysqld_exporter 对最常见的关系型数据库 Mysql 数据库进行监控数据采集。由于 MySQL 数据库不提供任何端点,所以 Prometheus 无法直接从中获取监控指标。为了解决这个问题,Prometheus 社区开发了对应的 exporter。其工作原理是使用底层应用程序理解的语言,从目标应用程序中提取监控指标,然后通过 REST 公开这些指标。

  这样,Prometheus 服务器就可以采集到对应的数据了。针对 MySQL,我们将使用 Prometheus 社区中的 MySQL 官方 “mysqld_exporter” 进行监控数据采集。

  在安装之前,首先在官方下载页面 https://github.com/prometheus/mysqld_exporter/releases/ 找到最新 mysqld_exporter 版本,下载最新版本中特定平台的二进制文件,如下:

云原生监控系统Prometheus——Exporter(MySQL数据库监控)

一、部署mysqld_exporter

  我这里都是 kubernetes 环境,就不讲二进制部署了。以下是 Deployment 运行的 MySQL 服务,以及 mysqld_exporter 的配置清单 yaml 文件:

  • mysql server 版本号:mysql:5.7.30
    • MySQL server 的 Deploymnet 配置清单 yaml 文件如下:
      kind: Deployment
      apiVersion: apps/v1
      metadata:
        name: mysql-4jravx
        namespace: devops-mysql-test
        labels:
          app: mysql-4jravx
          app.kubernetes.io/managed-by: Helm
          app.kubesphere.io/instance: mysql-4jravx
          chart: mysql-1.6.8
          heritage: Helm
          release: mysql-4jravx
        annotations:
          deployment.kubernetes.io/revision: '1'
          kubesphere.io/creator: admin
          meta.helm.sh/release-name: mysql-4jravx
          meta.helm.sh/release-namespace: devops-mysql-test
      spec:
        replicas: 1
        selector:
          matchLabels:
            app: mysql-4jravx
            release: mysql-4jravx
        template:
          metadata:
            creationTimestamp: null
            labels:
              app: mysql-4jravx
              release: mysql-4jravx
            annotations:
              kubesphere.io/creator: admin
          spec:
            volumes:
              - name: data
                persistentVolumeClaim:
                  claimName: mysql-4jravx
            initContainers:
              - name: remove-lost-found
                image: 'busybox:1.32'
                command:
                  - rm
                  - '-fr'
                  - /var/lib/mysql/lost+found
                resources:
                  requests:
                    cpu: 10m
                    memory: 10Mi
                volumeMounts:
                  - name: data
                    mountPath: /var/lib/mysql
                terminationMessagePath: /dev/termination-log
                terminationMessagePolicy: File
                imagePullPolicy: IfNotPresent
            containers:
              - name: mysql-4jravx
                image: 'mysql:5.7.30'
                ports:
                  - name: mysql
                    containerPort: 3306
                    protocol: TCP
                env:
                  - name: MYSQL_ROOT_PASSWORD
                    valueFrom:
                      secretKeyRef:
                        name: mysql-4jravx
                        key: mysql-root-password
                  - name: MYSQL_PASSWORD
                    valueFrom:
                      secretKeyRef:
                        name: mysql-4jravx
                        key: mysql-password
                        optional: true
                  - name: MYSQL_USER
                  - name: MYSQL_DATABASE
                resources:
                  requests:
                    cpu: 100m
                    memory: 256Mi
                volumeMounts:
                  - name: data
                    mountPath: /var/lib/mysql
                livenessProbe:
                  exec:
                    command:
                      - sh
                      - '-c'
                      - 'mysqladmin ping -u root -p${MYSQL_ROOT_PASSWORD}'
                  initialDelaySeconds: 30
                  timeoutSeconds: 5
                  periodSeconds: 10
                  successThreshold: 1
                  failureThreshold: 3
                readinessProbe:
                  exec:
                    command:
                      - sh
                      - '-c'
                      - 'mysqladmin ping -u root -p${MYSQL_ROOT_PASSWORD}'
                  initialDelaySeconds: 5
                  timeoutSeconds: 1
                  periodSeconds: 10
                  successThreshold: 1
                  failureThreshold: 3
                terminationMessagePath: /dev/termination-log
                terminationMessagePolicy: File
                imagePullPolicy: IfNotPresent
            restartPolicy: Always
            terminationGracePeriodSeconds: 30
            dnsPolicy: ClusterFirst
            serviceAccountName: default
            serviceAccount: default
            securityContext: {}
            schedulerName: default-scheduler
        strategy:
          type: Recreate
        revisionHistoryLimit: 10
        progressDeadlineSeconds: 600
  • mysqld_exporter 版本号:prom/mysqld-exporter:v0.11.0

    • mysqld_exporter 的 Deployment 配置清单 yaml 文件如下:
      kind: Deployment
      apiVersion: apps/v1
      metadata:
        name: mysql-e-0qunl8-mysql-exporter
        namespace: devops-mysql-test
        labels:
          app: mysql-exporter
          app.kubernetes.io/managed-by: Helm
          app.kubesphere.io/instance: mysql-e-0qunl8
          chart: mysql-exporter-0.5.6
          heritage: Helm
          release: mysql-e-0qunl8
        annotations:
          deployment.kubernetes.io/revision: '1'
          kubesphere.io/creator: admin
          meta.helm.sh/release-name: mysql-e-0qunl8
          meta.helm.sh/release-namespace: devops-mysql-test
      spec:
        replicas: 1
        selector:
          matchLabels:
            app: mysql-exporter
            release: mysql-e-0qunl8
        template:
          metadata:
            creationTimestamp: null
            labels:
              app: mysql-exporter
              release: mysql-e-0qunl8
            annotations:
              kubesphere.io/creator: admin
              prometheus.io/path: /metrics
              prometheus.io/port: '9104'
              prometheus.io/scrape: 'true'
          spec:
            containers:
              - name: mysql-exporter
                image: 'prom/mysqld-exporter:v0.11.0'
                ports:
                  - containerPort: 9104
                    protocol: TCP
                envFrom:
                  - secretRef:
                      name: mysql-e-0qunl8-mysql-exporter
                resources: {}
                livenessProbe:
                  httpGet:
                    path: /
                    port: 9104
                    scheme: HTTP
                  timeoutSeconds: 1
                  periodSeconds: 10
                  successThreshold: 1
                  failureThreshold: 3
                readinessProbe:
                  httpGet:
                    path: /
                    port: 9104
                    scheme: HTTP
                  timeoutSeconds: 1
                  periodSeconds: 10
                  successThreshold: 1
                  failureThreshold: 3
                terminationMessagePath: /dev/termination-log
                terminationMessagePolicy: File
                imagePullPolicy: IfNotPresent
            restartPolicy: Always
            terminationGracePeriodSeconds: 30
            dnsPolicy: ClusterFirst
            securityContext: {}
            schedulerName: default-scheduler
        strategy:
          type: RollingUpdate
          rollingUpdate:
            maxUnavailable: 25%
            maxSurge: 25%
        revisionHistoryLimit: 10
        progressDeadlineSeconds: 600

二、与 Prometheus 集成

  与 Prometheus 的配置文件 prometheus.yaml 中的 static_configs 模块内,继续追加配置内容来采集 mysqld_exporter 提供的数据。添加的参考配置内容如下:

scrape_configs:
  # The job name is added as a label `job=<job_name>` to any timeseries scraped from this config.
  - job_name: "prometheus"

    # metrics_path defaults to '/metrics'
    # scheme defaults to 'http'.

    static_configs:
      - targets: ["localhost:9090"]
    
  - job_name: "node_exporter"
    static_configs:
      - targets: ["192.168.2.121:9100"]

  - job_name: "mysqld_exporter"
    scrape_interval: 10s
    static_configs:
      - targets: ["192.168.2.121:9104"]
  • 2.1、mysqld_exporter 服务是否正常,可以通过 Prometheus UI 页面的 “status” 中的 “Targets”,如下图所示:
    云原生监控系统Prometheus——Exporter(MySQL数据库监控)
  • 2.2、进入 Targets 页面后,可以在列表中看到刚才配置好的 mysqld_exporter 的状态为 “UP”,说明 Prometheus 最后一次从 mysqld_exporter 中采集数据是成功的,此刻被监控的服务器主机工作状态是正常的。云原生监控系统Prometheus——Exporter(MySQL数据库监控)

三、metrics 查看

  MySQL 数据库的性能状态查询内容非常多,但通常必不可少的内容包括查询吞吐量(Buffer pool usage)、查询性能(Qurery execution performance)、连接情况(Connections)和缓冲池使用情况(Buffer pool usage)这四个与基本的性能和资源利用率相关的指标。

  • 3.1)查询吞吐量
  • 3.2)查询执行性能
  • 3.3)连接情况
  • 3.4)缓存池使用情况

原创文章,作者:Maggie-Hunter,如若转载,请注明出处:https://blog.ytso.com/274473.html

(0)
上一篇 2022年7月15日
下一篇 2022年7月15日

相关推荐

发表回复

登录后才能评论