Zabbix Postgresql 9.? autopartitioning

[alledm]

Thank you


I believe anything that has a "clock" field will work with this partitioning approach, so yes you can add all the history tables as you please (TEST IT FIRST )

[parcival]

Hi alledm, i see serval times deadlocks here.

please look here:
0474 and clock<=1361401374
2013-02-21 00:03:01.759 CET zabbix@zabbix 13150 127.0.0.1(12026) ERROR: deadlock detected
2013-02-21 00:03:01.759 CET zabbix@zabbix 13150 127.0.0.1(12026) DETAIL: Process 13150 waits for AccessExclusiveLock on relation 879751 of databa
se 19258; blocked by process 934.
Process 934 waits for AccessShareLock on relation 879724 of database 19258; blocked by process 13150.
Process 13150: select delete_partitions('7 days','day')
Process 934: select avg(value) from history_uint where itemid=32034 and clock>1361400474 and clock<=1361401374
2013-02-21 00:03:01.759 CET zabbix@zabbix 13150 127.0.0.1(12026) HINT: See server log for query details.
2013-02-21 00:03:01.759 CET zabbix@zabbix 13150 127.0.0.1(12026) CONTEXT: SQL statement "DROP TABLE partitions.history_log_20130214;"
PL/pgSQL function delete_partitions(interval,text) line 37 at EXECUTE statement
2013-02-21 00:03:01.759 CET zabbix@zabbix 13150 127.0.0.1(12026) STATEMENT: select delete_partitions('7 days','day')


any idea ?
thx
parcival

[alledm]

Hi,

When are you running the "delete partitions" jobs and how often?
To me it looks as if you probably have a graph open and refreshing while trying to delete the partitions.

The graph is probably also requesting data in the partitions being deleted.

