debeando / zenit Goto Github PK
View Code? Open in Web Editor NEWThe Swiss Army Knife of Database Management
License: Apache License 2.0
The Swiss Army Knife of Database Management
License: Apache License 2.0
The values stored in the field sqltext in auditlog have query with this special symbols:
>
Stands for the greater-than sign ( > )<
Stands for the less-than sign ( < )
Stands for the NewLine	
Stands for the TabFor example:
select text_value, value from constants where table_name = 'xxx' and column_name ='status'
To start, logs to send:
Use the same port from node_exporter.
Reported by: Joffrey MICHAIE <@db_cat_twitter>
Related issues: #42
The tools pt-query-digest, percona / go-mysql and proxysql calculate different fingerprint for the same query, maybe is possible to use a standard. The idea of this issue is open thread to discuss about it.
Percona rules:
In the config file, add option to don't send original query for security reason, only the query digest.
Example:
[mysql-slowlog]
log_path = /var/lib/mysql/slow.log
buffer_size = 100
buffer_timeout = 60
original_query = on
Call tail command is inestable when the application is closed or killed, and this process is stay in background and not kill the children process, for this reason, maybe is good idea replace by native go-tail.
The buffer to catch x events of log parser send when it is completed. But not in all cases this happen because maybe to fill buffer require more time to complete, is necessary force mechanism each x time.
# zenit -quiet
panic: close of closed channel
goroutine 19 [running]:
github.com/swapbyt3s/zenit/plugins/inputs/mysql/audit.Parser.func1(0xc42005a5a0, 0xc42005a540, 0xc4200d4840)
/Users/swapbyt3s/go/src/github.com/swapbyt3s/zenit/plugins/inputs/mysql/audit/audit.go:76 +0x1025
created by github.com/swapbyt3s/zenit/plugins/inputs/mysql/audit.Parser
/Users/swapbyt3s/go/src/github.com/swapbyt3s/zenit/plugins/inputs/mysql/audit/audit.go:19 +0x63
Like xinetd, you can specify TCP port, and this return http code 200 when everything MySQL checks are ok, if have any MySQL error return http code 503.
For example:
mysql:
slowlog:
enable: true
log_path: /var/lib/mysql/slow.log
slowlog
and auditlog
) from [mysql]
section.The description of this JSON style is here.
Example:
package main
import (
"fmt"
)
func main() {
tags := make(map[string]string)
tags["schema"] = "identity"
tags["table"] = "user"
fields := make(map[string]interface{})
fields["percentage"] = 10
item := make(map[string]interface{})
item["tags"] = tags
item["fields"] = fields
metric := make(map[string][]map[string]interface{})
metric["mysql_overflow"] = append(metric["mysql_overflow"], item)
metrics := make(map[string][]map[string][]map[string]interface{})
metrics["127.0.0.1"] = append(metrics["127.0.0.1"], metric)
fmt.Println(fmt.Sprintf("%#v", metrics))
}
Output
map[string][]map[string][]map[string]interface {}{
"127.0.0.1":[]map[string][]map[string]interface {}{
map[string][]map[string]interface {}{
"mysql_overflow":[]map[string]interface {}{
map[string]interface {}{
"fields":map[string]interface {}{"percentage":10},
"tags":map[string]string{
"schema":"identity",
"table":"user"}}}}}}
To prevent re-start daemon when is enabled any *.log files to parse follow after start service or/and enable logging in MySQL, maybe is a good idea to retry read this files when if not exists.
Create a YAML or JSON with all custom queries to execute every x time:
Example:
queries:
- description: "purchase by hours"
query: "SELECT count(id) FROM purchases WHERE created_at >=..."
schedule: every 1 hour
warning: 10
alert: 0
channel: kpi
Related projects:
Lis of "queries" to collect metrics on MongoDB:
rs.status()
db.serverStatus()
db.runCommand({ dbStats: 1 })
use <db_name>
db.runCommand( { collStats : "<collection_name>", scale: 1024 } )
Include this collect to this table:
SELECT * FROM stats_mysql_commands_counters;
---
general:
aws_access_key_id: xxx
aws_secret_access_key: xxx
inputs:
rds:
enable: true
username: monitor
password: monitor
hostname: [dns|instance_name]
plugins:
aurora: false
overflow: true
slave: true
status: true
tables: true
variables: true
Message:
panic: interface conversion: interface {} is uint64, not string
goroutine 8 [running]:
github.com/swapbyt3s/zenit/plugins/alerts/proxysql/status.(*ProxyPoolStatus).Collect(0xa440b0)
/Users/nicola/go/src/github.com/swapbyt3s/zenit/plugins/alerts/proxysql/status/status.go:37 +0x83d
github.com/swapbyt3s/zenit/plugins.Load(0xc420016b20)
/Users/nicola/go/src/github.com/swapbyt3s/zenit/plugins/plugins.go:65 +0x196
created by main.(*program).run
/Users/nicola/go/src/github.com/swapbyt3s/zenit/main.go:42 +0x6f
This is a proposal.
For example:
mysql:
dsn: root@tcp(127.0.0.1:3306)/
overflow: true
slave: true
status: true
tables: true
variables: true
slowlog:
enable: true
log_path: /var/lib/mysql/slow.log
buffer_size: 100
buffer_timeout: 60
auditlog:
enable: true
format: xml-old
log_path: /var/lib/mysql/audit.log
buffer_size: 100
buffer_timeout: 60
alerts:
readonly:
enable: true
lag:
enable: false
warning: 10
critical: 60
duration: 30
connections:
enable: true
warning: 70
critical: 90
duration: 30
os:
cpu: true
disk:
enable: true
alerts:
- volume: /var/lib/mysql/
enable: true
warning: 75
critical: 95
duration: 30
2019/11/08 08:38:44 E! - Plugin - OutputIndluxDB:Ping - Error: Get http://x.y.z.x:8086/ping: dial tcp 172.30.1.253:8086: socket: too many open files
thn-prd-proxysql /home/ec2-user# cat /proc/$(pgrep -x zenit)/limits
Limit Soft Limit Hard Limit Units
Max cpu time unlimited unlimited seconds
Max file size unlimited unlimited bytes
Max data size unlimited unlimited bytes
Max stack size 8388608 unlimited bytes
Max core file size 0 unlimited bytes
Max resident set unlimited unlimited bytes
Max processes 14679 14679 processes
Max open files 1024 4096 files
Max locked memory 65536 65536 bytes
Max address space unlimited unlimited bytes
Max file locks unlimited unlimited locks
Max pending signals 14679 14679 signals
Max msgqueue size 819200 819200 bytes
Max nice priority 0 0
Max realtime priority 0 0
Max realtime timeout unlimited unlimited us
To follow the same fingerprint to pt-query-digest. Replace all literals inside of IN() lists with a single placeholder, regardless of cardinality.
Related issues: #37
For example;
SELECT name FROM user WHERE id IN (?, ?, ?, ?, ?, ?);
To:
SELECT name FROM user WHERE id IN (?);
Implement service project for good practice and remove legacy daemon.
2018/09/28 09:34:23 E! - MySQL:Indexes - Impossible to execute query: Error 1317: Query execution was interrupted
panic: runtime error: invalid memory address or nil pointer dereference
panic: runtime error: invalid memory address or nil pointer dereference
[signal SIGSEGV: segmentation violation code=0x1 addr=0x0 pc=0x5937cb]
goroutine 17 [running]:
database/sql.(*Rows).close(0x0, 0x0, 0x0, 0x0, 0x0)
/usr/local/Cellar/go/1.10.1/libexec/src/database/sql/sql.go:2907 +0x6b
database/sql.(*Rows).Close(0x0, 0x0, 0x0)
/usr/local/Cellar/go/1.10.1/libexec/src/database/sql/sql.go:2903 +0x33
panic(0x778b40, 0x9db6e0)
/usr/local/Cellar/go/1.10.1/libexec/src/runtime/panic.go:502 +0x229
database/sql.(*Rows).Next(0x0, 0x35)
/usr/local/Cellar/go/1.10.1/libexec/src/database/sql/sql.go:2599 +0x30
github.com/swapbyt3s/zenit/plugins/inputs/mysql.Indexes()
/Users/nicola/go/src/github.com/swapbyt3s/zenit/plugins/inputs/mysql/indexes.go:47 +0x2a0
github.com/swapbyt3s/zenit/plugins/inputs.doCollectPlugins(0xc42013e000)
/Users/nicola/go/src/github.com/swapbyt3s/zenit/plugins/inputs/inputs.go:60 +0x268
created by github.com/swapbyt3s/zenit/plugins/inputs.Gather
/Users/nicola/go/src/github.com/swapbyt3s/zenit/plugins/inputs/inputs.go:33 +0xc9
time="2018-09-19T10:46:19Z" level=error msg="Error parsing /usr/local/prometheus/textfile_collector/zenit.prom: text format parsing error in line 1131: unexpected end of input stream" source="textfile.go:99"
Should by object_id = \'?\'
, not object_id = \\'?\\'
:
Example:
2018/08/20 08:08:05 D! - ClickHouse - Event capture: AuditLog - map[string]string{"os_login":"", "priv_user":"", "proxy_user":"", "user":"demo", "connection_id":"2529923", "db":"", "host_ip":"127.0.0.1", "os_user":"", "status":"0", "sqltext_digest":"SELECT ... FROM ... WHERE object_id = \\'?\\' object_type = \\'?\\'", "command_class":"error", "host":"", "name":"Query", "sqltext":"SELECT ... FROM ... WHERE .object_id = \\'xxxx\\' AND c0_.object_type = \\'xxxx\\'", "_time":"2018-08-20 08:08:05", "host_name":"localhost", "ip":"127.0.0.1"}
With this feature, is possible with one daemon access to multiple servers, for example RDS / Aurora.
Functionalities for multiple servers:
The logging parsing files is imposible, in the case for RDS or other service type require different method to process it.
Error gathering metrics: 7 error(s) occurred:
collected metric zenit_os
label:<name:"device" value:"/dev/xvda1" >
label:<name:"name" value:"disk" >
untyped:<value:7.48 >
has label dimensions inconsistent with previously collected metrics in the same metric family
collected metric zenit_os
label:<name:"device" value:"lo" >
label:<name:"name" value:"net" >
label:<name:"type" value:"receive" >
untyped:<value:2.62588484339e+12 >
has label dimensions inconsistent with previously collected metrics in the same metric family
collected metric zenit_os
label:<name:"device" value:"lo" >
label:<name:"name" value:"net" >
label:<name:"type" value:"transmit" >
untyped:<value:2.62588484339e+12 >
has label dimensions inconsistent with previously collected metrics in the same metric family
collected metric zenit_os
label:<name:"device" value:"eth0" >
label:<name:"name" value:"net" >
label:<name:"type" value:"receive" >
untyped:<value:5.31028131819e+12 >
has label dimensions inconsistent with previously collected metrics in the same metric family
collected metric zenit_os
label:<name:"device" value:"eth0" >
label:<name:"name" value:"net" >
label:<name:"type" value:"transmit" >
untyped:<value:5.345703981086e+12 >
has label dimensions inconsistent with previously collected metrics in the same metric family
collected metric zenit_os
label:<name:"name" value:"sysctl" >
label:<name:"type" value:"nr_open" >
untyped:<value:1.048576e+06 >
has label dimensions inconsistent with previously collected metrics in the same metric family
collected metric zenit_os
label:<name:"name" value:"sysctl" >
label:<name:"type" value:"file_max" >
untyped:<value:382744 >
has label dimensions inconsistent with previously collected metrics in the same metric family
Adapt all metrics to this standard: https://prometheus.io/docs/practices/naming/
For any reason, the master/primary server no show the cpu value:
mysql> select server_id, cpu from ro_replica_status;
+------------------------------+---------+
| server_id | cpu |
+------------------------------+---------+
| zzz-prd-mysql-xxxxxxx-node01 | 0 |
| zzz-prd-mysql-xxxxxxx-node02 | 26.4988 |
| zzz-prd-mysql-xxxxxxx-node03 | 8.75 |
+------------------------------+---------+
3 rows in set (0.00 sec)
The debug logging generate lot amount of logs and fill very fast the volume, to prevent it, maybe in the install script, is a good option to add new rules for logrotate.
For automatic test before merge in master, is necessary to use travis-ci.org.
This bug is reported by: @cyrill3
The format log in MariaDB have light difference and the parser ignore it:
Example:
# administrator command: Ping;
# User@Host: clickuser[clickuser] @ localhost [127.0.0.1]
# Thread_id: 4371 Schema: QC_hit: No
# Query_time: 0.002916 Lock_time: 0.000020 Rows_sent: 596 Rows_examined: 596
# Rows_affected: 0
SET timestamp=1535364205;
SHOW GLOBAL VARIABLES;
The description of this new XML style is here.
When apply indexes starting from slave, and for another reason have inconsistent index in all slaves, with this feature you can find the difference and them fixed.
The idea is to collect all indexes from all tables to detect with another tool the difference between servers, maybe in the future is possible for columns.
+-------+------------+--------------------+--------------+----------------+...+-------------+...+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |...| Cardinality |...|
+-------+------------+--------------------+--------------+----------------+...+-------------+...+
| demo | 0 | PRIMARY | 1 | id |...| 368135 |...|
| demo | 1 | idx_audit_at | 1 | created_at |...| 17 |...|
| demo | 1 | idx_audit_at | 2 | deleted_at |...| 12694 |...|
| demo | 1 | idx_publication_id | 1 | publication_id |...| 61355 |...|
+-------+------------+--------------------+--------------+----------------+...+-------------+...+
output example:
mysql_indexes{"schema": "test", "table"="demo", "index": "idx_audit_at", "column": "deleted_at"} = 12694
Case 1:
bytes_sent
Case 2: Exception
panic: runtime error: slice bounds out of range
goroutine 53 [running]:
github.com/swapbyt3s/zenit/common/sql/parser/slow.Event(0xc4201a6840, 0xc4201a68a0)
/Users/nicola/go/src/github.com/swapbyt3s/zenit/common/sql/parser/slow/slow.go:19
created by github.com/swapbyt3s/zenit/plugins/inputs/mysql/slow.Parser
/Users/nicola/go/src/github.com/swapbyt3s/zenit/plugins/inputs/mysql/slow/slow.go
When application is closing, it no close or kill the sub process, for example in this case is tail
tool remains in execution.
root@d1c86f2f36ff:~# ./zenit -stop
root@d1c86f2f36ff:~# ps aux
USER PID %CPU %MEM VSZ RSS TTY STAT START TIME COMMAND
root 1 0.0 0.1 20336 2692 pts/0 Ss+ Aug08 0:00 /bin/bash
root 13272 0.0 0.1 20360 3364 pts/2 Ss+ 02:43 0:00 /bin/bash
root 13307 0.0 0.1 20364 3356 pts/3 Ss+ 02:47 0:01 /bin/bash
root 17918 0.0 0.1 20364 3344 pts/4 Ss 17:26 0:00 /bin/bash
root 18370 0.0 0.0 4268 700 pts/4 S 21:16 0:00 /usr/bin/tail -n 0 -f /var/lib/mysql/audit.log
root 18383 0.0 0.0 17500 2024 pts/4 R+ 21:16 0:00 ps aux
root@d1c86f2f36ff:~# kill $(pgrep tail)
Should not exit when file not exit
2018/08/20 15:57:50 I! - ClickHouse - DSN: http://127.0.0.1:8123/?database=zenit
2018/08/20 15:57:50 E! - Tail - File not exist: /mnt/dbstorage/mysql-data/audit.log
A declarative, efficient, and flexible JavaScript library for building user interfaces.
๐ Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. ๐๐๐
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google โค๏ธ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.