Zabbix服务数据库优化和配置优化

王先森2021-08-122021-08-12

性能优化

监控数据分析

zabbix监控主机和监控项较少的时候，不需要优化。
数据库 200台主机 * 200个监控项 = 40000监控项/30秒 = 1333次写入/每秒。
写多，读少

优化思路

mariadb 5.5 innodb 升级到mysql5.7 tokudb。
去掉无用监控项，增加监控项的取值间隔，减少历史数据的保存周期。
被动模式改为主动模式。
针对zabbix-server进程数量调优。
针对zabbix-server缓存调优，谁的占用内存大，就加大他的缓存。

升级MySQL存储引擎

安装perconna源

1	yum install https://mirrors.tuna.tsinghua.edu.cn/percona/yum/percona-release-1.0-16.noarch.rpm

安装依赖

yum install epel-release -y
yum install jemalloc -y

## 查看库文件
[root@zabbix ~]# rpm -ql jemalloc
/usr/bin/jemalloc.sh
/usr/lib64/libjemalloc.so.1
/usr/share/doc/jemalloc-3.6.0
/usr/share/doc/jemalloc-3.6.0/COPYING
/usr/share/doc/jemalloc-3.6.0/README
/usr/share/doc/jemalloc-3.6.0/VERSION
/usr/share/doc/jemalloc-3.6.0/jemalloc.html

## 修改my.cnf
[mysqld_safe]
malloc-lib=/usr/lib64/libjemalloc.so.1

关闭大页内存

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

+++++++++++++++++++++++++++++++++++
TokuDB won’t be able to start if the transparent huge pages are enabled. Transparent huge pages is feature available in the newer kernel versions. You can check if the Transparent huge pages are enabled with:
$ cat /sys/kernel/mm/transparent_hugepage/enabled
 [always] madvise never
If transparent huge pages are enabled and you try to start the TokuDB engine you’ll get the following message in you error.log:
Transparent huge pages are enabled, according to /sys/kernel/mm/redhat_transparent_hugepage/enabled
Transparent huge pages are enabled, according to /sys/kernel/mm/transparent_hugepage/enabled
You can disable transparent huge pages permanently by passing transparent_hugepage=never to the kernel in your bootloader (NOTE: For this change to take an effect you’ll need to reboot your server).

You can disable the transparent huge pages by running the following command as root (NOTE: Setting this will last only until the server is rebooted):

echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag
++++++++++++++++++++++++++++++++++++++++

安装percona数据库及tokudb插件

下载tokudb地址：https://share.weiyun.com/85VzMF0i

1	yum localinstall Percona-Server-tokudb-57* -y

启动mysql

1 2	systemctl start mysqld grep -i password /var/log/mysqld.log

配置远程登录

mysql> SET PASSWORD = PASSWORD('NIhao123!');
mysql> ALTER USER USER() IDENTIFIED BY 'NIhao123!';
mysql> grant all privileges on *.* to root@"%" identified by "NIhao123!";
mysql> flush privileges;

[root@zabbix01 ]# systemctl restart mysqld

加载插件

### 命令安装
ps-admin -e -uroot -pNIhao123!
或ps-admin --enable-tokudb -uroot -pNIhao123!

### 手动安装
INSTALL PLUGIN tokudb SONAME 'ha_tokudb.so';
INSTALL PLUGIN tokudb_file_map SONAME 'ha_tokudb.so';
INSTALL PLUGIN tokudb_fractal_tree_info SONAME 'ha_tokudb.so';
INSTALL PLUGIN tokudb_fractal_tree_block_map SONAME 'ha_tokudb.so';
INSTALL PLUGIN tokudb_trx SONAME 'ha_tokudb.so';
INSTALL PLUGIN tokudb_locks SONAME 'ha_tokudb.so';
INSTALL PLUGIN tokudb_lock_waits SONAME 'ha_tokudb.so';
INSTALL PLUGIN tokudb_background_job_status SONAME 'ha_tokudb.so';

### 查看插件
show engines;
SELECT @@tokudb_version;

###添加zabbix用户
grant all on zabbix.* to zabbix@'localhost' identified by 'Zabbix_pass123';
创建数据库：
create database zabbix character set utf8 collate utf8_bin;

zabbix安装

[root@zabbix02 ~]# cat /etc/yum.repos.d/zabbix.repo
[zabbix]
name=Zabbix Official Repository - $basearch
baseurl=https://mirrors.tuna.tsinghua.edu.cn/zabbix/zabbix/4.0/rhel/7/$basearch/
enabled=1
gpgcheck=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-ZABBIX

