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.