Long loading of pages after migration from MySQL to PostgreSQL (ie. hosts, templates)

[gawron997]

Hello,
after migrating from MySQL to PostgreSQL, Zabbix became unusable due to issues with reading hosts in the Monitoring → Hosts and Data Collection → Hosts sections.

On the main admin account, everything loads quickly (within a few seconds). However, on other accounts with specific privileges (restricted access to certain host groups), loading these views can take several minutes. Additionally, during the SELECT query execution, one CPU core reaches 100% utilization.

I get two SELECT queries in debug mode:
- for main admin account:

SQL (0.086881): SELECT COUNT(DISTINCT t.triggerid) AS rowscount,i.hostid FROM triggers t,functions f,items i WHERE i.hostid IN (10124,10126,10127,10128,10129,10130,10131,10132,10133,10137,10152,10153,10154,10155,10156,10157,10158,10159,10160,10161,10162,10163,10164,10165,10169,10171,10172,10173,10174,10178,10179,10180,10181,10182,10187,10190,10191,10192,10193,10194,10195,10196,10226,10227,10231,10232,10250,10251,10252,10253,10254,10258,10285,10287,10288,10289,10290,10294,10299,10300,10301,10302,10315,10316,10317,10318,10319,10320,10321,10322,10323,10324,10325,10326,10327,10328,10329,10330,10331,10332,10333,10334,10335,10336,10337,10338,10339,10340,10341,10342,10343,10344,10345,10346,10347,10348,10349,10350,10353,10354,10355,10356,10357,10358,10359,10360,10386,10387,10388,10389,10390,10391,10392,10393,10398,10399,10400,10406,10407,10408,10415,10416,10417,10418,10419,10420,10421,10422,10423,10424,10425,10426,10427,10428,10429,10430,10431,10432,10433,10434,10435,10436,10437,10438,10439,10440,10441,10442,10443,10444,10445,10446,10447,10448,10449,10450,10451,10452,10453,10454,10455,10456,10457,10458,10459,10460,10461,10462,10463,10464,10465,10466,10467,10468,10469,10470,10471,10472,10473,10474,10475,10476,10477,10478,10479,10480,10481,10482,10483,10484,11576,11577,11578,11579,11580,11581,11608,11629,11630,11653,11654,11655,11656,11657,11658,11659,11660,11661,11662,11663,11664,11665,11666,11667,11668,11669,11670,11671,11672,11673,11674,11675,11676,11677,11678,11679,11680,11681,11682,11683,11684,11685,11686,11687,11688,11689,11690,11691,11692,11693,11694,11695,11696,11697,11698,11699,11704,11816,11824,19704) AND f.triggerid=t.triggerid AND f.itemid=i.itemid AND t.flags IN (0,4) GROUP BY i.hostid

- for another account with specific privileges:

SQL (173.652796): SELECT COUNT(DISTINCT t.triggerid) AS rowscount,i.hostid FROM triggers t,functions f,items i,host_hgset hh,permission p WHERE f.triggerid=t.triggerid AND f.itemid=i.itemid AND i.hostid=hh.hostid AND hh.hgsetid=p.hgsetid AND p.ugsetid=30 AND NOT EXISTS (SELECT NULL FROM functions f1 JOIN items i1 ON f1.itemid=i1.itemid JOIN host_hgset hh1 ON i1.hostid=hh1.hostid LEFT JOIN permission p1 ON hh1.hgsetid=p1.hgsetid AND p1.ugsetid=p.ugsetid WHERE t.triggerid=f1.triggerid AND p1.permission IS NULL) AND i.hostid IN (10113,10114,10169,10190,10191,10192,10193,10194,10195,10196,11653,11654,11655,11656,11657,11658,11659,11660,11661,11662,11663,11664,11665,11666,11667,11668,11669,11670,11671,11672,11673,11674,11675,11676,11677,11678,11679,11680,11681,11682,11683,11684,11685,11686,11687,11688,11689,11690,11691,11692,11693,11694,11695,11696,11697,11698,11699,11700,11701,11702,11703,11704,11705,11706,11707,11708,11709,11710,11711,11712,11713,11714,11715,11716,11717,11718,11719,11720,11721,11722,11723,11724,11725,11726,11727,11728,11729,11730,11731,11732,11733,11734,11735,11736,11737,11738,11739,11740,11741,11742,11743,11744,11745,11746,11747,11748,11749,11750,11751,11752,11753,11754,11755,11756,11757,11758,11759,11760,11761,11762,11763,11764,11765,11766,11767,11768,11769,11770,11771,11772,11773,11774,11775,11776,11777,11778,11779,11780,11781,11782,11783,11784,11785,11786,11787,11788,11789,11790,11791,11792,11793,11794,11795,11796,11797,11798,11799,11800,11801,11802,11803,11804,11805,11806,11807,11808,11809,11810,11811,11812,11813,11814,11815,11816,11817,11818,11819,11820,11821,11822,11823,11824,11825,11826,11827,11828,11829,11830,11831,11832,11833,11834,11835,11836,11837,11838,11839,11840,11841,11842,11843,11844,11845,11846,11847,11848,11849,11850,11851,11852,11853,11854,12965,12966,13015,13016,13457,13459,13460,14928,14929,14930,14931,14932,14934,14935,14936,14937,14938,14939,14940,14941,14942,14943,14944,14945,14946,14947,14948,14949,14950,14951,14952,14953,14954,16504,16505,16506,16507,16561) AND t.flags IN (0,4) GROUP BY i.hostid

These selects come from Data collection -> Hosts view. Has anyone come across this problem before?

Regards,
T

[gawron997]

Hi,

I think this problem is more related to the database field and its performance or Zabbix queries rather than the Zabbix server itself - when I disable the Zabbix server and only have Apache enabled, loading hosts can take forever (I gave up after 14 minutes on one account with specific privileges - Admin role / User role).
On the main admin account, all functionalities work perfectly, they load immediately.

Anyone can help me solve this problem?

Regards,
T

[cyber]

I have a bit similar case going on... If I get some solution, I can probably share it...

Superadmin account has not permission checks, so it loads everything quickly.
You did not mention any version of Zabbix... Or any details about setup... how big of environment? number of hosts/hostgroups/template groups?

[gawron997]

Hi

I solved my problem problem so I would like to share what kind of issue I encountered. First, here are some details about my setup.
I migrate the database from Zabbix Server 7.0.10 (two instances of Zabbix Server) and Mysql 8.0.37 (three instances of MySQL). Below is a screenshot with the details of Zabbix Server:

For migration I used a manual from Initmax :

https://www.initmax.com/wiki/zabbix-migration-from-mysql-to-postgresql/

To get to the point - during many tests while migrating database from MySQL to PostgreSQL via pgloader, I forgot about steps after migration such as:

• create a schema for indexes,
• create a schema for the alter table,
• create a schema for triggers.

After completing all the steps from the manual I mentioned above, loading hosts (in Monitoring → Hosts and Data Collection → Hosts sections) worked fine and didn’t cause an overload on the database and this operation was ending quite quickly.
Below SELECT query in debug mode for admin account with specific privileges:

 SQL (2.745399): SELECT COUNT(DISTINCT t.triggerid) AS rowscount,i.hostid FROM triggers t,functions f,items i,host_hgset hh,permission p WHERE f.triggerid=t.triggerid AND f.itemid=i.itemid AND i.hostid=hh.hostid AND hh.hgsetid=p.hgsetid AND p.ugsetid=25 AND NOT EXISTS (SELECT NULL FROM functions f1 JOIN items i1 ON f1.itemid=i1.itemid JOIN host_hgset hh1 ON i1.hostid=hh1.hostid LEFT JOIN permission p1 ON hh1.hgsetid=p1.hgsetid AND p1.ugsetid=p.ugsetid WHERE t.triggerid=f1.triggerid AND p1.permission IS NULL) AND i.hostid IN (10084,10187,11599,11600,11601,11602,11603,11604,11605,13132,13133,13153,13154,13198,16481,16482,16483,16484,16485,16486,16487,16488,16489,16490,16491,16492,16504,16505,16506,16514,16515,17177,17197,17198,17436,17518,17519,17521,17523,17524,17529,17530,17531,17533,17534,17535,17536,17537,17538,17539,17540,17541,17542,17543,17544,17545,17546,17547,17548,17549,17550,17551,17552,17553,17554,17555,17556,17557,17558,17559,17560,17561,17562,17563,17564,17565,17566,17567,17568,17569,17570,17571,17572,17573,17574,17575,17576,17577,17578,17579,17580,17581,17582,17583,17584,17585,17586,17587,17588,17589,17590,17591,17592,17593,17594,17595,17597,17598,17600,17601,17602,17808,17943,17965,17966,17971,17972,17973,17974,17975,17976,17977,18976,18980,19040,19041,19082,19083,19084,19085,19086,19087,19088,19089,19090,19091,19092,19093,19094,19095,19096,19097,19098,19100,19101,19102,19103,19105,19106,19107,19108,19109,19110,19111,19112,19113,19114,19115,19116,19117,19118,19119,19120,19121,19123,19127,19142,19143,19144,19145,19146,19147,19148,19149,19150,19151,19152,19154,19155,19156,19157,19158,19159,19160,19161,19162,19164,19165,19166,19175,19178,19179,19180,19231,19232,19270,19271,19280,19281,19282,19283,19284,19288,19438,19439,19440,19442,19443,19444,19486,19487,19489,19490,19491,19493,19568,19570,19596,19645,19646,19698,19724) AND t.flags IN (0,4) GROUP BY i.hostid

Before fixing it, when I tried to load hosts from multiple admin accounts (with permissions to diffrent host groups), this acction caused an oveload on the database. For instance, my VM with the database had 8 CPUs, and this action resulted in a load higher than 8 (sometimes even reaching 20).

Also worth mentioning - loading hosts (Monitoring → Hosts and Data Collection → Hosts sections) is a single-threaded operation. For instance, if five users try to load hosts simultaneously, you can see in htop that the database host will hit 100% load on five CPUs for the time it takes to load hosts.

Regards,
T