Giter Club home page Giter Club logo

actiontech_zabbix_mysql_monitor's Introduction

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. 安装

  1. 安装zabbix-agent
  2. 执行install.sh
    //请检查确保/etc/sudoers中包含#includedir /etc/sudoers.d //install.sh作用仅为拷贝文件至默认路径,可自行调整
  3. 创建采集mysql状态信息的用户
    mysql> grant process,select,replication client on *.* to zbx@’127.0.0.1’ identified by 'zabbix';
  4. 拷贝
    cp /var/lib/zabbix/actiontech/templates/userparameter_actiontech_mysql.conf /etc/zabbix/zabbix_agentd.d/
    //若改变登陆密码或默认路径,请相应调整userparameter_actiontech_mysql.conf
    重启agent
    service zabbix-agent restart
  5. 测试数据采集
    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占用
  6. 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名 相关出处 引入版本(默认1.0)
Key_read_requests SHOW /*!50002 GLOBAL */ STATUS
Key_reads SHOW /*!50002 GLOBAL */ STATUS
Key_write_requests SHOW /*!50002 GLOBAL */ STATUS
Key_writes SHOW /*!50002 GLOBAL */ STATUS
history_list SHOW /*!50000 ENGINE*/ INNODB STATUS
innodb_transactions SHOW /*!50000 ENGINE*/ INNODB STATUS
read_views SHOW /*!50000 ENGINE*/ INNODB STATUS
current_transactions SHOW /*!50000 ENGINE*/ INNODB STATUS
locked_transactions SHOW /*!50000 ENGINE*/ INNODB STATUS
active_transactions SHOW /*!50000 ENGINE*/ INNODB STATUS
pool_size Innodb_buffer_pool_pages_total or SHOW /*!50000 ENGINE*/ INNODB STATUS
free_pages Innodb_buffer_pool_pages_free or SHOW /*!50000 ENGINE*/ INNODB STATUS
database_pages Innodb_buffer_pool_pages_data or SHOW /*!50000 ENGINE*/ INNODB STATUS
modified_pages Innodb_buffer_pool_pages_dirty or SHOW /*!50000 ENGINE*/ INNODB STATUS
pages_read pages_read or SHOW /*!50000 ENGINE*/ INNODB STATUS
pages_created Innodb_pages_created or SHOW /*!50000 ENGINE*/ INNODB STATUS
pages_written pages_written or SHOW /*!50000 ENGINE*/ INNODB STATUS
file_fsyncs Innodb_data_fsyncs or SHOW /*!50000 ENGINE*/ INNODB STATUS
file_reads SHOW /*!50000 ENGINE*/ INNODB STATUS
file_writes SHOW /*!50000 ENGINE*/ INNODB STATUS
log_writes SHOW /*!50000 ENGINE*/ INNODB STATUS
pending_aio_log_ios SHOW /*!50000 ENGINE*/ INNODB STATUS
pending_aio_sync_ios SHOW /*!50000 ENGINE*/ INNODB STATUS
pending_buf_pool_flushes SHOW /*!50000 ENGINE*/ INNODB STATUS
pending_chkp_writes SHOW /*!50000 ENGINE*/ INNODB STATUS
pending_ibuf_aio_reads SHOW /*!50000 ENGINE*/ INNODB STATUS
pending_log_flushes Innodb_os_log_pending_fsyncs or SHOW /*!50000 ENGINE*/ INNODB STATUS
pending_log_writes SHOW /*!50000 ENGINE*/ INNODB STATUS
pending_normal_aio_reads Innodb_data_pending_reads or SHOW /*!50000 ENGINE*/ INNODB STATUS
pending_normal_aio_writes Innodb_data_pending_writes or SHOW /*!50000 ENGINE*/ INNODB STATUS
ibuf_inserts SHOW /*!50000 ENGINE*/ INNODB STATUS
ibuf_merged SHOW /*!50000 ENGINE*/ INNODB STATUS
ibuf_merges SHOW /*!50000 ENGINE*/ INNODB STATUS
spin_waits SHOW /*!50000 ENGINE*/ INNODB STATUS
spin_rounds SHOW /*!50000 ENGINE*/ INNODB STATUS
os_waits SHOW /*!50000 ENGINE*/ INNODB STATUS
rows_inserted Innodb_rows_inserted or SHOW /*!50000 ENGINE*/ INNODB STATUS
rows_updated Innodb_rows_updated or SHOW /*!50000 ENGINE*/ INNODB STATUS
rows_deleted Innodb_rows_deleted or SHOW /*!50000 ENGINE*/ INNODB STATUS
rows_read Innodb_rows_read or SHOW /*!50000 ENGINE*/ INNODB STATUS
Table_locks_waited SHOW /*!50002 GLOBAL */ STATUS
Table_locks_immediate SHOW /*!50002 GLOBAL */ STATUS
Slow_queries SHOW /*!50002 GLOBAL */ STATUS
Open_files SHOW /*!50002 GLOBAL */ STATUS
Open_tables SHOW /*!50002 GLOBAL */ STATUS
Opened_tables SHOW /*!50002 GLOBAL */ STATUS
innodb_open_files SHOW VARIABLES
open_files_limit SHOW VARIABLES
table_cache SHOW VARIABLES
Aborted_clients SHOW /*!50002 GLOBAL */ STATUS
Aborted_connects SHOW /*!50002 GLOBAL */ STATUS
Max_used_connections SHOW /*!50002 GLOBAL */ STATUS
Slow_launch_threads SHOW /*!50002 GLOBAL */ STATUS
Threads_cached SHOW /*!50002 GLOBAL */ STATUS
Threads_connected SHOW /*!50002 GLOBAL */ STATUS
Threads_created SHOW /*!50002 GLOBAL */ STATUS
Threads_running SHOW /*!50002 GLOBAL */ STATUS
max_connections SHOW VARIABLES
thread_cache_size SHOW VARIABLES
Connections SHOW /*!50002 GLOBAL */ STATUS
slave_running slave_lag or 0
slave_stopped slave_lag or 0
Slave_retried_transactions SHOW /*!50002 GLOBAL */ STATUS
slave_lag SHOW SLAVE STATUS or percona.heartbeat
Slave_open_temp_tables SHOW /*!50002 GLOBAL */ STATUS
Qcache_free_blocks SHOW /*!50002 GLOBAL */ STATUS
Qcache_free_memory SHOW /*!50002 GLOBAL */ STATUS
Qcache_hits SHOW /*!50002 GLOBAL */ STATUS
Qcache_inserts SHOW /*!50002 GLOBAL */ STATUS
Qcache_lowmem_prunes SHOW /*!50002 GLOBAL */ STATUS
Qcache_not_cached SHOW /*!50002 GLOBAL */ STATUS
Qcache_queries_in_cache SHOW /*!50002 GLOBAL */ STATUS
Qcache_total_blocks SHOW /*!50002 GLOBAL */ STATUS
query_cache_size SHOW VARIABLES
Questions SHOW /*!50002 GLOBAL */ STATUS
Com_update SHOW /*!50002 GLOBAL */ STATUS
Com_insert SHOW /*!50002 GLOBAL */ STATUS
Com_select SHOW /*!50002 GLOBAL */ STATUS
Com_delete SHOW /*!50002 GLOBAL */ STATUS
Com_replace SHOW /*!50002 GLOBAL */ STATUS
Com_load SHOW /*!50002 GLOBAL */ STATUS
Com_update_multi SHOW /*!50002 GLOBAL */ STATUS
Com_insert_select SHOW /*!50002 GLOBAL */ STATUS
Com_delete_multi SHOW /*!50002 GLOBAL */ STATUS
Com_replace_select SHOW /*!50002 GLOBAL */ STATUS
Select_full_join SHOW /*!50002 GLOBAL */ STATUS
Select_full_range_join SHOW /*!50002 GLOBAL */ STATUS
Select_range SHOW /*!50002 GLOBAL */ STATUS
Select_range_check SHOW /*!50002 GLOBAL */ STATUS
Select_scan SHOW /*!50002 GLOBAL */ STATUS
Sort_merge_passes SHOW /*!50002 GLOBAL */ STATUS
Sort_range SHOW /*!50002 GLOBAL */ STATUS
Sort_rows SHOW /*!50002 GLOBAL */ STATUS
Sort_scan SHOW /*!50002 GLOBAL */ STATUS
Created_tmp_tables SHOW /*!50002 GLOBAL */ STATUS
Created_tmp_disk_tables SHOW /*!50002 GLOBAL */ STATUS
Created_tmp_files SHOW /*!50002 GLOBAL */ STATUS
Bytes_sent SHOW /*!50002 GLOBAL */ STATUS
Bytes_received SHOW /*!50002 GLOBAL */ STATUS
innodb_log_buffer_size SHOW VARIABLES
unflushed_log log_bytes_written-log_bytes_flushed or innodb_log_buffer_size
log_bytes_flushed SHOW /*!50000 ENGINE*/ INNODB STATUS
log_bytes_written SHOW /*!50000 ENGINE*/ INNODB STATUS
relay_log_space SHOW SLAVE STATUS
binlog_cache_size SHOW VARIABLES
Binlog_cache_disk_use SHOW /*!50002 GLOBAL */ STATUS
Binlog_cache_use SHOW /*!50002 GLOBAL */ STATUS
binary_log_space SHOW MASTER LOGS
innodb_locked_tables SHOW /*!50000 ENGINE*/ INNODB STATUS
innodb_lock_structs SHOW /*!50000 ENGINE*/ INNODB STATUS
State_closing_tables SHOW PROCESSLIST
State_copying_to_tmp_table SHOW PROCESSLIST
State_end SHOW PROCESSLIST
State_freeing_items SHOW PROCESSLIST
State_init SHOW PROCESSLIST
State_locked SHOW PROCESSLIST
State_login SHOW PROCESSLIST
State_preparing SHOW PROCESSLIST
State_reading_from_net SHOW PROCESSLIST
State_sending_data SHOW PROCESSLIST
State_sorting_result SHOW PROCESSLIST
State_statistics SHOW PROCESSLIST
State_updating SHOW PROCESSLIST
State_writing_to_net SHOW PROCESSLIST
State_none SHOW PROCESSLIST
State_other SHOW PROCESSLIST
Time_top_1 SHOW PROCESSLIST 1.1版本引入
Time_top_2 SHOW PROCESSLIST 1.1版本引入
Time_top_3 SHOW PROCESSLIST 1.1版本引入
Time_top_4 SHOW PROCESSLIST 1.1版本引入
Time_top_5 SHOW PROCESSLIST 1.1版本引入
Time_top_6 SHOW PROCESSLIST 1.1版本引入
Time_top_7 SHOW PROCESSLIST 1.1版本引入
Time_top_8 SHOW PROCESSLIST 1.1版本引入
Time_top_9 SHOW PROCESSLIST 1.1版本引入
Time_top_10 SHOW PROCESSLIST 1.1版本引入
Handler_commit SHOW /*!50002 GLOBAL */ STATUS
Handler_delete SHOW /*!50002 GLOBAL */ STATUS
Handler_discover SHOW /*!50002 GLOBAL */ STATUS
Handler_prepare SHOW /*!50002 GLOBAL */ STATUS
Handler_read_first SHOW /*!50002 GLOBAL */ STATUS
Handler_read_key SHOW /*!50002 GLOBAL */ STATUS
Handler_read_next SHOW /*!50002 GLOBAL */ STATUS
Handler_read_prev SHOW /*!50002 GLOBAL */ STATUS
Handler_read_rnd SHOW /*!50002 GLOBAL */ STATUS
Handler_read_rnd_next SHOW /*!50002 GLOBAL */ STATUS
Handler_rollback SHOW /*!50002 GLOBAL */ STATUS
Handler_savepoint SHOW /*!50002 GLOBAL */ STATUS
Handler_savepoint_rollback SHOW /*!50002 GLOBAL */ STATUS
Handler_update SHOW /*!50002 GLOBAL */ STATUS
Handler_write SHOW /*!50002 GLOBAL */ STATUS
innodb_tables_in_use SHOW /*!50000 ENGINE*/ INNODB STATUS
innodb_lock_wait_secs SHOW /*!50000 ENGINE*/ INNODB STATUS
hash_index_cells_total SHOW /*!50000 ENGINE*/ INNODB STATUS
hash_index_cells_used SHOW /*!50000 ENGINE*/ INNODB STATUS
total_mem_alloc SHOW /*!50000 ENGINE*/ INNODB STATUS
additional_pool_alloc SHOW /*!50000 ENGINE*/ INNODB STATUS
uncheckpointed_bytes log_bytes_written-last_checkpoint
ibuf_used_cells SHOW /*!50000 ENGINE*/ INNODB STATUS
ibuf_free_cells SHOW /*!50000 ENGINE*/ INNODB STATUS
ibuf_cell_count SHOW /*!50000 ENGINE*/ INNODB STATUS
adaptive_hash_memory SHOW /*!50000 ENGINE*/ INNODB STATUS
page_hash_memory SHOW /*!50000 ENGINE*/ INNODB STATUS
dictionary_cache_memory SHOW /*!50000 ENGINE*/ INNODB STATUS
file_system_memory SHOW /*!50000 ENGINE*/ INNODB STATUS
lock_system_memory SHOW /*!50000 ENGINE*/ INNODB STATUS
recovery_system_memory SHOW /*!50000 ENGINE*/ INNODB STATUS
thread_hash_memory SHOW /*!50000 ENGINE*/ INNODB STATUS
innodb_sem_waits SHOW /*!50000 ENGINE*/ INNODB STATUS
innodb_sem_wait_time_ms SHOW /*!50000 ENGINE*/ INNODB STATUS
Key_buf_bytes_unflushed key_cache_block_size*Key_blocks_not_flushed
Key_buf_bytes_used key_buffer_size-(Key_blocks_unused*key_cache_block_size)
key_buffer_size SHOW VARIABLES
Innodb_row_lock_time SHOW /*!50002 GLOBAL */ STATUS
Innodb_row_lock_waits SHOW /*!50002 GLOBAL */ STATUS
Query_time_count_00 Percona Server or MariaDB
Query_time_count_01 Percona Server or MariaDB
Query_time_count_02 Percona Server or MariaDB
Query_time_count_03 Percona Server or MariaDB
Query_time_count_04 Percona Server or MariaDB
Query_time_count_05 Percona Server or MariaDB
Query_time_count_06 Percona Server or MariaDB
Query_time_count_07 Percona Server or MariaDB
Query_time_count_08 Percona Server or MariaDB
Query_time_count_09 Percona Server or MariaDB
Query_time_count_10 Percona Server or MariaDB
Query_time_count_11 Percona Server or MariaDB
Query_time_count_12 Percona Server or MariaDB
Query_time_count_13 Percona Server or MariaDB
Query_time_total_00 Percona Server or MariaDB
Query_time_total_01 Percona Server or MariaDB
Query_time_total_02 Percona Server or MariaDB
Query_time_total_03 Percona Server or MariaDB
Query_time_total_04 Percona Server or MariaDB
Query_time_total_05 Percona Server or MariaDB
Query_time_total_06 Percona Server or MariaDB
Query_time_total_07 Percona Server or MariaDB
Query_time_total_08 Percona Server or MariaDB
Query_time_total_09 Percona Server or MariaDB
Query_time_total_10 Percona Server or MariaDB
Query_time_total_11 Percona Server or MariaDB
Query_time_total_12 Percona Server or MariaDB
Query_time_total_13 Percona Server or MariaDB
wsrep_replicated_bytes Percona Server or MariaDB
wsrep_received_bytes Percona Server or MariaDB
wsrep_replicated Percona Server or MariaDB
wsrep_received Percona Server or MariaDB
wsrep_local_cert_failures Percona Server or MariaDB
wsrep_local_bf_aborts Percona Server or MariaDB
wsrep_local_send_queue Percona Server or MariaDB
wsrep_local_recv_queue Percona Server or MariaDB
wsrep_cluster_size Percona Server or MariaDB
wsrep_cert_deps_distance Percona Server or MariaDB
wsrep_apply_window Percona Server or MariaDB
wsrep_commit_window Percona Server or MariaDB
wsrep_flow_control_paused Percona Server or MariaDB
wsrep_flow_control_sent Percona Server or MariaDB
wsrep_flow_control_recv Percona Server or MariaDB
pool_reads Innodb_buffer_pool_reads
pool_read_requests Innodb_buffer_pool_read_requests
running_slave SHOW SLAVE STATUS
mysqld_port_listen netstat -ntlp
query_rt100s performance_schema.events_statements_summary_by_digest
query_rt10s performance_schema.events_statements_summary_by_digest
query_rt1s performance_schema.events_statements_summary_by_digest
query_rt100ms performance_schema.events_statements_summary_by_digest
query_rt10ms performance_schema.events_statements_summary_by_digest
query_rt1ms performance_schema.events_statements_summary_by_digest
query_avgrt performance_schema.events_statements_summary_by_digest
read_only SHOW VARIABLES
server_id SHOW VARIABLES
uncommitted_trx_duration_top_1 information_schema.processlist and information_schema.innodb_trx
uncommitted_trx_duration_top_2 information_schema.processlist and information_schema.innodb_trx
uncommitted_trx_duration_top_3 information_schema.processlist and information_schema.innodb_trx

