Ad Widget

**tim.mooney** · 24-05-2022, 19:41

Originally posted by rrmike

I was running Zabbix 4.4 with the goal of upgrading to Zabbix 6LTS. I'm on Ubuntu 18.04 and was running MySQL 5.7.

I started by upgrading Zabbix 4.4 to Zabbix 5.0. Then upgraded MySQL 5.7 to MySQL 8.

Good approach. You may already be doing this too, but you don't mention it, so I will: make sure you're running the latest version in the 5.0.x series. It's probably 5.0.22 or perhaps 5.0.23 right now.

Originally posted by rrmike

After upgrading to MySQL 8, Zabbix housekeeper processes is more then 75% busy. It runs for about 2 hours, then takes about 30min off and runs again for 2 hours. It's been doing this for for the last 7 days. Is this behavior normal? I have a small install, only 226 hosts. Do you recommend I do anything or wait for this process to finish it's work? I have not yet upgraded to Zabbix 6.

I'm no expert on the housekeeper, but I wouldn't expect it to be running for that long. At my site, even when the housekeeper was way behind (long story...), its scheduled run only took 2-3 minutes to complete. In your case, since it wasn't running for hours under Zabbix 5.0 + MySQL 5.7, it's reasonable to assume that there's some problem that was introduced by the change to MySQL 8.0. Either some kind of a tuning issue at the database level, or possibly a bad interaction between Zabbix's SQL commands and the optimizer with MySQL 8.0.x.

I assume you're running the database on the same host as Zabbix server and the Zabbix web front-end. Have you done any MySQL database tuning for the install, either under Zabbix 4.4 (or earlier) or since you've moved to Zabbix 5.0.x? If not, I would look at your innodb_buffer_pool size and try determine if it would benefit from any changes.

