Ad Widget

**tim.mooney** · 28-06-2022, 21:43

Originally posted by Roland_Junich

the company i started working for is running a zabbix installation that looks like it could need some love. The guy who originally set up the system has left the company without documenting what he did and why.
It is a docker installation using mysql:8 running on a VM with 8 CPUs, 8 GB RAM and approx. 1T HDD. My colleagues managed to regularly update the zabbix containers, so at the moment we are running zabbix 5.4.8

If that install has performance issues at any time in the future, the first thing to look at is more RAM, especially for MySQL. In that case, you would want to tune the setting for the innodb_cache_size

Originally posted by Roland_Junich

The MySQL data directory has a size of approx. 700 GB. The Zabbix DB itself has approx. 600GB. The 100GB difference are 30 days of binlogs. In the last few days, the zabbix db grew about 2GB per day.
The biggest table is history_uint with ~400 GB, followed by history with ~ 150 GB.

How often do you back up the mysql database? If you're doing nightly backups of the database using "mysql_dump" or some other hot backup method (perhaps the Percona hot backup tool), then you don't need to keep 30 days of binary logs. You can set mysql's 'expire_log_days' to tell it that it can expire the binary logs more frequently, but just make sure you're getting good backups of the database before you expire them.

Regarding your history_uint and history table sizes, that's not surprising considering what you say about "data storage period", below:

Originally posted by Roland_Junich

We have ~700 hosts and 86850 items. Housekeeping is enabled, and the data storage period is set to 365 days.
From what i have read on the forums so far, i know that the housekeeping is not working as expected.

Are you sure that housekeeping is not working? Because with a data storage period of 365 days, your database will not reach a steady state until a year after you add new items. In other words, if your site has added (or automatic discovery has discovered) new hosts in the last year, then because of your data storage period your database will grow for the next year.

If you read the documentation about "history" vs. "trends" storage, the recommendation is to keep "history" for a short period, but keep trends for whatever period you need.

https://www.zabbix.com/documentation...ory_and_trends

Originally posted by Roland_Junich

Is it possible to manually delete records from the history tables without shutting down the zabbix-server, or will this breaks something?
Is it possible to disable the binlogs?

Are there any tweakable housekeeper settings that might prevent the immense growth per day, in order to by us some time to clean up even more?

It is possible to manually delete records from the history tables, even with Zabbix live. That won't break anything, but it's also not going to do much to solve the overall problem.

You can also manually trigger a run of the housekeeper, see the man page for "zabbix_server" and the "--runtime-control" option. Be advised that the housekeeper has an internal algorithm for how much data it will clean up in one run, so if you do adjust history storage period and trend storage period, it may take many (MANY!) cycles of the housekeeper to get caught up and get to a steady state.

It's not advisable to disable the binlogs, but as long as you're backing up your database regularly, you only need to keep the binlogs that have not been included in a backup procedure. So, if you do weekly backups (not what I would recommend, but just as an example) of the database, then you only need to keep something like 8 days of binlogs. You can see 'expire_logs_days' to have MySQL automatically remove older stuff. Again, just make sure you're getting a good backup.

The tweakable settings for the housekeeper are mainly global overrides for history storage period and trend storage period.

Based on what you've described about your environment my advice is

1. Read up on history vs trends and then work with your management and other stakeholders to reduce your history storage period for many items. You can increase your trend storage period for the same items, if you can get by with less-granular data for stuff that's say 1 to 2 years old. You do not need to use the same history storage for every item, so spend some time thinking about how long you need very granular data for certain items. It could be that you keep history for some stuff for 90 days, but other stuff you only keep history for 30 days (but you keep trends for much longer).

2. Collect data as often/fast as you need to, but when you're examining your items, be on the lookout for stuff that's getting collected every 30 or 60 seconds, that doesn't need to be. Perhaps you have items in your environment that only really need to be collected every 5 minutes, or every 15 minutes, or maybe even just once an hour. Adjust the ones that make sense, but make sure you understand any triggers for the items before you adjust the item collection frequency. If the trigger uses multiple consecutive items to decide if a threshold has been crossed, and you change the collection rate from every 60 seconds to every 300 seconds, then you've changed how long it takes the trigger to decide if there's a problem.

3. Once you start reducing history storage period for items, even after just the first few widely-used items are adjusted, your housekeeper is going to start having more work to do. This is when you can start thinking about manually running the housekeeper on a more frequent basis or deleting old history from the database.

4. You probably know this, but many people do not, so it's worth discussing: most databases (including MySQL) do not shrink the size of their on-disk files when you delete data from tables. You might delete 10 million rows from your history_uint table, but the on-disk size of that table is going to stay the same. Deleting data from a table just creates "free slots" that MySQL will re-use when it needs to insert new values in the future. If you've deleted massive amounts of data from an over-sized table and you want to shrink the on-disk footprint, you need to take special action. One thing you can look into is MySQL's documentation for "OPTIMIZE TABLE" ( https://dev.mysql.com/doc/refman/8.0...ize-table.html ). That actually will shrink the table, but it does it by creating a new copy of the table that's smaller and then switching it into place of the old table, so while it's operating it requires even more disk space. Make sure you have enough free space on disk before you attempt this.

Backing up a table, dropping it, and then reloading the table from backup will also physically shrink it, since you've effectively created a new table that now only occupies as much disk space as it currently needs. This would be something you would do with Zabbix offline.

A third option is create a new, empty table from the existing table (see the docs for "CREATE TABLE LIKE ..." , https://dev.mysql.com/doc/refman/8.0...able-like.html ), copy some of the data from the old table to the new table using some kind of selection criteria (for history, it's probably based upon date), and then DROP the old table and rename the new table to take its place. Like the "OPTIMIZE TABLE" option, this requires additional space to populate the new (hopefully smaller) table with data from the old, oversized table.

Ad Widget

MySQL Database cleanup

MySQL Database cleanup

Comment