Uploaded image for project: 'ZABBIX FEATURE REQUESTS'
  1. ZABBIX FEATURE REQUESTS
  2. ZBXNEXT-2550

Update MySQL schema to use primary keys for history* tables to allow database partitioning

    Details

    • Type: Change Request
    • Status: Closed
    • Priority: Minor
    • Resolution: Won't fix
    • Affects Version/s: None
    • Fix Version/s: None
    • Component/s: Server (S)
    • Labels:

      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).

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              bunjiboys Asbjorn Kjaer
            • Votes:
              0 Vote for this issue
              Watchers:
              1 Start watching this issue

              Dates

              • Created:
                Updated:
                Resolved: