Uploaded image for project: 'ZABBIX BUGS AND ISSUES'
  1. ZABBIX BUGS AND ISSUES
  2. ZBX-20041

Postgres 13 with Zabbix server 5 running slower than 11

XMLWordPrintable

    • Icon: Problem report Problem report
    • Resolution: Unresolved
    • Icon: Trivial Trivial
    • None
    • None
    • Server (S)
    • 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"
      
      
      1. 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

            zabbix.support Zabbix Support Team
            konstantin.ilyasov Konstantin Ilyasov
            Votes:
            0 Vote for this issue
            Watchers:
            5 Start watching this issue

              Created:
              Updated: