Zabbix MySQL partitioning

[rsterenb]

I was looking at MySQL partitioning on https://www.zabbix.org/wiki/Docs/how...l_partitioning.

At 1.5.3, there is a line saying:

ALTER TABLE `history` ADD PARTITION p2011_10_23 VALUES LESS THAN (UNIX_TIMESTAMP("2011-10-24 00:00:00")) ENGINE = InnoDB;

This notation does not work for me on MariaDB-10.2. What works is:

ALTER TABLE `history` ADD PARTITION (PARTITION p2017_09_22 VALUES LESS THAN (UNIX_TIMESTAMP("2017-09-22 00:00:00")));

Not sure if the former version worked in some older version of MySQL though.
Is there someone who can merge this somewhere in the wiki, or should I create an account for editing the wiki and do it myself?


The recommendations say that: "Consider using Use XtraDB, rather than pure InnoDB. This backend engine is included in such MySQL forks as MariaDB and Percona."
As of MariaDB-10.2, XtraDB is no longer included: https://mariadb.com/kb/en/library/wh...ead-of-xtradb/


Finally, at 1.5.2 we need to do a SELECT on several tables to find the oldest date so we know which partitions must be made, and create SQL from that.
I've made a script that automatically generates the SQL for the supplied tables that is needed for initializing MySQL partitioning, and I would like to share it for review, maybe even for inclusion in the wiki.
Can I paste the script here and get feedback and/or corrections, or is another forum more appropriate?

[vesper1978]

The wiki on www.zabbix.org is community driven. If you'd like to make changes, feel free to sign up and contribute.

[rsterenb]

I just tried to create an account, twice, but each time I get the reply: "The authentication plugin denied the account creation."

[kloczek]

It would be good to remove this page as proper documentation about MySQL partitioning is on https://www.zabbix.org/wiki/Docs/howto/mysql_partition

[pnenov]

MySQL schema

i would be disagree with you, there is a lot to be discussed regarding partitioning ....

for example table items
this is the largest and most used table in the entire MySQL schema, it has been used literaly in each and every query and look what we have there:


