Heavy database queries from Web interface read operations

[stefano_775]

Hi everyone!

Our Zabbix 6.0.18 causes heavy database load when users navigate on a page which returns many results such as monitoring>hosts.

Some of the queries last minutes, clog several CPUs to 100% and continue even if the users navigate away from Zabbix frontend or click on another menu item such as reports>system info.

Changing the number of results in the user's settings makes no difference, and users have reasonable (<200) results per page anyway. This is often triggered by loosely filtered queries such as selecting all problems and a hostgroup with many hosts.
If users, by mistake, remove all filters the page times out, the submenu page (for example monitoring>hosts) becomes unavailable to them even after logging out and back in. Other submenu pages remain available. The only solution in such cases is to ask a colleague to send a link to a filtered view, which restores access to the specific unavailable submenu.

Htop shows all 20CPUs running at 95-100% and the SELECT and UPDATE queries running for a long time and causing the 100% CPU utilisation. Screenshot attached.
In some cases the only way to restore has been to restart the database, in other cases they eventually finish and CPU utilisation returns normal.

Looking at pg_top we have gathered part of one of the queries which was running for 20+ minutes:

SELECT f.triggerid,i.hostid FROM functions f,items i WHERE (f.triggerid IN (7849,7850,7851,7852,7856,7857,7863,7864,7866,7867 ,7869,7871,7872,7873,7875,7876,7877,7878,7879,7881 ,7883,7885,7889,7891,7892,7894,7895,7896,7898,7899 ,7900,7902,7904,7907,7908,7912,7913,7915,7917,7920 ,7930,7931,7932,7933,7934,7935,7937,7938,7940,7941 ,7943,7944,7945,7947,7949,7951,7953,7955,7958,7960 ,7963,7964,7966,7967,7969,7976,7980,7983,7984,7986 ,7987,7988,7989,7990,7991,7992,7993,7997,7999,8000 ,8001,8002,8005,8006,8007,8008,8010,8012,8014,8018 ,8019,8021,44186,44192,44200,44201,44202,44203,442 04,44205,44207,44208,44209,44210,44211,44212,66698 ,66699,66702,66704,66705,66710,66712,67619,74766,7 4768,74798,74800,74811,74812,74813,74818,74819,748 20,92470,92472,92473,92475,92476,92478,92479,92481 ,92482,92483,92484,92485,92486,92487,92491,92492,9 2493,92494,92495,92496,92497,92498,92499,92506,925 07,92508,92510,92511,92512,92513,92514,92515,92516 ,92517,92521,92522,92523,92527,92528,92529,92531,9 2532,92534,92535,92539,92540,92541,92542,92543,9

Please note the query above is cut from pg_top and was not available in full.

We are running 6.0.18 on frontends and servers (which run on different VMs), postgresql 14.8 with timeseries and ubuntu 22.04 for all VMs.

Zabbix is configured with ~40K hosts and 2 mil items.

Any known issue this might be related to? And any suggestion on what to do next?

Thanks

Stefano

[stefano_775]

bumping

[MRedbourne]

I'm assuming your Zabbix server split it's architecture into multiple VMs? Eg: VM to handle the webservice, several VMs for item processing (active agent), and a set of servers for the DBs?

I can't speak to everyone here - but that is a MASSIVE Zabbix environment. I would strongly suggest posting in this subforum (Zabbix for Large Environments - ZABBIX Forums) and asking for help there. This is outside the scope I suspect of what most people in this subforum know how to do.

I would also advise speaking with a DBA on your side. Again, that's a very large server. You can also look at running phpMyAdmin's Advisor Panel (and: Help with building a Zabbix Environment - ZABBIX Forums)

[stefano_775]

Thanks MRedbourne

I don't consider it a big environment (~40K hosts), but it has been built as HA for frontends, servers and databases, so I see where you are coming from in regards to the subforum.

The database is happy with the day-to-day load, in fact its load is very low, and the database VMs themselves have access to large CPU/memory/diskIO resources. What we are seeing are queries coming from the frontend which cause massive load every now and then, which I think could be due to a bug on the frontend. Zabbix should not make queries which a healthy DB take 20 minutes to complete, irrespectively of what the user selects in the frontend.

I will investigate more and try to post in the subforum you pointed me to. Thanks for your help!

[cyber]

Not that it really helps, but I do have same version and similar setup.. (less hosts) but no issues whatsoever. Something like "monitoring->hosts" without any filters returns data very quickly...

The query seems to be this one https://git.zabbix.com/projects/ZBX/...al.php#773-776
quite general one...

Maybe it is worth checking if DB config has enough memory allowed for queries or it starts to swap to disk... I think work_mem was it ? times number of connections... may end up eating all the memory etc...

[stefano_775]

Coming back to this post as I have noticed that every time the issue appears we see a massive spike in tuples readings/second from the database. Today it peaked at over 1.2 billion tuples/sec, which seems insanely high considering there wasn't anything out of the ordinary being done: just normal queries on the web interface to find hosts down etc.

I looked at the queries running while the load was high, using the following query:
```
SELECT now() - xact_start AS transaction_time, query
FROM pg_stat_activity
WHERE state IN ('idle in transaction','active')
ORDER BY 1 DESC;
```

The queries which had been running the longest looked normal and similar to the one in my last post (oldest at the bottom, newest at the top):

```
2389080 | 00:00:01.944304 | zabbix | commit;
2389066 | 00:00:01.965343 | zabbix | commit;
2388809 | 00:00:01.978251 | zabbix | select itemtagid,itemid,tag,value from item_tag
2389083 | 00:00:02.14259 | zabbix | commit;
2389091 | 00:00:02.167382 | zabbix | commit;
2389060 | 00:00:02.175863 | zabbix | commit;
2389095 | 00:00:02.22195 | zabbix | commit;
2389087 | 00:00:02.234568 | zabbix | commit;
2389086 | 00:00:02.318048 | zabbix | commit;
2389084 | 00:00:02.328528 | zabbix | commit;
2389073 | 00:00:02.377546 | zabbix | commit;
2389078 | 00:00:02.379105 | zabbix | commit;
2456939 | 00:00:02.491502 | zabbix | commit;
2389081 | 00:00:03.178814 | zabbix | commit;
2389079 | 00:00:03.375915 | zabbix | commit;
2389098 | 00:00:03.376255 | zabbix | select taskid,type,clock,ttl from task where status in (1,2) order by taskid
2389092 | 00:00:03.376591 | zabbix | commit;
2389090 | 00:00:03.386119 | zabbix | commit;
2389064 | 00:00:03.516234 | zabbix | select min(t.nextcheck) from httptest t,hosts h where t.hostid=h.hostid and mod(t.httptestid,1)=0 and t.status=0 and h.proxy_hostid is null and h.status=0 and (h.maintenance_status=0 or h.maintenance_type=0)
2389157 | 00:00:04.186233 | zabbix | commit;
2389085 | 00:00:04.375956 | zabbix | commit;
2389162 | 00:00:04.51464 | zabbix | select lastaccess from hosts where host='sdav' and status in (5,6)
2389099 | 00:00:05.376398 | zabbix | commit;
2389113 | 00:00:06.530881 | zabbix | select i.itemid,i.hostid from items i where i.key_='icmpping' and 1=0
2389056 | 00:00:21.985748 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=4
2389159 | 00:00:22.05464 | zabbix | select eventid from problem where source=0 and object=0 and not exists (select NULL from triggers where triggerid=objectid)
2389069 | 00:00:24.117458 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=3
2389070 | 00:00:24.165618 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=7
2389067 | 00:00:24.345687 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=1
2389071 | 00:00:24.918015 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=6
2389094 | 00:00:25.313199 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=9
2389059 | 00:00:25.357744 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=8
2389075 | 00:00:26.185855 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=2
2389074 | 00:00:26.480259 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=5
2389065 | 00:00:26.717688 | zabbix | select count(*),min(nextcheck) from drules where status=0 and mod(druleid,10)=0
2389082 | 00:00:28.460615 | zabbix | commit;
2389160 | 00:00:36.974536 | zabbix | select i.itemid,i.hostid from items i where i.key_='icmpping' and 1=0
2990333 | 00:00:55.20545 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565387' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990331 | 00:00:55.251388 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565362' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990328 | 00:00:55.257963 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565357' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990329 | 00:00:55.26555 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565360' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990332 | 00:00:55.26608 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565382' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990330 | 00:00:55.26647 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565366' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990324 | 00:00:55.291421 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565365' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990322 | 00:00:55.297091 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565358' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990327 | 00:00:55.314667 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565359' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990325 | 00:00:55.317349 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565363' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990326 | 00:00:55.317632 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565361' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990323 | 00:00:55.317648 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565364' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990319 | 00:00:55.31896 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565355' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990321 | 00:00:55.330015 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565354' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990318 | 00:00:55.330438 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565353' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2990320 | 00:00:55.332772 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565356' AND clock>='1696733576' AND clock<='1697471193' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+523141,737617)/(737617),0)
2989079 | 00:00:59.003762 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565448' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989126 | 00:00:59.870789 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565495' AND clock>='1696733576' AND clock<='1697471093' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0)
2989077 | 00:01:00.099041 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565468' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989088 | 00:01:00.15016 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565483' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989110 | 00:01:00.516643 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565475' AND clock>='1696733576' AND clock<='1697471093' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0)
2989114 | 00:01:00.74205 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565500' AND clock>='1696733576' AND clock<='1697471093' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0)
2989109 | 00:01:01.442948 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565474' AND clock>='1696733576' AND clock<='1697471093' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0)
2989072 | 00:01:01.671294 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565469' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989058 | 00:01:01.93728 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565465' AND clock>='1696733576' AND clock<='1697471091' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0)
2989075 | 00:01:02.610008 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565470' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989073 | 00:01:02.662541 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565445' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989116 | 00:01:02.714187 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565496' AND clock>='1696733576' AND clock<='1697471093' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0)
2989084 | 00:01:02.825151 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565479' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989045 | 00:01:02.852391 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565442' AND clock>='1696733576' AND clock<='1697471091' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0)
2989089 | 00:01:03.234053 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565471' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989076 | 00:01:03.380099 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565450' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989068 | 00:01:03.384589 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565488' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989051 | 00:01:03.498961 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565444' AND clock>='1696733576' AND clock<='1697471091' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0)
2989025 | 00:01:03.778975 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+283837,737513)/(737513),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565432' AND clock>='1696733576' AND clock<='1697471089' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+283837,737513)/(737513),0)
2989049 | 00:01:03.870965 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565485' AND clock>='1696733576' AND clock<='1697471091' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0)
2989083 | 00:01:03.876942 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565449' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989060 | 00:01:03.878962 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565464' AND clock>='1696733576' AND clock<='1697471091' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0)
2989005 | 00:01:03.886956 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565431' AND clock>='1696733576' AND clock<='1697471086' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0)
2989004 | 00:01:03.902961 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565430' AND clock>='1696733576' AND clock<='1697471086' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0)
2989091 | 00:01:03.91049 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565472' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989002 | 00:01:03.91098 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565429' AND clock>='1696733576' AND clock<='1697471086' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0)
2989012 | 00:01:03.910984 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+279235,737511)/(737511),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565439' AND clock>='1696733576' AND clock<='1697471087' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+279235,737511)/(737511),0)
2989033 | 00:01:03.910985 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+286138,737514)/(737514),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565440' AND clock>='1696733576' AND clock<='1697471090' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+286138,737514)/(737514),0)
2989057 | 00:01:03.910988 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565487' AND clock>='1696733576' AND clock<='1697471091' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0)
2989048 | 00:01:03.910993 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565477' AND clock>='1696733576' AND clock<='1697471091' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0)
2989026 | 00:01:03.910996 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+283837,737513)/(737513),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565453' AND clock>='1696733576' AND clock<='1697471089' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+283837,737513)/(737513),0)
2989003 | 00:01:03.910997 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565428' AND clock>='1696733576' AND clock<='1697471086' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0)
2989086 | 00:01:03.914555 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565446' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989117 | 00:01:03.914991 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565493' AND clock>='1696733576' AND clock<='1697471093' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+293041,737517)/(737517),0)
2989034 | 00:01:03.914994 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+286138,737514)/(737514),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565458' AND clock>='1696733576' AND clock<='1697471090' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+286138,737514)/(737514),0)
2989071 | 00:01:03.918852 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565466' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989037 | 00:01:03.918997 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+286138,737514)/(737514),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565452' AND clock>='1696733576' AND clock<='1697471090' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+286138,737514)/(737514),0)
2989047 | 00:01:03.919511 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565478' AND clock>='1696733576' AND clock<='1697471091' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0)
2989019 | 00:01:03.919526 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+281536,737512)/(737512),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565435' AND clock>='1696733576' AND clock<='1697471088' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+281536,737512)/(737512),0)
2989007 | 00:01:03.919794 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565438' AND clock>='1696733576' AND clock<='1697471086' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+276934,737510)/(737510),0)
2989078 | 00:01:03.919883 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565461' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989069 | 00:01:03.919938 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565489' AND clock>='1696733576' AND clock<='1697471092' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+290740,737516)/(737516),0)
2989020 | 00:01:03.91995 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+281536,737512)/(737512),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565436' AND clock>='1696733576' AND clock<='1697471088' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+281536,737512)/(737512),0)
2989059 | 00:01:03.9201 | zabbix | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565486' AND clock>='1696733576' AND clock<='1697471091' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+288439,737515)/(737515),0)
```

The database has over 80G of free memory (apologies cyber, thought I responded to your last post but didn't) and the connections to the DB are a problem: when this happens al 200 configured get utilised, but I believe this is a symptom rather than a cause?

Attached screenshots of the tuples/sec and CPU utilisation.

Thanks!

[stefano_775]

Something else I noticed, which is probably relevant, is that when the CPU load goes to 100% and the tuples/sec are in the billions, there is a large increase in access share locks on the database.

We get to ~ 500 locks.

I looked at the queries causing it, and the attached file shows the output.

Pasting a couple of entries here too, they all look similar anyway:

........
849732 | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565354' AND clock>='1697122641' AND clock<='1697727441' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) | AccessShareLock | trends_uint |
849732 | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565354' AND clock>='1697122641' AND clock<='1697727441' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) | AccessShareLock | trends_uint_pkey |
849732 | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565354' AND clock>='1697122641' AND clock<='1697727441' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) | AccessShareLock | trends_uint_clock_idx |
849732 | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565354' AND clock>='1697122641' AND clock<='1697727441' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) | AccessShareLock | _hyper_7_5821_chunk |
849732 | SELECT itemid,SUM(num) AS count,AVG(value_avg) AS avg,MIN(value_min) AS min,MAX(value_max) AS max,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) AS i,MAX(clock) AS clock FROM trends_uint WHERE itemid='5565354' AND clock>='1697122641' AND clock<='1697727441' GROUP BY itemid,round(2232* MOD(CAST(clock AS BIGINT)+550959,604800)/(604800),0) | AccessShareLock | 5821_2940_trends_uint_pkey |
.......

Are access share locks healthy? I looked at the item ids and it's all standard graphs that a colleague was going through, but he wasn't doing anything abnormal. Just not sure why the reading of the data would cause the access share locks.

Thanks

Stefano