I would advice you to:
* run the delete partitions while the system is not being used (if possible)
* Make sure that if you have graphs continuously refreshing they do not need data that is about to be deleted (so don't pick the 7d view on the graph)

Alternatively, please delete only older data (i.e. 8 days old) so that you are sure it is never needed by the plotting functions.

Regards
Alessandro

[c.mammoli]

Hi, since using autopartitioning with tables without an itemd column is not currently possible I decided to drop obsolete data in those table via cron and disable Housekeeping alltogheter:

@daily psql -q -U zabbix -c "SELECT delete_partitions('7 days', 'day')"
@daily psql -q -U zabbix -c "SELECT delete_partitions('2 months', 'month')"
@daily psql -q -U zabbix -c "delete from acknowledges where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"
@daily psql -q -U zabbix -c "delete from alerts where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"
@daily psql -q -U zabbix -c "delete from auditlog where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"
@daily psql -q -U zabbix -c "delete from events where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"
@daily psql -q -U zabbix -c "delete from service_alarms where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"

5184000 is obviously 2 months expressed in seconds.

HTH

[kofeyh]

Trying to understand this method a bit better,

The Zabbix Database I have uses the prefix 'public', do I need to change the SQL queries to use that, rather than 'partition'?

If I used 'partition' as a prefix then there's a bunch of errors spat out around the syntax when postgres attempts to action the table creation..

Also, there's no reference to the large tables like 'history', just history_sync, etc; is this able to be added?

[alledm]

Hi kofeyh,

the triggers are creating all the "child tables" under the partitions namespace only because it makes it easier to manage them and keeps everything tidy.

You will not have to change any SQL query and everything should be transparent to the application.

If you have problems could you please show me an example and will try to help you out.

Regards
Alessandro

Trying to understand this method a bit better,

The Zabbix Database I have uses the prefix 'public', do I need to change the SQL queries to use that, rather than 'partition'?

If I used 'partition' as a prefix then there's a bunch of errors spat out around the syntax when postgres attempts to action the table creation..

Also, there's no reference to the large tables like 'history', just history_sync, etc; is this able to be added?

[alledm]

Thank you for sharing c.mammoli!

Hi, since using autopartitioning with tables without an itemd column is not currently possible I decided to drop obsolete data in those table via cron and disable Housekeeping alltogheter:

@daily psql -q -U zabbix -c "SELECT delete_partitions('7 days', 'day')"
@daily psql -q -U zabbix -c "SELECT delete_partitions('2 months', 'month')"
@daily psql -q -U zabbix -c "delete from acknowledges where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"
@daily psql -q -U zabbix -c "delete from alerts where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"
@daily psql -q -U zabbix -c "delete from auditlog where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"
@daily psql -q -U zabbix -c "delete from events where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"
@daily psql -q -U zabbix -c "delete from service_alarms where clock < (SELECT (EXTRACT( epoch FROM now() ) - 5184000))"

5184000 is obviously 2 months expressed in seconds.

HTH

[kofeyh]

.. and to answer my own question; I screwed up.

It helps if you have Postgres 9.2 or higher installed. I'd not realised a Centos 6.4 server I just build had such an old version installed.

Now all working just fine. I think. Mostly.

[kofeyh]

Hi kofeyh,

the triggers are creating all the "child tables" under the partitions namespace only because it makes it easier to manage them and keeps everything tidy.

You will not have to change any SQL query and everything should be transparent to the application.

If you have problems could you please show me an example and will try to help you out.

Regards
Alessandro

Indeed it does. Now. As per my last comment, it turned out I had an old (like really old) version of postgresql installed. Upgraded to 9.2 and it all seems to work.

[calebwong]

Eqnuiry on auto-partitioning setup

I am trying to setup the auto-partitioning by follow the wiki steps.

There is a question about the setup,

In part of "Create the main function" and "Remove unwanted old partitions", those last 2 lines is grant the FUNCTION owner to DB user 'postgres'.

When I try to execute these line, the postgresql prompted out the error 'ERROR: must be member of role "postgres"'

My DB user is 'zabbix', Should I change the value from "postgres" to "zabbix"? (ALTER FUNCTION trg_partition()
OWNER TO 'zabbix'; )
Or
Add DB user "zabbix" into role of "postgres"?

On the other hand, This is my first time to use 'postgres' as DB type. May I know, How can I verify /check the auto-partitioning is in function and working?

Thanks.
Regards
Caleb

[alledm]

Hello Caleb,

sure change the user to suit your installation. Will fix the typo in the documentation soon.

regarding checking the replication, have a look at tables being created unter the partition. namespace and their content to see that it is actually working.

Regards
Alessandro

[calebwong]

thanks, work perfeclty

Hello Caleb,

sure change the user to suit your installation. Will fix the typo in the documentation soon.

regarding checking the replication, have a look at tables being created unter the partition. namespace and their content to see that it is actually working.

Regards
Alessandro

I have installed a Postgresql 9.2 /zabbix v2.0.6 on Debain squeeze.
Thanks.

[Andrey123q]

recommendations to DB

Hello. I have next questions. If I'll implement this autoparitioning method on my working zabbix database does it save all old statistics?
Does it recommended to implement autoparitioning to an empty DB or fullness and size of DB is not important?
Do I understand right: delete_partitions function will remove statistics only from moment of 1st partitions creation and it will save all older statistics? For instance, I have statistics in Zabbix from Jan 2013, and in Dec 2013 I have implemented autopartitioning. Therefore all next partitions deletion will delete statistics only from Dec 2013, but all statistics from Jan to Nov 2013 will be saved, doesn't it?

P.S. Now I have Zabbix 2.0.2, PostgreSQL 8.4 and I want to upgrade both of them to actual versions with implementation of paritioning. Also I have tried autopartitioning in test invironment and it works good.

[alledm]

It will work seamlessly on a full database.
You will end up in this situation


+-Main Table *everything until Dec 2013*
+-Dec2013
+-Jan2014

As you said the delete_partitions will only remove data from "monthly" or "daily" partitions. Everything else you need to delete yourself from the Main Table

You can migrate from Main Table to month style table by re-inserting the lines and then deleting the old rows from the old table.

Regards
Alessandro

[Jason]

If you had the disk space then you'd keep the data in the main table till you'd reached x months from today (where x is the number of months where keep data for) and then delete all data from that table and then start on the normal partition cycle.

I'd have thought could have used select into though to move data from main table into partition tables and then purged the data from main table.