Is MySQL 8.0 using the same /etc/my.cnf and /etc/my.cnf.d/*.cnf files that MySQL 5.7 was, or did the upgrade replace the older config files with stock config files for MySQL 8.0? If you're still using the same files that MySQL 5.7 had, have you verified that there aren't any deprecated or outdated settings that should just be removed (or updated)?

**rrmike** · 24-05-2022, 21:58

You may already be doing this too, but you don't mention it, so I will: make sure you're running the latest version in the 5.0.x series. It's probably 5.0.22 or perhaps 5.0.23 right now.

Yes, I'm on Zabbix 5.0.23.

I assume you're running the database on the same host as Zabbix server and the Zabbix web front-end.

Correct.

Is MySQL 8.0 using the same /etc/my.cnf and /etc/my.cnf.d/*.cnf files that MySQL 5.7 was, or did the upgrade replace the older config files with stock config files for MySQL 8.0? If you're still using the same files that MySQL 5.7 had, have you verified that there aren't any deprecated or outdated settings that should just be removed (or updated)?

I think you nailed the issue here. Though, I thought I looked through those files but apparently I did not!

There are major differences in this file: /etc/mysql/mysql.conf.d/mysqld.cnf
MySLQ 5.7:

Code:

/etc/mysql/mysql.conf.d/mysqld.cnf

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * Basic Settings
#
user            = mysql
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
port            = 3306
basedir         = /usr
datadir         = /var/lib/mysql
tmpdir          = /tmp
lc-messages-dir = /usr/share/mysql
skip-external-locking
#
# 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         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP
#max_connections        = 100
#table_open_cache       = 64
#thread_concurrency     = 10
#
# * Query Cache Configuration
#
query_cache_limit       = 1M
query_cache_size        = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
#slow_query_log         = 1
#slow_query_log_file    = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# 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
#binlog_do_db           = include_database_name
#binlog_ignore_db       = include_database_name
#
# * 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!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

MySQL 8:

Code:

[mysqld]
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
datadir = /var/lib/mysql
log-error = /var/log/mysql/error.log

Do you think it's safe to copy everything from the MySQL 5.7 config file to the MySQL 8 config file?

Have you done any MySQL database tuning for the install, either under Zabbix 4.4 (or earlier) or since you've moved to Zabbix 5.0.x? If not, I would look at your innodb_buffer_pool size and try determine if it would benefit from any changes.

I'm not a MySQL person so I've not made any changes.

innodb_buffer_pool size is not listed in the config file. Where would you put it and what value do you recommend. I bumped up the resources on the VM to help the Zabbix churn through this. Currently it's got 24Gb RAM.

**tim.mooney** · 25-05-2022, 02:06

Responding in reverse order...

innodb_buffer_pool_size is the single most valuable tunable for a MySQL/MariaDB/Percona install that is primarily using InnoDB (or its other names) as a backend. In most cases, the larger you can make the InnoDB buffer pool, the better. On a dedicated database server (one where you're not also running zabbix_server and a web front-end) you would typically set InnoDB's buffer pool to 70-80% of system RAM (possibly higher for big RAM boxes), though for some versions of MariaDB and probably MySQL, you also divide the pool into chunks.

For a box that is not a dedicated database server, such as an all-in-one Zabbix server, I would probably start by making the innodb_buffer_pool_size 30-40% of system RAM, with an eye to increasing that if you don't run into issues with other parts of the stack not having enough RAM. You can probably make it as large as 60% of system RAM and not impact zabbix_server or php-fpm/httpd, but I would start with the 30% or 40% setting first.

As far as where to set it, it should be somewhere in the section after the '[mysqld]' section marker (the config file format is very similar to INI file format, if you are familiar with that).

There's some info in the Zabbix blog about tuning, but the article is a little light on any specifics:

https://blog.zabbix.com/mysql-perfor...-zabbix/13899/

Regarding the 5.7 settings vs. the 8.0 settings...

A lot of the 5.7 settings are commented out, so they're not being applied, but there are several settings from 5.7 that are being applied. Some of them could definitely impact query performance (query_cache_limit, query_cache_size, the thread settings, etc). It's difficult for me to give concrete suggestions, but here's what I would recommend:

1) the MySQL documentation site is pretty good. You should be able to look up every one of the uncommented settings (e.g. 'query_cache_size') and have it give a brief overview of what it does and also how it has changed between versions. If any of them say they're deprecated at MySQL 8.0, then comment them out (make a note in the comment, "deprecated at MySQL 8.0" or similar).

2) if the MySQL documentation tells you how the setting has changed between versions, then that may give you a hint about whether you should keep the setting for MySQL 8.0 or comment it out. For example, if the 'query_cache_size' under MySQL 5.5 was only 4M, and it increased to 8M at 5.7, then you know that someone (your predecessor?) thought that 8M wasn't enough for your Zabbix install, so he or she increased it to 16M. If MySQL 8.0 defaults to 16M or higher for that setting OR if it recommends not setting it (perhaps suggesting some other setting instead, that influences multiple settings), then you want to follow that recommendation.

3) if you can't figure out from the documentation what the MySQL 8.0 defaults are for some of these values, then what I would do is this:

find a time when you can shut down your Zabbix server for a while, so you can do some MySQL investigation
once zabbix_server is shut down, also shut down your MySQL 8.0 daemon temporarily
move the 'mysqld.conf' file out of /etc/mysqld.conf.d/ (or rename it so that it doesn't end with .conf). The goal here is to get the config file out of place, so that it isn't read by the server on startup
once the file is out of the way, restart the mysqld server. Assuming it starts up OK, it's now running with default settings for nearly everything.
connect up to the mysql server and do things like
Code:
```
SHOW GLOBAL VARIABLES LIKE '%query%';
```
etc, and then make a note of what the default value is for MySQL 8.0 for each of the settings you care about. You can use any of the variables from the moved config file with the "SHOW GLOBAL VARIABLES" statement to get what its default value is.
once you have that info, you can make decisions about whether your 5.7 settings are artificially limiting some setting that has now been increased to a larger default value with MySQL 8.0 (or has otherwise changed). That may guide what settings you comment out, adjust, etc.
once you've figured out what the defaults are, undo your changes by stopping mysqld, moving the config file back into place, restarting mysqld, and then restarting zabbix_server.

I haven't looked at any of the MySQL 8.0 defaults (my site is using MariaDB, not MySQL), but I'm at least a little suspicious that one or more of the settings that were in place for your 5.7 install might be contributing to the slow housekeeping performance, but without spending more time looking at it, I'm not sure. I think any cases where you find your 5.7 setting is smaller than what 8.0 defaults too, that definitely suggests further investigation is warranted.

Honestly, though, if the innodb_buffer_pool_size is not being set, just setting that (and potentially innodb_buffer_pool_instances, if that's a thing for MySQL 8.0) might be a big win.

One more question: when your housekeeper runs for 2.5 hours and then completes, how many records is it typically removing during a run? This info is probably being logged to your zabbix_server.log file every time the housekeeper runs. I'm curious how much cleanup it's actually doing in the 2.5 hours you've indicated it takes to run.

**rrmike** · 25-05-2022, 17:21

Thanks for all this information. It's going to take some time to master it all!

One more question: when your housekeeper runs for 2.5 hours and then completes, how many records is it typically removing during a run? This info is probably being logged to your zabbix_server.log file every time the housekeeper runs. I'm curious how much cleanup it's actually doing in the 2.5 hours you've indicated it takes to run.

Is this what you are looking for:

Code:

sudo cat /var/log/zabbix/zabbix_server.log | grep housekeeper
3219:20220523:072350.047 housekeeper [deleted 6021402 hist/trends, 45000 items/triggers, 8 events, 8 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 16454.559009 sec, idle for 1 hour(s)]
3219:20220523:082350.546 executing housekeeper
3219:20220523:113601.179 housekeeper [deleted 6022323 hist/trends, 45000 items/triggers, 4 events, 20 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11530.631564 sec, idle for 1 hour(s)]
3219:20220523:123601.663 executing housekeeper
3219:20220523:154941.418 housekeeper [deleted 6021837 hist/trends, 45000 items/triggers, 4 events, 6 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11619.753409 sec, idle for 1 hour(s)]
3219:20220523:164941.928 executing housekeeper
3219:20220523:200501.533 housekeeper [deleted 6030450 hist/trends, 45000 items/triggers, 14 events, 7 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11719.603501 sec, idle for 1 hour(s)]
3219:20220523:210502.020 executing housekeeper
3219:20220524:002009.273 housekeeper [deleted 6053992 hist/trends, 45000 items/triggers, 5 events, 9 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11707.250980 sec, idle for 1 hour(s)]
3219:20220524:012009.739 executing housekeeper
3219:20220524:043352.251 housekeeper [deleted 6088391 hist/trends, 45000 items/triggers, 6 events, 18 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11622.510095 sec, idle for 1 hour(s)]
3219:20220524:053352.737 executing housekeeper
3219:20220524:093132.895 housekeeper [deleted 5929487 hist/trends, 45000 items/triggers, 0 events, 9 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 14260.156059 sec, idle for 1 hour(s)]
3219:20220524:103133.398 executing housekeeper
3219:20220524:134450.960 housekeeper [deleted 6066631 hist/trends, 45000 items/triggers, 4 events, 15 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11597.559680 sec, idle for 1 hour(s)]
3219:20220524:144451.439 executing housekeeper
3219:20220524:175708.444 housekeeper [deleted 6068470 hist/trends, 45000 items/triggers, 9 events, 7 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11537.003650 sec, idle for 1 hour(s)]
3219:20220524:185708.921 executing housekeeper
3219:20220524:221456.657 housekeeper [deleted 6068614 hist/trends, 45000 items/triggers, 3 events, 6 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11867.734889 sec, idle for 1 hour(s)]
3219:20220524:231457.136 executing housekeeper
3219:20220525:023149.439 housekeeper [deleted 6075824 hist/trends, 45000 items/triggers, 4 events, 6 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11812.300147 sec, idle for 1 hour(s)]
3219:20220525:033149.919 executing housekeeper
3219:20220525:064856.658 housekeeper [deleted 6083925 hist/trends, 45000 items/triggers, 3 events, 5 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11826.737873 sec, idle for 1 hour(s)]
3219:20220525:074857.141 executing housekeeper
3219:20220525:110003.268 housekeeper [deleted 6069288 hist/trends, 45000 items/triggers, 7 events, 15 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 11466.124997 sec, idle for 1 hour(s)]
3219:20220525:120003.762 executing housekeeper

I compared the 5.7 settings to the 8.0 documentation. 8.0 default settings are all greater or have been depreciated. So I don't think there is any point in bringing any of that over (remember my 8.0 config file was basically blank). I added innodb_buffer_pool_size = 1000M to the config. I suspect that's too small and should be 10000M. If I remember housekeeper waits an hour after MySQL is restarted to run. We'll see what happens.

Attached Files

**rrmike** · 25-05-2022, 21:15

I made three changes to the config file (and Zabbix as a whole runs much faster).

innodb_buffer_pool_size = 1000M

Code:

3219:20220525:154226.861 executing housekeeper
3219:20220525:161059.743 housekeeper [deleted 6079950 hist/trends, 45000 items/triggers, 6 events, 10 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 1712.881210 sec, idle for 1 hour(s)]

innodb_buffer_pool_size = 5000M

Code:

3219:20220525:171100.252 executing housekeeper
3219:20220525:173326.945 housekeeper [deleted 6071417 hist/trends, 45000 items/triggers, 6 events, 2 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 1346.691427 sec, idle for 1 hour(s)]

innodb_buffer_pool_size = 5000M
innodb_log_file_size = 512M

Code:

3219:20220525:183327.452 executing housekeeper
3219:20220525:185211.933 housekeeper [deleted 6032384 hist/trends, 45000 items/triggers, 2 events, 2 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 1124.479174 sec, idle for 1 hour(s)]

Two questions.

We've made a huge difference! Thanks. Does this look good or do I need to keep tuning?
Before the MySQL upgrade, CPU usage was pretty consistent at about 10%. Now (even after the above tuning) when the housekeeper does it's job CPU utilization goes up to about 25%. Is that expected or is there another parameter that needs tweaking?

**tim.mooney** · 25-05-2022, 22:15

Originally posted by rrmike

I made three changes to the config file (and Zabbix as a whole runs much faster).
Two questions.

We've made a huge difference! Thanks. Does this look good or do I need to keep tuning?
Before the MySQL upgrade, CPU usage was pretty consistent at about 10%. Now (even after the above tuning) when the housekeeper does it's job CPU utilization goes up to about 25%. Is that expected or is there another parameter that needs tweaking?

I'm glad the changes have made a big difference. I suspected they might, but one never knows. :-)

The answer to the "should I do more tuning" is always dependent on a lot of factors. Are you satisfied with the current performance? Do you have other high-priority projects that have been delayed while you're working on this? Is it worth your time to spend more time on tuning?

Tuning is kind of an art. In a software system with many components, you generally aim to eliminate the worst bottleneck that has the biggest impact on performance, but as soon as you've eliminated the "worst bottleneck", the next worst bottleneck is "promoted". Tuning a complex system definitely has diminishing returns, meaning it may be possible to continue to eliminate smaller and smaller bottlenecks and continue to improve performance, but it becomes more difficult, more time consuming, and more costly to do so. At some point, for a system that is not "life and death", you stop and say "good enough". In most cases, it's up to you and your management to decide if you're at the "good enough" point.

I do think there probably is some additional performance you could gain, but I don't think it's going to be as significant as the improvements you've already seen. I suspect you could go a little larger with the buffer pool and get some additional performance, but that's just a guess. Tuning generally involves a "make a change, then measure the effects of the change", in a cycle. It might be time to do more measuring. The first thing I would check is how effective your new, larger InnoDB buffer pool is. Doing some web searches to find good queries for determining your buffer pool hit rate, or utilization, would probably be worthwhile. There was a query in that MySQL tuning post on the Zabbix blog that I linked earlier; that might be a place to start. You might be able to find some good info on the Percona blog; those guys & gals are a wealth of knowledge on MySQL/MariaDB/Percona DB.

As far as the CPU utilization, that I'm not sure about. That may just be "normal" for MySQL 8.0, or it could be something pathological with the query optimizer that's triggered during the queries that run during housekeeping. It could also be something that could be improved with tuning, but I suspect it's going to be more challenging to get to the bottom of that mystery than it was to make the first simple changes to improve the performance you have so far. You may need to do some general web searches about increased CPU utilization with MySQL 8.0 to see if that's something other applications are causing too.

One thing I would probably look into, if I were you, is also setting innodb_buffer_pool_instances. I mentioned that setting in my earlier post. I wasn't certain if MySQL 8.0 used it (or had deprecated it), but it looks like it's still a thing for MySQL 8.0. See the documentation here:

https://dev.mysql.com/doc/refman/8.0...fer-pools.html

Basically, rather than having one big buffer pool, at least at 8.0 it's more efficient to divide the pool into multiple smaller pools, to reduce certain types of contention. Whether or not you further increase your overall buffer pool size, dividing it into multiple equal-sized instances may have some performance gains. If you stay with '5000M' for your overall pool size, I would probably try innodb_buffer_pool_instances=5 to get 5x1000M instances. Just be advised that the instances idea my be deprecated in some later version of MySQL, so if you some day upgrade to "MySQL 10.0" or whatever, you may need to drop the instances (but keep the overall pool size). That's already happened for MariaDB, no idea if or when it might happen for MySQL. They've diverged enough now that it might not ever happen, but I would bet it eventually will.

**rrmike** · 26-05-2022, 22:29

The answer to the "should I do more tuning" is always dependent on a lot of factors. Are you satisfied with the current performance? Do you have other high-priority projects that have been delayed while you're working on this? Is it worth your time to spend more time on tuning?

lol. Yeah, I'm done! But, I'm sure another project will require me to learn more MySQL and I'll come back and do some further tuning.

One thing I would probably look into, if I were you, is also setting innodb_buffer_pool_instances.

It looks like the default is 8. So I'll leave it the way it is.

The housekeeper process get better overnight and today.

Code:

3219:20220526:164605.268 executing housekeeper
3219:20220526:165911.510 housekeeper [deleted 1851968 hist/trends, 45000 items/triggers, 1 events, 1 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 786.240851 sec, idle for 1 hour(s)]
3219:20220526:175912.006 executing housekeeper
3219:20220526:181233.304 housekeeper [deleted 1860089 hist/trends, 45000 items/triggers, 2 events, 3 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 801.295714 sec, idle for 1 hour(s)]
3219:20220526:191233.827 executing housekeeper
3219:20220526:192600.395 housekeeper [deleted 1870675 hist/trends, 55000 items/triggers, 8 events, 3 problems, 0 sessions, 0 alarms, 0 audit, 0 records in 806.565347 sec, idle for 1 hour(s)]

Thanks for all your advice. I really appreciate it.

**LenR** · 10-06-2022, 17:26

Maybe a dead thread, but I use the mysqltuner script and phpMyAdmin to get at least some other opinion on mysql tuning.

**rrmike** · 14-06-2022, 16:55

Thanks. I don't have phpMyAdmin installed but I have run mysqltuner. Maybe I'll give that a try at some point.

Ad Widget

MySQL 5.7 upgrade to MySQL 8, Housekeeper more than 75% busy

MySQL 5.7 upgrade to MySQL 8, Housekeeper more than 75% busy

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment