Partitioning in PostgreSQL

[DanOliver]

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.

[NOB]

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.

[noxis]

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]

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.

[hml]

SQL for partitioning

Hi DanOliver,
Have you put together SQLs to partition the history tables in postgres? Could you share them if you have?
Regards,
Hristo

[DanOliver]

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.

[hml]

History tables partition with postgres

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.

[rodman]

Did anyone tested partitioning with postgres? Or this this script hml made? Did it help with DB load?

Thanks in advance.

[DanOliver]

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.

[sersad]

DanOliver your new script is not ready yet?

[f.koch]

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

[sersad]

Hi,
I think i will publisch this in the wiki soon.

rgds Florian

Thanks! I will wait.

[sire]

The same thing for MySQL is described here http://zabbixzone.com/zabbix/partitioning-tables/ (just in case if someone needs that).

[Colttt]

any news about this?

[f.koch]

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