Ideas of Zabbix plus Postgres plus Partitioning plus Upgrading

[mrjoshuap]

First time poster here, and I've a few questions...

We currently have a single zabbix server with a single proxy. Currently, we monitor 600 hosts, with around 60,000 items and around 230 NVPS on Zabbix 1.8.

We are in process of upgrading to 2.2 (which will be a new installation, without any data migrations) and moving from MySQL to PostgreSQL. I've been reading the sparse information regarding database partitioning Zabbix w/PostgreSQL and the numerous different ways to accomplish it. There seem to be a bunch of competing theories.

Based on the wiki, I'm taking the suggestion to use pg_partman as the leading contender at the moment...

1. If I utilize partitioning in the new Zabbix 2.2 environment, how would future upgrades requiring schema changes work? Would I have to un-partition, upgrade and then repartition? Would schema changes just fail?

2. Using pg_partman to accomplish the partitioning, the 'clock' field of the database tables is an integer, not a timestamp, so I must use ID based, not time based. How would one go about having similar retention periods as time based (or at least the ability to just drop old data tables instead of utilizing the housekeeper)?

3. Has anyone actually done any partitioning with Zabbix 2.2 and PostgreSQL?

4. Is data partitioning actually recommended to be used or is it just one of those "you can do it if you're brave enough" kind of things?

As for pg_partman, since nobody has posted anything on what you do to actually partition the tables, here's my first stab (you'll obviously need to fix the schema if yours is different):

-- Create daily partitions based on the clock -  (60*60*24)
SELECT partman.create_parent('public.acknowledges', 'clock', 'id-static', '86400');
SELECT partman.create_parent('public.alerts', 'clock', 'id-static', '86400');
SELECT partman.create_parent('public.auditlog', 'clock', 'id-static', '86400');
SELECT partman.create_parent('public.events', 'clock', 'id-static', '86400');
SELECT partman.create_parent('public.service_alarms', 'clock', 'id-static', '86400');

-- Create monthly partitions based on the clock - (60*60*24*30)
SELECT partman.create_parent('public.history', 'clock', 'id-static', '2592000');
SELECT partman.create_parent('public.history_log', 'clock', 'id-static', '2592000');
SELECT partman.create_parent('public.history_str', 'clock', 'id-static', '2592000');
SELECT partman.create_parent('public.history_text', 'clock', 'id-static', '2592000');
SELECT partman.create_parent('public.history_uint', 'clock', 'id-static', '2592000');
SELECT partman.create_parent('public.trends', 'clock', 'id-static', '2592000');
SELECT partman.create_parent('public.trends_uint', 'clock', 'id-static', '2592000');

Thanks In Advance!

[richlv]

the most important question - why do you want to use partitioning ?

[mrjoshuap]

the most important question - why do you want to use partitioning ?

The short answer is that our post-upgrade plans include doubling our host count (and of course item counts) to over 1000+ hosts. Our current database consumes between 250-300GB of disk space and the current housekeeping calls take 2-3 hours ... I'm expecting our DB footprint to hit somewhere between 500-750GB based solely on the current size and the expected number of new hosts.

After talking with a few folks and some research, it seemed the best approach was to disable Zabbix's internal housekeeping and partition the data so that history cleanup doesn't take as long and allow us to grow our hosts without too much hit on performance.

[Jason]

I've used this method to do the partitioning and it's worked fine for us.

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

I tested upgrade of a system from 2.0.9 that was already partitioned and appeared to work fine, but not tried partitioning of fresh install on 2.2

[dirckcopeland]

back to richlv question

So back to richv's question, why use partitioning at all. Thats the question Rich has posed and he is a certified Zabbix administrator. I'm just getting started in Zabbix administration and would like to know the benefits, if any of partitioning. Specifically partitioning the history table, but I don't quite understand what that buys me, or what it really does.. Any insight would be appreciated from the experts.

[Jason]

Ok, why partition?

For us the answer was ease of maintenance. We originally used mysql and innodb tables. In order to reclaim disk space back from deleted items and minimise the load on the server we originally disabled housekeeper and used scheduled cron jobs to purge old items and compact the tables.

This was taking an increasing amount of time to run as the database grew (and during this period server was pretty much unusable) so we decided to move to using partitions when we moved to version 2.0. As mysql didn't support the foreign keys for partitions we went down the postgres route.

This gave the advantage that could break the data up into date sized chunks and then it was easy to drop the partition reclaim, the disk space without impacting on server performance at all. Housekeeping is no longer needed as the old data is automatically purged.

Using partitions does have its limitations... It requires you to have a common time for keeping data and trends for. If you have some data you need to keep indefinitely then it won't work without some customising of the partitioning. You are also relying on a scheduled task or manual maintenance to delete the old partitions so if there is a problem with this then you might suddenly find your drive full (although hopefully zabbix will have told you before this happens!!!!)

[trikke76]

why drop to reclaim free space ?

PostgreSQL uses vacuum to clean up the database and reclaim space if i am correct ?

u will have to run a full vacuum to reclaim diskspace however

[Jason]

It does, but if you've already got a database/zabbix server that is highly loaded then you don't want to put any more IO intensive operations on it that you have to. For us this seemed the simplest and lowest 'cost' method of keeping the database size under control.

[trikke76]

what version of postgresql are u using ?
the full vacuum works much better starting from version 9

normally there is no need to run a full vacuum
vacuum itself runs and reclaims space all the time (not to the os touhg)
have u tried to play with the vacuum settings to make it run more frequently without impacting your I/O to much ? U also might want to increase vacuum_cost_delay to avoid to much impact on your db