Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-18946

better way to apply double.sql patch, as it may be applied *instantly* on newer MySQL/MariaDB versions

    XMLWordPrintable

Details

    • Team A
    • Sprint 79 (Aug 2021), Sprint 80 (Sep 2021), Sprint 81 (Oct 2021), Sprint 82 (Nov 2021)
    • 1

    Description

      Some data from zabbix devs (actual person/source is unknown):

      MySQL timing on changing floats from DOUBLE(16,4) to DOUBLE for history and trends tables, having 1M random rows each:

      mysql v5.0.15, win32: history: 38.9s, trends: 6.2s
      mysql v5.1.30, win32: history: 105.4s, trends: 8.0s
      mysql v5.5.8, win32: history: 11.3s, trends: 4.4s
      mysql v5.6.10, win32: history: 0.0063s, trends: 0.0069s
      mysql v5.7.9, win32: history: 0.0073s, trends: 0.0073s
      mysql v8.0.11, win64: history: 0.0082s, trends: 0.0075s
      mysql v8.0.17, win64: history: 0.0084s, trends: 0.0074s

      Data from an experienced zabbix user:

      Apparently related to 5.6 changing to INPLACE table alterations, whenever possible.
      Quoting from https://dev.mysql.com/doc/refman/5.6/en/alter-table.html:

      ALTER TABLE operations that use the INPLACE algorithm include:
      ...
      Operations that only modify table metadata. These operations are immediate because the server only alters the table .frm file, not touch table contents.
      Metadata-only operations include:
      ...
      Changing the default value of a column

      There's a limitation:

      As of MySQL 5.6.16, ALTER TABLE upgrades MySQL 5.5 temporal columns to 5.6 format for ADD COLUMN, CHANGE COLUMN, MODIFY COLUMN, ADD INDEX, and FORCE operations. This conversion cannot be done using the INPLACE algorithm
      ...

      This shouldn't affect the history and trends tables, as they do not use temporal columns.

      For MariaDB, this apparently appeared in 10.0.
      Quoting from https://mariadb.com/kb/en/alter-table/#algorithm :

      In MariaDB 5.5 and before, ALTER TABLE operations required making a temporary copy of the table, which can be slow for large tables.

      In MariaDB 10.0 and later, the ALTER TABLE statement supports the ALGORITHM clause.

      There might be specific before 10.0.11/12, from https://mariadb.com/kb/en/alter-table/#alter-online-table :

      In MariaDB 10.0.12 and later, this statement is equivalent to the following:
      ALTER TABLE ... LOCK=NONE;

      In MariaDB 10.0.11, this statement is equivalent to the following:
      ALTER TABLE ... ALGORITHM=INPLACE;

      But... MariaDB docs do not seem to match the observed effect on MariaDB 5.5.

      Looking at https://www.zabbix.com/documentation/5.0/manual/installation/upgrade_notes_500#minimum_required_database_versions :

      Minimum database versions required for Zabbix 5.0.0 have been upped to:
      MySQL 5.5.62
      MariaDB 10.0.37
      PostgreSQL 9.2.24
      Oracle 11.2

      Would it be feasible for Zabbix server to detect MySQL/MariaDB version and opt to upgrade the history and trends tables during the automatic process?

      Or at lest document it explicitly, that it's not aways takes a lot of time.
      https://www.zabbix.com/documentation/5.0/manual/installation/upgrade_notes_500#enabling_extended_range_of_numeric_float_values

      Attachments

        Activity

          People

            martins-v Martins Valkovskis
            zalex_ua Oleksii Zagorskyi
            Votes:
            0 Vote for this issue
            Watchers:
            3 Start watching this issue

            Dates

              Created:
              Updated: