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

Long SQL request in Latest data

    XMLWordPrintable

Details

    • Problem report
    • Status: Open
    • Trivial
    • Resolution: Unresolved
    • 5.0.9
    • None
    • Frontend (F)
    • Debian 10.8
      Zabbix 5.0.9 from repo.zabbix.com repository
      PostgreSQL 13.2 from apt.postgresql.org repository

    Description

      Steps to reproduce:

      1. I open the Latest data page in web interface without filters. This happens very quickly.
      2. Then I select a specific host with a large number of ports and this page takes a very long time to open.
      3. I turned on debug mode and I see a long request. This is only one.
      4. I tried to change this and then this request runs quickly.

       

      ******************** Script profiler ********************
      Total time: 17.032669
      Total SQL time: 16.992388
      SQL count: 102 (selects: 91 | executes: 23)
      Peak memory usage: 12M
      Memory limit: 128M
      ...
      SQL (16.486092): SELECT itemid FROM history_uint WHERE (itemid IN (41876,41888,41889,41926,41927,41928,41929,41932,41933,41936,41937,41938,41939,41940,41941,41942,41943,41944,41945,41963,41964,41965,41966,41967,41968,41969,41970,41971,41972,41973,41974,41975,41976,41977,41978,41979,41980,41981,41982,41983,41984,41985,41986,41987,41988,41989,41990,41991,41992,41993,41994,41995,41996,41997,41998,41999,42000,42001,42002,42003,42004,42005,42006,42007,42008,42009,42010,42011,42012,42013,42014,42015,42016,42017,42018,42019,42020,42021,42022,42023,42024,42025,42026,42027,42028,42029,42030,42031,42032,42033,42034,42035,42036,42037,42038,42039,42040,42041,42042,42043,42044,42045,42046,42047,42048,42049,42050,42051,42052,42053,42054,42055,42056,42057,42058,42059,42060,42061,42062,42063,42064,42065,42066,42067,42068,42069,42070,42071,42072,42073,42074,42075,42076,42077,42078,42079,42080,42081,42082,42083,42084,42085,42086,42087,42088,42089,42090,42091,42092,42093,42094,42095,42096,42097,42098,42099,42100,42101,42102,42103,42104,42105,42106,42107,42108,42109,42110,42111,42112,42113,42114,42115,42116,42117,42118,42119,42120,42121,42122,42123,42124,42125,42126,42127,42128,42129,42130,42131,42132,42133,42134,42135,42136,42137,42138,42139,42140,42141,42142,42143,42144,42145,42146,42147,42148,42149,42150,42151,42152,42153,42154,42155,42156,42157,42158,42159,42160,42161,42162,42163,42164,42165,42166,42167,42168,42169,42170,42171,42172,42173,42174,42175,42176,42177,42178,42179,42180,42181,42182,42183,42184,42185,42186,42187,42188,42189,42190,42191,42192,42193,42194,42195,42196,42197,42198,42199,42200,42201,42202,42203,42204,42205,42206,42207,42208,42209,42210,42211,42212,42213,42214,42215,42216,42217,42218,42219,42220,42221,42222,42223,42224,42225,42226,42227,42228,42229,42230,42231,42232,42233,42234,42235,42236,42237,42238,42239,42240,42241,42242,42243,42244,42245,42246,42247,42248,42249,42250,42251,42252,42253,42254,42255,42256,42257,42258,42259,42260,42261,42262,42263,42264,42265,42266,42267,42268,42269,42270,42271,42272,42273,42274,42275,42276,42277,42278,42279,42280,42281,42282,42283,42284,42285,42286,42287,42288,42289,42290,42291,42292,42293,42294,42295,42296,42297,42298,42299,42300,42301,42302,42303,42304,42305,42306,42307,42308,42309,42310,42311,42312,42313,42314,42315,42316,42317,42318,42319,42320,42321,42322,42323,42324,42325,42326,42327,42328,42329,42330,42331,42332,42333,42334,42335,42336,42337,42338,42339,42340,42341,42342,42343,42344,42345,42346,42347,42348,42349,42350,42351,42352,42353,42354,42355,42356,42357,42358,42359,42360,42361,42362,42363,42364,42365,42366,42367,42368,42369,42370,42371,42372,42373,42374,42375,42376,42377,42378,42379,42380,42381,42382,42383,42384,42385,42386,42387,42388,42389,42390,42391,42392,42393,42394,42395,42396,42397,42398,42399,42400,42401,42402,42403,42404,42405,42406,42407,42408,42409,42410,42411,42412,42413,42414,42415,42416,42417,42418,42419,42420,42421,42422,42423,42424,42425,42426,42427,42428,42429,42430,42431,42432,42433,42434,42435,42436,42437,42438,42439,42440,42441,42442,42443,42444,42445,42446,42447,42448,42449,42450,42451,42452,42453,42454,42455,42456,42457,42458,42459,42460,42461,42462,42463,42464,42465,42466,42467,42468,42469,42470,42471,42472,42473,42474,42475,42476,42477,42478,42479,42480,42481,42482,42483,42484,42485,42486,42487,42488,42489,42490,42491,42492,42493,42494,42495,42496,42497,42498,42499,42500,42501,42502,42503,42504,42505,42506,42507,42508,42509,42510,42511,42512,42513,42514,42515,42516,42517,42518,42519,42520,42521,42522,42523,42524,42525,42526,42527,42528,42529,42530,42531,42532,42533,42534,42535,42536,42537,42538,42539,42540,42541,42542,42543,42544,42545,42546,42547,42548,42549,42550,42551,42552,42553,42554,42555,42556,42557,42558,42559,42560,42561,42562,42563,42564,42565,42566,42567,42568,42569,42570,42571,42572,42573,42574,42575,42576,42577,42578,42579,42580,42581,42582,42583,42584,42585,42586,42587,42588,42589,42590,42591,42592,42593,42594,42595,42596,42597,42598,42599,42600,42601,42602,42603,42604,42605,42606,42607,42608,42609,42610,42611,42612,42613,42614,42615,42616,42617,42618,42619,42620,42621,42622,42623,42624,42625,42626,42627,42628,42629,42630,42631,42632,42633,42634,42635,42636,42637,42638,42639,42640,42641,42642,42643,42644,42645,42646,42647,42648,42649,42650,42651,42652,42653,42654,42655,42656,42657,42658,42659,42660,42661,42662,42663,42664,42665,42666,42667,42668,42669,42670,42671,42672,42673,42674,42675,42676,42677,42678,42679,42680,42681,42682,42683,42684,42685,42686,42687,42688,42689,42690,42691,42692,42693,42694,42695,42696,42697,42698,42699,42700,42701,42702,42703,42704,42705,42706,42707,42708,42709,42710,42711,42712,42713,42714,42715,42716,42717,42718,42719,42720,42721,42722,42723,42724,42725,42726,42727,42728,42729,42730,42731,42732,42733,42734,42735,42736,42737,42738,42739,42740,42741,42742,42743,42744,42745,42746,42747,42748,42749,42750,42751,42752,42753,42754,42755,42756,42757,42758,42759,42760,42761,42762,42763,42764,42765,42766,42767,42768,42769,42770,42771,42772,42773,42774,42775,42776,42777,42778,42779,42780,42781,42782,42783,42784,42785,42786,42787,42788,42789,42790,42791,42792,42793,42794,42795,42796,42797,42798,42799,42800,42801,42802,42803,42804,42805,42806,42807,42808,42809,42810,42811,42812,42813,42814,42815,42816,42817,42818,42819,42820,42821,42822,42823,42824,42825,42826,42827,42828,42829,42830,42831,42832,42833,42834,42835,42836,42837,42838,42839,42840,42841,42842,42843,42844,42845,42846,42847,42848,42849,42850,42851,42852,42853,42854,42855,42856,42857,42858,42859,42860,42861,42862,42863,42864,42865,42866,42867,42868,42869,42870,42871,42872,42873,42874,42875,42876,42877,42878,42879,42880,42881,42882,42883,42884,42885,42886,42887,42888,42889,42890,42891,42892,42893) OR itemid IN (42894,42895,42896,42897,42898,42899,42900,42901,42902,42903,42904,42905,42906,42907,42908,42909,42910,42911,42912,42913,42914,42915,42916,42917,42918,42919,42920,42921,42922,42923,42924,42925,42926,42927,42928,42929,42930,42931,42932,42933,42934,42935,42936,42937,42938,42939,42940,42941,42942,42943,42944,42945,42946,42947,42948,42949,42950,42951,42952,42953,42954,42955,42956,42957,42958,42959,42960,42961,42962,42963,42964,42965,42966,42967,42968,42969,42970,42971,42972,42973,42974,42975,42976,42977,42978,42979,42980,42981,42982,42983,42984,42985,42986,42987,42988,42989,42990,42991,42992,42993,42994,42995,42996,42997,42998,42999,43000,43001,43002,43003,43004,43005,43006,43007,43008,43009,43010,43011,43012,43013,43014,43015,43016,43017,43018,43019,43020,43021,43022,43023,43024,43025,43026,43027,43028,43029,43030,43031,43032,43033,43034,43035,43036,43037,43038,43039,43040,43041,43042,43043,43044,43045,43046,43047,43048,43049,43050,43051,43052,43053,43054,43055,43056,43057,43058,43059,43060,43061,43062,43063,43064,43065,43066,43067,43068,43069,43070,43071,43072,43073,43074,43075,43076,43077,43078,43079,43080,43081,43082,43083,43084,43085,43086,43087,43088,43089,43090,43091,43092,43093,43094,43095,43096,43097,43098,43099,43100,43101,43102,43103,43104,43105,43106,43107,43108,43109,43110,43111,43112,43113,43114,43115,43116,43117,43118,43119,43120,43121,43122,43123,43124,43125,43126,43127,43128,43129,43130,43131,43132,43133,43134,43135,43136,43137,43138,43139,43140,43141,43142,43143,43144,43145,43146,43147,43148,43149,43150,43151,43152,43153,43154,43155,43156,43157,43158,43159,43160,43161,43162,43163,43164,43165,43166,43167,43168,43169,43170,43171,43172,43173,43174,43175,43176,43177,43178,43179,43180,43181,43182,43183,43184,43185,43186,43187,43188,43189,43190,43191,43192,43193,43194,43195,43196,43197,43198,43199,43200,43201,43202,43203,43204,43205,43206,43207,43208,43209,43210,43211,43212,43213,43214,43215,43216,43217,43218,43219,43220,43221,43222,43223,43224,43225,43226,43227,43228,43229,43230,43231,43232,43233,43234,43235,43236,43237,43238,43239,43240,43241,43242,43243,43244,43245,43246,43247,43248,43249,43250,43251,43252,43253,43254,43255,43256,43257,43258,43259,43260,43261,43262,43263,43264,43265,43266,43267,43268,43269,43270,43271,43272,43273,43274,43275,43276,43277,43278,43279,43280,43281,43282,43283,43284,43285,43286,43287,43288,43289,43290,43291,43292,43293,43294,43295,43296,43297,43298,43299,43300,43301,43302,43303,43304,43305,43306,43307,43308,43309,43310,43311,43312,43313,43314,43315,43316,43317,43318,43319,43320,43321,43322,43323,43324,43325,43326,43327,43328,43329,43330,43331,43332,43333,43334,43335,43336,43337,43338,43339,43340,43341,43342,43343,43344,43345,43346,43347,43348,43349,43350,43351,43352,43353,43354,43355,43356,43357,43358,43359,43360,43361,43362,43363,43364,43365,43366,43367,43368,43369,43370,43371,43372,43373,43374,43375,43376,43377,43378,43379,43380,43381,43382,43383,43384,43385,43386,43387,43388,43389,43390,43391,43392,43393,43394,43395,43396,43397,43398,43399,43400,43401,43402,43403,43404,43405,43406,43407,43408,43409,43410,43411,43412,43413,43414,43415,43416,43417,43418,43419,43420,43421,43422,43423,43424,43425,43426,43427,43428,43429,43430,43431,43432,43433,43434,43435,43436,43437,43438,43439,43440,43441,43442,43443,43444,43445,43446,43447,43448,43449,43450,43451,43452,43453,43454,43455,43456,43457,43458,43459,43460,43461,43462,43463,43464,43465,43466,43467,43468,43469,43470,43471,43472,43473,43474,43475,43476,43477,43478,43479,43480,43481,43482,43483,43484,43485,43486,43487,43488,43489,43490,43491,43492,43493,43494,43495,43496,43497,43498,43499,43500,43501,43502,43503,43504,43505,43506,43507,43508,43509,43510,43511,43512,43513,43514,43515,43516,43517,43518,43519,43520,43521,43522,43523,43524,43525,43526,43527,43528,43529,43530,43531,43532,43533,43534,43535,43536,43537,43538,43539,43540,43541,43542,43543,43544,43545,43546,43547,43548,43549,43550,43551,43552,43553,43554,43555,43556,43557,43558,43559,43560,43561,43562,43563,43564,43565,43566,43567,43568,43569,43570,43571,43572,43573,43574,43575,43576,43577,43578,43579,43580,43581,43582,43583,43584,43585,43586,43587,43588,43589,43590,43591,43592,43593,43594,43595,43596,43597,43598,43599,43600,43601,43602,43603,43604,43605,43606,43607,43608,43609,43610,43611,43612,43613,43614,43615,43616,43617,43618,43619,43620,43621,43622,43623,43624,43625,43626,43627,43628,43629,43630,43631,43632,43633,43634,43635,43636,43637,43638,43639,43640,43641,43642,43643,43644,43645,43646,43647,43648,43649,43650,43651,43652,43653,43654,43655,43656,43657,43658,43659,43660,43661,43662,44381,44541,44542)) AND clock>1617012912 GROUP BY itemid
      zabbix.php:21 → require_once() → ZBase->run() → ZBase->processRequest() → CController->run() → CControllerLatestView->doAction() → CControllerLatest->prepareData() → CHistoryManager->getItemsHavingValues() → CHistoryManager->getItemsHavingValuesFromSql() → DBselect() in include/classes/api/managers/CHistoryManager.php:78
      ...
      

      I tried this SQL query in psql.

      >16 sec: SELECT itemid FROM history_uint WHERE (itemid IN (A,B,C) OR itemid IN (D,E,F)) AND clock>1617002580 GROUP BY itemid

       

      I tried concatenating the values in a SQL query or splitting it into two queries and it is very fast.

      <1 sec: SELECT itemid FROM history_uint WHERE itemid IN (A,B,C,D,E,F) AND clock>1617002580 GROUP BY itemid

      <1 sec: SELECT itemid FROM history_uint WHERE itemid IN (A,B,C) AND clock>1617002580 GROUP BY itemid

      <1 sec: SELECT itemid FROM history_uint WHERE itemid IN (D,E,F) AND clock>1617002580 GROUP BY itemid

       

      Please note that I am using PostgreSQL 13.2.

      Attachments

        1. fast.txt
          21 kB
        2. jit-disabled.txt
          22 kB
        3. jit-enabled.txt
          22 kB
        4. slow.txt
          21 kB

        Issue Links

          Activity

            People

              ssimonenko Sergey Simonenko
              sagatov Evgeny
              Votes:
              2 Vote for this issue
              Watchers:
              5 Start watching this issue

              Dates

                Created:
                Updated: