Потихоньку, помаленьку за год база разрослась до 24 гигов.
Начались проблемы со slow query, Zabbix history syncer processes more than 75% busy, Zabbix poller processes more than 75% busy, Disk I/O is overloaded, отказу триггеров и прочим чудесам.
Обновляться боюсь, ибо после последнего обновления упало всё вообще. Доходит до того, что веб-страница дашборда обновляется по нескольку минут, показывая крутящаяся колесо.
Куда бежать, чего смотреть пока не понятно.
Подскажите, кто хорошо разбирается, куда смотреть?
Характеристики сервера:
Виртуальная машина на VMware
Guest OS: Linux 3.10.0-693.17.1.el7.x86_64 CentOS Linux release 7.4.1708 (Core)
VM Version: 8
CPU: 2 vCPU
Memory: 4096 MB
Memory Overhead: 45,87 MB
zabbix-server-mysql 3.4.7
zabbix-* 3.4.7
mariadb 5.5.56
zabbix_server.conf
my.cnf
Последний лог (/var/log/zabbix/zabbix_server.log):
mysqltuner
Прошу помощи с настройкой!
Начались проблемы со slow query, Zabbix history syncer processes more than 75% busy, Zabbix poller processes more than 75% busy, Disk I/O is overloaded, отказу триггеров и прочим чудесам.
Обновляться боюсь, ибо после последнего обновления упало всё вообще. Доходит до того, что веб-страница дашборда обновляется по нескольку минут, показывая крутящаяся колесо.
Куда бежать, чего смотреть пока не понятно.
Подскажите, кто хорошо разбирается, куда смотреть?
Характеристики сервера:
Виртуальная машина на VMware
Guest OS: Linux 3.10.0-693.17.1.el7.x86_64 CentOS Linux release 7.4.1708 (Core)
VM Version: 8
CPU: 2 vCPU
Memory: 4096 MB
Memory Overhead: 45,87 MB
zabbix-server-mysql 3.4.7
zabbix-* 3.4.7
mariadb 5.5.56
| Zabbix сервер запущен | Да | localhost:10051 |
| Количество узлов сети (активированных/деактивированных/шаблонов) | 192 | 96 / 9 / 87 |
| Количество элементов данных (активированных/деактивированных/неподдерживаемых) | 8061 | 5537 / 2499 / 25 |
| Количество триггеров (активированных/деактивированных [проблема/ок]) | 2573 | 2211 / 362 [1 / 2210] |
| Количество пользователей (в сети) | 8 | 3 |
| Требуемое быстродействие сервера, новые значения в секунду | 72.53 |
Code:
# This is a configuration file for Zabbix server daemon
# To get more information about Zabbix, visit http://www.zabbix.com
############ 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=
### 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
### Option: LogFile
# Log file name for LogType 'file' parameter.
#
# Mandatory: no
# Default:
# LogFile=
LogFile=/var/log/zabbix/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
LogFileSize=0
### 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
PidFile=/var/run/zabbix/zabbix_server.pid
### 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=localhost
### Option: DBName
# Database name.
# For SQLite3 path to database file must be provided. DBUser and DBPassword are ignored.
#
# Mandatory: yes
# Default:
# DBName=
DBName=***************
### Option: DBSchema
# Schema name. Used for IBM DB2 and PostgreSQL.
#
# Mandatory: no
# Default:
# DBSchema=
### Option: DBUser
# Database user. Ignored for SQLite.
#
# Mandatory: no
# Default:
# DBUser=
DBUser=**************
### Option: DBPassword
# Database password. Ignored for SQLite.
# Comment this line if no password is used.
#
# Mandatory: no
# Default:
DBPassword=**********************
### Option: DBSocket
# Path to MySQL socket.
#
# Mandatory: no
# Default:
# DBSocket=/tmp/mysql.sock
### Option: DBPort
# Database port when not using local socket. Ignored for SQLite.
#
# Mandatory: no
# Range: 1024-65535
# Default (for MySQL):
# DBPort=3306
############ 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.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartIPMIPollers=0
### 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
### Option: StartPingers
# Number of pre-forked instances of ICMP pingers.
#
# Mandatory: no
# Range: 0-1000
# Default:
StartPingers=5
### Option: StartDiscoverers
# Number of pre-forked instances of discoverers.
#
# Mandatory: no
# Range: 0-250
# Default:
StartDiscoverers=3
### Option: StartHTTPPollers
# Number of pre-forked instances of HTTP pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
StartHTTPPollers=3
### Option: StartTimers
# Number of pre-forked instances of timers.
# Timers process time-based trigger functions and maintenance periods.
# Only the first timer process handles the maintenance periods.
#
# 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
### Option: JavaGateway
# IP address (or hostname) of Zabbix Java gateway.
# Only required if Java pollers are started.
#
# Mandatory: no
# Default:
# JavaGateway=
### Option: JavaGatewayPort
# Port that Zabbix Java gateway listens on.
#
# Mandatory: no
# Range: 1024-32767
# Default:
# JavaGatewayPort=10052
### Option: StartJavaPollers
# Number of pre-forked instances of Java pollers.
#
# Mandatory: no
# Range: 0-1000
# Default:
# StartJavaPollers=0
### Option: StartVMwareCollectors
# Number of pre-forked vmware collector instances.
#
# Mandatory: no
# Range: 0-250
# Default:
StartVMwareCollectors=5
### Option: VMwareFrequency
# How often Zabbix will connect to VMware service to obtain a new data.
#
# Mandatory: no
# Range: 10-86400
# Default:
VMwareFrequency=60
### Option: VMwarePerfFrequency
# How often Zabbix will connect to VMware service to obtain performance data.
#
# Mandatory: no
# Range: 10-86400
# Default:
# VMwarePerfFrequency=60
### 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
### 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
SNMPTrapperFile=/var/log/snmptrap/snmptrap.log
### Option: StartSNMPTrapper
# If 1, SNMP trapper process is started.
#
# Mandatory: no
# Range: 0-1
# Default:
StartSNMPTrapper=1
### 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
### 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.
# SQLite3 does not use this parameter, deletes all corresponding rows without a limit.
# 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=0
### Option: SenderFrequency
# How often Zabbix will try to send unsent alerts (in seconds).
#
# Mandatory: no
# Range: 5-3600
# Default:
# SenderFrequency=30
### 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
CacheSize=16M
### Option: CacheUpdateFrequency
# How often Zabbix will perform update of configuration cache, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
# CacheUpdateFrequency=60
### Option: StartDBSyncers
# Number of pre-forked instances of DB Syncers.
#
# Mandatory: no
# Range: 1-100
# Default:
StartDBSyncers=5
### 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=64M
### Option: TrendCacheSize
# Size of trend cache, in bytes.
# Shared memory size for storing trends data.
#
# Mandatory: no
# Range: 128K-2G
# Default:
# TrendCacheSize=4M
### 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
### Option: UnavailableDelay
# How often host is checked for availability during the unavailability period, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
# UnavailableDelay=60
### Option: UnreachableDelay
# How often host is checked for availability during the unreachability period, in seconds.
#
# Mandatory: no
# Range: 1-3600
# Default:
# UnreachableDelay=15
### Option: AlertScriptsPath
# Full path to location of custom alert scripts.
# Default depends on compilation options.
#
# Mandatory: no
# Default:
# AlertScriptsPath=${datadir}/zabbix/alertscripts
AlertScriptsPath=/usr/lib/zabbix/alertscripts
### Option: ExternalScripts
# Full path to location of external scripts.
# Default depends on compilation options.
#
# Mandatory: no
# Default:
# ExternalScripts=${datadir}/zabbix/externalscripts
ExternalScripts=/usr/lib/zabbix/externalscripts
### Option: FpingLocation
# Location of fping.
# Make sure that fping binary has root ownership and SUID flag set.
#
# Mandatory: no
# Default:
# FpingLocation=/usr/sbin/fping
### Option: Fping6Location
# Location of fping6.
# Make sure that fping6 binary has root ownership and SUID flag set.
# Make empty if your fping utility is capable to process IPv6 addresses.
#
# Mandatory: no
# Default:
# Fping6Location=/usr/sbin/fping6
### Option: SSHKeyLocation
# Location of public and private keys for SSH checks and actions.
#
# Mandatory: no
# Default:
# SSHKeyLocation=
### 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
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
### 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-3600*24*7
# Default:
# ProxyConfigFrequency=3600
### 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
### 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
### 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
### Option: Include
# You may include individual files or all files in a directory in the configuration file.
# Installing Zabbix will create include directory in /usr/local/etc, unless modified during the compile time.
#
# Mandatory: no
# Default:
# Include=
# Include=/usr/local/etc/zabbix_server.general.conf
# Include=/usr/local/etc/zabbix_server.conf.d/
# Include=/usr/local/etc/zabbix_server.conf.d/*.conf
### Option: SSLCertLocation
# Location of SSL client certificates.
# This parameter is used only in web monitoring.
#
# Mandatory: no
# Default:
# SSLCertLocation=${datadir}/zabbix/ssl/certs
SSLCertLocation=/usr/share/zabbix/ssl/certs
### Option: SSLKeyLocation
# Location of private keys for SSL client certificates.
# This parameter is used only in web monitoring.
#
# Mandatory: no
# Default:
# SSLKeyLocation=${datadir}/zabbix/ssl/keys
### Option: SSLCALocation
# Override the location of certificate authority (CA) files for SSL server certificate verification.
# If not set, system-wide directory will be used.
# This parameter is used only in web monitoring and SMTP authentication.
#
# Mandatory: no
# Default:
# SSLCALocation=
####### LOADABLE MODULES #######
### Option: LoadModulePath
# Full path to location of server modules.
# Default depends on compilation options.
#
# Mandatory: no
# Default:
# LoadModulePath=${libdir}/modules
### Option: LoadModule
# Module to load at server startup. Modules are used to extend functionality of the server.
# Format: LoadModule=<module.so>
# The modules must be located in directory specified by LoadModulePath.
# It is allowed to include multiple LoadModule parameters.
#
# Mandatory: no
# Default:
# LoadModule=
####### TLS-RELATED PARAMETERS #######
### Option: TLSCAFile
# Full pathname of a file containing the top-level CA(s) certificates for
# peer certificate verification.
#
# Mandatory: no
# Default:
# TLSCAFile=
### Option: TLSCRLFile
# Full pathname of a file containing revoked certificates.
#
# Mandatory: no
# Default:
# TLSCRLFile=
### Option: TLSCertFile
# Full pathname of a file containing the server certificate or certificate chain.
#
# Mandatory: no
# Default:
# TLSCertFile=
### Option: TLSKeyFile
# Full pathname of a file containing the server private key.
#
# Mandatory: no
# Default:
# TLSKeyFile=
Code:
[mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 # Settings user and group are ignored when systemd is used. # If you need to run mysqld under a different user or group, # customize your systemd unit file for mariadb according to the # instructions in http://fedoraproject.org/wiki/Systemd optimizer_switch = 'index_condition_pushdown=off' innodb_buffer_pool_size = 2048M # slow_query_log = 0 # slow_query_log_file = /var/log/mariadb/mysql-slow-query.log long_query_time = 2 log_slow_verbosity = query_plan log-queries-not-using-indexes query_cache_size = 8M join_buffer_size = 256K thread_cache_size = 4 innodb_buffer_pool_instances = 2 [mysqld_safe] log-error=/var/log/mariadb/mariadb.log pid-file=/var/run/mariadb/mariadb.pid # # include all files from the config directory # !includedir /etc/my.cnf.d
Code:
14526:20180411:142359.481 slow query: 181.935145 sec, "update alerts set status=1,retries=0,error='' where alertid=191724; update alerts set status=1,retries=0,error='' where alertid=191725; " 14489:20180411:142359.483 slow query: 181.567870 sec, "insert into history (itemid,clock,ns,value) values (36014,1523445655,388063809,0.000633),(36013,1523445655,388063809,0.000000),(43337,1523445655,388063809,0.000773),(43336,1523445655,388063809,0.000000),(26277,1523445657,380363733,2.473333),(45297,1523445657,412617128,71.728828),(23277,1523445657,412767651,70.011451),(35817,1523445657,414179682,0.966667),(40617,1523445657,596594866,99.965629),(44727,1523445657,621441353,0.000000),(44607,1523445657,622392419,53342.024605),(43197,1523445657,623326736,0.000000),(44667,1523445657,625740368,4259840.000000),(44697,1523445657,627191852,0.000000),(44757,1523445657,628208460,49.000000),(33657,1523445657,629903741,0.038333),(44637,1523445657,630858923,0.000000),(26217,1523445657,653896761,0.000000),(24957,1523445657,863948776,1.300000); " 14486:20180411:142359.487 slow query: 180.564373 sec, "insert into history (itemid,clock,ns,value) values (43339,1523445656,470308080,0.000000),(43340,1523445656,470308080,0.001500),(44638,1523445658,874634777,0.000000),(33658,1523445658,878049181,0.050000),(44728,1523445658,879257524,8.000000),(44758,1523445658,880275431,99.000000),(45298,1523445658,882071507,96.148915); " 14485:20180411:142359.488 slow query: 180.360853 sec, "insert into history (itemid,clock,ns,value) values (24958,1523445658,925387631,0.696667),(26218,1523445658,951294187,0.000000),(44668,1523445658,955232675,6356992.000000),(44608,1523445658,964601730,13.093079),(44698,1523445658,968449055,0.000000),(35818,1523445658,969616238,1.036667),(40618,1523445658,973015310,99.999198),(43019,1523445659,88469600,31.442038); " 14487:20180411:142359.488 slow query: 180.252316 sec, "insert into history (itemid,clock,ns,value) values (24658,1523445659,142069832,2.342500),(31319,1523445659,143241725,0.004167),(33659,1523445659,144307935,0.028333),(44669,1523445659,145369277,0.000000),(42838,1523445659,166717298,56.857760),(44699,1523445659,167859881,0.000000),(45299,1523445659,205389394,95.194582); " 14488:20180411:142359.489 slow query: 180.131957 sec, "insert into history (itemid,clock,ns,value) values (44729,1523445659,292456493,10.000000),(26219,1523445659,293495934,0.000000),(43199,1523445659,297883784,99.315640),(44639,1523445659,299030069,0.000000),(40559,1523445659,301626591,100.000000),(44609,1523445659,301893263,-0.000000); " 14508:20180411:142359.490 slow query: 171.908221 sec, "update item_discovery set lastcheck=1523445667 where itemid in (43475,43476,43477); " 14477:20180411:142359.492 slow query: 182.288215 sec, "delete from history_uint where itemid=45689 and clock<1515669507" 14511:20180411:142359.493 slow query: 169.600748 sec, "update item_discovery set lastcheck=1523445669 where itemid in (43631,43632,43633); " 14490:20180411:142359.494 slow query: 165.191797 sec, "update escalations set nextcheck=1523445734 where escalationid=36983; update escalations set nextcheck=1523445734 where escalationid=36984; update escalations set nextcheck=1523445734 where escalationid=36985; update escalations set nextcheck=1523445734 where escalationid=36986; update escalations set nextcheck=1523445734 where escalationid=36987; update escalations set nextcheck=1523445734 where escalationid=36988; update escalations set nextcheck=1523445734 where escalationid=36989; update escalations set nextcheck=1523445734 where escalationid=36990; update escalations set nextcheck=1523445734 where escalationid=36991; update escalations set nextcheck=1523445734 where escalationid=36992; update escalations set nextcheck=1523445734 where escalationid=36993; update escalations set nextcheck=1523445734 where escalationid=36994; update escalations set nextcheck=1523445734 where escalationid=39222; update escalations set nextcheck=1523445734 where escalationid=39223; update escalations set nextcheck=1523445734 where escalationid=39224; update escalations set nextcheck=1523445734 where escalationid=39225; update escalations set nextcheck=1523445734 where escalationid=40476; update escalations set nextcheck=1523445734 where escalationid=40481; update escalations set nextcheck=1523445734 where escalationid=41359; update escalations set nextcheck=1523445734 where escalationid=41360; update escalations set nextcheck=1523445734 where escalationid=41361; update escalations set nextcheck=1523445734 where escalationid=41362; update escalations set nextcheck=1523445734 where escalationid=41363; update escalations set nextcheck=1523445734 where escalationid=41364; " 14512:20180411:142359.509 slow query: 161.900584 sec, "update item_discovery set lastcheck=1523445677 where itemid between 37168 and 37216; " 14509:20180411:142359.517 slow query: 161.778991 sec, "update item_discovery set lastcheck=1523445677 where itemid between 37162 and 37167; " 14510:20180411:142359.517 slow query: 161.810258 sec, "update item_discovery set lastcheck=1523445677 where itemid between 37156 and 37161; " 14478:20180411:142359.526 slow query: 58.876375 sec, "insert into events (eventid,source,object,objectid,clock,ns,value) values (261112,0,0,18410,1523445780,637289712,1),(261113,0,0,18411,1523445780,637294124,1); " 14479:20180411:142359.538 slow query: 160.337490 sec, "update httptest set nextcheck=1523445739 where httptestid=12" 14481:20180411:142359.562 slow query: 152.768127 sec, "update httptest set nextcheck=1523445746 where httptestid=11" 14480:20180411:142359.562 slow query: 142.668989 sec, "update httptest set nextcheck=1523445756 where httptestid=7" 14488:20180411:142401.574 item "51" became not supported: Timeout while executing a shell script. 14488:20180411:142401.575 item "52" became not supported: Timeout while executing a shell script. 14488:20180411:142401.926 item "52" became supported 14477:20180411:142523.102 slow query: 80.095295 sec, "delete from trends_uint where itemid=26112 and clock<1491909507" 14508:20180411:142523.104 slow query: 79.984104 sec, "commit;" 14489:20180411:142523.115 slow query: 79.813846 sec, "commit;" 14509:20180411:142523.136 slow query: 79.978893 sec, "commit;" 14513:20180411:142523.247 slow query: 79.502104 sec, "commit;" 14488:20180411:142523.249 slow query: 78.764224 sec, "commit;" 14485:20180411:142523.249 slow query: 78.379879 sec, "commit;" 14486:20180411:142523.249 slow query: 78.194453 sec, "commit;" 14487:20180411:142523.255 slow query: 77.761551 sec, "commit;" 14512:20180411:142523.255 slow query: 76.953942 sec, "commit;" 14511:20180411:142523.255 slow query: 76.950040 sec, "commit;" 14510:20180411:142523.257 slow query: 79.998069 sec, "commit;" 14526:20180411:142523.257 slow query: 74.634559 sec, "commit;" 14479:20180411:142523.305 slow query: 62.357480 sec, "update httptest set nextcheck=1523446160 where httptestid=24" 14480:20180411:142523.306 slow query: 22.589367 sec, "update httptest set nextcheck=1523445960 where httptestid=10" 14490:20180411:142523.309 slow query: 22.743298 sec, "commit;" 14481:20180411:142523.309 slow query: 22.691670 sec, "update httptest set nextcheck=1523445960 where httptestid=5" 14489:20180411:142523.572 item "etzpcri_10.70:cert_niias_51" became supported 14477:20180411:142528.358 housekeeper [deleted 73596 hist/trends, 0 items/triggers, 0 events, 5 problems, 0 sessions, 0 alarms, 0 audit items in 420.743703 sec, idle for 1 hour(s)] 14526:20180411:143019.167 slow query: 174.293764 sec, "select a.alertid,a.mediatypeid,a.sendto,a.subject,a.message,a.status,a.retries,e.source,e.object,e.objectid from alerts a left join events e on a.eventid=e.eventid where alerttype=0 and a.status=3 order by a.alertid" 14490:20180411:143019.169 slow query: 174.708844 sec, "insert into alerts (alertid,actionid,eventid,userid,clock,mediatypeid,sendto,subject,message,status,error,esc_step,alerttype,acknowledgeid,p_eventid) values (191764,7,261127,10,1523446044,1,*******,'OK: Disk I/O is overloaded on zabbix.pkitrans.ru','Имя хоста - zabbix IP - 127.0.0.1 2018.04.11 14:09:21 Событие закончилось в 14:27:21 2018.04.11 Статус события: OK Название события: Disk I/O is overloaded on zabbix Важность: предупреждение URL события: Описание: Элемент CPU iowait time на узле zabbix.pkitrans.ru: получил значение 2.39 % Идентификатор события: 261085',3,'',1,0,null,261085); " 14486:20180411:144328.208 slow query: 166.132904 sec, "insert into history_uint (itemid,clock,ns,value) values (36167,1523446837,870718356,0),(33581,1523446841,359037670,263057829888),(45041,1523446841,360322590,7440354),(44981,1523446841,386449520,0),(29141,1523446841,388707481,667),(29201,1523446841,389429527,60501053440),(41381,1523446841,389787020,1192),(43121,1523446841,390258040,0),(36581,1523446841,391580254,397294592),(40541,1523446841,393284499,118),(33521,1523446841,397238395,39560),(40001,1523446841,397532036,16147012),(42581,1523446841,402384324,0),(37361,1523446841,408252915,0),(37353,1523446841,408252915,0),(37352,1523446841,408252915,0),(37360,1523446841,408252915,0),(26580,1523446841,416446201,34),(26616,1523446841,416446201,6183),(26593,1523446841,416446201,0),(26602,1523446841,416446201,0),(26609,1523446841,416446201,1012402),(26589,1523446841,416446201,0),(26590,1523446841,416446201,2754),(26600,1523446841,416446201,118),(26618,1523446841,416446201,4346),(26603,1523446841,416446201,8225),(26620,1523446841,416446201,177789),(26627,1523446841,416446201,20043),(26587,1523446841,416446201,2170550),(26596,1523446841,416446201,47815),(26613,1523446841,416446201,6183),(26588,1523446841,416446201,29548),(26608,1523446841,416446201,5427),(26614,1523446841,416446201,70314),(26586,1523446841,416446201,2135),(26581,1523446841,416446201,78),(26597,1523446841,416446201,172205),(26585,1523446841,416446201,1151438),(26623,1523446841,416446201,86530),(26583,1523446841,416446201,77295),(26607,1523446841,416446201,29899),(26584,1523446841,416446201,130),(26591,1523446841,416446201,14336),(26622,1523446841,416446201,1181566),(26615,1523446841,416446201,120324),(26601,1523446841,416446201,0),(26606,1523446841,416446201,196910),(26598,1523446841,416446201,305480),(26624,1523446841,416446201,5711),(26599,1523446841,416446201,433041),(26604,1523446841,416446201,4),(26595,1523446841,416446201,222702),(26610,1523446841,416446201,7757),(26582,1523446841,416446201,52879),(26594,1523446841,416446201,1802),(26611,1523446841,416446201,559152),(26626,1523446841,416446201,0),(26621,1523446841,416446201,53368),(26625,1523446841,416446201,0),(26605,1523446841,416446201,5632),(26617,1523446841,416446201,0),(42071,1523446841,428524705,0),(46901,1523446841,441281847,0),(42101,1523446841,473844102,55697408),(42161,1523446841,536901267,1344); " 14488:20180411:144328.211 slow query: 163.130309 sec, "insert into history_uint (itemid,clock,ns,value) values (47106,1523446842,212471233,1),(42112,1523446842,212471233,0),(34263,1523446842,212471233,1),(36156,1523446842,926693067,0),(47115,1523446843,599044,1),(31664,1523446844,827501780,2504),(29204,1523446844,832081877,239776505856),(43244,1523446844,833239995,4408),(33584,1523446844,835524399,36539207680),(40604,1523446844,846218506,2),(45104,1523446844,851274987,271474688),(46544,1523446844,857574472,1),(26144,1523446844,861430053,60),(43184,1523446844,864105590,1),(42584,1523446844,867085491,0),(44984,1523446844,872047725,80),(35924,1523446844,872751958,67),(41084,1523446844,874868465,3576),(33644,1523446844,875855570,1),(46724,1523446844,877722837,1),(42074,1523446844,897718154,0),(42134,1523446844,933726231,0),(33594,1523446844,961810782,1),(33597,1523446844,961810782,0),(33599,1523446844,961810782,2),(33596,1523446844,961810782,2),(33595,1523446844,961810782,603),(33589,1523446844,961810782,2579399),(42164,1523446844,985954701,38256); " 14486:20180411:145357.437 slow query: 175.838719 sec, "insert into history (itemid,clock,ns,value) values (45301,1523447461,250096171,97.533460),(26221,1523447461,255054491,98.608783),(44641,1523447461,264009109,-0.000000),(43201,1523447461,265074153,0.050067),(44701,1523447461,266097458,0.000000),(44611,1523447461,267083745,-0.000000),(44731,1523447461,291107660,21.000000),(40261,1523447461,292799656,40.737885),(31321,1523447461,293124474,0.000000),(44671,1523447461,293888633,0.000000),(42781,1523447461,470748269,2.593333); " 14488:20180411:145357.628 slow query: 177.038345 sec, "insert into history (itemid,clock,ns,value) values (44730,1523447460,189092327,0.000000),(44700,1523447460,190133914,0.000000),(31320,1523447460,215223300,0.000000),(24660,1523447460,224138323,1.694167),(25080,1523447460,225726149,69.354644),(44670,1523447460,228418970,0.000000),(43200,1523447460,230776325,0.000000),(44610,1523447460,239150684,0.000000),(45300,1523447460,242850477,96.615834),(44640,1523447460,244194457,0.000000); " 14486:20180411:145529.625 item "etzpcri_10.70:cert_niias_52" became supported 14478:20180411:145954.167 slow query: 53.630220 sec, "insert into problem (eventid,source,object,objectid,clock,ns) values (261170,0,0,17546,1523447940,523857569); " 14489:20180411:145954.168 slow query: 179.466324 sec, "insert into history (itemid,clock,ns,value) values (36004,1523447812,685355183,0.000000),(36005,1523447812,685355183,0.000797),(40554,1523447814,197489521,0.217428),(44604,1523447814,205789441,10411.000000),(44694,1523447814,206626316,520192.000000),(44664,1523447814,212125609,1989935104.000000),(40074,1523447814,213044883,99.663161),(44754,1523447814,225331071,0.000000),(44724,1523447814,228860944,1.000000),(44634,1523447814,254564243,24.000000),(45294,1523447814,258043821,70.006131),(23274,1523447814,258186912,99.986458); " 14479:20180411:145954.179 slow query: 49.621636 sec, "select h.hostid,h.host,h.name,t.httptestid,t.name,t.agent,t.authentication,t.http_user,t.http_password,t.http_proxy,t.retries,t.ssl_cert_file,t.ssl_key_file,t.ssl_key_password,t.verify_peer,t.verify_host,t.delay from httptest t,hosts h where t.hostid=h.hostid and t.nextcheck<=1523447944 and mod(t.httptestid,3)=0 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0)" 14486:20180411:145954.185 slow query: 178.478572 sec, "insert into history (itemid,clock,ns,value) values (36008,1523447813,683722754,0.000733),(36007,1523447813,683722754,0.000000),(44605,1523447815,281387688,0.000000),(44635,1523447815,314991921,76023.000000),(23275,1523447815,315626233,99.834775),(45295,1523447815,316307820,92.463971),(44665,1523447815,316622010,9437184.000000),(40555,1523447815,317446133,0.217464),(43195,1523447815,317890544,0.000000),(26275,1523447815,318552596,2.234444),(44695,1523447815,319638336,11693056.000000),(36355,1523447815,322115315,48.969043),(44725,1523447815,322987805,0.000000),(44755,1523447815,325036357,92.000000),(23635,1523447815,325219800,99.999332); " 14487:20180411:145954.185 slow query: 177.477025 sec, "insert into history (itemid,clock,ns,value) values (44696,1523447816,357067030,0.000000),(44756,1523447816,358026470,96.000000),(24956,1523447816,377215313,0.594444),(43196,1523447816,378343175,0.000000),(45296,1523447816,380141554,93.922230),(44666,1523447816,386120566,11534336.000000),(44726,1523447816,386985011,0.000000),(35816,1523447816,388312753,1.184444),(40616,1523447816,392054634,99.106506),(44606,1523447816,394293826,0.000000),(44636,1523447816,398966933,0.000000),(26276,1523447816,400185846,2.650000),(23276,1523447816,410341678,90.383339); " 14488:20180411:145954.185 slow query: 176.477021 sec, "insert into history (itemid,clock,ns,value) values (36010,1523447814,699375232,0.000000),(36011,1523447814,699375232,0.000890),(40674,1523447817,259638208,2620597.000000),(40675,1523447817,259638208,0.005224),(40671,1523447817,260158894,2620597.000000),(43046,1523447817,317521895,36.000000),(43047,1523447817,317521895,0.054655),(43043,1523447817,318214549,36.000000),(44667,1523447817,480008045,4259840.000000),(44607,1523447817,480842908,53378.032361),(40617,1523447817,480878705,99.965629),(44727,1523447817,481873892,0.000000),(45297,1523447817,505959350,96.662089),(44637,1523447817,506764871,0.000000),(24957,1523447817,537605732,0.550000),(33657,1523447817,538645928,0.037500),(23277,1523447817,539874747,71.739111),(26217,1523447817,541131647,0.640000),(44757,1523447817,542033975,49.000000),(26277,1523447817,543130607,2.930000),(35817,1523447817,544183238,1.300000),(44697,1523447817,544951048,0.000000),(43197,1523447817,546089120,0.000000); " 14485:20180411:145954.217 slow query: 175.495052 sec, "insert into history (itemid,clock,ns,value) values (36014,1523447815,699735670,0.000567),(43337,1523447815,699735670,0.002207),(43336,1523447815,699735670,0.000000),(36013,1523447815,699735670,0.000000),(26681,1523447818,336175062,69240.000000),(26682,1523447818,336175062,0.160326),(43064,1523447818,398011178,26.000000),(43065,1523447818,398011178,0.075630),(43061,1523447818,398519109,26.000000),(44850,1523447818,427775914,0.027048),(44849,1523447818,427775914,0.000000),(44846,1523447818,428249414,0.000000),(26684,1523447818,541126663,59370.000000),(26685,1523447818,541126663,0.204480),(45134,1523447818,596697104,6.000000),(45135,1523447818,596697104,0.165956),(45131,1523447818,597254542,6.000000),(24958,1523447818,685559352,0.600000),(44698,1523447818,686627369,0.000000),(35818,1523447818,688123341,1.186667),(26688,1523447818,696426503,0.154808),(26687,1523447818,696426503,103799.000000),(42838,1523447818,696532948,56.855288),(26678,1523447818,697362141,77469.000000),(44668,1523447818,704384628,6356992.000000),(42668,1523447818,706091916,0.000000),(42669,1523447818,706091916,0.006402),(42672,1523447818,707950396,0.001415),(42671,1523447818,707950396,941342.000000),(42665,1523447818,708322745,470671.000000),(44728,1523447818,714863538,8.000000),(26218,1523447818,716949504,0.230000),(44608,1523447818,717868208,11.792907),(33658,1523447818,719181347,0.004167),(45298,1523447818,720099775,95.841239); " 14480:20180411:145954.234 slow query: 54.250405 sec, "update httptest set nextcheck=1523447999 where httptestid=4" 14481:20180411:145954.234 slow query: 54.102619 sec, "update httptest set nextcheck=1523448000 where httptestid=5"
Code:
Please enter your MySQL administrative password: >> MySQLTuner 1.6.0 - Major Hayden <[email protected]> >> Bug reports, feature requests, and downloads at http://mysqltuner.com/ >> Run with '--help' for additional options and output filtering [--] Skipped version check for MySQLTuner script ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the SUPER,REPLICATION CLIENT privilege(s) for this operation ERROR 1227 (42000) at line 1: Access denied; you need (at least one of) the REPLICATION SLAVE privilege(s) for this operation [OK] Currently running supported MySQL version 5.5.56-MariaDB [OK] Operating on 64-bit architecture -------- Storage Engine Statistics ------------------------------------------- [--] Status: +ARCHIVE +Aria +BLACKHOLE +CSV +FEDERATED +InnoDB +MRG_MYISAM [--] Data in InnoDB tables: 22G (Tables: 140) [!!] Total fragmented tables: 140 -------- Security Recommendations ------------------------------------------- ERROR 1142 (42000) at line 1: SELECT command denied to user '****'@'localhost' for table 'user' [OK] There is no anonymous account in all database users ERROR 1142 (42000) at line 1: SELECT command denied to user '****'@'localhost' for table 'user' [OK] All database users have passwords assigned ERROR 1142 (42000) at line 1: SELECT command denied to user '****'@'localhost' for table 'user' ERROR 1142 (42000) at line 1: SELECT command denied to user '****'@'localhost' for table 'user' [!!] There is not basic password file list ! -------- Performance Metrics ------------------------------------------------- [--] Up for: 2d 5h 38m 8s (11M q [62.069 qps], 184K conn, TX: 10B, RX: 2B) [--] Reads / Writes: 81% / 19% [--] Binary logging is disabled [--] Total buffers: 2.3G global + 2.9M per thread (151 max threads) [OK] Maximum reached memory usage: 2.5G (67.66% of installed RAM) [OK] Maximum possible memory usage: 2.7G (73.41% of installed RAM) [OK] Slow queries: 2% (264K/11M) [OK] Highest usage of available connections: 50% (76/151) [OK] Aborted connections: 0.00% (3/184887) [OK] Query cache efficiency: 41.9% (4M cached / 11M selects) [!!] Query cache prunes per day: 59476 [OK] Sorts requiring temporary tables: 0% (0 temp sorts / 3M sorts) [!!] Joins performed without indexes: 20526 [OK] Temporary tables created on disk: 15% (86K on disk / 572K total) [OK] Thread cache hit rate: 99% (1K created / 184K connections) [OK] Table cache hit rate: 107% (356 open / 330 opened) [OK] Open file limit used: 2% (21/1K) [OK] Table locks acquired immediately: 100% (5M immediate / 5M locks) -------- MyISAM Metrics ----------------------------------------------------- [!!] Key buffer used: 18.2% (24M used / 134M cache) [!!] Cannot calculate MyISAM index size - re-run script as root user -------- InnoDB Metrics ----------------------------------------------------- [--] InnoDB is enabled. [!!] InnoDB buffer pool / data size: 2.0G/22.7G [OK] InnoDB buffer pool instances: 2 [OK] InnoDB Used buffer: 100.00% (131070 used/ 131070 total) [OK] InnoDB Read buffer efficiency: 99.98% (1089239369 hits/ 1089419359 total) [!!] InnoDB Write buffer efficiency: 0.00% (0 hits/ 1 total) [OK] InnoDB log waits: 0.00% (0 waits / 1110303 writes) -------- AriaDB Metrics ----------------------------------------------------- [--] AriaDB is disabled. -------- Replication Metrics ------------------------------------------------- [--] No replication slave(s) for this server. [--] This is a standalone server.. -------- Recommendations ----------------------------------------------------- General recommendations: Run OPTIMIZE TABLE to defragment tables for better performance Enable the slow query log to troubleshoot bad queries Adjust your join queries to always utilize indexes Variables to adjust: query_cache_size (> 8M) join_buffer_size (> 256.0K, or always use indexes with joins) innodb_buffer_pool_size (>= 22G) if possible.
Прошу помощи с настройкой!
Comment