| items | CREATE TABLE `items` (
`itemid` bigint(20) unsigned NOT NULL,
`type` int(11) NOT NULL DEFAULT '0',
`snmp_community` varchar(64) NOT NULL DEFAULT '',
`snmp_oid` varchar(255) NOT NULL DEFAULT '',
`hostid` bigint(20) unsigned NOT NULL,
`name` varchar(255) NOT NULL DEFAULT '',
`key_` varchar(255) NOT NULL DEFAULT '',
`delay` int(11) NOT NULL DEFAULT '0',
`history` int(11) NOT NULL DEFAULT '90',
`trends` int(11) NOT NULL DEFAULT '365',
`status` int(11) NOT NULL DEFAULT '0',
`value_type` int(11) NOT NULL DEFAULT '0',
`trapper_hosts` varchar(255) NOT NULL DEFAULT '',
`units` varchar(255) NOT NULL DEFAULT '',
`multiplier` int(11) NOT NULL DEFAULT '0',
`delta` int(11) NOT NULL DEFAULT '0',
`snmpv3_securityname` varchar(64) NOT NULL DEFAULT '',
`snmpv3_securitylevel` int(11) NOT NULL DEFAULT '0',
`snmpv3_authpassphrase` varchar(64) NOT NULL DEFAULT '',
`snmpv3_privpassphrase` varchar(64) NOT NULL DEFAULT '',
`formula` varchar(255) NOT NULL DEFAULT '',
`error` varchar(2048) NOT NULL DEFAULT '',
`lastlogsize` bigint(20) unsigned NOT NULL DEFAULT '0',
`logtimefmt` varchar(64) NOT NULL DEFAULT '',
`templateid` bigint(20) unsigned DEFAULT NULL,
`valuemapid` bigint(20) unsigned DEFAULT NULL,
`delay_flex` varchar(255) NOT NULL DEFAULT '',
`params` text NOT NULL,
`ipmi_sensor` varchar(128) NOT NULL DEFAULT '',
`data_type` int(11) NOT NULL DEFAULT '0',
`authtype` int(11) NOT NULL DEFAULT '0',
`username` varchar(64) NOT NULL DEFAULT '',
`password` varchar(64) NOT NULL DEFAULT '',
`publickey` varchar(64) NOT NULL DEFAULT '',
`privatekey` varchar(64) NOT NULL DEFAULT '',
`mtime` int(11) NOT NULL DEFAULT '0',
`flags` int(11) NOT NULL DEFAULT '0',
`interfaceid` bigint(20) unsigned DEFAULT NULL,
`port` varchar(64) NOT NULL DEFAULT '',
`description` text NOT NULL,
`inventory_link` int(11) NOT NULL DEFAULT '0',
`lifetime` varchar(64) NOT NULL DEFAULT '30',
`snmpv3_authprotocol` int(11) NOT NULL DEFAULT '0',
`snmpv3_privprotocol` int(11) NOT NULL DEFAULT '0',
`state` int(11) NOT NULL DEFAULT '0',
`snmpv3_contextname` varchar(255) NOT NULL DEFAULT '',
`evaltype` int(11) NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`),
UNIQUE KEY `items_1` (`hostid`,`key_`),
KEY `items_3` (`status`),
KEY `items_4` (`templateid`),
KEY `items_5` (`valuemapid`),
KEY `items_6` (`interfaceid`),
CONSTRAINT `c_items_1` FOREIGN KEY (`hostid`) REFERENCES `hosts` (`hostid`) ON DELETE CASCADE,
CONSTRAINT `c_items_2` FOREIGN KEY (`templateid`) REFERENCES `items` (`itemid`) ON DELETE CASCADE,
CONSTRAINT `c_items_3` FOREIGN KEY (`valuemapid`) REFERENCES `valuemaps` (`valuemapid`),
CONSTRAINT `c_items_4` FOREIGN KEY (`interfaceid`) REFERENCES `interface` (`interfaceid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1




UNIQUE KEY `items_1` (`hostid`,`key_`),

basically that statement makes virtually impossible this table to be partitioned

also this statement severely impair the performance of the zabbix itself, first because such a index can not be cached second because the column key_ contents expressions which in some occasions can be very long and all this is used as a index .....

guys, with all my respect, i like zabbix very much, but you still have a lot a job to do on the MySQL schema design, rather than to think how to close our thread

[kloczek]

[..]
basically that statement makes virtually impossible this table to be partitioned

So far no one needs to partition items table.
Content of the items table is constantly present in zabbix server configuration cache and this table needs to be updated only when list of the monitored items needs to be updated.

[pnenov]

So far no one needs to partition items table.
Content of the items table is constantly present in zabbix server configuration cache and this table needs to be updated only when list of the monitored items needs to be updated.

i have to disagree with you again

1) table items may have hundreds of thousands and even millions records
2) unique indexes can not be cached
3) rendering pages like Monitoring-> triggers, Monitoring-> latest data, Monitoring->overview, actively use items table

and this is just on the front-end, on the back-end (data from zabbix server) the situation is even worse, cuz items table has so many constrains and cascade delete constrains, so as like i said this table has been used virtually by each and every query and it is updated very often as well - every time you modify a hosts or the templates assigned to the hosts

so, long story short - my items table has 4 millions records and its size is close to 2G, and i desperately need to partition it

just one single host may have up to 300-500 items - monitoring the linux OS, hard drives and network shares and IPMI controller for example and you already have 500 items for one single host in the items table
if you monitor 2-3000 instances plus 5-10 applications on each instance and you easily can have few millions records on items table ... and your zabbix server will be nearly dead

[kloczek]

I don't think that it will be any issue with HASH partitions using itemid column and it is only type of the partitions which I see which could be added on items table.

Just one single host may have up to 300-500 items - monitoring the linux OS, hard drives and network shares and IPMI controller for example and you already have 500 items for one single host in the items table if you monitor 2-3000 instances plus 5-10 applications on each instance and you easily can have few millions records on items table ... and your zabbix server will be nearly dead

