Prometheus监控云数据库之SQL Server

1,078次阅读

共计 9960 个字符，预计需要花费 25 分钟才能阅读完成。

实验环境

通过 prometheus-mssql-exporter，实现监控云上的云数据库SQL Server

服务器：CentOS 7，Docker

SQLServer：腾讯云云数据库SQL Server

环境准备

配置文件目录

准备Prometheus配置文件，用于映射进容器内，方便在服务器上进行修改

[root@VM-1-14-centos ~]# mkdir /opt/config/prometheus -p
[root@VM-1-14-centos prometheus]# cat prometheus.yml 
global:
  scrape_interval:     60s
  evaluation_interval: 60s

scrape_configs:
  - job_name: prometheus
    static_configs:
      - targets: ['localhost:9090']
        labels:
          instance: prometheus

镜像准备

[root@VM-1-14-centos ~]# docker pull prom/prometheus
[root@VM-1-14-centos ~]# docker pull grafana/grafana
[root@VM-1-14-centos ~]# docker pull awaragi/prometheus-mssql-exporter
[root@VM-1-14-centos prometheus]# docker images
REPOSITORY                          TAG       IMAGE ID       CREATED        SIZE
prom/prometheus                     latest    c10e9cbf22cd   6 hours ago    194MB
grafana/grafana                     latest    8c51782cb526   2 days ago     249MB
awaragi/prometheus-mssql-exporter   latest    b963e6c7b735   4 months ago   77.4MB

启动环境

Prometheus

[root@VM-1-14-centos prometheus]# docker run -it --name prometheus -p 9090:9090 -v /opt/config/prometheus/prometheus.yml:/etc/prometheus/prometheus.yml -d prom/prometheus

查看日志

[root@VM-1-14-centos prometheus]# docker logs -f prometheus 
ts=2021-11-06T02:46:21.448Z caller=main.go:406 level=info msg="No time or size retention was set so using the default time retention" duration=15d
ts=2021-11-06T02:46:21.448Z caller=main.go:444 level=info msg="Starting Prometheus" version="(version=2.31.1, branch=HEAD, revision=411021ada9ab41095923b8d2df9365b632fd40c3)"
ts=2021-11-06T02:46:21.448Z caller=main.go:449 level=info build_context="(go=go1.17.3, user=root@9419c9c2d4e0, date=20211105-20:35:02)"
ts=2021-11-06T02:46:21.448Z caller=main.go:450 level=info host_details="(Linux 3.10.0-1160.11.1.el7.x86_64 #1 SMP Fri Dec 18 16:34:56 UTC 2020 x86_64 de387b60f1ff (none))"
ts=2021-11-06T02:46:21.449Z caller=main.go:451 level=info fd_limits="(soft=1048576, hard=1048576)"
ts=2021-11-06T02:46:21.449Z caller=main.go:452 level=info vm_limits="(soft=unlimited, hard=unlimited)"
ts=2021-11-06T02:46:21.450Z caller=web.go:542 level=info component=web msg="Start listening for connections" address=0.0.0.0:9090
ts=2021-11-06T02:46:21.451Z caller=main.go:839 level=info msg="Starting TSDB ..."
ts=2021-11-06T02:46:21.454Z caller=head.go:479 level=info component=tsdb msg="Replaying on-disk memory mappable chunks if any"
ts=2021-11-06T02:46:21.454Z caller=head.go:513 level=info component=tsdb msg="On-disk memory mappable chunks replay completed" duration=8.419µs
ts=2021-11-06T02:46:21.454Z caller=head.go:519 level=info component=tsdb msg="Replaying WAL, this may take a while"
ts=2021-11-06T02:46:21.454Z caller=head.go:590 level=info component=tsdb msg="WAL segment loaded" segment=0 maxSegment=0
ts=2021-11-06T02:46:21.454Z caller=head.go:596 level=info component=tsdb msg="WAL replay completed" checkpoint_replay_duration=70.56µs wal_replay_duration=221.542µs total_replay_duration=327.576µs
ts=2021-11-06T02:46:21.455Z caller=tls_config.go:195 level=info component=web msg="TLS is disabled." http2=false
ts=2021-11-06T02:46:21.455Z caller=main.go:866 level=info fs_type=EXT4_SUPER_MAGIC
ts=2021-11-06T02:46:21.455Z caller=main.go:869 level=info msg="TSDB started"
ts=2021-11-06T02:46:21.455Z caller=main.go:996 level=info msg="Loading configuration file" filename=/etc/prometheus/prometheus.yml
ts=2021-11-06T02:46:21.456Z caller=main.go:1033 level=info msg="Completed loading of configuration file" filename=/etc/prometheus/prometheus.yml totalDuration=994.915µs db_storage=773ns remote_storage=6.071µs web_handler=314ns query_engine=1.052µs scrape=709.822µs scrape_sd=28.958µs notify=910ns notify_sd=2.309µs rules=3.86µs
ts=2021-11-06T02:46:21.456Z caller=main.go:811 level=info msg="Server is ready to receive web requests."

