Zabbix server + proxySQL

[wlp2s0]

Hello!

I need an advice.
I'll try my best to describe a problem I'm facing. But it is a tricky one.

I have heavy-load zabbix server with 286 hosts and about 8k checks. Mostly SNMP.
Zabbix server version 4.0 with MySQL database
MySQL version 5.7.23

As amount of checks and hosts increased, MySQL IO load increased dramatically and I got a problem that using web interface becomes a pain. At this point I had mysql slave replication to perform zabbix-full backups because only-configuration backups are not an option. I need history (full database) backups at least once a week.

I decided to test proxySQL with zabbix server installation and configured it to forward "SELECT *" requests to slave replication. I thought that this way I will get rid of lags during WEB interface requests. As I didn't find any information if it is a good idea or not, I decided to give it a try.

Generally, it works fine and much faster. But sometimes zabbix sends for 300+ emails about status change. We have a problem - 150 emails queue created and problem solved - 150 emails, when it should send 6 (3+3).

I can't find a pattern and reason. But I think that zabbix tries to send an email, writes it to master database and checks if email queue was created, using SELECT. But as all SELECT requests go to slave, slave may be doesn't have a queue yet and zabbix server recreates it again. It doesn't happen all the time and it is very tricky to debug it.

The only solution here is to send email-checks SELECT requests to master database. But I need to know the request structure. What table holds email queue and what request is being send there to add it to proxySQL rule table.

But may be is it generally a bad idea to have this configuration?
Please advice!
Thanks

[ingus.vilnis]

Hello and welcome to Zabbix forums!

I would say that this setup might involve risks in case your slave is falling behind the master.

Before using proxySQL did you make sure the MySQL configuration is optimized as much as possible to fit your hardware? The sizing details provided are not that large to suffer from DB performance problems unless the database host is really limited by poor hardware specs.

[a.martin]

Hi,

I just install new zabbix 6 and I wanted to have mariadb redundant using Galera cluster with 2 nodes but I am also using proxysql to be able to route the database which be available in case one is going down. The proxysql is installed in the same VM as zabbix server and I have 2 more VMs for each of db nodes.

I am configuring zabbix server and frontend pointing the proxysql to access the db but I am getting some weird error (Lock wait timeout exceeded; try restarting transaction [delete from problem where r_clock<>0 and r_clock<1650373241]
200838:20220420:150131.334 slow query: 50.001817 sec, "delete from problem where r_clock<>0 and r_clock<1650373241") but if I pointed directly to one of my nodes then I dont get any error.

Could I get some solution how I could configure my proxysql or another better solution to be able to zabbix automatically get the right db node in case one isnt available?

[kloczek]

How much did you give for innodb memory pool?
8k items it is almost nothing. I think that all what you need to do is just increase innodb memory.

[wlp2s0]

I'm so sorry for a very delayed answer.

Thank you for you suggestions.
I moved from MySQL to MariaDB to see if there will be any performance changes. I used the same configuration on MariaDB. Master's IO utilization decreased by 3 times and general performance increased.
Also now I reconfigured zabbix server to send all queues to master and zabbix-frontend to send SELECT to slave.
I've gained a big performance increase using this configuration.

About Items.
Actually I have even less Items now. I cleaned a bit and it is around 6k items and 366 hosts.

My zabbix database is almost 40GB in size right now. May be that will explain a lot.
Zabbix is monitoring broadcast transmitters that have a lot of channel's and sensor's data. And this data is about analog signals mostly. As an example:

"slow query: 3.006735 sec, "insert into history_uint..." of one SNMP OID
And amount of data that zabbix is inserting is about 30KB(in this particular queue). And most of zabbix items return a lot of data.

Also, we have about 6 zabbix-frontend connections all time. Those are dispatchers who are constantly monitoring Dashboards or graphics. And they are generating a lot of SELECTs when they use custom graphics and periods, that's why I think that separating their SELECTs from Master database is a right think to do. Also, strange behavior: the longest period to load is when you try to see data for last two days even compare with last 7 or 30 days.

And at the end about proxySQL with zabbix:
1. It is a bad idea to connect zabbix-servers via proxysql because slave lag will bring you a lot of pain even if you have "max_replication_lag"=1 set in ProxySQL. Zabbix will become confused even with one second difference.
2. In my case it is completely save to send all frontend SELECTs to the slave and I use "max_replication_lag"=50 in my proxysql configuration. And as those SELECTs can be very long and heavy, zabbix server can use master database with no issue.

[max.ch.88]

wlp2s0, does your schema look like on the picture?