Optimize the mysql case by using sidecar for metrics exporter and fluent bit (#81)
diff --git a/deploy/platform/kubernetes/feature-mysql/fluent-bit.yaml b/deploy/platform/kubernetes/feature-mysql/fluent-bit.yaml
new file mode 100644
index 0000000..6a48c40
--- /dev/null
+++ b/deploy/platform/kubernetes/feature-mysql/fluent-bit.yaml
@@ -0,0 +1,172 @@
+# 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: slowsql-mysql-fluent-bit
+ labels:
+ app: slowsql-mysql-fluent-bit
+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 = "mysql::"..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"
diff --git a/deploy/platform/kubernetes/feature-mysql/mysql.yaml b/deploy/platform/kubernetes/feature-mysql/mysql.yaml
index 63f3677..795cdca 100644
--- a/deploy/platform/kubernetes/feature-mysql/mysql.yaml
+++ b/deploy/platform/kubernetes/feature-mysql/mysql.yaml
@@ -16,6 +16,25 @@
# under the License.
#
+apiVersion: v1
+kind: ConfigMap
+metadata:
+ name: mysqld-config
+ labels:
+ app: mysqld-config
+data:
+ 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
@@ -48,6 +67,10 @@
ON SCHEDULE EVERY 1 SECOND
DO COMMIT;
+ CREATE EVENT `event_5`
+ ON SCHEDULE EVERY 1 SECOND
+ DO SELECT SLEEP(3);
+
---
apiVersion: v1
kind: Service
@@ -59,6 +82,10 @@
ports:
- protocol: TCP
port: 3306
+ name: mysqld
+ - protocol: TCP
+ port: 9104
+ name: mysqld-exporter
---
apiVersion: apps/v1
@@ -80,46 +107,19 @@
sidecar.istio.io/inject: "false"
spec:
containers:
- - name: mysql
- image: mysql:8.0.30
+ - name: fluent-bit
+ image: fluent/fluent-bit:1.9
env:
- - name: MYSQL_ROOT_PASSWORD
- value: password
- ports:
- - containerPort: 3306
----
-apiVersion: v1
-kind: Service
-metadata:
- name: mysql-service
-spec:
- selector:
- app: mysql-service
- ports:
- - protocol: TCP
- port: 9104
-
----
-apiVersion: apps/v1
-kind: Deployment
-metadata:
- name: mysql-service-deployment
- labels:
- app: mysql-service
-spec:
- replicas: 1
- selector:
- matchLabels:
- app: mysql-service
- template:
- metadata:
- labels:
- app: mysql-service
- annotations:
- sidecar.istio.io/inject: "false"
- spec:
- containers:
- - name: mysql
+ - name: SW_SERVICE
+ valueFrom:
+ fieldRef:
+ fieldPath: metadata.name
+ volumeMounts:
+ - name: slowsql-mysql-fluent-bit
+ mountPath: /fluent-bit/etc/
+ - name: shared-data
+ mountPath: /data/
+ - name: mysqld-exporter
image: prom/mysqld-exporter:v0.14.0
env:
- name: DATA_SOURCE_NAME
@@ -131,6 +131,38 @@
ports:
- containerPort: 9104
name: metrics
+ - 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: slowsql-mysql-fluent-bit
+ configMap:
+ name: slowsql-mysql-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: mysqld-config
+ configMap:
+ name: mysqld-config
+ items:
+ - key: mysqld-config-file
+ path: config-file.cnf
+
---
apiVersion: batch/v1
kind: Job
diff --git a/deploy/platform/kubernetes/feature-mysql/open-telemetry.yaml b/deploy/platform/kubernetes/feature-mysql/open-telemetry.yaml
index f26b750..8ec063c 100644
--- a/deploy/platform/kubernetes/feature-mysql/open-telemetry.yaml
+++ b/deploy/platform/kubernetes/feature-mysql/open-telemetry.yaml
@@ -38,7 +38,7 @@
relabel_configs:
- source_labels: [__meta_kubernetes_pod_container_name, __meta_kubernetes_pod_container_port_name]
action: keep
- regex: mysql;metrics # @feature: mysql; reference the name of the metrics port
+ regex: mysqld-exporter;metrics # @feature: mysql; reference the name of the metrics port
- source_labels: [__meta_kubernetes_pod_name]
target_label: host_name
regex: (.+)
@@ -62,6 +62,7 @@
- batch
exporters:
- otlp
+ - logging
---
apiVersion: apps/v1
diff --git a/deploy/platform/kubernetes/feature-slowsql-mysql/fluent-bit.yaml b/deploy/platform/kubernetes/feature-slowsql-mysql/fluent-bit.yaml
deleted file mode 100644
index b2a6743..0000000
--- a/deploy/platform/kubernetes/feature-slowsql-mysql/fluent-bit.yaml
+++ /dev/null
@@ -1,330 +0,0 @@
-# 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 f1de27d..d502676 100644
--- a/docs/readme.md
+++ b/docs/readme.md
@@ -97,8 +97,7 @@
| `function` | Deploy [OpenFunction](https://openfunction.dev/) and export trace to SkyWalking. | Command [ofn](https://github.com/OpenFunction/cli) is required to run this feature. |
| `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. | |
+| `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. | |
### Kubernetes
