I have a Zabbix database on a postgresql backend that grew to ~1TB of disk space. The Zabbix housekeeper (a process for removing old data from the tables) was running for over 18 hours so I used this script to enable partitioning.
But then - exactly at midnight - Zabbix stopped inserting data into the database, right when a new partitioned table should get created.
I found out that when I kill the running autovacuum process (mostly on the table
pg_cancel_backend() the Zabbix server can insert again as some locks go away 1.
I understand that the vacuum process is needed in order to free up transaction ID's so I should NOT do this every day.
- Is partitioning a bad idea? (at least in an already running setup?)
- Should I have started with partitioned tables all along?
- Should I throw out all our monitoring data, and insert it again into correctly partitioned tables? (a huge amount of work...)
- Should I disable autovacuum on those partitioned tables? (assuming that the issue comes from a global lock on all schemas
The final goal is to reduce the load on the database and improve performance.
By enabling partitioning I was able to reduce the runtime of the housekeeper down to a few minutes/seconds via disabling the housekeeping of
trend_data. But it's no positive result if the monitoring stops working after midnight...
The Hardware is quite oversized (all SSD and performing only OK-ish). I guess because I don't really know what knobs to tweak I better not touch them at all.
1: Unfortunately, I only figured this out 7 Hours later, in the morning. Now I have 2 days of missing monitoring data from 00:00 - 07:00