Again: zabbix server on processing incoming data is doing this against data held inn zabbix server memory. This processing would be very slow if it will be necessary to do this constantly querying soe some items table data.
Only bits which could improve partitioning items table is moment when new items needs to be added/deleted/enabled/disabled or just after zabbix server start when zabbix server configuration cache is filled using items and other tables content.

So question is: do you have any problems with latency of those operations or not?
If not you are digging i wrong place and instead starting from sentence that it is not possible to partition items table you should describe your issues.

[pnenov]

I don't think that it will be any issue with HASH partitions using itemid column and it is only type of the partitions which I see which could be added on items table.
.

well, it is an issue in fact
in order to be able to partition any table in MySQL, you need:

1) the table must not refereed by any other tables
2) the table must not have constrains
3) all columns you use as key for partitioning must be included in all unique keys and primary indexes

so we have hostid and key_ in unique index and itemid in primary key
so you either you need to add hostid into primary key , either you must add itemid into unique index
both ways will lead you to compromising the data
but even if you do this you have to remove all referrers from each and every table which has a constrain with items table and it makes 11 tables to be precise

presently i try to remove the UNIQUE KEY `items_1` (`hostid`,`key_`)
the check if combination hostid and key_ is unique can be done from the code
the key_ column contains large strings and it must not be indexed - the index gets very big and slows down all operations related to table items


yes, i am 100% sure the issue i have - large latency on the web interface when you try to Monitoring-> triggers, Monitoring-> latest data, Monitoring->overview dues to items table
these pages do auto-refresh and in fact it makes them not usable
consecutively it overloads the DB and the server (php processes hang up up to the timeout)

[kloczek]

Please .. I've not been asking about what is the cause of the issue which you are dealing with.
Please describe you issue describing of what you are observing.
Please as well separate raw observations from your understanding and conclusions.

So as Romans says "ab ovo" .. please start from the beginning (Latin "ab ovo" means "from the egg" .. as philosophers at Roma time come to conclusion that egg was before chicken ):
- what are you observing?
- what are you DB settings? At least: tx_isolation, innodb_buffer_pool_size but probaly best would be good to have at least all settings with non default values
- on what kind of hardware everything is working?
- what kind OS you are using?
- any IO stats will be extremely helpful (it does't need to be in from of graphs but at least in form of avg rates of read and write IOs).
- I know that already you are using MySQL. It would good to know which one version exactly you are using.

[pnenov]

kloczek,

only the very small companies and single users host their databases on one single server

I am using a galera cluster and many other people are using multi-disks dataspaces

what i am trying to say is that zabbix's schema is lacking enterprise capabilities one of which is partitioning - it is virtually impossible to partition most of the tables in Zabbix's schema

It seems to me Zabbix team prefer to stay with the small companies and single user rather than to create a product which can be used in an enterprise and distributed architectures

[kloczek]

only the very small companies and single users host their databases on one single server

Sorry but I don't see such correlation.
More likely not using HA i result lack of experience people architecting exact stack and this may happen in any company size.

I am using a galera cluster and many other people are using multi-disks dataspaces

So you've choose way of implementing HA which by definition has biggest possible penalty on write operations ..
Do you know that it is not only way of have enough HA level in zabbix stack and native zabbix stack architecture has quite high HA build-in?
Do you know that biggest set of obstacles on on building really high performance zabbix server DB backend are related to provide not high read DB operations but in provide high level inserts/update queries speed .. however to pass those obstacles you must first guarantee really low latency red IO operations?

what i am trying to say is that zabbix's schema is lacking enterprise capabilities one of which is partitioning - it is virtually impossible to partition most of the tables in Zabbix's schema

Because .. ? Where are argument supporting such thesis? (these are only rhetorical question)

It seems to me Zabbix team prefer to stay with the small companies and single user rather than to create a product which can be used in an enterprise and distributed architectures

You must known that zabbix already has been tested beyond the scale which you have reached.

It is really hard to make any real comments without looking with what kind of problems you are dealing over answers on the questions which I've gave you.
Dispute it is type of the conversation in which people are using facts and contr facts.
Without those facts it is only and just normal conversation.