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

XMLWordPrintable

    • Icon: Change Request Change Request
    • Resolution: Won't fix
    • Icon: Minor Minor
    • None
    • None
    • Server (S)

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

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

              Created:
              Updated:
              Resolved: