Postgresql Database Size + Housekeeping

[pdaemon]

Hi,

We are running a reasonably large environment with the following stats:

Number of hosts (enabled/disabled/templates) 1138 829 / 11 / 298
Number of items (enabled/disabled/not supported) 40107 39609 / 1 / 497
Number of triggers (enabled/disabled [problem/ok]) 16973 15259 / 1714 [78 / 15181]
Number of users (online) 88 3
Required server performance, new values per second 508.48

The Zabbix Master (VM) is running v3.0.2 (upgraded previously from 2.4.X versions) with 13 Zabbix Proxies attached and sending data back to the Postgresql Database (9.2.14) on the Master

The spec of the Zabbix Master (VM) is:

24GB RAM
4 vCPU
750GB of space for Postgresql DB.

Currently I have History set to 7 days and Trends set to 365 days, but the database just seems to keep on growing, even with Housekeeping on.

The size of the database 4 days ago was:
name | owner | size
-----------+----------+---------
zabbix | zabbix | 526 GB

relation | size
-------------------------+---------
public.history_uint_1 | 139 GB
public.history_uint | 129 GB
public.history_1 | 110 GB
public.history | 100 GB
public.trends_uint | 13 GB
public.trends_uint_pkey | 11 GB

and now already:
name | owner | size
-----------+----------+---------
zabbix | zabbix | 537 GB

relation | size
-------------------------+---------
public.history_uint_1 | 144 GB
public.history_uint | 133 GB
public.history_1 | 111 GB
public.history | 101 GB
public.trends_uint | 13 GB
public.trends_uint_pkey | 11 GB

It seems the trends tables are staying reasonably static, but the History tables are increasing in size substantially...over the last 4 days by 11GB!

Also the history_uint index is 129GB in size.

Does this seem normal behaviour in terms of the size increase of the DB? What sort of total size of DB should I be seeing for this number of items and hosts counts?

Another question: What is the best way to verify that Housekeeping is working as it should and has deleted all the history records out of the DB that are more than 1 year old for example?

Thanks Alot

Regards
Philipp

[pdaemon]

I'm going to try to re-index the large history tables to see if that makes any difference, but I'll have to have a downtime as I believe the tables are locked when you re-index the table.

It seems re-indexing can reduce the size of your DB. Also when history cleanup happens the size on the disk doesn't actually go down in size until a vacuum is done.

The DB size is now 554Gb. I'll let you know how it goes.

[kloczek]

https://www.zabbix.org/wiki/Docs/howto/zabbix2_postgresql_autopartitioning

[pdaemon]

Will the auto-partitioning work with Zabbix 3.0?

[kloczek]

Partitioning introduces completely different way of maintaining history and trends data and this way is only way to deal with growing amount of data when env is growing.
Simple instead remove oldest data by DELETE query history tables are divided into daily parts stored in tables partitions and on daily bases is executed short SQL script creating in advance new partitions for next days and dropping oldest partitions.
So instead use DELETE query which generates not only write IOs but read IOs as well (to delete something you need to read from data files exactly where are parts which needs to be deleted) is done single DROP TABLE PARTITION query which always takes only few IOs.
As same as with history* tabled is with trends* tables but because those tables contains significantly less data than history* tables partitions usually are created on monthly bases.
So in other words all items are aligned to use exactly the same time window of raw history and trends data, and nothing deletes these data over DELETE statement.
After switching to partitioned tables you will have very predictable characteristics of memory, CPU and IOs usage because every day after midnight new data will be written to empty partitions and utilization of those resources will be changing during a day with log(N) where N is number of rows stored in history tables.
Best way of introduction of partitioned tables is use slave DB where you can do whole partitioning process without stopping writes to history and trends tables. When everything will be partitioned and fully synced with master all what you need is quick stop zabbix srv and web frontend (switch to maintenance mode) -> promote you slave as new master -> start zabbix server enable web frontend access.

I can tell you that zabbix developers are working on integrate partitioning into zabbix and they even have some work done here but still couple of things needs to be done in zabbix server C code and whole solution needs to be more tested. However do not expect that this will happen shortly (IMO it may happen within year) so do not wait and try to understand partitioning as it is now provided and implement it in your DB backend.

[Linwood]

I didn't see them, are their scripts to do the initial partition movements for existing data?

And of the several schemes in the wiki for postgresql partitioning, is this there any consensus which is the best to use (is this it?). I'm particuarly interested whether (this one was actually found to work better, since it does have a simpler trigger function without dynamic sql, though in and of itself it won't facilitate initial placement of existing data to the right tables as it's limited to about 3 partitions for the inserts.

One downside of the wiki is there appears to be no feedback from people who have used it; is there some I am not missing, or another place to look?