We got this behavior with 2.0.2 version.
If a Zabbix user has permissions set the way there are many groups of hosts which are permitted for them, and the total amount of hosts permitted for the user is set over 1500 (like in my case), the pages open much slower than for the super users (up to 60 seconds). This certainly has to do with the permissions queries and we got this issue after Zabbix 1.8.2 -> 2.0.2 upgrade only. Our critical page is tr_status.php, which should be opened very quickly, but it does not for our regular users.
Is there any recommendation how to fix this manually until is hot fixed in the new releases?
I am got same problem.
All my investigation here:
http://www.zabbix.com/forum/showthread.php?t=30136
Noticed the same issue too with 2.0.3 (and PostgreSQL) after the count of monitored hosts increased from ~100 to ~500 (~50 host groups).
I was able to relax the situation by creating new statistics. But I fear that this issue will come back if Zabbix gets the full load of ~5000 servers - on condition Zabbix fully satisfies all our requirements
SELECT DISTINCT g.groupid,g.name FROM groups g,rights r,users_groups ug,hosts_groups hg,hosts h WHERE r.id=g.groupid AND r.groupid=ug.usrgrpid AND ug.userid=94 AND r.permission>=2 AND NOT EXISTS ( SELECT gg.groupid FROM groups gg,rights rr,users_groups ugg WHERE rr.id=g.groupid AND rr.groupid=ugg.usrgrpid AND ugg.userid=94 AND rr.permission<2) AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=0 AND EXISTS (SELECT i.hostid FROM items i WHERE hg.hostid=i.hostid) AND g.groupid BETWEEN 000000000000000 AND 099999999999999; +---------+-------------------------------------------------------------------------+ 11 rows in set (3.21 sec) mysql> explain SELECT DISTINCT g.groupid,g.name FROM groups g,rights r,users_groups ug,hosts_groups hg,hosts h WHERE r.id=g.groupid AND r.groupid=ug.usrgrpid AND ug.userid=94 AND r.permission>=2 AND NOT EXISTS ( SELECT gg.groupid FROM groups gg,rights rr,users_groups ugg WHERE rr.id=g.groupid AND rr.groupid=ugg.usrgrpid AND ugg.userid=94 AND rr.permission<2) AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=0 AND EXISTS (SELECT i.hostid FROM items i WHERE hg.hostid=i.hostid) AND g.groupid BETWEEN 000000000000000 AND 099999999999999; +----+--------------------+-------+--------+---------------------------------+------------------+---------+-------------------+-------+--------------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+--------------------+-------+--------+---------------------------------+------------------+---------+-------------------+-------+--------------------------------+ | 1 | PRIMARY | ug | ref | users_groups_1,c_users_groups_2 | c_users_groups_2 | 8 | const | 1 | Using temporary | | 1 | PRIMARY | h | ref | PRIMARY,hosts_2 | hosts_2 | 4 | const | 10920 | Using index | | 1 | PRIMARY | hg | ref | hosts_groups_1,hosts_groups_2 | hosts_groups_1 | 8 | zabbix.h.hostid | 1 | Using where; Using index | | 1 | PRIMARY | g | eq_ref | PRIMARY | PRIMARY | 8 | zabbix.hg.groupid | 1 | Using where | | 1 | PRIMARY | r | ref | rights_1,rights_2 | rights_2 | 8 | zabbix.g.groupid | 2 | Using where; Distinct | | 3 | DEPENDENT SUBQUERY | i | ref | items_1 | items_1 | 8 | zabbix.hg.hostid | 40 | Using index | | 2 | DEPENDENT SUBQUERY | ugg | ref | users_groups_1,c_users_groups_2 | c_users_groups_2 | 8 | const | 1 | | | 2 | DEPENDENT SUBQUERY | rr | ref | rights_1,rights_2 | rights_2 | 8 | zabbix.g.groupid | 2 | Using where | | 2 | DEPENDENT SUBQUERY | gg | index | NULL | groups_1 | 194 | NULL | 28 | Using index; Using join buffer | +----+--------------------+-------+--------+---------------------------------+------------------+---------+-------------------+-------+--------------------------------+ 9 rows in set (0.00 sec)
SELECT DISTINCT s.*,hg.hostid FROM scripts s,rights r,users_groups ug,hosts_groups hg
WHERE hg.groupid=r.id AND r.groupid=ug.usrgrpid AND ug.userid=94
AND (hg.groupid=s.groupid OR s.groupid IS NULL) AND (ug.usrgrpid=s.usrgrpid OR s.usrgrpid IS NULL) AND (( (hg.groupid IN ('13','12','19','147')) AND hg.groupid=s.groupid)
OR (s.groupid IS NULL AND scriptid BETWEEN 000000000000000 AND 099999999999999)) ORDER BY s.name;
+----------+------------+---------------------------------+-------------+----------+---------+-------------+--------------+------+------------+--------+
39220 rows in set (1.47 sec)
+----+-------------+-------+------+---------------------------------+------------------+---------+--------------------+------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------------------------+------------------+---------+--------------------+------+---------------------------------+
| 1 | SIMPLE | ug | ref | users_groups_1,c_users_groups_2 | c_users_groups_2 | 8 | const | 1 | Using temporary; Using filesort |
| 1 | SIMPLE | r | ref | rights_1,rights_2 | rights_1 | 8 | zabbix.ug.usrgrpid | 14 | |
| 1 | SIMPLE | hg | ref | hosts_groups_2 | hosts_groups_2 | 8 | zabbix.r.id | 6255 | |
| 1 | SIMPLE | s | ALL | PRIMARY,c_scripts_1,c_scripts_2 | NULL | NULL | NULL | 2 | Using where; Using join buffer |
+----+-------------+-------+------+---------------------------------+------------------+---------+--------------------+------+---------------------------------+
Query from charts.php:
SELECT DISTINCT g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=0 AND EXISTS (SELECT 1 FROM items i,graphs_items gi WHERE i.hostid=hg.hostid AND i.itemid=gi.itemid LIMIT 1) AND g.groupid BETWEEN 000000000000000 AND 099999999999999; +---------+-------------------------------------------------------------------------+ 22 rows in set (1.45 sec)
modified query:
SELECT DISTINCT g.groupid,g.name FROM groups g
WHERE
EXISTS (SELECT NULL FROM hosts_groups hg,hosts h, items i,graphs_items gi
WHERE hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=0 AND i.hostid=hg.hostid AND i.itemid=gi.itemid)
AND g.groupid BETWEEN 000000000000000 AND 099999999999999;
+---------+-------------------------------------------------------------------------+
22 rows in set (0.00 sec)
SQL (1.715516): SELECT h.hostid FROM hosts h WHERE EXISTS (SELECT hgg.hostid FROM hosts_groups hgg JOIN rights r ON r.id=hgg.groupid AND (r.groupid IN ('14')) WHERE h.hostid=hgg.hostid GROUP BY hgg.hostid HAVING MIN(r.permission)>=2) AND h.status IN (0,1) AND h.hostid BETWEEN 000000000000000 AND 099999999999999
make_latest_issues() -> CAPIObject->getScriptsByHosts() -> CAPIObject->__call() -> czbxrpc::call() -> czbxrpc::callAPI() -> call_user_func() -> CScript->getScriptsByHosts() -> CScript->get() -> CScript->addRelatedObjects() -> CHost->get() -> DBselect()
It executes on Dashboard in Latest 20 issues for users.type = 1. Why do we select all hosts?
Also the similar query but without permissions check for type = 3:
SQL (0.019905): SELECT h.hostid FROM hosts h WHERE h.status IN (0,1) AND h.hostid BETWEEN 000000000000000 AND 099999999999999 make_latest_issues() -> CAPIObject->getScriptsByHosts() -> CAPIObject->__call() -> czbxrpc::call() -> czbxrpc::callAPI() -> call_user_func() -> CScript->getScriptsByHosts() -> CScript->get() -> CScript->addRelatedObjects() -> CHost->get() -> DBselect()
latest.php:
CPageFilter->_construct()
> CPageFilter>_initGroups()> CAPIObject>get()> CAPIObject>_call()> czbxrpc::call() -> czbxrpc::callAPI() -> call_user_func() -> CHostGroup>get() -> DBselect()
Old:
mysql> SELECT SQL_NO_CACHE DISTINCT g.groupid,g.name FROM groups g,hosts_groups hg,hosts h WHERE EXISTS (SELECT r.id FROM rights r WHERE g.groupid=r.id AND (r.groupid IN ('14')) GROUP BY r.id HAVING MIN(r.permission)>=2) AND hg.groupid=g.groupid AND h.hostid=hg.hostid AND h.status=0 AND g.groupid BETWEEN 000000000000000 AND 099999999999999;
+---------+-------------------------------------------------------------------------+
11 rows in set (2.17 sec)
New:
mysql> SELECT SQL_NO_CACHE DISTINCT g.groupid,g.name FROM groups g WHERE EXISTS (SELECT r.id FROM rights r WHERE g.groupid=r.id AND (r.groupid IN ('14')) GROUP BY r.id HAVING MIN(r.permission)>=2) AND EXISTS (select h.hostid FROM hosts h JOIN hosts_groups hg USING (hostid) WHERE h.status = 0 and hg.groupid = g.groupid) AND g.groupid BETWEEN 000000000000000 AND 099999999999999;
+---------+-------------------------------------------------------------------------+
11 rows in set (0.00 sec)
(1) when development on this is finished, please add a list of all places/functionality that received improvements (to be used as a source for whatsnew) 
oleg.egorov
Improved performance in:
Host groups
Hosts
Applications
Discovery rules
Graphs and graph prototypes
Host interfaces
Items and item prototypes
Templates
Triggers and trigger prototypes
Actions
Alerts
Events
Maps
Screens
Web checks
Maintenances
IT services
Dashboard
Overviews
Improved HTML pages size in some places
Query generation time average x2 faster
zalex_ua As I know form Alexey P. for SQL execution time there is a performance improvement x2-10 times in a huge installation.
I'm not sure what Oleg meant by Query generation time
oleg.egorov I some places x50 and more, in many places x2-10 and x0.03-x0.1. Average x2 - guaranteed!
If You want, I can accurately calculate!
And PHP now use less memory!
<richlv> in general, did this improve performance in listing entities, viewing/editing them or everywhere ? for example, listing of maps, viewing/editing them or all of that ?
oleg.egorov All of that
Fixed in svn://svn.zabbix.com/branches/dev/ZBX-4789 r32047
In maintenances changed output result.
If maintenance allowed in one group, and not defined (by default in Deny list) in second group.
In old version:
We can't see it.
Now:
We see it!
<richlv> so this means that not only performance was changed but also logic in some cases ? is the maintenance only place where the logic was changed ?
oleg.egorov Logical changes was only for maintenances
Which version will this be fixed in officially.
Robert,
It will be fixed in 2.0.5 and 2.1.0 (trunk).
Successfully tested! Please review my changes in r32134:32135.
oleg.egorov CLOSED
FIXED IN 2.0.5rc1 r32139
Please review changes for 2.0.5
Fixed performance loss in HostGroups
FIXED IN svn://svn.zabbix.com/branches/dev/ZBX-4789
Successfully tested! Please review my changes in r32190, r32206, r32208 and r32210.
FIXED IN 2.1.0(trunk) r32213
CLOSED
Why fix version is empty ?
REOPENED
(10)
Changes here caused a regression:
> Trigger severity "Recovery" events not shown in notification messages.
in other words - OK events are missing
Broken in rev 32192
SVN log message: - A......... ZBX-4789 fixed HostGroups (improved performance)
Need to check is this related only to notification messages or not ?
REOPENED
dotneft The problem in dbConditionInt function. Just example:
$options['value'] = array(1,0);
SDII($options['value']); -> Array
(
[0] => 1
[1] => 0
)
SDII(dbConditionInt('e.value', $options['value'])); -> e.value=1
array_multisort($options['value'], SORT_NUMERIC, SORT_ASC);
SDII(dbConditionInt('e.value', $options['value'])); -> e.value IN (0,1)
CLOSED
tested with r32833 on a mid-sized installation (400 hosts, 5000 triggers, 350 vps) and seems to be FIXED
Zabbix 2.4.1 - issue remains