[ZBXNEXT-2550] Update MySQL schema to use primary keys for history* tables to allow database partitioning Created: 2014 Oct 28  Updated: 2014 Oct 28  Resolved: 2014 Oct 28

Status: Closed
Project: ZABBIX FEATURE REQUESTS
Component/s: Server (S)
Affects Version/s: None
Fix Version/s: None

Type: Change Request Priority: Minor
Reporter: Asbjorn Kjaer Assignee: Unassigned
Resolution: Won't fix Votes: 0
Labels: database, mysql
Remaining Estimate: Not Specified
Time Spent: Not Specified
Original Estimate: Not Specified


 Description   

With MySQL it is a requirement for partitioning that the field you want to partition on be part of the primary key for the table. The current schema for the history tables uses non-unique indexes on these tables and no primary keys.

Example from database/mysql/schema.sql:
CREATE TABLE `history` (
`itemid` bigint unsigned NOT NULL,
`clock` int NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int NOT NULL DEFAULT '0',
KEY `history_1` (`itemid`,`clock`)
) ENGINE=InnoDB;
CREATE INDEX `history_1` ON `history` (`itemid`,`clock`);

This would need to be changed to be a primary key instead:
CREATE TABLE `history` (
`itemid` bigint unsigned NOT NULL,
`clock` int NOT NULL DEFAULT '0',
`value` double(16,4) NOT NULL DEFAULT '0.0000',
`ns` int NOT NULL DEFAULT '0',
PRIMARY KEY (`itemid`,`clock`)
) ENGINE=InnoDB;

Obviously this would add one new constraint to the table, that any one item can only have a single value per second, but anyone that wants to partition their database to better scale the datastore, would need to accept this constraint regardless (unless you want to include another field into the primary key).



 Comments   
Comment by Asbjorn Kjaer [ 2014 Oct 28 ]

turns out that the assumption you need the field to be part of primary key is only correct if a primary key exists.

Generated at Fri Mar 29 17:17:25 EET 2024 using Jira 9.12.4#9120004-sha1:625303b708afdb767e17cb2838290c41888e9ff0.