Different SQL query between admin and normal user.

[akapap]

Hi,

does anyone now why there is a difference between queries when log into zabbix by admin and normal user.

When I log as a normal user I see one big select. So only one connection to database is made and one proces of postgres is bind to one CPU.

When I log as a admin I see several sql select. So few connection are made to database and lots of postgres process use all of my CPU resource.

I'd like to normal user search's database with all resource that is on my hardware.

Now query with admin takes 3s, query with normal user takes about 40s.

Best regards.

[Alexei]

What specific screens are you talking about?

[akapap]

It one of the screen when I have lots of graph. When I open it with admin user I get:

#from debug
Time:0.009364 SQL: SELECT MAX(g.graphtype) as graphtype, MIN(gi.yaxisside) as yaxissidel, MAX(gi.yaxisside) as yaxissider, MAX(g.height) as height FROM graphs g, graphs_items gi WHERE g.graphid=499 AND gi.graphid=g.graphid
Time:0.001188 SQL: SELECT g.graphid, g.show_legend as legend, g.show_3d as show3d FROM graphs g WHERE g.graphid=499
Time:0.000951 SQL: SELECT * FROM screens_items WHERE screenid=13 AND x=1 AND y=1
Time:0.009502 SQL: SELECT MAX(g.graphtype) as graphtype, MIN(gi.yaxisside) as yaxissidel, MAX(gi.yaxisside) as yaxissider, MAX(g.height) as height FROM graphs g, graphs_items gi WHERE g.graphid=618 AND gi.graphid=g.graphid
Time:0.000615 SQL: SELECT g.graphid, g.show_legend as legend, g.show_3d as show3d FROM graphs g WHERE g.graphid=618
Time:0.000792 SQL: SELECT * FROM screens_items WHERE screenid=13 AND x=0 AND y=2
Time:0.000928 SQL: SELECT MAX(g.graphtype) as graphtype, MIN(gi.yaxisside) as yaxissidel, MAX(gi.yaxisside) as yaxissider, MAX(g.height) as height FROM graphs g, graphs_items gi WHERE g.graphid=516 AND gi.graphid=g.graphid
Time:0.000564 SQL: SELECT g.graphid, g.show_legend as legend, g.show_3d as show3d FROM graphs g WHERE g.graphid=516
Time:0.000771 SQL: SELECT * FROM screens_items WHERE screenid=13 AND x=1 AND y=2
Time:0.000308 SQL: SELECT MAX(g.graphtype) as graphtype, MIN(gi.yaxisside) as yaxissidel, MAX(gi.yaxisside) as yaxissider, MAX(g.height) as height FROM graphs g, graphs_items gi WHERE g.graphid=533 AND gi.graphid=g.graphid
Time:0.001325 SQL: SELECT g.graphid, g.show_legend as legend, g.show_3d as show3d FROM graphs g WHERE g.graphid=533
Time:0.000747 SQL: SELECT * FROM screens_items WHERE screenid=13 AND x=0 AND y=3
Time:0.000879 SQL: SELECT MAX(g.graphtype) as graphtype, MIN(gi.yaxisside) as yaxissidel, MAX(gi.yaxisside) as yaxissider, MAX(g.height) as height FROM graphs g, graphs_items gi WHERE g.graphid=482 AND gi.graphid=g.graphid
Time:0.000603 SQL: SELECT g.graphid, g.show_legend as legend, g.show_3d as show3d FROM graphs g WHERE g.graphid=482
Time:0.000732 SQL: SELECT * FROM screens_items WHERE screenid=13 AND x=1 AND y=3
Time:0.000867 SQL: SELECT MAX(g.graphtype) as graphtype, MIN(gi.yaxisside) as yaxissidel, MAX(gi.yaxisside) as yaxissider, MAX(g.height) as height FROM graphs g, graphs_items gi WHERE g.graphid=632 AND gi.graphid=g.graphid
Time:0.000588 SQL: SELECT g.graphid, g.show_legend as legend, g.show_3d as show3d FROM graphs g WHERE g.graphid=632
Time:0.000772 SQL: SELECT * FROM screens_items WHERE screenid=13 AND x=0 AND y=4

I get lots of seperated search.

But when I open it with normal user I get one big search that it bind only to one postgres process and CPU:

#from debug
Time:32.64646 LONG SQL: SELECT g.graphid FROM graphs g,graphs_items gi,items i,hosts_groups hg,rights r,users_groups ug WHERE (g.graphid/100000000000000) in (0) AND gi.graphid=g.graphid AND i.itemid=gi.itemid AND hg.hostid=i.hostid AND r.id=hg.groupid AND r.groupid=ug.usrgrpid AND ug.userid=4 AND r.permission>=2 AND NOT EXISTS( SELECT gii.graphid FROM graphs_items gii, items ii WHERE gii.graphid=g.graphid AND gii.itemid=ii.itemid AND EXISTS( SELECT hgg.groupid FROM hosts_groups hgg, rights rr, users_groups ugg WHERE ii.hostid=hgg.hostid AND rr.id=hgg.groupid AND rr.groupid=ugg.usrgrpid AND ugg.userid=4 AND rr.permission<2)) AND (g.graphid IN (390,390,389,618,533,632,448,629,611,526,642,441,6 26,605,520,635,435,627,616,531,631,446,628,494,613 ,511,528,477,639,426,443,460,490,609,507,524,473,6 36,422,439,456,484,603,491,610,508,525,474,638,423 ,440,457,487,606,504,521,470,637,419,436,453,619,6 49,652,655,648,651,654,656,657,664,665,666,660,661 ,662,659,658,663,488,607,505,522,471,643,420,437,4 54,489,608,506,523,472,644,421,438,455,669,668,670 ,671,499,516,482,431,465,624,492,509,475,424,458,6 21,486,503,469,418,452,622,497,514,480,429,463,623 ,501,518,467,640,416,433,450,495,614,512,529,478,6 41,427,444,461,673,674,673,674,498,617,515,532,481 ,646,430,447,464,733,691,677,719,705,789,747,761,7 36,694,680,722,708,792,750,764,737,695,681,723,709 ,793,751,765,738,696,682,724,710,794,752,766,744,7 02,688,730,716,800,758,772,741,699,685,727,713,797 ,755,769,732,690,676,718,704,788,746,760,743,701,6 87,729,715,799,757,771,740,698,684,726,712,796,754 ,768,735,693,679,721,707,791,749,763,734,692,678,7 20,790,706,748,762,770,742,700,686,728,714,798,756 ,739,697,683,725,711,795,753,767))

Best regards.

[Alexei]

I believe we already fixed the long queries in pre 1.8.1.