Why (for now) you should not be using PostgreSQL as zabbix DB backend?

[kloczek]

Answer on the question is in the presentation https://fosdem.org/2019/schedule/eve...tgresql_fsync/
I must say that in the past I've experienced few times serious PostqreSQL failures and I never had such problems with MySQL.
This presentation as well says that ZFS immune to the discussed case.
I think that zabbix admins should watch this video.

[Alexei]

Interesting! I remember a few cases from my limited personal experience running tons of Zabbix dev environments on VirtualBox VMs when MySQL unrecoverably corrupted InnoDB partition. I never had such issues with different versions of PostgreSQL I also use on a daily basis.

[kloczek]

Interesting! I remember a few cases from my limited personal experience running tons of Zabbix dev environments on VirtualBox VMs when MySQL unrecoverably corrupted InnoDB partition. I never had such issues with different versions of PostgreSQL I also use on a daily basis.

That probably because on Linux innodb_flush_method=O_DIRECT is not default and probably you did not change this :P
https://dev.mysql.com/doc/refman/5.7...db-diskio.html
https://dev.mysql.com/doc/refman/5.7...b_flush_method

VBox passes guest system direct IOs to host system storage so as long in guest direct IO is used it should be no issues with hidden corruptions.
I never had issue with MySQL on Linux because I'm always using innodb_flush_method=O_DIRECT
Funny thing is that on Solaris innodb_flush_method=O_DIRECT is reported as ignored config param

Nevertheless in presentation is clearly explained that probability of the hidden corruption is for now relatively high (in case sudden outage or some errors on storage physical path.

[Alexei]

Thanks for the followup. I think you are right as I tend to have my settings to deliver better performance, not happy about MySQL fsyncing every second or every commit. I cannot verify it now since all my VMs run PostgreSQL nowadays.

[kloczek]

Thanks for the followup. I think you are right as I tend to have my settings to deliver better performance, not happy about MySQL fsyncing every second or every commit. I cannot verify it now since all my VMs run PostgreSQL nowadays.

Remember what I've told you few times that you should try Solaris and ZFS
One time you will try it than you will forget about using Linux for highest demanding permanence Zabbix DB backends
You can do test for free because from release 11.4 GA few months ago it was no significant updates in ZFS code released in monthly released SRUs so with distributed for free to test repo 11.4 GA is possible to evaluate what is really worth.
Only issue is that trying it in VM does not make any sense.

Another video from FOSDEM which you should see is about ARC and ZFS explaining how it works: https://fosdem.org/2019/schedule/event/zfs_caching/
Only watching this keep in mind that this is about ZFS and ARC from OpenZFS and what is in Solaris 11.4 GA has integrated few more (really kicking ass) ARC and L2ARC improvements.
Biggest advantage of the ZFS with compression is using compressed records in ARC which effectively works like using current HW with bigger memory cache multiplied by DB compression ratio.
On zabbix MySQL data even with gzip-1 have 3.6x or more compression ratio is not a gig deal. Such ratio is not possible to reach using tables with compressed rows.
Even bigger compression ratio is possible to reach with Oracle columnar compression. I saw few times compression ratio bigger than 20x on tables with mostly numerical data in columns. I would be not surprised if with Oracle columnar compression will be possible to reach at least 10x ratio.
Remember that even latency of the Intel Optane NVMe which has less than 100 micro seconds read latency you cannot beat even DDR3 which latency is less than 10 but nano seconds.
With biggest databases putting everything on such low latency storage would be waste of money as only fraction of whole capacity needs to be quickly accessible. This issue transparently solves L2ARC which will be holding those data which cannot fit in RAM but still may be quite often used.
With L2ARC on top slow SSDs or even 5.4k rpm spindles is possible to have storage with latency of the L2ARC but with total size of the spindles beneath.
Other thing is that ZFS from Sol 11.4 has improved deduplication so it is possible to combine compression with deduplication which effectively increases physical size of the used storage.
I still had no opportunity to test it after 11.4 GA but I would be not surprised if deduplicartion with zabbix data will give at least .40% if not even 2-3 times. I'm planning now to buy some HW when I would be able to do such tests but I will be able to do those tests probably not faster than in month or two.

Issue is that typical 1U pizza box HW or a bit bigger 2U with one or two CPU sockets has a lot not used CPU time. ZFS allow refine this this not used time to compress/decompress ZFS data., Remember as well that ZFS internally is highly multi threaded so more CPU core you have than probability that even single IO compression/decompression will be scaled across more than one core is higher.
If total size of uncompressed memory will be like few hundreds GB try to thing about price of such HW and compare it with price smaller box with memory 3-5 times smaller. So put this difference in price on one side of the scale and on the other side 500 pounds/year cost of Solaris support. Which case it will be cheaper (not telling that in case of the Solaris you will be using way cheaper spindles type of storage as that one which will be below L2ARC).
In other words: using Linux above some threshold of the performance really sucks and still will suck for very long time (source code of the ARC and L2ARC is available more than decade and sill none of the Linux developers have been able to even read this code with correct level of understanding how it works).