爱可生 mysql监控_actiontech-zabbix-mysql-monitor

广绪

2023-12-01

actiontech zabbix mysql monitor

percona monitoring plugins zabbix的go语言版本

0. 版本下载使用1.0分支

1. item取值与percona版本差异

使用mysqld端口自动发现相关item(zabbix Low level discovery)

innodb_transactions

取值方法：SHOW /\*!50000 ENGINE*/ INNODB STATUS,可得行Trx id counter 861144，取值861144

percona将861144作为十六进制字符解析，actiontech版本将861144作为十进制字符解析

unpurged_txns：

取值方法：SHOW /\*!50000 ENGINE*/ INNODB STATUS,可得行Purge done for trx's n:o < 861135 undo n:o < 0，取值861135

percona将861135作为十六进制字符解析，actiontech版本将861135作为十进制字符解析

running_slave，slave_lag:

取值方法：如果SHOW SLAVE STATUS为空，认为该mysql为master，设置running_slave=1，slave_lag=0；如果SHOW SLAVE STATUS不为空，与percona处理相同，依据slave_io_running及slave_sql_running等具体参数值设置

增加mysqld_port_listen

取值方法：netstat -ntlp |awk -F '[ ]+|/' '$4~/:port$/{print $8}'其中port为参数传入值

若上述命令结果等于mysqld，则值为1，反之为0

增加query_rt100s、query_rt10s、query_rt1s、query_rt100ms、query_rt10ms、query_rt1ms(默认关闭，设置get_qrt_mysql参数可开启)

取值方法SELECT 'query_rt100s' as rt, ifnull(sum(COUNT_STAR),0) as cnt FROM performance_schema.events_statements_summary_by_digest WHERE AVG_TIMER_WAIT >= 100000000000000 UNION SELECT 'query_rt10s', ifnull(sum(COUNT_STAR),0) as cnt FROM performance_schema.events_statements_summary_by_digest WHERE AVG_TIMER_WAIT BETWEEN 10000000000000 AND 10000000000000 UNION SELECT 'query_rt1s', ifnull(sum(COUNT_STAR),0) as cnt FROM performance_schema.events_statements_summary_by_digest WHERE AVG_TIMER_WAIT BETWEEN 1000000000000 AND 10000000000000 UNION SELECT 'query_rt100ms', ifnull(sum(COUNT_STAR),0) as cnt FROM performance_schema.events_statements_summary_by_digest WHERE AVG_TIMER_WAIT BETWEEN 100000000000 AND 1000000000000 UNION SELECT 'query_rt10ms', ifnull(sum(COUNT_STAR),0) as cnt FROM performance_schema.events_statements_summary_by_digest WHERE AVG_TIMER_WAIT BETWEEN 10000000000 AND 100000000000 UNION SELECT 'query_rt1ms', ifnull(sum(COUNT_STAR),0) as cnt FROM performance_schema.events_statements_summary_by_digest WHERE AVG_TIMER_WAIT BETWEEN 1000000000 AND 10000000000 UNION SELECT 'query_rt100us', ifnull(sum(COUNT_STAR),0) as cnt FROM performance_schema.events_statements_summary_by_digest WHERE AVG_TIMER_WAIT <= 1000000000

增加query_avgrt(默认关闭，设置get_qrt_mysql参数可开启)

取值方法:select round(avg(AVG_TIMER_WAIT)/1000/1000/1000,2) as avgrt from performance_schema.events_statements_summary_by_digest

去除SHOW /\*!50000 ENGINE*/ INNODB STATUS输出中INDIVIDUAL BUFFER POOL INFO段落的信息，避免重复计算

spin_rounds

取值方法：SHOW /\*!50000 ENGINE*/ INNODB STATUS,可得行

Mutex spin waits 2537, rounds 28527, OS waits 789 RW-shared spins 535, rounds 7850, OS waits 251 RW-excl spins 172, rounds 2334, OS waits 62取值spin_rounds = 28527 + 7850 + 2334

percona取值spin_rounds = 28527，actiontech版本增加RW-shared spins rounds以及RW-excl spin rounds

2. trigger与percona版本差异