出现Server is ready to receive web requests 即为启动成功，Prometheus已经正常运行中。

查看端口

[root@VM-1-14-centos prometheus]# netstat -tunlp | grep 9090
tcp        0      0 0.0.0.0:9090            0.0.0.0:*               LISTEN      30074/docker-proxy  
tcp6       0      0 :::9090                 :::*                    LISTEN      30080/docker-proxy

出现9090监听表示服务已正常运行中。

访问测试

Prometheus监控云数据库之SQL Server

Grafana

[root@VM-1-14-centos prometheus]# mkdir /opt/config/grafana
# 因为grafana用户会在这个目录写入文件，增加写入权限（777用于测试）
[root@VM-1-14-centos prometheus]# chmod 777 /opt/config/grafana
[root@VM-1-14-centos prometheus]# docker run -it --name grafana -p 3000:3000 -v /opt/config/grafana:/var/lib/grafana -d grafana/grafana

查看日志

[root@VM-1-14-centos prometheus]# docker logs grafana
...
INFO[11-06|02:56:04] Executing migration                      logger=migrator id="create library_element table v1"
INFO[11-06|02:56:04] Executing migration                      logger=migrator id="add index library_element org_id-folder_id-name-kind"
INFO[11-06|02:56:04] Executing migration                      logger=migrator id="create library_element_connection table v1"
INFO[11-06|02:56:04] Executing migration                      logger=migrator id="add index library_element_connection element_id-kind-connection_id"
INFO[11-06|02:56:04] Executing migration                      logger=migrator id="add unique index library_element org_id_uid"
INFO[11-06|02:56:04] Executing migration                      logger=migrator id="create kv_store table v1"
INFO[11-06|02:56:04] Executing migration                      logger=migrator id="add index kv_store.org_id-namespace-key"
INFO[11-06|02:56:04] migrations completed                     logger=migrator performed=346 skipped=0 duration=2.666984493s
INFO[11-06|02:56:04] Created default admin                    logger=sqlstore user=admin
INFO[11-06|02:56:04] Created default organization             logger=sqlstore
INFO[11-06|02:56:04] Starting plugin search                   logger=plugins
INFO[11-06|02:56:04] Registering plugin                       logger=plugins id=input
INFO[11-06|02:56:04] Live Push Gateway initialization         logger=live.push_http
INFO[11-06|02:56:04] HTTP Server Listen                       logger=http.server address=[::]:3000 protocol=http subUrl= socket=

查看端口

[root@VM-1-14-centos prometheus]# netstat -tunlp | grep 3000
tcp        0      0 0.0.0.0:3000            0.0.0.0:*               LISTEN      31717/docker-proxy  
tcp6       0      0 :::3000                 :::*                    LISTEN      31723/docker-proxy

访问测试

Prometheus监控云数据库之SQL Server

输入默认密码: admin/admin，即登陆 Grafana 控制面板

Prometheus监控云数据库之SQL Server

配置Prometheus为数据源

依次进入：设置 – Data Sources – Add data source

查看 Prometheus 的 IP 地址

[root@VM-1-14-centos prometheus]# docker inspect prometheus | grep "IPAddress"
"IPAddress": "172.17.0.2"

