Zabbix Postgresql 9.? autopartitioning

[alledm]

Jason, glad you fixed it.

rsvancara, any approach is good at long as it works.
I personally don't like to receive emails when stuff is working as expected, and I have too many time found out that "crontab -e" and "crontab -r" are only one fat finger away

[Jason]

The wiki article could probably do with updating to include something about creating the partitions schema and granting rights if it doesn't already exist...

[alledm]

Good point. Done

[Jason]

Just wondering...


Could we have the retention period defined in the create partitions function? i.e. '30 days' and '6 months'

Then when it creates a table for say history_uint_pXXXXXXX it runs the delete check for the same name using the defined retention periods?

That way then you'd not need a cron job at all as it would be purely automatic.

[alledm]

I thought of it, but then it means that if you change your mind on the retention period you need to change the storage procedure, while in this case it is easier to control the crontab entry.
Of course a global variable would solve it I guess... but too obscure

[Jason]

Could the retention periods be stored in a file or a table that the script reads? That way they could easily be updated?

This is a nice way of managing the partitions, but I'm not a fan of the cron job...

[alledm]

Yes i guess so you could have a table in the partitions scheme where you have a default retention and optionally a per table one.

Might add it in the future, but feel free to add it yourself if the crontab is really making you feel itchy

[volter]

Race condition

Hello alledm!

I think your trigger function has a race condition:

Neither CREATE TABLE nor CREATE TABLE IF NOT EXISTS are safe. The trigger function is called very often and you could easily end up with one of them being executed with the wrong pre-assumption, the table doesn't exist yet, while it will, when it actually tries to create the table. Please compare: http://archives.postgresql.org/pgsql...4/msg00189.php

Is it possible to incorporate the suggested solution into the trigger function?

[alledm]

Hello volter,

glad to see you are using it

I had thought of this eventuality, but to be honest I was being optimistic as the last INSERT will be attempted regardless of whether the table creation was successful or not (So even if multiple query try to create a table, only one will succede, while the other will fail and carry on inserting the row)***

and because it appends only once a month I was prepared to sacrifice an insert of 2, but that's pure laziness talking

Will take a look at the post you sent and fix the query. Feel free to contribute if you have already fixed it.

***on a second thought, I might need to trap the exception and silent it otherwise it will stop and error the whole procedure, right?

[rlljorge]

I have some doubts about the partitioning ..

Every day a new partition and format created using the tablename_pYYYYMMDD correct?

If I wanted to remove the data folder I remove this table eg drop table history_p20121201, correct?

More data are still in orignal table "HISTORY"?

How do I clear the table HISTORY? Since housekeper is disabled?

And what I'm seeing in my database ....

zabbix => select count (1) from partitions.history_p2012_11_30;
* count
-------
* 50267
(1 entry)

zabbix => select count (1) from partitions.history_p2012_12_01;
* count
-------
* 50400
(1 entry)

zabbix => select count (1) from partitions.history_p2012_12_02;
* count
-------
* 50400
(1 entry)

zabbix => select count (1) from partitions.history_p2012_12_03;
* count
-------
* 50400
(1 entry)

zabbix => select count (1) from public.history;
* count
--------
* 204907
(1 entry)

It seems to me that although the data are separated into partitions and he always saves the original table, this is correct?

[alledm]

Hello rlljorge,

If you are curious, I advice you read from here about postgresql inheritance:

http://www.postgresql.org/docs/9.2/static/ddl-inherit.html

Try running

  SELECT COUNT (1) FROM ONLY public.history;

which should return you the rows actually stored in the "mother" table.

Otherwise Posgresql defaults to read from both mother and child tables.

[mark4u]

Hi guys,

Can anyone share his knowledge about performence psql9 and zabbix2?

[rlljorge]

Hello rlljorge,

If you are curious, I advice you read from here about postgresql inheritance:

http://www.postgresql.org/docs/9.2/static/ddl-inherit.html

Try running

  SELECT COUNT (1) FROM ONLY public.history;

which should return you the rows actually stored in the "mother" table.

Otherwise Posgresql defaults to read from both mother and child tables.

I did not know this function in POSTGRESQL.
Now I could understand how it works.

Thank you!

[rlljorge]

This is working very well for me.
My performance problem are solved, thank you for your work!

Zabbix 2.0.3 + Postregress + Auto Partition = Perfect

[rickster]

typo?

Thank you for providing this to the community.

Can you confirm if there is a typo in the create trigger statement for history_str_sync? shouldn't this be history_str?

Many thanks

Ricky