[zabbix-non-supported]
name=Zabbix Official Repository non-supported - $basearch 
baseurl=https://mirrors.tuna.tsinghua.edu.cn/zabbix/non-supported/rhel/7/$basearch/
enabled=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-ZABBIX
gpgcheck=0
[root@zabbix02 ~]#

yum install -y zabbix-server-mysql zabbix-web-mysql zabbix-agent

修改配置文件

[root@zabbix02 ~]# vim /etc/zabbix/zabbix_server.conf 
DBPassword=Zabbix_pass123
[root@zabbix02 ~]# vim /etc/httpd/conf.d/zabbix.conf 
php_value date.timezone Asia/Shanghai

导入zabbix数据库

1
2
3

cd /usr/share/doc/zabbix-server-mysql-4.0.27/  
sed -i s/InnoDB/tokudb/g create.sql 
zcat create.sql.gz |mysql -uzabbix -pZabbix_pass123 zabbix

重启zabbix相关服务

1
2
3

systemctl restart zabbix-server
systemctl restart zabbix-agent
systemctl restart httpd

web 安装部署

web 安装部署文档

zabbix配置文件优化

############ GENERAL PARAMETERS #################

### Option: ListenPort
# Listen port for trapper.
#
# Mandatory: no
# Range: 1024-32767
# Default:
ListenPort=10051 #监听端口

### Option: SourceIP
# Source IP address for outgoing connections.
#
# Mandatory: no
# Default:
# SourceIP= #多IP地址情况下，与其他服务器通信使用的源IP地址

### Option: LogType
# Specifies where log messages are written to:
# system - syslog
# file - file specified with LogFile parameter
# console - standard output
#
# Mandatory: no
# Default:
# LogType=file #日志类型，支持system、file和console

### Option: LogFile
# Log file name for LogType 'file' parameter.
#
# Mandatory: yes, if LogType is set to file, otherwise no
# Default:
# LogFile=

LogFile=/tmp/zabbix_server.log #日志路径

### Option: LogFileSize
# Maximum size of log file in MB.
# 0 - disable automatic log rotation.
#
# Mandatory: no
# Range: 0-1024
# Default:
# LogFileSize=1 #指定日志滚动写入大小，当达到指定大小时，会删除就日志重新从头写入新日志

### Option: DebugLevel
# Specifies debug level:
# 0 - basic information about starting and stopping of Zabbix processes
# 1 - critical information
# 2 - error information
# 3 - warnings
# 4 - for debugging (produces lots of information)
# 5 - extended debugging (produces even more information)
#
# Mandatory: no
# Range: 0-5
# Default:
# DebugLevel=3 #自定义日志级别

### Option: PidFile
# Name of PID file.
#
# Mandatory: no
# Default:
# PidFile=/tmp/zabbix_server.pid #PID文件路径

### Option: SocketDir
# IPC socket directory.
# Directory to store IPC sockets used by internal Zabbix services.
#
# Mandatory: no
# Default:
# SocketDir=/tmp #socket文件路径

### Option: DBHost
# Database host name.
# If set to localhost, socket is used for MySQL.
# If set to empty string, socket is used for PostgreSQL.
#
# Mandatory: no
# Default:
DBHost=10.1.1.210 #数据库地址

### Option: DBName
# Database name.
#
# Mandatory: yes
# Default:
# DBName=

DBName=zabbix #数据库名称

### Option: DBSchema
# Schema name. Used for IBM DB2 and PostgreSQL.
#
# Mandatory: no
# Default:
# DBSchema= #数据库访问协议

### Option: DBUser
# Database user.
#
# Mandatory: no
# Default:
# DBUser=

DBUser=zabbix #数据库用户名称

### Option: DBPassword
# Database password.
# Comment this line if no password is used.
#
# Mandatory: no
# Default:
DBPassword=Zabbix_pass123 #数据库用户密码

### Option: DBSocket
# Path to MySQL socket.
#
# Mandatory: no
# Default:
# DBSocket= #数据库socket文件

### Option: DBPort
# Database port when not using local socket.
#
# Mandatory: no
# Range: 1024-65535
# Default:
DBPort=3306 #数据库端口

### Option: HistoryStorageURL
# History storage HTTP[S] URL.
#
# Mandatory: no
# Default:
# HistoryStorageURL= #elasticsearch服务器地址，保存zabbix历史数据到ES里面，优化zabbix性能，新版本zabbix的才支持

