Tuning API performance?

[jameskirsop]

We've got a couple of calls performed from the Grafana Zabbix plugin via Zabbix's API that are taking a long time to execute and return results:

+--------+---------------+-----------+-------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| Id | User | Host | db | Command | Time | State | Info | Progress |
+--------+---------------+-----------+-------------+---------+------+--------------------------+------------------------------------------------------------------------------------------------------+----------+
| 120069 | zabbix_web | localhost | zabbix_prod | Query | 472 | Sending data | SELECT h.* FROM history_uint h WHERE h.itemid=385160 AND h.clock>=1593525600 AND h.clock<=1611798736 | 0.000 |
| 120071 | zabbix_web | localhost | zabbix_prod | Query | 472 | Sending data | SELECT h.* FROM history_uint h WHERE h.itemid=385040 AND h.clock>=1593525600 AND h.clock<=1611798736 | 0.000 |

These queries are trying to pull data for the last 6 months, but are taking many minutes to execute (making them useless for showing graphs on demand).

What can be done, if anything, to improve the speed at which these results are returned. The grafana dashboard loads a bunch of different data from a single device, and other data for shorter time ranges seems to be returned relatively quickly (as you might expect).

[Zdenek_OMNISENSUIT]

Hello.

You are trying select data from very big table (history_uint).
You not wrote info about database or whole zabbix system.
Anyway, generally you can use partitioning, fine tuning of db/zabbix env.
You can check data from Zabbix env itself monitoring to identify bottleneck.
Or just think about your queries and how database handle them.

-------------------------
omnisensuit.com

[gofree]

in my days with grafana zabbix plugin Ive encountered simillar thing

simply put the performance of the DB comes in place also the number or retrieved item values ( data ) this makes the thing unusable - especially when you have several long term metrics in the graph

things to consider:

- is your DB on SSD storage ? enough CPU ? optimizied? etc....
- the same goes with server where the frontend is installed ( Ive seen high cpu spikes there when the query is performed via API - zabbix frontend ) - solutions could be a separate frontend just for API requests ( especially when the normal is heavily used by humans )
- check possible the network hickups ( will not be a big deal I guess )
- if I remeber correctly there was an option to connect directly to the DB in the zabbix-grafana plugin - maybe you can setup a read only replica of your DB and configure the plugin to obtain data from it - the read/write load should be lower on it compared to live DB so the performance might be better ( never tried is though - so its up to you to investigate this possibility )

[jameskirsop]

in my days with grafana zabbix plugin Ive encountered simillar thing

I'm glad I'm not alone!!

You raise some helpful suggestions gofree. I'm still battling away at trying to optimize mariadb with some performance issues in recent versions. This increasingly seems like a black art, or some form of magic...

Frontend, database and Grafana are all on the same host, and our Grafana clients are usually connected via a gigabit connection to the network that our Zabbix server resides on, so bandwidth shouldn't be a problem.

Have you tried running multiple frontends before? I'm not sure how that would help if the backend database is the source of our problem - but perhaps there's something under the hood that might make this a good option??