-
Problem report
-
Resolution: Fixed
-
Trivial
-
None
-
None
-
None
-
Debian 10.10 -> Debian 11.0
postgresql-11 11.13 and postgresql-13 13.4
zabbix-server-pgsql 5.0.16-1+debian11
Steps to reproduce:
1) We have installed Zabbix server 5.0.14 (LTS) on vanilla Debian 10.10 with Postgres 11 (without timescaled) for about 1+ year
2) Last week we attempted to update to latest Stable: to vanilla Debian 11.0 with Zabbix 5.0.16 - everything was fine.
3) Than we tried to upgrade DB Postgres version from 11 to 13 (via pg_upgrade) - and it started to run...very slow with almost 100% CPU consumption on every cores. In zabbix logs - there were a lot of messages, like
806:20211001:112508.662 slow query: 239.162916 sec, "select distinct itemid from trends_uint where clock>=1633071600 and itemid in (...)" 808:20211001:112510.351 slow query: 234.060737 sec, "select distinct itemid from trends_uint where clock>=1633071600 and itemid in (...)" 804:20211001:112528.155 slow query: 174.753070 sec, "select distinct itemid from trends_uint where clock>=1633071600 and itemid in (...)" 837:20211001:112555.540 slow query: 72.212926 sec, "select t.itemid,t.lastlogsize,t.mtime from item_rtdata t,items i,hosts h where t.itemid=i.itemid and i.hostid=h.hostid and h.proxy_hostid=12484 order by t.itemid" 806:20211001:112603.053 slow query: 53.697721 sec, "select distinct itemid from trends_uint where clock>=1633071600 and itemid in (251665,251702,279260,279261,280751,280803,280809,280815,280821,280827,280863,280869,280875,280881,280887,280923,280929,280935,280941,280947,281167,281173,281179,281185,281221,281227,281233,281239,281245,281281,281287,281293,281299,281305,281341,281347,281353,281359,281365,281401,281407,281413,281419,281425,281782,281784,281786,281790,281986,281987,281998,281999,282001,282002,282003,282004,282006,282007,282018,282019,282021,282022,282023,282024,282025,282026,282027,282028,282030,282031,282032,282033,282034,282035,282037,282038,282039,282040,282047,282049,282050,282051,282052,282053,282054,282055,282056,282057,282058,282059,282060,282061,282063,282064,282065,282066,282067,282068,282069,282070,282081,282082,282127,282128,282129,282130,282131,282132,282133,282134,282135,282136,282139,282140,282142,282143,282145,282146,282147,282148,282149,282150,282151,282153,282154,282155,282166,282167,282168,282169,282170,282171,282172,282173,282174,282175,282176,282177,282178,282179,282180,282181,282182,282183,282184,282185,282186,282187,282188,282189,282190,282191,282192,282193,282194,282195,282196,282197,282198,282199,282200,282201,282202,282203,282204,282205,282206,282207,282208,282209,282210,282211,282212,282213,282214,282215,282216,282217,282218,282219,282220,282221,282222,282223,282224,282225,282226,282227,282228,282229,282230,282231,282232,282233,282234,282235,282236,282237,282267,282268,282269,282270,282271,282272,282273,282274,282275,282276,282277,282278,282279,282280,282281,282282,282283,282284,282285,282286,282287,282288,282289,282290,282291,282292,282293,282294,282295,282296,282297,282298,282299,282300,282301,282302,282303,282304,282305,291632,291662,291663,291664,311367)" 833:20211001:112609.126 slow query: 100.224117 sec, "select t.itemid,t.type,t.snmp_oid,t.hostid,t.key_,t.delay,t.status,t.value_type,t.trapper_hosts,t.logtimefmt,t.params,t.ipmi_sensor,t.authtype,t.username,t.password,t.publickey,t.privatekey,t.flags,t.interfaceid,t.jmx_endpoint,t.master_itemid,t.timeout,t.url,t.query_fields,t.posts,t.status_codes,t.follow_redirects,t.post_type,t.http_proxy,t.headers,t.retrieve_mode,t.request_method,t.output_format,t.ssl_cert_file,t.ssl_key_file,t.ssl_key_password,t.verify_peer,t.verify_host,t.allow_traps from items t,hosts r where t.hostid=r.hostid and r.proxy_hostid=10418 and r.status in (0,1) and t.flags<>2 and t.type in (0,7,20,12,2,3,9,10,11,13,14,16,17,5,19,18) order by t.itemid"
- Changes in configuration...
None, we made configuration for Postgres 13 the same, as for Postgres 11 (except for new values, that we did not change)
Result:
We rolled back to Postgres 11 and everything is back to normal.
Expected:
After we upgraded DB to Postgres 13 Zabbix works the same as with Postgres 11