High load after upgrade to 2.0.2.

[zerg]

Hi.
After upgrade from 1.8.12 to 2.0.2
I'am see very big CPU usage(user time) +80% - mysqld eats all:

PID USER PR NI VIRT RES SHR S %CPU %MEM TIME+ COMMAND
26335 mysql 20 0 17.4g 13g 3996 S 453.6 83.3 215:36.05 mysqld

I see alot happens in mysqld while strace it:
getsockname(19, {sa_family=AF_FILE, path="/var/lib/mysql\1"}, [28]) = 0
fcntl(19, F_SETFL, O_RDONLY) = 0
fcntl(19, F_GETFL) = 0x2 (flags O_RDWR)
fcntl(19, F_SETFL, O_RDWR|O_NONBLOCK) = 0
setsockopt(19, SOL_IP, IP_TOS, [8], 4) = -1 EOPNOTSUPP (Operation not supported)

Configuration of server doesn't change.
hosts: 1.3k
items: 58k
nvps: ~240

I/O on the disk not big.
I'am don't see any errors in mysql or in zabbix_server.

[zerg]

I determine problem.

1)I'am switch off Apache and see that my CPU load reduce to 10-20% - like past half year.
2)This day i'am replace new web-frontend files to old(1.8.12) - yes i have some problems but. In dashboard i see al functions. And load been 10-20%.
3)If i'am replce to new(2.0.2) files load again up to 100%.
4)Than i found some bad SQL:
| 16070 | zabbix | localhost | zabbix | Query | 47 | Sending data | SELECT DISTINCT t.* FROM triggers t,functions f,items i,hosts_groups hg,rights r,users_groups ug W |
| 16071 | zabbix | localhost | zabbix | Query | 44 | Sending data | SELECT DISTINCT t.*,h.name AS hostname,h.host,h.hostid FROM triggers t,functions f,items i,hosts_g |

I seen this SQL comming a lot from from dashboard.php:
$lastiss = make_latest_issues($dashconf);
$lastiss->show();


This function is not pretty simple, and i'am don't found any deep solution(SQL-tun, PHP-customiztion etc)

I'founded worst solution.
Change some refresh interval in dashboard.php(from 60/120s - 600s) and in user profile(30/60s - 120s).

For me may be it's some help. But SQL-doesn't fixing...

[Geekzilla]

Well Im running this very same version but i've got only 80 hosts, so my db doesnt get as much load as yours.

Still I didnt find any problems with the db queries so far. Are you sure your hardware can handle all the load and requests ?

[zerg]

Well Im running this very same version but i've got only 80 hosts, so my db doesnt get as much load as yours.

Still I didnt find any problems with the db queries so far. Are you sure your hardware can handle all the load and requests ?

Yes. Cause this hardware in 1.8.12 idle about 90%(all subsystem - disk,mem,CPU).

Now i going deep inside in my problem.
General problem in dasboard.php. This is not joke. :-)
In section calling varios menus(system status, host status, last issue).
Full query like this:
SELECT DISTINCT e.* FROM events e,functions f,items i,hosts_groups hg,rights r,users_groups
ug WHERE e.object=0 AND f.triggerid=e.objectid AND f.itemid=i.itemid AND hg.hostid=i.hostid AND r.id=hg.groupid AND r.groupid=ug.usrgrpid AND ug.userid=52 AND r.permission>=2 AN
D NOT EXISTS ( SELECT ff.triggerid FROM functions ff,items ii WHERE ff.triggerid=e.objectid AND ff.itemid=ii.itemid AND EXISTS ( SELECT hgg.groupid FROM hosts_groups hgg,rights r
r,users_groups gg WHERE hgg.hostid=ii.hostid AND rr.id=hgg.groupid AND rr.groupid=gg.usrgrpid AND gg.userid=52 AND rr.permission<2)) AND e.eventid BETWEEN 000000000000000 AND 099
999999999999 AND ( (e.value_changed IN ('1')) ) ORDER BY e.eventid LIMIT 1 OFFSET 0


In my box this query freez and eats CPU(this query freez 217seconds).
If i'am use dashboard.php from 1.8.12 and new database(dirty hack) - this problem are disappear. Think main problem in compare SQL(new changes in security in fronted made for 2xxx) may be i'am find some pepole that have same problem and we create Bug Report.

For my installation i'am create 2 hack:
1)Clean not nessaseary submenu - left only last 20 issue.
2)Use crontab and a script that kill long query:
http://stackoverflow.com/questions/2...-query-timeout

[Colttt]

can you post the link to the Bug-Report?

[zerg]

can you post the link to the Bug-Report?

Oo. In russian community some people can help me.
This is case:

https://support.zabbix.com/browse/ZBX-4789

Waitnig fix...