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 58b82e8 Add feature slow sql to showcase using mysql as example (#80)
58b82e8 is described below
commit 58b82e858e998ad8c2c2455aeca07174ae5ade88
Author: kezhenxu94 <[email protected]>
AuthorDate: Thu Sep 29 15:06:13 2022 +0800
Add feature slow sql to showcase using mysql as example (#80)
---
.../feature-slowsql-mysql/fluent-bit.yaml | 227 +++++++++++++++++++++
.../kubernetes/feature-slowsql-mysql/mysql.yaml | 162 +++++++++++++++
.../feature-slowsql-mysql/shared-volume.yaml | 46 +++++
docs/readme.md | 1 +
4 files changed, 436 insertions(+)
diff --git a/deploy/platform/kubernetes/feature-slowsql-mysql/fluent-bit.yaml
b/deploy/platform/kubernetes/feature-slowsql-mysql/fluent-bit.yaml
new file mode 100644
index 0000000..ebb8dd3
--- /dev/null
+++ b/deploy/platform/kubernetes/feature-slowsql-mysql/fluent-bit.yaml
@@ -0,0 +1,227 @@
+# 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: mysql-slowsql; fluent bit log configurations
+---
+apiVersion: v1
+kind: ConfigMap
+metadata:
+ name: fluent-bit-slowsql-mysql
+ labels:
+ app: fluent-bit-slowsql-mysql
+data:
+ fluent-bit-conf: |
+ [SERVICE]
+ flush 1
+ log_level info
+ parsers_File fluent-bit-parser.conf
+ [INPUT]
+ name tail
+ path /data/slow.log
+ read_from_head true
+ multiline.parser my-log-format
+ [FILTER]
+ name grep
+ match *
+ exclude log mysqld, Version:.*
+ [FILTER]
+ name grep
+ match *
+ exclude log Tcp port: 0.*
+ [FILTER]
+ name grep
+ match *
+ exclude log Time .*
+ [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 = {}
+
+ time = string.sub(arr[1], 9)
+ time = string.sub(time,1,19)
+ time = string.gsub(time,"-","");
+ time = string.gsub(time,"T","");
+ time = string.gsub(time,":","");
+ y1 = string.sub(time,1,4)
+ m1 = string.sub(time,5,6)
+ d1 = string.sub(time,7,8)
+ h1 = string.sub(time,9,10)
+ min1 = string.sub(time,11,12)
+ s1 = string.sub(time,13,14)
+ re1["time"] = os.time()
+
+ re1["layer"] = "MYSQL"
+ record["layer"] = "MYSQL"
+ id1,_ = string.find(arr[2],"Id:")
+ service = string.sub(arr[2],14,id1-1)
+ service = string.gsub(service," ","");
+ service = string.sub(service,1,10)
+ service = "mysql::"..service
+ record["service"]=service
+ re1["service"]= service
+
+ f1,_ = string.find(arr[3],"Lock")
+ query_time = string.sub(arr[3],15,f1-3)
+ local qt,_ = math.modf(query_time*1000)
+ re1["query_time"] = qt
+ re1["statement"] = ""
+
+ re1["id"] = uuid()
+
+ for i=4,#arr,1 do
+ re1["statement"] = re1["statement"]..arr[i]
+ end
+ 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: |
+ [MULTILINE_PARSER]
+ name my-log-format
+ type regex
+ flush_timeout 1000
+ rule "start_state" "^(# Time: .*)" "second_state"
+ rule "second_state" "^(# User@Host.*)" "third_state"
+ rule "third_state" "^(# Query_time: .*)" "statement"
+ rule "statement" "^\w+.*" "statement"
+
+---
+apiVersion: apps/v1
+kind: Deployment
+metadata:
+ name: fluent-bit-mysql-slowsql
+ labels:
+ app: fluent-bit-mysql-slowsql
+spec:
+ replicas: 1
+ selector:
+ matchLabels:
+ app: fluent-bit-mysql-slowsql
+ template:
+ metadata:
+ labels:
+ app: fluent-bit-mysql-slowsql
+ annotations:
+ sidecar.istio.io/inject: "false"
+ spec:
+ affinity:
+ podAffinity:
+ requiredDuringSchedulingIgnoredDuringExecution:
+ - labelSelector:
+ matchExpressions:
+ - key: app
+ operator: In
+ values:
+ - "mysql-slowsql"
+ topologyKey: "kubernetes.io/hostname"
+ containers:
+ - name: fluent-bit
+ image: fluent/fluent-bit:1.9
+ volumeMounts:
+ - name: fluent-bit-slowsql-mysql
+ mountPath: /fluent-bit/etc/
+ - name: slowsql-mysql-data
+ mountPath: /data
+ volumes:
+ - name: fluent-bit-slowsql-mysql
+ configMap:
+ name: fluent-bit-slowsql-mysql
+ 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: slowsql-mysql-data
+ persistentVolumeClaim:
+ claimName: slowsql-mysql-pvc
diff --git a/deploy/platform/kubernetes/feature-slowsql-mysql/mysql.yaml
b/deploy/platform/kubernetes/feature-slowsql-mysql/mysql.yaml
new file mode 100644
index 0000000..e32a50e
--- /dev/null
+++ b/deploy/platform/kubernetes/feature-slowsql-mysql/mysql.yaml
@@ -0,0 +1,162 @@
+# 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: mysql-slowsql; mysqld configuration
+---
+apiVersion: v1
+kind: ConfigMap
+metadata:
+ name: slowsql-mysql-cnf
+ labels:
+ app: slowsql-mysql-cnf
+data:
+ config-file: |
+ [mysqld]
+ init_connect='SET NAMES utf8'
+ slow_query_log=ON
+ event_scheduler=ON
+ long_query_time=1
+ slow_query_log_file=/data/slow.log
+ [client]
+ default-character-set=utf8
+ [mysql]
+ default-character-set=utf8
+
+---
+apiVersion: v1
+kind: ConfigMap
+metadata:
+ name: mock-sql-mysql-slowsql
+ labels:
+ app: mysql-load
+data:
+ mock-sql: |
+ CREATE DATABASE IF NOT EXISTS test;
+ USE test;
+ CREATE TABLE IF NOT EXISTS `t1`(
+ `te1` VARCHAR(100) NOT NULL
+ )ENGINE=InnoDB DEFAULT CHARSET=utf8;
+ SET GLOBAL event_scheduler = 1;
+
+ CREATE EVENT `event_1`
+ ON SCHEDULE EVERY 1 SECOND
+ DO INSERT INTO t1 values('test');
+
+ CREATE EVENT `event_2`
+ ON SCHEDULE EVERY 1 SECOND
+ DO UPDATE t1 SET `te1` = 1;
+
+ CREATE EVENT `event_3`
+ ON SCHEDULE EVERY 1 SECOND
+ DO DELETE FROM t1;
+
+ CREATE EVENT `event_4`
+ ON SCHEDULE EVERY 1 SECOND
+ DO COMMIT;
+
+ CREATE EVENT `event_5`
+ ON SCHEDULE EVERY 1 SECOND
+ DO SELECT SLEEP(3);
+
+---
+apiVersion: v1
+kind: Service
+metadata:
+ name: mysql-slowsql
+spec:
+ selector:
+ app: mysql-slowsql
+ ports:
+ - protocol: TCP
+ port: 3306
+
+---
+apiVersion: apps/v1
+kind: Deployment
+metadata:
+ name: mysql-deployment-slowsql
+ labels:
+ app: mysql-slowsql
+spec:
+ replicas: 1
+ selector:
+ matchLabels:
+ app: mysql-slowsql
+ template:
+ metadata:
+ labels:
+ app: mysql-slowsql
+ annotations:
+ sidecar.istio.io/inject: "false"
+ spec:
+ containers:
+ - name: mysql
+ image: mysql:8.0.30
+ env:
+ - name: MYSQL_ROOT_PASSWORD
+ value: password
+ ports:
+ - containerPort: 3306
+ volumeMounts:
+ - name: slowsql-mysql-cnf
+ mountPath: /etc/mysql/conf.d/config-file.cnf
+ subPath: config-file
+ - name: slowsql-mysql-data
+ mountPath: /data
+ volumes:
+ - name: slowsql-mysql-cnf
+ configMap:
+ name: slowsql-mysql-cnf
+ - name: slowsql-mysql-data
+ persistentVolumeClaim:
+ claimName: slowsql-mysql-pvc
+
+---
+apiVersion: batch/v1
+kind: Job
+metadata:
+ name: mysql-load-deployment-slowsql # @feature: mysql-slowsql; set up job to
trigger mysql slow commands, you don't need this in production env.
+spec:
+ template:
+ metadata:
+ name: mysql-load-deployment-slowsql
+ annotations:
+ sidecar.istio.io/inject: "false"
+ spec:
+ restartPolicy: Never
+ initContainers:
+ - name: wait-for-mysql
+ image: busybox
+ command: ["sh", "-c", "until nc -z mysql-slowsql 3306 > /dev/null;
do echo Waiting for mysql-slowsql.; sleep 2; done;"]
+ containers:
+ - name: mysql-load-slowsql
+ image: mysql:8.0.30
+ command:
+ - bash
+ - -c
+ - mysql -hmysql-slowsql -uroot -ppassword < /conf/mock.sql
+ volumeMounts:
+ - name: mock-sql-vol-mysql-slowsql
+ mountPath: /conf
+ volumes:
+ - name: mock-sql-vol-mysql-slowsql
+ configMap:
+ name: mock-sql-mysql-slowsql
+ items:
+ - key: mock-sql
+ path: mock.sql
diff --git
a/deploy/platform/kubernetes/feature-slowsql-mysql/shared-volume.yaml
b/deploy/platform/kubernetes/feature-slowsql-mysql/shared-volume.yaml
new file mode 100644
index 0000000..a7774d5
--- /dev/null
+++ b/deploy/platform/kubernetes/feature-slowsql-mysql/shared-volume.yaml
@@ -0,0 +1,46 @@
+# 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: mysql-slowsql: set up PVC so fluent bit can access to slow sql
logs from mysql, you can use any other methods to send logs to fluent bit
+apiVersion: v1
+kind: PersistentVolume
+metadata:
+ name: slowsql-mysql-pv
+ labels:
+ type: local
+spec:
+ storageClassName: manual
+ capacity:
+ storage: 1Gi
+ accessModes:
+ - ReadWriteMany
+ hostPath:
+ path: "/tmp/slowsql-mysql"
+
+---
+kind: PersistentVolumeClaim
+apiVersion: v1
+metadata:
+ name: slowsql-mysql-pvc
+spec:
+ accessModes:
+ - ReadWriteMany
+ storageClassName: manual
+ resources:
+ requests:
+ storage: 1Gi
diff --git a/docs/readme.md b/docs/readme.md
index f2f2e56..f1de27d 100644
--- a/docs/readme.md
+++ b/docs/readme.md
@@ -98,6 +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, execute the sample SQLs
periodically, and export their metrics to SkyWalking.
|
|
+| `slowsql-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. |
|
| `postgresql` | Start a PostgreSQL server, execute the sample SQLs
periodically, and export their metrics to SkyWalking.
|
|
### Kubernetes
