MySQL database growth

[WebGreg]

Hi.

What is your experience with database growth? I'm a newbie who was surprised that the database was growing over 1GB per day. Currently, 44 hosts (including 27 switches).
Administration > Housekeeping > Data storage period > 365d... The same "Trigger data storage period".
But it looks like I won't be able to handle that much. What is your approach? Do you shorten the storage time, restrict heavily monitored parameters, or extend the checking time? What have you changed in the default settings (and templates) since you installed the server?

[PavelZ]

I don't have any ready advice on settings, but you can start by evaluating the data you've already accumulated.

I used to run SQL queries, but now I only use this project https://github.com/pavlozt/zabbix-db-auditor/ . Almost the same.
It is not a separate program, you will also need to install Grafana. But Grafana is often already used by administrators.
There is currently no way to count specific elements. It is assumed that you detail down to the template level to understand what needs to be worked on.

[WebGreg]

Hi PavelZ Thank you for your reply. I'm considering Grafana, but maybe later. For now, I need to get to know Zabbix. Although maybe I'm approaching it wrong, because Grafana does not require such commitment and just makes analysis and decision-making in this area easier. Zabbix-db-auditor - I didn't know this, but it looks interesting. Thank you.

Can you share information about how many hosts you monitor and what the growth is and how long you store the data? If more people shared this information, I would have some reference point.

[PavelZ]

It is VERY customer-dependent. Most often, the issue is discussed not based on feasibility, but on the economic possibilities of storage.

The parameters selected in Zabbix, 30d for operational data (history tables) and 1 year for trends, don't they look reasonable?

Also, don't forget that many people use partitions and here everything becomes even more complicated:
you need to disable separate storage history and select a single interval.

There are simply unsuccessful templates.

[WebGreg]

It is VERY customer-dependent..

There is no doubt about it and I don't expect ready-made settings. I'm just wondering about the trend of settings reached by people with more experience, which I could take as a starting point.

The parameters selected in Zabbix, 30d for operational data (history tables) and 1 year for trends, don't they look reasonable?

Yep.

Also, don't forget that many people use partitions and here everything becomes even more complicated:
you need to disable separate storage history and select a single interval.

And this is something I was completely unaware of. Thanks!
At the moment I have:
Number of items (enabled/disabled/not supported)
16289 / 357 / 203

[markfree]

Following the discussion, I'm interested in knowing what kind of optimisations administrators are using for the Zabbix DB, especially large DB, besides the usual DB partitioning.
I'm mostly biased to MySQL which I have more experience with, but any tip is welcome.

[PavelZ]

In my opinion, many people just switch to postgresql tsdb.
It's just a matter of the complexity of support. Everything works very simply in MySQL.

By the way, also enable compression. It works well for text data and for rarely changing monitoring indicators. Worse than TSDB, but practice will show whether it is enough for you.
It is for this purpose that Zabbix DB Auditor displays whether the tables are compressed.

[markfree]

Zabbix DB is "insert" intensive. Are there specific configurations that you use for Zabbix DB?

[PavelZ]

First of all, I would like to point out that it is rare to speed up MySQL with just settings.
The database executes the application queries and to reduce the work, it is reasonable to start by reducing the queries.
But everyone is forced to do the tuning because it is much easier than to rework the application.

Сhanges in the scheme or partitioning, reducing the storage time, actions on the part of the application , turn on compression (reduce size of written bytes) - that's really helps.

However, there are some settings that I find useful in descending order of usefulness:

innodb_flush_log_at_trx_commit = 2 (or even 0)
innodb_buffer_pool_size should be large enough because history syncer also reads data (why do it like that is a mystery to me)
Make sure sync_binlog=0 if you use replication or binary log (usually not).

There are many more settings, but I suggest focusing on these.

[markfree]

Indeed. These are helpful settings.