You are viewing a plain text version of this content. The canonical link for it is here.
Posted to notifications@skywalking.apache.org by ke...@apache.org on 2022/10/11 06:05:03 UTC
[skywalking-showcase] branch main updated: Add PostgreSQL monitor feature (#84)
This is an automated email from the ASF dual-hosted git repository.
kezhenxu94 pushed a commit to branch main
in repository https://gitbox.apache.org/repos/asf/skywalking-showcase.git
The following commit(s) were added to refs/heads/main by this push:
new 0b4a9b2 Add PostgreSQL monitor feature (#84)
0b4a9b2 is described below
commit 0b4a9b239c6c11449a83c54caf0261321cd4c2f6
Author: kezhenxu94 <ke...@apache.org>
AuthorDate: Tue Oct 11 14:04:58 2022 +0800
Add PostgreSQL monitor feature (#84)
---
Makefile.in | 4 +-
.../kubernetes/feature-postgresql/fluent-bit.yaml | 147 +++++++++++++++++++++
.../feature-postgresql/open-telemetry.yaml | 2 +-
.../kubernetes/feature-postgresql/psql.yaml | 130 ++++++++++++------
docs/readme.md | 2 +-
5 files changed, 243 insertions(+), 42 deletions(-)
diff --git a/Makefile.in b/Makefile.in
index 65f86ba..eed3154 100644
--- a/Makefile.in
+++ b/Makefile.in
@@ -26,8 +26,8 @@ TAG ?= $(shell git rev-parse --short HEAD)
ES_IMAGE ?= docker.elastic.co/elasticsearch/elasticsearch-oss:7.10.0
-SW_OAP_IMAGE ?= ghcr.io/apache/skywalking/oap:dd6b7a1fc0f8581e25c86cf6c904848f0c574e92
-SW_UI_IMAGE ?= ghcr.io/apache/skywalking/ui:dd6b7a1fc0f8581e25c86cf6c904848f0c574e92
+SW_OAP_IMAGE ?= ghcr.io/apache/skywalking/oap:091b7810c1d48e4432a319bc955479fd09e23307
+SW_UI_IMAGE ?= ghcr.io/apache/skywalking/ui:091b7810c1d48e4432a319bc955479fd09e23307
SW_CLI_IMAGE ?= ghcr.io/apache/skywalking-cli/skywalking-cli:ec85225f3fc0d0d7e8a9513b828d305c7cb399ad
SW_EVENT_EXPORTER_IMAGE ?= ghcr.io/apache/skywalking-kubernetes-event-exporter/skywalking-kubernetes-event-exporter:8a012a3f968cb139f817189afb9b3748841bba22
SW_AGENT_JAVA_IMAGE ?= ghcr.io/apache/skywalking-java/skywalking-java:3f88d735ba2bfd1196aff946502447d4b14450c8-java8
diff --git a/deploy/platform/kubernetes/feature-postgresql/fluent-bit.yaml b/deploy/platform/kubernetes/feature-postgresql/fluent-bit.yaml
new file mode 100644
index 0000000..f69acae
--- /dev/null
+++ b/deploy/platform/kubernetes/feature-postgresql/fluent-bit.yaml
@@ -0,0 +1,147 @@
+# Licensed to the Apache Software Foundation (ASF) under one
+# or more contributor license agreements. See the NOTICE file
+# distributed with this work for additional information
+# regarding copyright ownership. The ASF licenses this file
+# to you under the Apache License, Version 2.0 (the
+# "License"); you may not use this file except in compliance
+# with the License. You may obtain a copy of the License at
+#
+# http://www.apache.org/licenses/LICENSE-2.0
+#
+# Unless required by applicable law or agreed to in writing,
+# software distributed under the License is distributed on an
+# "AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+# KIND, either express or implied. See the License for the
+# specific language governing permissions and limitations
+# under the License.
+#
+
+# @feature: slowsql-psql; fluent bit log configurations
+---
+apiVersion: v1
+kind: ConfigMap
+metadata:
+ name: slowsql-psql-fluent-bit
+ labels:
+ app: slowsql-psql-fluent-bit
+data:
+ fluent-bit-conf: |
+ [SERVICE]
+ flush 1
+ log_level info
+ parsers_File fluent-bit-parser.conf
+ [INPUT]
+ name tail
+ path /data/log/slow.log
+ parser my-log-format
+ [FILTER]
+ name grep
+ match *
+ regex log \w*-\w*-\w* \w*:\w*:\w*.\w* UTCLOG: duration: \w*.\w* ms statement.*
+ [FILTER]
+ name lua
+ match *
+ script fluent-bit-script.lua
+ call rewrite_body
+ [OUTPUT]
+ name stdout
+ match *
+ format json
+ [OUTPUT]
+ name http
+ match *
+ host oap
+ port 12800
+ uri /v3/logs
+ format json
+
+ fluent-bit-script-lua: |
+ function rewrite_body(tag, timestamp, record)
+ log = record["log"]
+ record["log"] = nil
+ record["date"] = nil
+ record["tags"] = {data={{key="LOG_KIND", value="SLOW_SQL"}}}
+ arr = split(log,"\n")
+ re1 = {}
+
+ re1["time"] = os.time()
+
+ re1["layer"] = "POSTGRESQL"
+ record["layer"] = "POSTGRESQL"
+ _,durationIndex = string.find(log,"duration: ")
+ msIndex,_ = string.find(log," ms")
+ duration = string.sub(log,durationIndex+1,msIndex)
+ _,statementAf = string.find(log,"statement: ")
+ re1["statement"] = string.sub(log,statementAf+1)
+ duration = string.sub(log,durationIndex+1,msIndex-1)
+ d1 = math.floor(tonumber(duration))
+ re1["query_time"] = d1
+
+ service = "postgresql::"..os.getenv("SW_SERVICE")
+ record["service"] = service
+ re1["service"] = service
+
+ re1["id"] = uuid()
+
+ jsonstr = table2json(re1)
+ record["body"]={json={}}
+ record["body"]["json"]["json"] = jsonstr
+ return 1, timestamp, record
+ end
+ function split(input, delimiter)
+ input = tostring(input)
+ delimiter = tostring(delimiter)
+ if (delimiter == "") then return false end
+ local pos, arr = 0, {}
+ for st, sp in function() return string.find(input, delimiter, pos, true) end do
+ table.insert(arr, string.sub(input, pos, st - 1))
+ pos = sp + 1
+ end
+ table.insert(arr, string.sub(input, pos))
+ return arr
+ end
+
+ function uuid()
+ local seed={'e','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f'}
+ local tb={}
+ for i=1,32 do
+ table.insert(tb,seed[math.random(1,16)])
+ end
+ local sid=table.concat(tb)
+ return string.format('%s-%s-%s-%s-%s',
+ string.sub(sid,1,8),
+ string.sub(sid,9,12),
+ string.sub(sid,13,16),
+ string.sub(sid,17,20),
+ string.sub(sid,21,32)
+ )
+ end
+
+ function table2json(t)
+ local function serialize(tbl)
+ local tmp = {}
+ for k, v in pairs(tbl) do
+ local k_type = type(k)
+ local v_type = type(v)
+ local key = (k_type == "string" and '"' .. k .. '":') or (k_type == "number" and "")
+ local value =
+ (v_type == "table" and serialize(v)) or (v_type == "boolean" and tostring(v)) or
+ (v_type == "string" and '"' .. v .. '"') or
+ (v_type == "number" and v)
+ tmp[#tmp + 1] = key and value and tostring(key) .. tostring(value) or nil
+ end
+ if table.maxn(tbl) == 0 then
+ return "{" .. table.concat(tmp, ",") .. "}"
+ else
+ return "[" .. table.concat(tmp, ",") .. "]"
+ end
+ end
+ assert(type(t) == "table")
+ return serialize(t)
+ end
+
+ fluent-bit-parser-conf: |
+ [PARSER]
+ name my-log-format
+ format regex
+ regex \w*-\w*-\w* \w*:\w*:\w*.\w* UTCLOG: duration: \w*.\w* ms statement.*
diff --git a/deploy/platform/kubernetes/feature-postgresql/open-telemetry.yaml b/deploy/platform/kubernetes/feature-postgresql/open-telemetry.yaml
index dffbd43..3a55902 100644
--- a/deploy/platform/kubernetes/feature-postgresql/open-telemetry.yaml
+++ b/deploy/platform/kubernetes/feature-postgresql/open-telemetry.yaml
@@ -38,7 +38,7 @@ data:
relabel_configs:
- source_labels: [__meta_kubernetes_pod_container_name, __meta_kubernetes_pod_container_port_name]
action: keep
- regex: psql;metrics # @feature: postgresql; reference the name of the metrics port
+ regex: psql-exporter;metrics # @feature: postgresql; reference the name of the metrics port
- source_labels: [__meta_kubernetes_pod_name]
target_label: host_name
regex: (.+)
diff --git a/deploy/platform/kubernetes/feature-postgresql/psql.yaml b/deploy/platform/kubernetes/feature-postgresql/psql.yaml
index 300f97e..e56325d 100644
--- a/deploy/platform/kubernetes/feature-postgresql/psql.yaml
+++ b/deploy/platform/kubernetes/feature-postgresql/psql.yaml
@@ -16,6 +16,49 @@
# under the License.
#
+---
+apiVersion: v1
+kind: ConfigMap
+metadata:
+ name: psql-slowsql-conf
+data:
+ psql-config-file: |
+ listen_addresses = '*'
+ max_connections = 100
+
+ shared_buffers = 128MB
+
+ max_wal_size = 1GB
+ min_wal_size = 80MB
+
+ log_timezone = 'Etc/UTC'
+
+ datestyle = 'iso, mdy'
+
+ timezone = 'Etc/UTC'
+
+ lc_messages = 'en_US.utf8'
+
+ lc_monetary = 'en_US.utf8'
+ lc_numeric = 'en_US.utf8'
+ lc_time = 'en_US.utf8'
+
+ default_text_search_config = 'pg_catalog.english'
+
+ logging_collector = on
+ log_directory = 'log'
+ log_filename = 'slow.log'
+ log_rotation_age = 1d
+ log_rotation_size = 10MB
+ log_min_messages = info
+
+ log_min_duration_statement = 1000
+ log_checkpoints = on
+ log_connections = on
+ log_disconnections = on
+ log_duration = on
+ log_line_prefix = '%m'
+
---
apiVersion: v1
kind: ConfigMap
@@ -48,6 +91,10 @@ spec:
ports:
- protocol: TCP
port: 5432
+ name: psql
+ - protocol: TCP
+ port: 9187
+ name: psql-exporter
---
apiVersion: apps/v1
@@ -69,64 +116,71 @@ spec:
sidecar.istio.io/inject: "false"
spec:
containers:
+ - name: fluent-bit
+ image: fluent/fluent-bit:1.9
+ env:
+ - name: SW_SERVICE
+ valueFrom:
+ fieldRef:
+ fieldPath: metadata.name
+ volumeMounts:
+ - name: slowsql-psql-fluent-bit
+ mountPath: /fluent-bit/etc/
+ - name: shared-data
+ mountPath: /data/
- name: psql
image: postgres:14.1
+ command: ["docker-entrypoint.sh", "-c", "config_file=/etc/postgresql.conf"]
env:
- name: POSTGRES_PASSWORD
value: password
ports:
- - containerPort: 3306
----
-apiVersion: v1
-kind: Service
-metadata:
- name: psql-service
-spec:
- selector:
- app: psql-service
- ports:
- - protocol: TCP
- port: 9104
-
----
-apiVersion: apps/v1
-kind: Deployment
-metadata:
- name: psql-service-deployment
- labels:
- app: psql-service
-spec:
- replicas: 1
- selector:
- matchLabels:
- app: psql-service
- template:
- metadata:
- labels:
- app: psql-service
- annotations:
- sidecar.istio.io/inject: "false"
- spec:
- containers:
- - name: psql
+ - containerPort: 5432
+ volumeMounts:
+ - name: psql-slowsql-conf
+ mountPath: /etc/postgresql.conf
+ subPath: postgresql.conf
+ - name: shared-data
+ mountPath: /var/lib/postgresql/data/
+ - name: psql-exporter
image: prometheuscommunity/postgres-exporter:v0.11.0
- env:
- - name: DATA_SOURCE_NAME
- value: postgresql://postgres:password@psql:5432/postgres?sslmode=disable
resources:
limits:
cpu: 100m
memory: "128Mi"
+ env:
+ - name: DATA_SOURCE_NAME
+ value: postgresql://postgres:password@psql:5432/postgres?sslmode=disable
ports:
- containerPort: 9187
name: metrics
+ volumes:
+ - name: shared-data
+ emptyDir: {}
+ - name: slowsql-psql-fluent-bit
+ configMap:
+ name: slowsql-psql-fluent-bit
+ items:
+ - key: fluent-bit-conf
+ path: fluent-bit.conf
+ - key: fluent-bit-parser-conf
+ path: fluent-bit-parser.conf
+ - key: fluent-bit-script-lua
+ path: fluent-bit-script.lua
+ - name: psql-slowsql-conf
+ configMap:
+ name: psql-slowsql-conf
+ items:
+ - key: psql-config-file
+ path: postgresql.conf
+
---
apiVersion: batch/v1beta1
kind: CronJob
metadata:
name: psql-load-deployment # @feature: postgresql; set up job to trigger postgresql commands, you don't need this in production env.
spec:
- schedule: "*/5 * * * *"
+ schedule: "*/1 * * * *"
successfulJobsHistoryLimit: 1
failedJobsHistoryLimit: 1
jobTemplate:
diff --git a/docs/readme.md b/docs/readme.md
index d502676..280178f 100644
--- a/docs/readme.md
+++ b/docs/readme.md
@@ -98,7 +98,7 @@ Currently, the features supported are:
| `trace-profiling` | Deploy tools to submit trace profiling tasks. | Only support deployment with SkyWalking agents installed, currently Java agent and Python agent support trace profiling. |
| `rover` | Deploy SkyWalking Rover and detect the processes in the Kubernetes environment. | Only support deployment in the Kubernetes environment, docker is not supported. |
| `mysql` | Start a MySQL server and load generator to execute the sample SQLs periodically, set up fluent bit to fetch slow logs and export to OAP, and export their metrics to SkyWalking. | |
-| `postgresql` | Start a PostgreSQL server, execute the sample SQLs periodically, and export their metrics to SkyWalking. | |
+| `postgresql` | Start a PostgreSQL server, and load generator to execute the sample SQLs periodically, set up fluent bit to fetch slow logs and export to OAP, and export their metrics to SkyWalking. | |
### Kubernetes