Partitioning in PostgreSQL

[sire]

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?

[f.koch]

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]

One important problem for partitioned PostgreSQL has been fixed here https://support.zabbix.com/browse/ZBX-4881 starting from v 1.8.13

[mcmyst]

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 ?

[ToomasAas]

I have no experience with database partitioning but I think I need to implement this, because currently the housekeeper process is really hogging my PostgreSQL server.

I wonder how this affects upgrading Zabbix in the future. If I implement it now in Zabbix 1.8 and then want to upgrade to Zabbix 2.0 sometime in the future, is this possible or do I then have to do something special with the database?

[Colttt]

how must i use the script on the wiki-page?

[mcmyst]

I have no experience with database partitioning but I think I need to implement this, because currently the housekeeper process is really hogging my PostgreSQL server.

I wonder how this affects upgrading Zabbix in the future. If I implement it now in Zabbix 1.8 and then want to upgrade to Zabbix 2.0 sometime in the future, is this possible or do I then have to do something special with the database?

If you take a look at the second last comment the guy says that after having partitioned his database he is not able to upgrade to 2.0, the database patch is showing errors.

http://zabbixzone.com/zabbix/partitioning-tables/

[f.koch]

If you take a look at the second last comment the guy says that after having partitioned his database he is not able to upgrade to 2.0, the database patch is showing errors.
http://zabbixzone.com/zabbix/partitioning-tables/

this is about mysql, the zabbix version 2 database uses foreign keys, and mysql don't support this with partitioning
http://dev.mysql.com/doc/refman/5.1/...mitations.html

if you use a customized database you normally can not use the database patch directly, you need to look at the patch and build a new one with partitioning support or run the statements manually against all child tables and the master table (if needed). Maybe this is enough, maybe not.

regards flo

[Colttt]

i got an error.. any idea??

this is the first one..
as user zabbix

psql -f create_zbx_partitions_1 zabbix
psql:create_zbx_partitions_1:63: ERROR:  invalid type name "rec record"
LINE 9:         rec record;

 psql --version
psql (PostgreSQL) 9.1.4
contains support for command-line editing

zabbix version 2.0.1

[f.koch]

Hi,

updated version for zabbix 2 is online on zabbix.org

http://www.zabbix.org/wiki/Docs/howto/zabbix2_postgresql_partitioning

regards flo

[Colttt]

what is the difference between the new version and the old partitions ??

[f.koch]

what is the difference between the new version and the old partitions ??

the old version is for zabbix 1.8.x , the new is for 2.0.x, there are differences in the database schema.
in the new one all tables with history data are partitioned, in the old one only the biggest..

the new one automatically cleans the master table indexes and constraints, and the partition creating function is much cleaner and more robust.

emergency partitions for all tables are in place, so if a partition is missing ( whyever ), all date go into the emergency partition.


regards flo