Clustered mysql

[ari]

Is there a way we can add multiple mysql database hosts to DBHost for the Zabbix server configuration?

I don't mind if it is loadbalancing (each request to a different server) or just failover (fail to the second node if the first stops responding).

Alternatively a not as good, but OK solution might be to intercept the event Zabbix throws when it can't connect to the database and cause that to rewrite the connection to the second database. Maybe we can do that with monit. Has anyone tried?

[Garth]

Is there a way we can add multiple mysql database hosts to DBHost for the Zabbix server configuration?

I don't mind if it is loadbalancing (each request to a different server) or just failover (fail to the second node if the first stops responding).

Alternatively a not as good, but OK solution might be to intercept the event Zabbix throws when it can't connect to the database and cause that to rewrite the connection to the second database. Maybe we can do that with monit. Has anyone tried?

Sounds like you want Mysql/MariaDB master-master replication with a vip (pacemaker/corosync) to run checks and move the vip if the primary server dies. Then point the zabbix server and web bits to the vip you create.

Multi master databases are difficult to get right, and something like database proxy might help. But they generally only really improve read performance, with only a minimum improvement in writes.

If you are worried about db performance, you could also look at partitioning the table

https://www.zabbix.org/wiki/Docs/howto/mysql_partition

I'm currently evaluating Zabbix and have gone with the master-master replication and vip approach. To start with, the database element will be virtual machines, but I expect iops will become and issue down the line, so partitioning the database and moving to real hardware might be needed.

[kloczek]

Multi master databases are difficult to get right, and something like database proxy might help. But they generally only really improve read performance, with only a minimum improvement in writes.

This improvement is possible only by only fact that you may have a bit higher probability of hitting data for selects which are cached in memory of one of the nodes or read IO busy first backed will be still able to do the same IOs on second node.
Proper way of improving read latency i just by add enough memory to cache MRU/FRU data. Usually as base factor how much memory is needed could be used size of the monitoring data stored in 12-24h. If history* tables are partitioned as good estimations could be used size of the last day partitions.

In case of using master-master definitely write speed will be lower as it will be required setup with full sync of written data across all nodes.

Master-master setup is only good up to some threshold of DB traffic (as technology simplifying DB backend maintenance) above which will be only possible to use master-slave setup with promote slave as new master and restart zabbix server process. In such scenarios as all possible monitoring will be done over proxies (everything except internal zabbix server metrics). In such cases fact that slave may be a bit behind master can be ignored as zabbix server would be able to fiil those gaps out of data stored on proxies.

In other words: there is no magik would and always is some cost. If someone is choosing master-master with this will come some benefits but always will be some costs in form of performance.

Another drawback of using MySQL master-master setup is lack of possibility use MySQL transaction-isolation=READ-COMMITTED semantics which allows still read history/trends data when for example hosts and templates are modified/added/deleted.
The same problem is with use as zabbix dB backend AWS Aurora which ignores MySQL transaction-isolation=READ-COMMITTED
I suppose many people which are using large scale AWS envs with many hosts added and deleted on demand with autoscaling when as zabbix DB backend is used MySQL Aurora instance.

[Rodney]

Hi,

What do you mean "add enough memory to cache MRU/FRU data"?

Is it on Zabbix Server or MySQL?

Which parameters?

Thanks.

[kloczek]

What do you mean "add enough memory to cache MRU/FRU data"?

Is it on Zabbix Server or MySQL?

MRU -> Most Recently Used
MFU -> Most Frequently Used

These are two basic classes of the caching algorithms/strategies used by many applications.
MySQL uses both to minimize in memory buffers loses/hits ration (lose is causing read IO from the storage).