Too many long running queries in Zabbix PG "SELECT h.* FROM history..."

[g8a]

Hello!
I'm experiencing a lot of long running queries on our zabbix postgresql, and they come from Grafana dashboards (it's the main assumption - actually the DB overload could be caused by Grafana Direct DB Connection bypassing the API and querying history tables directly. Reducing pm.max_children helps with RAM but does not stop the DB flood)

seconds_running | table_name | application_name | query_preview
-----------------+--------------+------------------+--------------------------------------------------------------
57249 | history_uint | | SELECT h.* FROM history_uint h WHERE h.itemid IN (XXXX)
57249 | history | | SELECT h.* FROM history h WHERE h.itemid IN (XXXX)

Tables affected are "history_uint" and "history" as you can see, and this is affecting our environment because Grafana dashboards never load information.


psql (PostgreSQL) 13.23
zabbix_server (Zabbix) 7.0.21

We have tried restarting, scaling the server ram, upgrading the pm.max_children from 50 to 180...

All of these still not affecting and queries will still take too much to run.

Any ideas how to troubleshoot this? this happens since last Azure maintenance (last weekend, they restarted the zabbix db)

Thanks in advance.

[guntis_liepins]

This query is insane - it is not limited in time and size. Something seriously bad and wrong is done with Grafana dashboard.
These queries mean that grafana dashboard for some reason reads ALL history for included items...
You must fix queries for Grafana dashboard.
There are some other options like partitioning , TimescaleDB and reducing size of history tables, but with queries which go multiple times over all history tables - it will not help.

[g8a]

This query is insane - it is not limited in time and size. Something seriously bad and wrong is done with Grafana dashboard.
These queries mean that grafana dashboard for some reason reads ALL history for included items...
You must fix queries for Grafana dashboard.
There are some other options like partitioning , TimescaleDB and reducing size of history tables, but with queries which go multiple times over all history tables - it will not help.

Thanks for your answer.

The problem is we are using zabbix data source, and not the db one, so we can't customize the db query.