Original bug report:
ZBXNEXT-3089

Test results

Data was initially sent to the proxy (with server being down), and then total history sync time was collected.

Selects used:

select * from history_uint where clock < 1631836800;
select * from history_uint where clock > 1631836800 and clock < 1631923200;
select * from history_uint where clock > 1631836800 and clock < 1632009600;

Inserts contained exactly the same queries for each db test (resembling normal history data, not random).
Queries were executed without SQL_NO_CACHE.

In case when partitioning was used, tables were partitioned by clock, one partition per day (24 hours / 86400 seconds). Data was resembling normal history data, without itemid-clock-ns duplicates.

Primary key was configured as follows: 

PRIMARY KEY (`itemid`,`clock`,`ns`)

 
Relative differences between same db versions should be taken into account.
 

Available in versions:

• pre-6.0.0alpha6 f98a1dae8e

So if I understand that correctly, when I update our Zabbix server that exists since 2.0, from 5.4 to 6.0, nothing will change, right?

That is right starko, history tables can only be upgraded manually to use primary keys.

What impact does that have for all customers, who upgrade to 6.0 instead of re-installing? I'm no DBA, but if I read this table correctly, it would become a little bit faster, but not tremendously?

(Debian 11 bullseye, MariaDB 10.5, no Partitioning)

starko , Zabbix will not switch to primary keys automatically when upgrading from earlier releases. It should be done manually. If you install a fresh Zabbix 6.0 then the DB schema will have primary keys for historical tables. Depending on DB engine you may expect better performance and lower (up-to 30%) disk usage.

alexei Thank you very much for the clarification!

Last time something fundamentally changed in the DB schema, the switch to new default charset utf8 and collation utf8_bin for MySQL & MariaDB, the Zabbix server database upgrade process didn't touch the schema, but Zabbix company provided a conversion script, via ZBX-17357 which resulted in the following documentation article, which was extremely useful to us: https://www.zabbix.com/documentation/current/manual/appendix/install/db_charset_coll

Will there be a similar conversion script, for customers who want to follow the primary key schema change, after they've upgraded to 6.0 LTS?

starko , I should have mentioned it earlier, the database conversion scripts will be provided for all support DB engines. It will be part of the official documentation and/or release notes.

Wow, that's great news, thank you very much!

@Alexei Very interesting topic! Do you think we can have more details about these numbers:

It seems huge difference for Postgres+TSDB for ingestion time and MariaDB seems more fast for "select" compared to Postgres (without TSDB).

Thanks so much

dimitri.bellini , take these benchmarks with a grain of salt as the configuration of the databases and hardware was different.

@Alexei ok thanks

So what is the current recommendation for operating larger Zabbix installations?
Postgres+TSDB or MySQL?
Why would you run these tests on different hardware/different configuration? To give a bad impression for Postgres+TSDB?

When my colleague saw these results he bashed my for migrating away from MySQL to TimeScaleDB last year with huge pain points (https://support.zabbix.com/browse/ZBX-16347?focusedCommentId=416975&page=com.atlassian.jira.plugin.system.issuetabpanels:comment-tabpanel#comment-416975 ...) .

Sometimes it seems to me your "test environment" while developing consists of monitoring the laptop running your zabbix container and the switch port in front of it.

rstumbaum, I can assure you that we have a whole range of test platforms available starting from beefy bare metal servers to the whole range of AWS/GCP/Azure server instances and K8S clusters. The test we ran here had only one goal, i.e. discover any drawbacks of having primary keys as well as identify possible benefits. These tests were not aimed to benchmarking of the performance of various storage engines.  

There is no silver bullet when it comes to selection of an ideal database. It always depends on your priorities (like speed vs maintainability vs HA options etc).

Available in versions:

• pre-6.0.0beta2 - 0c7c2bc853, 37acc41791,  d8775d8aed, 7654d91f65, 6cf43e9376, 563b2df62b, f98a1dae8e

Documentation updated:

• 3 Database upgrade to primary keys
• Upgrade notes for 6.0.0
• 5 What's new in Zabbix 6.0.0

The primary keys upgrade guide does not mention removing the CSV files.

Also, will this work for users that use partitions? If not, will we provide any help to them?

In 3 Database upgrade to primary keys, section Improving performance,

both external links (MySQL 5.7, MySQL 8.0) are pointing to "Not Found" content.

Also, "3 Database upgrade to primary keys" document has no mention to backup the database first.

markfree, the issues you mentioned have been fixed. Thank you.