View Full Version : Partitioning in PostgreSQL
DanOliver
13-10-2009, 00:15
I'm looking to deploy a Zabbix installation that I estimate will be collecting about 200 values per second, keeping most data for around 40 days, and then summary data for at least a year. My database backend will be PostgreSQL 8.4.x.
Looking at the schema and examining the query logs, it looks like tables like history_uint would probably be suited to day-by-day partitioning. I’ve done some quick trials and everything appears to work ok.
However, before I go off an implement all of the scripts necessary to manage the partitions, I wondered if anyone can shed light on whether Postgres performance does suffer in this scenario and whether anyone had implemented partitioning before?
To the best of my knowledge, timestamps in Zabbix come from the agent/proxy. Therefore, time ordering can not be guaranteed. Therefore, I’m thinking along the following lines:
- partition on a daily basis
- partition on ‘clock’ field
- create partitions one day ahead
- partition trigger knows about tables up-to five days in the past and one day ahead
- remove tables once no rows left, i.e. allow Zabbix clean-up routines to remove data as configured by each host item
If this hasn’t been done before and no-one can tell me it’s not necessary, I’ll publish my scripts in the cookbook/on the wiki.
Hi DanOliver
I can well imagine, that nobody did this before.
But I think it's a good approach to partition the DB like that.
Be aware, that other history_xxx DB tables like history_str
should be partitioned in that way, too.
We use MySQL (5.0.x) because this DB works well for new releases.
Partitioning is supported from 5.1.x on.
So, no experience here.
I am curious about the results.
Regards
Norbert.
I was considering implementing this with MySQL and Zabbix. It would potentially be a massive performance increase for the housekeeping to be able to just drop off a partition.
DanOliver
14-10-2009, 15:17
I'm not sure that dropping partitions is a sound idea when you're not in control of what future versions may expect to exist. You also limit your ability to keep the full details on a particular key, should you want to for some reason.
My primary concern was that the normal indexes would be very inefficient.
Hi DanOliver,
Have you put together SQLs to partition the history tables in postgres? Could you share them if you have?
Regards,
Hristo
DanOliver
22-10-2009, 10:21
I've been out of the office since mid-last week, so I've not looked at doing this. I'll try and take a look this week, but might not be able to until next week.
Hi,
I have put together a small script which will generate the SQLs for partitioning
the history tables in zabbix running with postgres.
Hopefully I have included all tables that need partitioning.
I had to turn off the housekeeper but I guess the reason needs to be discussed in different thread.
Did anyone tested partitioning with postgres? Or this this script hml made? Did it help with DB load?
Thanks in advance.
DanOliver
08-01-2010, 18:31
Sorry -- I still intend to publish my scripts. Unfortunately, after I got the draft version done I got assigned to another project that took up all of my time.
I'm going to be looking at Zabbix 1.8 in the next week or two and will hopefully post a PostgreSQL script then.
DanOliver your new script is not ready yet?
Hi,
i have partitioned the biggest tables (history and trends) in our postgresql 9.0.4 DB on a monthly base.
I have a python script creating the needed tables every month .
I think i will publisch this in the wiki soon.
rgds Florian
Hi,
I think i will publisch this in the wiki soon.
rgds Florian
Thanks! I will wait.
The same thing for MySQL is described here http://zabbixzone.com/zabbix/partitioning-tables/ (just in case if someone needs that).
Hi,
i wrote a little wiki how to
http://www.zabbix.com/wiki/howto/db/postgres/partition
feedback is very welcome, feel free to correct me if i had made a mistake
rgds flo
Hi,
i wrote a little wiki how to
http://www.zabbix.com/wiki/howto/db/postgres/partition
feedback is very welcome, feel free to correct me if i had made a mistake
rgds flo
I'm not very familiar with DB triggers, I see there are triggers on INSERT operation. Could you please explain, how are UPDATEs and SELECTs handled? I mean, how does the DB engine know which of the table's partitions to look for a requested value during a SELECT operation, for example, and what about UPDATEs? Is this a built-in feature of any RDBMS?
I'm not very familiar with DB triggers, I see there are triggers on INSERT operation. Could you please explain, how are UPDATEs and SELECTs handled? I mean, how does the DB engine know which of the table's partitions to look for a requested value during a SELECT operation, for example, and what about UPDATEs? Is this a built-in feature of any RDBMS?
hi,
yes the trigger only handle INSERTS, we don't need UPDATE because in the history tables no UPDATE occurs.
if you use the SELECT with "where clock ..." (zabbix do this) the planer can skip partitions who can't include the data ( there are the check constraints for).
rgds flo
zalex_ua
28-04-2012, 16:25
One important problem for partitioned PostgreSQL has been fixed here https://support.zabbix.com/browse/ZBX-4881 starting from v 1.8.13
Why this partitioning feature isn't integrated in zabbix server ?
I mean that housekeeper could be removed and changed to a functionality that make partitioning of the database. Then, based on a general history and trends setting this functionality could delete old partitions and create new ones automatically.
Does it sounds crazy ?