Sprint 79 (Aug 2021), Sprint 80 (Sep 2021), Sprint 81 (Oct 2021), Sprint 82 (Nov 2021), Sprint 83 (Dec 2021), Sprint 84 (Jan 2022), Sprint 85 (Feb 2022), Sprint 86 (Mar 2022), Sprint 87 (Apr 2022), Sprint 88 (May 2022), Sprint 89 (Jun 2022), Sprint 90 (Jul 2022), Sprint 91 (Aug 2022), Sprint 92 (Sep 2022), Sprint 93 (Oct 2022)
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.
Minimum database versions required for Zabbix 5.0.0 have been upped to:
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.