### Option: HistoryStorageTypes
# Comma separated list of value types to be sent to the history storage.
#
# Mandatory: no
# Default:
# HistoryStorageTypes=uint,dbl,str,log,text #elasticsearch索引类型

### Option: HistoryStorageDateIndex
# Enable preprocessing of history values in history storage to store values in different indices based on date.
# 0 - disable
# 1 - enable
#
# Mandatory: no
# Default:
# HistoryStorageDateIndex=0 #将历史数据保存到不同的elasticsearch索引

### Option: ExportDir
# Directory for real time export of events, history and trends in newline delimited JSON format.
# If set, enables real time export.
#
# Mandatory: no
# Default:
# ExportDir= #定义实时导出触发器事件，监控项采集值，趋势数据的目录

### Option: ExportFileSize
# Maximum size per export file in bytes.
# Only used for rotation if ExportDir is set.
#
# Mandatory: no
# Range: 1M-1G
# Default:
# ExportFileSize=1G #定义每个导出文件的最大大小

############ ADVANCED PARAMETERS ################

### Option: StartPollers
# Number of pre-forked instances of pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartPollers=5 #预启动多少个实例数量

### Option: StartIPMIPollers
# Number of pre-forked instances of IPMI pollers.
# The IPMI manager process is automatically started when at least one IPMI poller is started.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartIPMIPollers=0 #IPMI收集数量

### Option: StartPreprocessors
# Number of pre-forked instances of preprocessing workers.
# The preprocessing manager process is automatically started when preprocessor worker is started.
#
# Mandatory: no
# Range: 1-1000
# Default:
# StartPreprocessors=3 #预启动多少个进程用于处理zabbix agent数据

### Option: StartPollersUnreachable
# Number of pre-forked instances of pollers for unreachable hosts (including IPMI and Java).
# At least one poller for unreachable hosts must be running if regular, IPMI or Java pollers
# are started.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartPollersUnreachable=1 #不可达主机的轮询进程的初始实例启动数量

### Option: StartTrappers
# Number of pre-forked instances of trappers.
# Trappers accept incoming connections from Zabbix sender, active agents and active proxies.
# At least one trapper process must be running to display server availability and view queue
# in the frontend.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartTrappers=5 #预启动的Trappers进程数据

### Option: StartPingers
# Number of pre-forked instances of ICMP pingers.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartPingers=1 #ICMP ping进程的初始实例数量

### Option: StartDiscoverers
# Number of pre-forked instances of discoverers.
#
# Mandatory: no
# Range: 0-250
# Default:
# StartDiscoverers=1 #自动发现主机的进程数量

### Option: StartHTTPPollers
# Number of pre-forked instances of HTTP pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartHTTPPollers=1 #http 进程数量

### Option: StartTimers
# Number of pre-forked instances of timers.
# Timers process maintenance periods.
# Only the first timer process handles host maintenance updates. Problem suppression updates are shared
# between all timers.
#
# Mandatory: no
# Range: 1-1000
# Default:
# StartTimers=1 #计时器实例数量，计时器用于计算问题的发生时间和步骤同步等

### Option: StartEscalators
# Number of pre-forked instances of escalators.
#
# Mandatory: no
# Range: 0-100
# Default:
# StartEscalators=1 #escalators进程的初始实例数量，用于处理动作中的自动步骤的进程的数量

### Option: StartAlerters
# Number of pre-forked instances of alerters.
# Alerters send the notifications created by action operations.
#
# Mandatory: no
# Range: 0-100
# Default:
# StartAlerters=3 #报警实例预启动数量

### Option: JavaGateway
# IP address (or hostname) of Zabbix Java gateway.
# Only required if Java pollers are started.
#
# Mandatory: no
# Default:
JavaGateway=192.168.7.101 #javagateway服务器地址，java pollers必须设置

### Option: JavaGatewayPort
# Port that Zabbix Java gateway listens on.
#
# Mandatory: no
# Range: 1024-32767
# Default:
JavaGatewayPort=10052 #javagateway端口

### Option: StartJavaPollers
# Number of pre-forked instances of Java pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
StartJavaPollers=20 #java轮训实例预启动数量

### Option: StartVMwareCollectors
# Number of pre-forked vmware collector instances.
#
# Mandatory: no
# Range: 0-250
# Default:
# StartVMwareCollectors=0 #用于设置监控VMWARE Esxi主机实例时使用，若为0则不启用，若要监控ESXI主机，此值最少为1，根据监控ESXI数量设置对应数值