Prometheus监控云数据库之SQL Server

至此，数据源配置完成。

SQLServer配置

新增数据库账号

Prometheus监控云数据库之SQL Server

创建测试库

Prometheus监控云数据库之SQL Server

部署SQL Server Exporter

  注意部署 Exporter 的 CVM 是否可以正常访问到 SQL Server

[root@VM-1-14-centos prometheus]# docker run -it --name mssql_exporter -e SERVER=172.16.1.19 -e USERNAME=exporter -e PASSWORD=sqltestpwd -e DEBUG=app -p 4000:4000 awaragi/prometheus-mssql-exporter
[root@VM-1-14-centos prometheus]# docker ps
CONTAINER ID   IMAGE                               COMMAND                  CREATED          STATUS          PORTS                                       NAMES
30b4ec5cb05f   awaragi/prometheus-mssql-exporter   "node index.js"          6 minutes ago    Up 2 minutes    0.0.0.0:4000->4000/tcp, :::4000->4000/tcp   mssql_exporter
6a70521f74c6   grafana/grafana                     "/run.sh"                24 minutes ago   Up 24 minutes   0.0.0.0:3000->3000/tcp, :::3000->3000/tcp   grafana
de387b60f1ff   prom/prometheus                     "/bin/prometheus --c…"   34 minutes ago   Up 34 minutes   0.0.0.0:9090->9090/tcp, :::9090->9090/tcp   prometheus

查看指标

[root@VM-1-14-centos prometheus]# curl localhost:4000/metrics |grep -v ^# | grep -v ^$
  % Total    % Received % Xferd  Average Speed   Time    Time     Time  Current
                                 Dload  Upload   Total   Spent    Left  Speed
100  5831  100  5831    0     0  59618      0 --:--:-- --:--:-- --:--:-- 60113
up 1
mssql_instance_local_time 1636168713
mssql_connections{database="master",state="current"} 36
mssql_connections{database="Monitor",state="current"} 1
mssql_connections{database="msdb",state="current"} 2
mssql_deadlocks 0
mssql_user_errors 1042
mssql_kill_connection_errors 0
mssql_database_state{database="master"} 0
mssql_database_state{database="tempdb"} 0
mssql_database_state{database="model"} 0
mssql_database_state{database="msdb"} 0
mssql_database_state{database="Monitor"} 0
mssql_database_state{database="testdbA"} 0
mssql_database_state{database="testdbB"} 0
mssql_log_growths{database="testdbB"} 0
mssql_log_growths{database="testdbA"} 0
mssql_log_growths{database="tempdb"} 0
mssql_log_growths{database="msdb"} 0
mssql_log_growths{database="Monitor"} 0
mssql_log_growths{database="model"} 0
mssql_log_growths{database="mssqlsystemresource"} 0
mssql_log_growths{database="master"} 0
mssql_database_filesize{database="master",logicalname="master",type="0",filename="L:\\Microsoft SQL Server\\MSSQL10_50.S17\\MSSQL\\DATA\\master.mdf"} 4096
mssql_database_filesize{database="master",logicalname="mastlog",type="1",filename="L:\\Microsoft SQL Server\\MSSQL10_50.S17\\MSSQL\\DATA\\mastlog.ldf"} 1280
mssql_database_filesize{database="tempdb",logicalname="tempdev",type="0",filename="L:\\Microsoft SQL Server\\MSSQL10_50.S17\\MSSQL\\DATA\\tempdb.mdf"} 102400
mssql_database_filesize{database="tempdb",logicalname="templog",type="1",filename="L:\\Microsoft SQL Server\\MSSQL10_50.S17\\MSSQL\\DATA\\templog.ldf"} 102400
mssql_database_filesize{database="model",logicalname="modeldev",type="0",filename="L:\\Microsoft SQL Server\\MSSQL10_50.S17\\MSSQL\\DATA\\model.mdf"} 2304
mssql_database_filesize{database="model",logicalname="modellog",type="1",filename="L:\\Microsoft SQL Server\\MSSQL10_50.S17\\MSSQL\\DATA\\modellog.ldf"} 768
mssql_database_filesize{database="msdb",logicalname="MSDBData",type="0",filename="L:\\Microsoft SQL Server\\MSSQL10_50.S17\\MSSQL\\DATA\\MSDBData.mdf"} 16640
mssql_database_filesize{database="msdb",logicalname="MSDBLog",type="1",filename="L:\\Microsoft SQL Server\\MSSQL10_50.S17\\MSSQL\\DATA\\MSDBLog.ldf"} 24384
mssql_database_filesize{database="Monitor",logicalname="Monitor",type="0",filename="L:\\gamedb\\58017\\Monitor.mdf"} 349440
mssql_database_filesize{database="Monitor",logicalname="Monitor_log",type="1",filename="L:\\gamedb\\58017\\Monitor_log.LDF"} 5824
mssql_database_filesize{database="testdbA",logicalname="testdbA",type="0",filename="L:\\gamedb\\58017\\\\testdbA_258.mdf"} 358400
mssql_database_filesize{database="testdbA",logicalname="testdbA_log",type="1",filename="L:\\gamedb\\58017\\\\testdbA_258_log.ldf"} 204800
mssql_database_filesize{database="testdbB",logicalname="testdbB",type="0",filename="L:\\gamedb\\58017\\\\testdbB_414.mdf"} 358400
mssql_database_filesize{database="testdbB",logicalname="testdbB_log",type="1",filename="L:\\gamedb\\58017\\\\testdbB_414_log.ldf"} 204800
mssql_page_life_expectancy 1650
mssql_batch_requests 8766
mssql_page_fault_count 465438
mssql_memory_utilization_percentage 100
mssql_total_physical_memory_kb 803891844
mssql_available_physical_memory_kb 407730176
mssql_total_page_file_kb 816171652
mssql_available_page_file_kb 353473116