8. trigger列表

注:以下列表未列出trigger依赖,详情可见actiontech_zbx_3.x_template_mysql_server.xml.xml

trigger名 触发器表达式
MySQL is down on {HOST.NAME} {proc.num[mysqld].last(0)}=0
MySQL {#MYSQLPORT} connections utilization more than 95% on {HOST.NAME} {Actiontech MySQL Server Template:MySQL.[{#MYSQLPORT},Threads_connected].last(0)}/{Actiontech MySQL Server Template:MySQL.[{#MYSQLPORT},max_connections].last(0)}>0.95
MySQL {#MYSQLPORT} active threads more than 100 on {HOST.NAME} {Actiontech MySQL Server Template:MySQL.[{#MYSQLPORT},Threads_running].last(0)}>100
MySQL {#MYSQLPORT} slave lag more than 600 on {HOST.NAME} {Actiontech MySQL Server Template:MySQL.[{#MYSQLPORT},slave_lag].last(0)}>600
Slave {#MYSQLPORT} is stopped on {HOST.NAME} {Actiontech MySQL Server Template:MySQL.[{#MYSQLPORT},running_slave].last(0)}=0
MySQL {#MYSQLPORT} max_connections less than 4999 on {HOST.NAME} {Actiontech MySQL Server Template:MySQL.[{#MYSQLPORT},max_connections].last(0)}<4999
MySQL {#MYSQLPORT} Open_files_limit less than 65534 on {HOST.NAME} {Actiontech MySQL Server Template:MySQL.[{#MYSQLPORT},open_files_limit].last(0)}<65534
MySQL {#MYSQLPORT} port is not in listen state on {HOST.NAME} {Actiontech MySQL Server Template:net.tcp.listen[{#MYSQLPORT}].last(0)}=0
{#MYSQLPORT} port is not mysqld on {HOST.NAME} {Actiontech MySQL Server Template:MySQL.[{#MYSQLPORT},mysqld_port_listen].last(0)}=0

9. issues

  • 欢迎在issues中提出任何使用问题

actiontech_zabbix_mysql_monitor's People

Contributors

actiontech-bot avatar ikarishinjieva avatar re-f avatar

Stargazers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

Watchers

 avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar  avatar

actiontech_zabbix_mysql_monitor's Issues

删除wrapper脚本

将脚本判断主从角色和缓存文件维护功能合并到主程序里

spin_rounds计算不全

show engine innodb status输出解析中spin_rounds计算缺失RW-shared spins rounds(5.5.17增加)和 RW-excl spin rounds(5.5.17增加)

支持performance_schema

支持从performance_schema里获取sql响应时间,原程序的响应时间监控项仅支持percona server

Support MySQL 5.7

show innodb status

mysql> SHOW /*!50000 ENGINE*/ INNODB STATUS;
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Type   | Name | Status                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| InnoDB |      | 
=====================================
2015-12-13 16:34:43 0x7fe9782b4700 INNODB MONITOR OUTPUT
=====================================
Per second averages calculated from the last 15 seconds
-----------------
BACKGROUND THREAD
-----------------
srv_master_thread loops: 852474 srv_active, 0 srv_shutdown, 17588 srv_idle
srv_master_thread log flush and writes: 870062
----------
SEMAPHORES
----------
OS WAIT ARRAY INFO: reservation count 2695766
OS WAIT ARRAY INFO: signal count 2861714
RW-shared spins 0, rounds 1341306, OS waits 183508
RW-excl spins 0, rounds 553078, OS waits 13765
RW-sx spins 4184, rounds 119993, OS waits 3770
Spin rounds per wait: 1341306.00 RW-shared, 553078.00 RW-excl, 28.68 RW-sx
------------
TRANSACTIONS
------------
Trx id counter 784052289
Purge done for trx's n:o < 784052289 undo n:o < 0 state: running but idle
History list length 1758
LIST OF TRANSACTIONS FOR EACH SESSION:
---TRANSACTION 422152722650960, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422152722654608, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422152722653696, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422152722652784, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422152722651872, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
---TRANSACTION 422152722657344, not started
0 lock struct(s), heap size 1136, 0 row lock(s)
--------
FILE I/O
--------
I/O thread 0 state: waiting for completed aio requests (insert buffer thread)
I/O thread 1 state: waiting for completed aio requests (log thread)
I/O thread 2 state: waiting for completed aio requests (read thread)
I/O thread 3 state: waiting for completed aio requests (read thread)
I/O thread 4 state: waiting for completed aio requests (read thread)
I/O thread 5 state: waiting for completed aio requests (read thread)
I/O thread 6 state: waiting for completed aio requests (read thread)
I/O thread 7 state: waiting for completed aio requests (read thread)
I/O thread 8 state: waiting for completed aio requests (read thread)
I/O thread 9 state: waiting for completed aio requests (read thread)
I/O thread 10 state: waiting for completed aio requests (read thread)
I/O thread 11 state: waiting for completed aio requests (read thread)
I/O thread 12 state: waiting for completed aio requests (read thread)
I/O thread 13 state: waiting for completed aio requests (read thread)
I/O thread 14 state: waiting for completed aio requests (write thread)
I/O thread 15 state: waiting for completed aio requests (write thread)
I/O thread 16 state: waiting for completed aio requests (write thread)
I/O thread 17 state: waiting for completed aio requests (write thread)
I/O thread 18 state: waiting for completed aio requests (write thread)
I/O thread 19 state: waiting for completed aio requests (write thread)
I/O thread 20 state: waiting for completed aio requests (write thread)
I/O thread 21 state: waiting for completed aio requests (write thread)
I/O thread 22 state: waiting for completed aio requests (write thread)
I/O thread 23 state: waiting for completed aio requests (write thread)
I/O thread 24 state: waiting for completed aio requests (write thread)
I/O thread 25 state: waiting for completed aio requests (write thread)
Pending normal aio reads: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] , aio writes: [0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0] ,
 ibuf aio reads:, log i/o's:, sync i/o's:
Pending flushes (fsync) log: 0; buffer pool: 0
1845120 OS file reads, 322178887 OS file writes, 8595398 OS fsyncs
0.13 reads/s, 16384 avg bytes/read, 692.42 writes/s, 20.87 fsyncs/s
-------------------------------------
INSERT BUFFER AND ADAPTIVE HASH INDEX
-------------------------------------
Ibuf: size 1, free list len 78, seg size 80, 221065 merges
merged operations:
 insert 410367, delete mark 0, delete 0
discarded operations:
 insert 0, delete mark 0, delete 0
Hash table size 9461113, node heap has 1 buffer(s)
Hash table size 9461113, node heap has 43 buffer(s)
Hash table size 9461113, node heap has 1 buffer(s)
Hash table size 9461113, node heap has 7 buffer(s)
Hash table size 9461113, node heap has 2 buffer(s)
Hash table size 9461113, node heap has 1 buffer(s)
Hash table size 9461113, node heap has 5028 buffer(s)
Hash table size 9461113, node heap has 19 buffer(s)
701.82 hash searches/s, 481.37 non-hash searches/s

---
LOG

---
Log sequence number 338086806104
Log flushed up to   338086701989
Pages flushed up to 337893695348
Last checkpoint at  337892779162
0 pending log flushes, 0 pending chkp writes
287170691 log i/o's done, 651.60 log i/o's/second
----------------------
BUFFER POOL AND MEMORY
----------------------
Total large memory allocated 35181821952
Dictionary memory allocated 845480
Buffer pool size   2097024
Free buffers       8192
Database pages     2083730
Old database pages 769025
Modified db pages  14765
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 698403, not young 367390
0.07 youngs/s, 0.40 non-youngs/s
Pages read 1845072, created 5545638, written 31512819
0.13 reads/s, 1.60 creates/s, 31.66 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 2083730, unzip_LRU len: 0
I/O sum[15632]:cur[0], unzip sum[0]:cur[0]
----------------------
INDIVIDUAL BUFFER POOL INFO
----------------------
---BUFFER POOL 0
Buffer pool size   262144
Free buffers       1024
Database pages     260480
Old database pages 96133
Modified db pages  1779
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 89745, not young 52407
0.00 youngs/s, 0.00 non-youngs/s
Pages read 226913, created 696652, written 3928743
0.00 reads/s, 0.60 creates/s, 3.93 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 260480, unzip_LRU len: 0
I/O sum[1954]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 1
Buffer pool size   262112
Free buffers       1024
Database pages     260445
Old database pages 96120
Modified db pages  1094
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 96934, not young 63327
0.00 youngs/s, 0.20 non-youngs/s
Pages read 235207, created 682482, written 3910248
0.07 reads/s, 0.00 creates/s, 3.93 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 260445, unzip_LRU len: 0
I/O sum[1954]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 2
Buffer pool size   262144
Free buffers       1024
Database pages     260482
Old database pages 96134
Modified db pages  1906
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 88308, not young 23120
0.00 youngs/s, 0.00 non-youngs/s
Pages read 227545, created 685713, written 3912381
0.00 reads/s, 0.07 creates/s, 4.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 260482, unzip_LRU len: 0
I/O sum[1954]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 3
Buffer pool size   262112
Free buffers       1024
Database pages     260448
Old database pages 96121
Modified db pages  2463
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 92299, not young 47113
0.00 youngs/s, 0.00 non-youngs/s
Pages read 229238, created 695905, written 3987887
0.00 reads/s, 0.13 creates/s, 4.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 260448, unzip_LRU len: 0
I/O sum[1954]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 4
Buffer pool size   262144
Free buffers       1024
Database pages     260484
Old database pages 96135
Modified db pages  1931
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 99976, not young 58704
0.00 youngs/s, 0.00 non-youngs/s
Pages read 234007, created 695549, written 3940433
0.00 reads/s, 0.60 creates/s, 4.00 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 260484, unzip_LRU len: 0
I/O sum[1954]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 5
Buffer pool size   262112
Free buffers       1024
Database pages     260453
Old database pages 96123
Modified db pages  1954
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 26168, not young 19195
0.00 youngs/s, 0.20 non-youngs/s
Pages read 223661, created 694001, written 3942893
0.07 reads/s, 0.20 creates/s, 3.93 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 260453, unzip_LRU len: 0
I/O sum[1954]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 6
Buffer pool size   262144
Free buffers       1024
Database pages     260481
Old database pages 96134
Modified db pages  1644
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 99554, not young 56165
0.00 youngs/s, 0.00 non-youngs/s
Pages read 233919, created 697369, written 3960427
0.00 reads/s, 0.00 creates/s, 3.93 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 260481, unzip_LRU len: 0
I/O sum[1954]:cur[0], unzip sum[0]:cur[0]
---BUFFER POOL 7
Buffer pool size   262112
Free buffers       1024
Database pages     260457
Old database pages 96125
Modified db pages  1994
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 105419, not young 47359
0.07 youngs/s, 0.00 non-youngs/s
Pages read 234582, created 697967, written 3929807
0.00 reads/s, 0.00 creates/s, 3.93 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 260457, unzip_LRU len: 0
I/O sum[1954]:cur[0], unzip sum[0]:cur[0]
--------------
ROW OPERATIONS
--------------
0 queries inside InnoDB, 0 queries in queue
0 read views open inside InnoDB
Process ID=122556, Main thread ID=140640873428736, state: sleeping
Number of rows inserted 283194386, updated 292299839, deleted 131, read 839949762
79.33 inserts/s, 651.89 updates/s, 0.00 deletes/s, 651.89 reads/s
----------------------------
END OF INNODB MONITOR OUTPUT
============================
 |
+--------+------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> exit

Got exception

windows how to set up

windows how to set up?

需要安装go语言环境,直接提供一个exe版本吧:);才稍微了解了下go,还是不错的语言

增加关于process监控的功能

  1. 监控MySQL运行用户下的process(进程数和线程数)与ulimit限制的关系, 超过一定比例告警
  2. 监控系统级别的process, 与ulimit限制的关系, 超过一定比例告警
  3. 监控MySQL max_connection与ulimit的限制关系, 超过一定比例告警

增加关于processlist中Time的监控

目标
监控数据库连接状态,判断是否存在长时间执行的SQL或者未关闭连接。
实现
建议在脚本中采集processlist中Time值的监控,取TOP10即可。如果可以的话最好一并调整监控模板中的监控项以及触发器。望采纳。

支持自动发现实例

主程序参考zabbix lld功能实现自动发现实例,模板中支持相关监控项原型

import Template error

Created: Application "MySQL" on "Actiontech MySQL Server Template".
Created: Item "Total number of mysqld processes" on "Actiontech MySQL Server Template".
mb_strlen() expects parameter 1 to be string, array given [conf.import.php:130 → CConfigurationImport->import() → CConfigurationImport->processDiscoveryRules() → CAPIObject->create() → CAPIObject->__call() → czbxrpc::call() → czbxrpc::callAPI() → call_user_func() → CDiscoveryRule->create() → CDiscoveryRule->createReal() → DB::insert() → DB::checkValueTypes() → zbx_strlen() → mb_strlen() in /usr/share/zabbix/include/func.inc.php:955]
mysqli_real_escape_string() expects parameter 2 to be string, array given [conf.import.php:130 → CConfigurationImport->import() → CConfigurationImport->processDiscoveryRules() → CAPIObject->create() → CAPIObject->__call() → czbxrpc::call() → czbxrpc::callAPI() → call_user_func() → CDiscoveryRule->create() → CDiscoveryRule->createReal() → DB::insert() → DB::checkValueTypes() → zbx_dbstr() → mysqli_real_escape_string() in /usr/share/zabbix/include/db.inc.php:1224]
Array to string conversion [conf.import.php:130 → CConfigurationImport->import() → CConfigurationImport->processDiscoveryRules() → CAPIObject->create() → CAPIObject->_call() → czbxrpc::call() → czbxrpc::callAPI() → call_user_func() → CDiscoveryRule->create() → CDiscoveryRule->createReal() → DB::insert() → implode() in /usr/share/zabbix/include/classes/db/DB.php:450]
Error in query [INSERT INTO items (name,type,snmp_community,snmp_oid,delay,status,snmpv3_contextname,snmpv3_securityname,snmpv3_securitylevel,snmpv3_authprotocol,snmpv3_authpassphrase,snmpv3_privprotocol,snmpv3_privpassphrase,delay_flex,params,ipmi_sensor,authtype,username,password,publickey,privatekey,port,filter,lifetime,description,key
,trapper_hosts,hostid,flags,value_type,data_type,interfaceid,itemid) VALUES ('discovery mysql port','0','','','30','0','','','0','0','','0','','','','','0','','','','','',Array,'30','','MySQL.port_discovery','','10105','1','4','0',NULL,'23661')] [Unknown column 'Array' in 'field list']

监控项增加、调整建议

  1. 增加监控状态、配置信息:read_only、server_id
    获取方式
    show variables like 'server_id'
    show global status like 'read_only'

  2. 用户会话非空闲状态的top3持续时间
    目前进取show processlist或select * from Information_schema.processlist输出中time字段,并未进行过滤
    建议
    获取show processlist或select * from Information_schema.processlist输出中time字段,根据以下条件过滤
    states not in ('','sleep') and user not in ('root','repl')
    并且展示数据建议使用用户与时间关联,而非TOP

  3. 未提交事务最长持续时间
    建议监控未提交事务的状态,如果存在多值取top3即可。
    SELECT p.user,p.time FROM information_schema.innodb_trx t INNER JOIN information_schema.processlist p ON t.trx_mysql_thread_id = p.id WHERE t.trx_state = 'RUNNING' AND p.time > 10 AND p.command = 'Sleep';
    展示数据建议使用用户与时间关联

Support MySQL 8.0

MySQL 8.0 has been released for a few months, it is time to support it.

使用动态用户参数代替静态

将当前的静态key,改成动态key,例如
UserParameter=mysql.status[*], program -uzabbix -pzabbix -P$1 |grep -w $2|awk '{print $$2}'

$1 用来表示实例端口
$2 表示监控key

程序以key value形式输出
例如:zabbix_get -s agent_ip -k mysql.status[3306,uptime]

Recommend Projects

  • React photo React

    A declarative, efficient, and flexible JavaScript library for building user interfaces.

  • Vue.js photo Vue.js

    🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.

  • Typescript photo Typescript

    TypeScript is a superset of JavaScript that compiles to clean JavaScript output.

  • TensorFlow photo TensorFlow

    An Open Source Machine Learning Framework for Everyone

  • Django photo Django

    The Web framework for perfectionists with deadlines.

  • D3 photo D3

    Bring data to life with SVG, Canvas and HTML. 📊📈🎉

Recommend Topics

  • javascript

    JavaScript (JS) is a lightweight interpreted programming language with first-class functions.

  • web

    Some thing interesting about web. New door for the world.

  • server

    A server is a program made to process requests and deliver data to clients.

  • Machine learning

    Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.

  • Game

    Some thing interesting about game, make everyone happy.

Recommend Org

  • Facebook photo Facebook

    We are working to build community through open source technology. NB: members must have two-factor auth.

  • Microsoft photo Microsoft

    Open source projects and samples from Microsoft.

  • Google photo Google

    Google ❤️ Open Source for everyone.

  • D3 photo D3

    Data-Driven Documents codes.