ZABBIX Forums  
  #1  
Old 20-09-2017, 17:34
rsterenb rsterenb is offline
Junior Member
 
Join Date: Apr 2015
Posts: 17
Default Zabbix MySQL partitioning

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:
Code:
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:
Code:
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?
Reply With Quote
  #2  
Old 20-09-2017, 18:49
vesper1978 vesper1978 is offline
Junior Member
 
Join Date: Nov 2016
Posts: 24
Default

The wiki on www.zabbix.org is community driven. If you'd like to make changes, feel free to sign up and contribute.
Reply With Quote
  #3  
Old 20-09-2017, 21:45
rsterenb rsterenb is offline
Junior Member
 
Join Date: Apr 2015
Posts: 17
Default

I just tried to create an account, twice, but each time I get the reply: "The authentication plugin denied the account creation."
Reply With Quote
  #4  
Old 21-09-2017, 13:10
kloczek kloczek is offline
Senior Member
 
Join Date: Jun 2006
Location: UK/London
Posts: 872
Default

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
Reply With Quote
  #5  
Old 06-12-2017, 16:48
pnenov pnenov is offline
Junior Member
 
Join Date: Dec 2017
Posts: 6
Default 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
Reply With Quote
  #6  
Old 06-12-2017, 20:47
kloczek kloczek is offline
Senior Member
 
Join Date: Jun 2006
Location: UK/London
Posts: 872
Default

Quote:
Originally Posted by pnenov View Post
[..]
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.
Reply With Quote
  #7  
Old 06-12-2017, 21:47
pnenov pnenov is offline
Junior Member
 
Join Date: Dec 2017
Posts: 6
Default

Quote:
Originally Posted by kloczek View Post
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
Reply With Quote
  #8  
Old 06-12-2017, 21:59
kloczek kloczek is offline
Senior Member
 
Join Date: Jun 2006
Location: UK/London
Posts: 872
Default

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.

Quote:
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.

Last edited by kloczek; 06-12-2017 at 22:10.
Reply With Quote
  #9  
Old 06-12-2017, 22:31
pnenov pnenov is offline
Junior Member
 
Join Date: Dec 2017
Posts: 6
Default

Quote:
Originally Posted by kloczek View Post
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)

Last edited by pnenov; 06-12-2017 at 22:44.
Reply With Quote
  #10  
Old 06-12-2017, 22:59
kloczek kloczek is offline
Senior Member
 
Join Date: Jun 2006
Location: UK/London
Posts: 872
Default

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.

Last edited by kloczek; 06-12-2017 at 23:19.
Reply With Quote
Reply

Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump


All times are GMT +2. The time now is 09:20.