### Option: VMwareFrequency
# How often Zabbix will connect to VMware service to obtain a new data.
#
# Mandatory: no
# Range: 10-86400
# Default:
# VMwareFrequency=60 #监控vmware获取最新数据间隔频率，单位为秒

### Option: VMwarePerfFrequency
# How often Zabbix will connect to VMware service to obtain performance data.
#
# Mandatory: no
# Range: 10-86400
# Default:
# VMwarePerfFrequency=60 #监控vmware获取性能数据间隔

### Option: VMwareCacheSize
# Size of VMware cache, in bytes.
# Shared memory size for storing VMware data.
# Only used if VMware collectors are started.
#
# Mandatory: no
# Range: 256K-2G
# Default:
# VMwareCacheSize=8M #vmware数据缓存大小，会占用zabbix server服务器内存

### Option: VMwareTimeout
# Specifies how many seconds vmware collector waits for response from VMware service.
#
# Mandatory: no
# Range: 1-300
# Default:
# VMwareTimeout=10 #超时时间

### Option: SNMPTrapperFile
# Temporary file used for passing data from SNMP trap daemon to the server.
# Must be the same as in zabbix_trap_receiver.pl or SNMPTT configuration file.
#
# Mandatory: no
# Default:
# SNMPTrapperFile=/tmp/zabbix_traps.tmp #snmp触发器临时文件路径

### Option: StartSNMPTrapper
# If 1, SNMP trapper process is started.
#
# Mandatory: no
# Range: 0-1
# Default:
# StartSNMPTrapper=0 #SNMP触发器预启动数量

### Option: ListenIP
# List of comma delimited IP addresses that the trapper should listen on.
# Trapper will listen on all network interfaces if this parameter is missing.
#
# Mandatory: no
# Default:
# ListenIP=0.0.0.0 #监听地址

# ListenIP=127.0.0.1

### Option: HousekeepingFrequency
# How often Zabbix will perform housekeeping procedure (in hours).
# Housekeeping is removing outdated information from the database.
# To prevent Housekeeper from being overloaded, no more than 4 times HousekeepingFrequency
# hours of outdated information are deleted in one housekeeping cycle, for each item.
# To lower load on server startup housekeeping is postponed for 30 minutes after server start.
# With HousekeepingFrequency=0 the housekeeper can be only executed using the runtime control option.
# In this case the period of outdated information deleted in one housekeeping cycle is 4 times the
# period since the last housekeeping cycle, but not less than 4 hours and not greater than 4 days.
#
# Mandatory: no
# Range: 0-24
# Default:
# HousekeepingFrequency=1 #多少小时清理一次代理端数据库的 history数据

### Option: MaxHousekeeperDelete
# The table "housekeeper" contains "tasks" for housekeeping procedure in the format:
# [housekeeperid], [tablename], [field], [value].
# No more than 'MaxHousekeeperDelete' rows (corresponding to [tablename], [field], [value])
# will be deleted per one task in one housekeeping cycle.
# If set to 0 then no limit is used at all. In this case you must know what you are doing!
#
# Mandatory: no
# Range: 0-1000000
# Default:
# MaxHousekeeperDelete=5000 ##每次最多删除历史数据的行数

### Option: CacheSize
# Size of configuration cache, in bytes.
# Shared memory size for storing host, item and trigger data.
#
# Mandatory: no
# Range: 128K-8G
# Default:
# CacheSize=8M #配置信息缓存大小

### Option: CacheUpdateFrequency
# How often Zabbix will perform update of configuration cache, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
# CacheUpdateFrequency=60 #Zabbix更新缓存数据的频率，单位为秒，范围是1-3600

### Option: StartDBSyncers
# Number of pre-forked instances of DB Syncers.
#
# Mandatory: no
# Range: 1-100
# Default:
# StartDBSyncers=4 #zabbix和数据库同步数据的进程数量

### Option: HistoryCacheSize
# Size of history cache, in bytes.
# Shared memory size for storing history data.
#
# Mandatory: no
# Range: 128K-2G
# Default:
# HistoryCacheSize=16M #历史数据的缓存大小

### Option: HistoryIndexCacheSize
# Size of history index cache, in bytes.
# Shared memory size for indexing history cache.
#
# Mandatory: no
# Range: 128K-2G
# Default:
# HistoryIndexCacheSize=4M #历史数据索引缓存的大小

