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

Use primary keys for historical tables (PoC)


    • Icon: Change Request Change Request
    • Resolution: Fixed
    • Icon: Trivial Trivial
    • 6.0.0alpha7, 6.0 (plan)
    • None
    • Server (S)
    • None
    • Sprint 80 (Sep 2021), Sprint 81 (Oct 2021), Sprint 82 (Nov 2021), Sprint 83 (Dec 2021), Sprint 84 (Jan 2022), Sprint 85 (Feb 2022)
    • 2

      1. Zabbix must use primary keys for all tables including history* tables
      1. New schema will be used only for Zabbix 6.0 and later installations
        1. Older schema will be used when upgrading to Zabbix 6.0
      2. Existing database queries must be reviewed to take advantage of the primary keys
        1. For example: the function that calculates last value for a given itemid
      3. Zabbix Server syncer processes must nicely handle duplicate data without loss of data in case if a transaction that writes data to history fails
        1. Possible solution: if transaction fails due to "primary key violation" then we may split data into two chunks and retry, then repeat. Alternatively we may try to check for duplicates by selecting data from the database
      4. We must make sure that the new schema works fine with PostgreSQL, MySQL, MariaDB and Oracle and does not create any issues with existing partitioning schemas and TimescaleDB logic
      5. We must research benefits of using primary keys v.s. older schema and document findings at least for MySQL and PostgreSQL: performance gains, saving of disk space
        1. It must be done for a large data set

            dgoloscapov Dmitrijs Goloscapovs
            palivoda Rostislav Palivoda
            Team A
            2 Vote for this issue
            22 Start watching this issue