配置 Promtheus 采集

编辑promtheus.yml

global:
  scrape_interval:     60s
  evaluation_interval: 60s

scrape_configs:
  - job_name: prometheus
    static_configs:
      - targets: ['localhost:9090']
        labels:
          instance: prometheus
  # 新增
  - job_name: SQLServer
    static_configs:
      - targets: ['172.17.0.4:4000']
        labels:
          instance: localhost

重启Promtheus

[root@VM-1-14-centos prometheus]# docker restart prometheus

查看是否正常采集

Prometheus监控云数据库之SQL Server

配置监控视图

导入监控视图模板

Prometheus监控云数据库之SQL Server

选择数据源

Prometheus监控云数据库之SQL Server

效果展示

Prometheus监控云数据库之SQL Server

正文完
Prometheus
发表至： Prometheus 云原生
2021-11-06
 
mervinwang
版权声明：本站原创文章，由 mervinwang 2021-11-06发表，共计9960字。
转载说明：除特殊说明外本站文章皆由CC-4.0协议发布，转载请注明出处。
文章搜索
Tekton实战系列之Tekton
Kubernetes Pod 垂直自动伸缩（VPA）
Kubernetes网络之Falnnel和Calico
如何选择 Thanos 的 Sidecar 和 Receiver 两种模式？
文章搜索
热门文章
Linux 如何排查命令执行记录及开启审计

Linux 如何排查命令执行记录及开启审计

了解是谁执行了这个操作以及具体执行了什么命令是非常重要的。 本文将详细介绍如何反查命令的执行人 1 背景 假设...
随机文章
Linux 如何排查命令执行记录及开启审计

Linux 如何排查命令执行记录及开启审计

了解是谁执行了这个操作以及具体执行了什么命令是非常重要的。 本文将详细介绍如何反查命令的执行人 1 背景 假设...
标签云
AnsibleApacheAWSCDNCICDContainerdDockerELKetcdGoGrafanaHadoophtmlJinkensJmeterJSKongKubernetesLinuxMysqlNetworkNginxOracleOSSPHPPrometheusPythonRDSShellSRESSLSystemTektonThanosTomcatWAFWindowsWordpressZabbix云原生前端开发备份安全容器木马系统命令腾讯云阿里云