Zabbix Postgresql 9.? autopartitioning

[Jason]

With Zabbix 2.2.X I'm seeing lots of the following being logged...

ERROR: relation "history_p2014_04_25" already exists
CONTEXT: SQL statement "CREATE TABLE IF NOT EXISTS partitions.history_p2014_04_25 (CHECK ((clock >= '1398380400' AND clock < '1398466800'))) INHERITS (history)"
PL/pgSQL function trg_partition() line 37 at EXECUTE statement
STATEMENT: insert into history (itemid,clock,ns,value) values (67984,1398380404,407074714,1271600.274097);

Do the functions need tweaking?

[BDiE8VNy]

That's a race condition.
I encounter the same when using pg_partman. This made me reinvent the wheel (again): Higher performant partitioning in PostgreSQL

[pietro54]

Is there some one that is using this metod with zabbix 2.2.2++ ? (with success of course)

[Slash]

Is there some one that is using this metod with zabbix 2.2.2++ ? (with success of course)

Using it since February (following all zabbix latest zabbix release) and it works great (current version is 2.2.4).

[nhathaitrieu6]

Hi guys,

Can anyone share his knowledge about performence psql9 and zabbix2?

[pietro54]

Hi,
can I sum all of this?

I read whole thread, and I`m not sure is partitioning works well.
What should I do?:
1. Create schema partitions,
2. create function
3. create triggers
4... something more?

What I got:
Schema partitions,
yes, tables are creating - this is ok.
But I`m not sure if zabbix is reading from them, I`m afraid that zabbix still read data from mother table.

I don`t see any performance difference when I come to my overview web page(It take ~6second to reload this page).

is this ok? I should not see any performance tune up in this case?
I thought that if zabbix is reloading overview, he should research whole table, but when he will got partitions he should search only in a specific table. Hope this is clear for you.

[alledm]

Hi Pietro,

I hope you have solved your problem by now.
This procedure creates new partitioned tables from the moment you start using it, but it does not move all your current data to the partitioned tables.

To force your historic data to be partitioned, I advised you to simply copy your data from the master table and re_add it. this will trigger the creation of the partitions.

All you are left to to then is to delete all the duplicated data left in the master table.
I hope it is clear.

regards
Alessandro

Hi,
can I sum all of this?

I read whole thread, and I`m not sure is partitioning works well.
What should I do?:
1. Create schema partitions,
2. create function
3. create triggers
4... something more?

What I got:
Schema partitions,
yes, tables are creating - this is ok.
But I`m not sure if zabbix is reading from them, I`m afraid that zabbix still read data from mother table.

I don`t see any performance difference when I come to my overview web page(It take ~6second to reload this page).

is this ok? I should not see any performance tune up in this case?
I thought that if zabbix is reloading overview, he should research whole table, but when he will got partitions he should search only in a specific table. Hope this is clear for you.

[pietro54]

Hi, Alessandro
Sorry for long replay.

Thanks for your answer, its clear for me now.
New tables are used. Everything is ok with partitioning, but still I don`t see any performance impact to my system.

My main target was to improve load the overview page, because in my company many users are using this web page.
I was hoping that idea with partitining will help, but apparently not

Regards,
Pietro

[apalacheno]

@pietro54: Partitioning in the sense of this thread will help you with housekeeping performance. The larger your database, the more impact the housekeeping process will have on performance, as it slows down the database as a whole during housekeeping. So yes, it does help with overall performance.

But the problem you encounter with the Zabbix Web Frontend sounds like a different story, though. Generally speaking, the performance of the web frontend is largely independent of database partitioning.

@Alessandro: Thank you very much for your work - very much appreciated and highly sought after. Nice to have a self-maintaining implementOnce-forgetThereafter solution :-)

@all: Can somebody confirm this autopartitioning method is still working with Zabbix 2.4.x?

Cheers,

Robert

[Slash]

@all: Can somebody confirm this autopartitioning method is still working with Zabbix 2.4.x?

It still works without issue with Zabbix 2.4.x, you can upgrade from 2.2 without changing anything.

[alledm]

Nice to see people are still using it

Truth be told: I have never used it myself and I just created it to help out a colleague

Unfortunately I have not used Zabbix in 3 years but nice to see people are still using this

[apalacheno]

It still works without issue with Zabbix 2.4.x, you can upgrade from 2.2 without changing anything.

That's good to hear, thanks for the confirmation!

Maybe Alessandro can add a 'tested-with-2.4.x'-Tag to the wiki, so others get this information as well? I still consider this as the most elegant and robust solution for PostgreSQL.

[BDiE8VNy]

[...] the most elegant and robust solution for PostgreSQL.

...if there wouldn't be the risk of a race condition on higher loads.

[apalacheno]

...if there wouldn't be the risk of a race condition on higher loads.

Hi BDiE8VNy,

I guess you are referring to Jason's post, right?

What would be the worst-case scenario when a race condition occurs? Data loss or just that a new partition can't be added (and data is written to the old partition instead)?

[BDiE8VNy]

When deviating from default configuration and configure Zabbix server/proxy to use one history syncer process only, then this procedure should be fine.

But when having multiple processes, then creation of the same missing partition might be tried in parallel. The probability of issues increase the higher the process count and the more NVPS have to be processed.

On parallel creation bad things may happen. Staring from deadlocks, which may completely block history synchronization for hours, ending up with data loss when unique constraints occur during creation of the missing table.