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
commit 5e5ea4cec751efe0cdd453094fcca589ed63aab3 Author: kezhenxu94 <[email protected]> AuthorDate: Thu Sep 29 18:33:55 2022 +0800 Add feature slow sql to showcase using mysql as example (#80) --- .../feature-slowsql-mysql/fluent-bit.yaml | 330 +++++++++++++++++++++ docs/readme.md | 1 + 2 files changed, 331 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..b2a6743 --- /dev/null +++ b/deploy/platform/kubernetes/feature-slowsql-mysql/fluent-bit.yaml @@ -0,0 +1,330 @@ +# 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-mysql; 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 = os.getenv("SW_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" + + mysqld-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-slowsql-mysql + 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: slowsql-mysql +spec: + selector: + app: slowsql-mysql + ports: + - protocol: TCP + port: 3306 + +--- +apiVersion: apps/v1 +kind: Deployment +metadata: + name: slowsql-mysql + labels: + app: slowsql-mysql +spec: + replicas: 1 + selector: + matchLabels: + app: slowsql-mysql + template: + metadata: + labels: + app: slowsql-mysql + annotations: + 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: fluent-bit-slowsql-mysql + mountPath: /fluent-bit/etc/ + - name: shared-data + mountPath: /data/ + - name: mysql + image: mysql:8.0.30 + env: + - name: MYSQL_ROOT_PASSWORD + value: password + ports: + - containerPort: 3306 + volumeMounts: + - name: mysqld-config + mountPath: /etc/mysql/conf.d/ + - name: shared-data + mountPath: /data/ + volumes: + - name: shared-data + emptyDir: {} + - 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: mysqld-config + configMap: + name: fluent-bit-slowsql-mysql + items: + - key: mysqld-config-file + path: config-file.cnf +--- +apiVersion: batch/v1 +kind: Job +metadata: + name: slowsql-mysql-load-generator # @feature: slowsql-mysql; 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 slowsql-mysql 3306 > /dev/null; do echo Waiting for slowsql-mysql.; sleep 2; done;"] + containers: + - name: mysql-load-slowsql + image: mysql:8.0.30 + command: + - bash + - -c + - mysql -hslowsql-mysql -uroot -ppassword < /conf/mock.sql + volumeMounts: + - name: mock-sql-vol-slowsql-mysql + mountPath: /conf + volumes: + - name: mock-sql-vol-slowsql-mysql + configMap: + name: mock-sql-slowsql-mysql + items: + - key: mock-sql + path: mock.sql 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
