-
Problem report
-
Resolution: Commercial support required
-
Trivial
-
None
-
6.2.6
50-server.cnf
[server][mysqld][embedded][mariadb][mariadb-10.6]# # * Basic Settings ##user = mysql pid-file = /run/mysqld/mysqld.pid basedir = /usr #datadir = /var/lib/mysql #tmpdir = /tmp# Broken reverse DNS slows down connections considerably and name resolve is # safe to skip if there are no "host by domain name" access grants #skip-name-resolveskip-name-resolve=1# Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 127.0.0.1# # * Fine Tuning ##key_buffer_size = 128M key_buffer_size = 256M #max_allowed_packet = 1G max_allowed_packet = 1G #thread_stack = 192K #thread_cache_size = 8 thread_cache_size = 32 # This replaces the startup script and checks MyISAM tables if needed # the first time they are touched #myisam_recover_options = BACKUP #max_connections = 100 max_connections = 400 #table_cache = 64join_buffer_size = 64M table_definition_cache = 600 performance_schema=ON performance-schema-instrument='stage/%=ON' performance-schema-consumer-events-stages-current=ON performance-schema-consumer-events-stages-history=ON performance-schema-consumer-events-stages-history-long=ON # # * Logging and Replication ## Both location gets rotated by the cronjob. # Be aware that this log type is a performance killer. # Recommend only changing this at runtime for short testing periods if needed! #general_log_file = /var/log/mysql/mysql.log #general_log = 1# When running under systemd, error logging goes via stdout/stderr to journald # and when running legacy init error logging goes to syslog due to # /etc/mysql/conf.d/mariadb.conf.d/50-mysqld_safe.cnf # Enable this if you want to have error logging into a separate file #log_error = /var/log/mysql/error.loglog_error = /var/log/mysql/error.log# Enable the slow query log to see queries with especially long duration #slow_query_log_file = /var/log/mysql/mariadb-slow.log slow_query_log_file = /var/log/mysql/slow.log #long_query_time = 10 long_query_time = 5 #log_slow_verbosity = query_plan,explain log_slow_verbosity = query_plan,explain #log-queries-not-using-indexes log-queries-not-using-indexes #min_examined_row_limit = 1000# The following can be used as easy to replay backup logs or for replication. # note: if you are setting up a replication slave, see README.Debian about # other settings you may need to change. #server-id = 1 log_bin = /var/log/mysql/mysql-bin.log expire_logs_days = 10 #max_binlog_size = 100M# # * SSL/TLS ## For documentation, please read # https://mariadb.com/kb/en/securing-connections-for-client-and-server/ #ssl-ca = /etc/mysql/cacert.pem #ssl-cert = /etc/mysql/server-cert.pem #ssl-key = /etc/mysql/server-key.pem #require-secure-transport = on# # * Character sets ## MySQL/MariaDB default is Latin1, but in Debian we rather default to the full # utf8 4-byte character set. See also client.cnf character-set-server = utf8mb4 collation-server = utf8mb4_general_ci# # * InnoDB ## InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/. # Read the manual for more InnoDB related options. There are many! # Most important is to give InnoDB 80 % of the system RAM for buffer use: # https://mariadb.com/kb/en/innodb-system-variables/#innodb_buffer_pool_size #innodb_buffer_pool_size = 8Ginnodb_buffer_pool_size = 16Ginnodb_log_file_size = 4Ginnodb_strict_mode=OFF # this is only for embedded server
zabbix_server.conf
LogFile=/var/log/zabbix/zabbix_server.log LogFileSize=0 PidFile=/run/zabbix/zabbix_server.pid SocketDir=/run/zabbix DBSocket=/run/mysqld/mysqld.sock StartPollers=10 StartPollersUnreachable=4 StartDiscoverers=4 SNMPTrapperFile=/var/log/snmptrap/snmptrap.log StartSNMPTrapper=1 CacheSize=512 MHistoryCacheSize=128M HistoryIndexCacheSize=32M TrendCacheSize=32M TrendFunctionCacheSize=32M ValueCacheSize=512M Timeout=4 LogSlowQueries=3000 StartLLDProcessors=8
in zabbix_server.log
296342:20221208:131502.746 slow query: 48.771166 sec, "select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid,g.discover from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=45046" 296341:20221208:131510.497 slow query: 45.466623 sec, "select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid,g.discover from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=39807" 296344:20221208:131603.898 slow query: 49.375416 sec, "select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid,g.discover from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=45047" 296343:20221208:131614.728 slow query: 49.549676 sec, "select distinct g.graphid,g.name,g.width,g.height,g.yaxismin,g.yaxismax,g.show_work_period,g.show_triggers,g.graphtype,g.show_legend,g.show_3d,g.percent_left,g.percent_right,g.ymin_type,g.ymin_itemid,g.ymax_type,g.ymax_itemid,g.discover from graphs g,graphs_items gi,items i,item_discovery id where g.graphid=gi.graphid and gi.itemid=i.itemid and i.itemid=id.itemid and id.parent_itemid=39807"