### Option: TrendCacheSize
# Size of trend cache, in bytes.
# Shared memory size for storing trends data.
#
# Mandatory: no
# Range: 128K-2G
# Default:
# TrendCacheSize=4M #用于设置划分多少系统共享内存用于存储计算出来的趋势数据，此参数值从一定程度上可影响数据库读压力，范围是128K-2G

### Option: ValueCacheSize
# Size of history value cache, in bytes.
# Shared memory size for caching item history data requests.
# Setting to 0 disables value cache.
#
# Mandatory: no
# Range: 0,128K-64G
# Default:
# ValueCacheSize=8M #历史值缓存的大小，用于缓存历史数据请求的共享内存大小。

### Option: Timeout
# Specifies how long we wait for agent, SNMP device or external check (in seconds).
#
# Mandatory: no
# Range: 1-30
# Default:
# Timeout=3

Timeout=4 #数据获取等待超时时间

### Option: TrapperTimeout
# Specifies how many seconds trapper may spend processing new data.
#
# Mandatory: no
# Range: 1-300
# Default:
# TrapperTimeout=300 #触发器处理新数据的最长时间，单位是秒

### Option: UnreachablePeriod
# After how many seconds of unreachability treat a host as unavailable.
#
# Mandatory: no
# Range: 1-3600
# Default:
# UnreachablePeriod=45 #当主机不可达多少秒后，设置为主机不可用，单位是秒，范围是1-3600

### Option: UnavailableDelay
# How often host is checked for availability during the unavailability period, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
# UnavailableDelay=60 #当主机不可用了，多久检查一次该主机的可用性，单位为秒，范围是1-3600

### Option: UnreachableDelay
# How often host is checked for availability during the unreachability period, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
# UnreachableDelay=15 #当主机不可到达了，多久检查一次该主机的可用性，单位为秒，范围是1-3600

### Option: AlertScriptsPath
# Full path to location of custom alert scripts.
# Default depends on compilation options.
# To see the default path run command "zabbix_server --help".
#
# Mandatory: no
# Default:
# AlertScriptsPath=${datadir}/zabbix/alertscripts #监控报警脚本路径,取决于编译时候的datadir参数

### Option: LogSlowQueries
# How long a database query may take before being logged (in milliseconds).
# Only works if DebugLevel set to 3, 4 or 5.
# 0 - don't log slow queries.
#
# Mandatory: no
# Range: 1-3600000
# Default:
# LogSlowQueries=0 慢日志时间(以毫秒为单位),仅当DebugLevel设置为3,4或5时才可用,0 - 不记录慢查询,范围是1-3600000

LogSlowQueries=3000

### Option: TmpDir
# Temporary directory.
#
# Mandatory: no
# Default:
# TmpDir=/tmp #临时文目录

### Option: StartProxyPollers
# Number of pre-forked instances of pollers for passive proxies.
#
# Mandatory: no
# Range: 0-250
# Default:
# StartProxyPollers=1 #启用多少子进程与代理端通信，若代理较多可考虑加大此数值，范围是0-250

### Option: ProxyConfigFrequency
# How often Zabbix Server sends configuration data to a Zabbix Proxy in seconds.
# This parameter is used only for proxies in the passive mode.
#
# Mandatory: no
# Range: 1-3600247
# Default:
# ProxyConfigFrequency=3600 #proxy被动模式下，server多少秒同步配置文件至proxy，该参数仅用于被动模式下的代理，范围是1-3600247

### Option: ProxyDataFrequency
# How often Zabbix Server requests history data from a Zabbix Proxy in seconds.
# This parameter is used only for proxies in the passive mode.
#
# Mandatory: no
# Range: 1-3600
# Default:
# ProxyDataFrequency=1 #被动模式下，zabbix server间隔多少秒向proxy请求历史数据

### Option: AllowRoot
# Allow the server to run as 'root'. If disabled and the server is started by 'root', the server
# will try to switch to the user specified by the User configuration option instead.
# Has no effect if started under a regular user.
# 0 - do not allow
# 1 - allow
#
# Mandatory: no
# Default:
# AllowRoot=0 #是否允许root启动zabbix

### Option: User
# Drop privileges to a specific, existing user on the system.
# Only has effect if run as 'root' and AllowRoot is disabled.
#
# Mandatory: no
# Default:
# User=zabbix #设置zabbix 启动用户