增加MySQL {#MYSQLPORT} max_connections less than 4999 on {HOST.NAME}

增加MySQL {#MYSQLPORT} Open_files_limit less than 65534 on {HOST.NAME}

增加MySQL {#MYSQLPORT} port is not in listen state on {HOST.NAME}

增加{#MYSQLPORT} port mysql is down on {HOST.NAME}

删除原有的使用proc num监控的MySQL is down trigger(多实例下结果不正确)

删除原有的mysql active threads more than 40, mysql connections utilization more than 80%, mysql slave lag more than 300的trigger(zabbix 2.4 LLD下的trigger没有depend功能, 删除这些原本依赖depend的trigger以避免错误)

3. 限制

暂不支持mysql ssl连接方式

不完全支持Percona Server or MariaDB

仅支持zabbix2.4.0及以上版本

4. 增加对mysql 5.7支持

针对SHOW /*!50000 ENGINE*/ INNODB STATUS输出

5.6与5.7的几句差异

mysql 5.6

SEMAPHORES输出

Mutex spin waits 2537, rounds 28527, OS waits 789

RW-shared spins 535, rounds 7850, OS waits 251

RW-excl spins 172, rounds 2334, OS waits 62

计算：

spin_waits = 2537 + 535 + 172

spin_rounds = 28527 + 7850 + 2334

os_waits = 789 + 251 + 62

FILE I/O输出

Pending normal aio reads: 0, aio writes: 0,

ibuf aio reads: 0, log i/o's: 0, sync i/o's: 0

计算：

pending_normal_aio_reads = 0

pending_normal_aio_writes = 0

pending_ibuf_aio_reads = 0

pending_aio_log_ios = 0

pending_aio_sync_ios = 0

LOG输出

0 pending log writes, 0 pending chkp writes

计算：

pending_log_writes = 0

pending_chkp_writes = 0

BUFFER POOL AND MEMORY输出

Total memory allocated 137363456; in additional pool allocated 0

计算：

total_mem_alloc = 137363456

additional_pool_alloc = 0

mysql 5.7

SEMAPHORES输出(移除Mutex spin，新增RW-sx spins)

RW-shared spins 500, rounds 132474, OS waits 100787

RW-excl spins 0, rounds 200953, OS waits 6214

RW-sx spins 28837, rounds 826250, OS waits 26397

计算：

spin_waits = 500 + 0 + 28837

spin_rounds = 132474 + 200953 + 826250

os_waits = 100787 + 6214 + 26397

FILE I/O输出(拆分为每个io thread的值)

Pending normal aio reads: [1, 2, 3, 4] , aio writes: [5, 6, 7, 8] ,

ibuf aio reads:, log i/o's:, sync i/o's:

计算：

数组全部元素相加，空值为0

pending_normal_aio_reads = 1 + 2 + 3 + 4

pending_normal_aio_writes = 5 + 6 + 7 + 8

pending_ibuf_aio_reads = 0

pending_aio_log_ios = 0

pending_aio_sync_ios = 0

LOG输出

1 pending log flushes, 0 pending chkp writes

log flushes 与 log writes意义相同，计算方式不变

计算：

pending_log_writes = 1

pending_chkp_writes = 0

BUFFER POOL AND MEMORY输出(移除in additional pool allocated)

Total large memory allocated 137428992

计算：

total_mem_alloc = 137428992

additional_pool_alloc = 0

针对SHOW SLAVE STATUS输出，支持多源复制

slave_lag: 取多通道中seconds_behind_master的最大值

running_slave: 当所有通道的slave_io_running和slave_sql_running状态为Yes时, runing_slave值为1

relay_log_space: 取所有通道的relay_log_space的和

5. 安装

安装zabbix-agent

执行install.sh

//请检查确保/etc/sudoers中包含#includedir /etc/sudoers.d

//install.sh作用仅为拷贝文件至默认路径，可自行调整

创建采集mysql状态信息的用户

mysql> grant process,select,replication client on *.* to zbx@’127.0.0.1’ identified by 'zabbix';

拷贝

cp /var/lib/zabbix/actiontech/templates/userparameter_actiontech_mysql.conf /etc/zabbix/zabbix_agentd.d/

//若改变登陆密码或默认路径，请相应调整userparameter_actiontech_mysql.conf

重启agent

service zabbix-agent restart

测试数据采集

sudo -u zabbix -H /var/lib/zabbix/actiontech/scripts/actiontech_mysql_monitor --host 127.0.0.1 --user zbx --pass zabbix --items max_connections

//被监控mysql最大连接数

sudo -u zabbix -H /var/lib/zabbix/actiontech/scripts/actiontech_mysql_monitor --host 127.0.0.1 --user zbx --pass zabbix --items running_slave

//主从复制状态

sudo -u zabbix -H /var/lib/zabbix/actiontech/scripts/actiontech_mysql_monitor --discovery_port true

//json格式的mysqld端口占用

sudo -u zabbix -H /var/lib/zabbix/actiontech/scripts/actiontech_mysql_monitor --port 3306 --items mysqld_port_listen

//3306端口是否被mysqld占用

zabbix server导入配置模板actiontech_zbx_3.x_template_mysql_server.xml，添加主机、模板，开始监控

6. 文件列表

文件名

对应percona版本文件名

无

get_mysql_stats_wrapper.sh

actiontech_mysql_monitor

ss_get_mysql_stats.php

userparameter_actiontech_mysql.conf

userparameter_percona_mysql.conf

zabbix版本<=3.0使用actiontech_zbx_3.0_template_mysql_server.xml； zabbix版本==3.4使用actiontech_zbx_3.4_template_mysql_server.xml

zabbix_agent_template_percona_mysql_server_ht_2.0.9-sver1.1.5.xml

install.sh

无

actiontech_mysql_monitor

默认路径：/var/lib/zabbix/actiontech/scripts/actiontech_mysql_monitor

备注：二进制可执行文件，可单独使用(actiontech_mysql_monitor --help查看帮助actiontech_mysql_monitor --version查询版本)

userparameter_actiontech_mysql.conf

默认路径：/var/lib/zabbix/actiontech/templates/userparameter_actiontech_mysql.conf

备注：zabbix-agent的key配置文件

actiontech_zbx_3.x_template_mysql_server.xml.xml

默认路径：/var/lib/zabbix/actiontech/templates/actiontech_zbx_3.x_template_mysql_server.xml.xml

备注：模板文件，可导入zabbix

7. 所有item及其出处列表

item名

爱可生 mysql监控_actiontech-zabbix-mysql-monitor

相关阅读

相关文章

相关问答

相关文档