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:03:22 UTC
[skywalking-showcase] branch psql created (now a9d531e)
This is an automated email from the ASF dual-hosted git repository.
kezhenxu94 pushed a change to branch psql
in repository https://gitbox.apache.org/repos/asf/skywalking-showcase.git
at a9d531e Add PostgreSQL monitor feature
This branch includes the following new commits:
new a9d531e Add PostgreSQL monitor feature
The 1 revisions listed above as "new" are entirely new to this
repository and will be described in separate emails. The revisions
listed as "add" were already present in the repository and have only
been added to this reference.
[skywalking-showcase] 01/01: Add PostgreSQL monitor feature
Posted by ke...@apache.org.
This is an automated email from the ASF dual-hosted git repository.
kezhenxu94 pushed a commit to branch psql
in repository https://gitbox.apache.org/repos/asf/skywalking-showcase.git
commit a9d531e8c9b0025d7819c9a2dbcdef044f32fbdc
Author: kezhenxu94 <ke...@apache.org>
AuthorDate: Tue Oct 11 14:03:11 2022 +0800
Add PostgreSQL monitor feature
---
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