Problems with "Latest data" after 2.2.0 upgrade

[tchjts1]

When I select just 1 server to look at it loads fine. But when I select everything, it will try to load for around 30 seconds and eventually just display a blank screen.

On my test install of 2.2.1 (compiled) which is
* Zabbix App and DB server on the same VM, 2vCPU's and 8GB RAM
* Monitoring 1 ESX server with ~ 50 guest VM's, 1200 items monitored, 35 NVPS

From the time I click on "All" in latest data until it finishes loading is about 8 seconds.

[mcdougak]

Ok, so you have more resources AND less items monitored than I.

So this leads me to believe I am under resourcing my Zabbix VM.

The documentation is somewhat vague in regards to recommended supporting hardware specs. What would you suggest for the following ... please not this is only our virtual environment and not any physical hardware such as additional servers and switches etc.

Number of hosts (monitored/not monitored/templates) 227
Number of items (monitored/disabled/not supported) 7308
Number of triggers (enabled/disabled) [problem/ok] 226 226
Required server performance, new values per second 141.13 -

Thanks!

[tchjts1]

Our 2.0.9 production setup is this:

Zabbix App server on its own VM - 4vCPU's and 8GB of RAM
Zabbix DB server on its own VM - 4vCPU's and 16GB of RAM (MySql)
OS is RHEL6 on both. DB is stored on the SAN

And hosts stats:

[jansonz]

Latest data on Zabbix 2.2.1 is not working well for me either... Takes long time even to load data for one host.

[Crypty]

Hi, the same issue with 2.2.2 Zabbix. Loading "Latest data" was much quicker with 2.0.X...

[skygge]

I have very big problem after 2.2 update.

I have hundreds of printers monitored with Zabbix via SNMP.
Every month I have to get counters of these printers and send them to our service support.

I made simple view, it worked fast (couple seconds) and very well until the 2.2 update when "item_lastvalue" was removed.

I had to rewrite this view and get last value from history tables.

And now, my query runs over 2 hours!
I'm not so familiar with SQL, so maybe someone could help me to improve my select and make it faster....

It looks like this:

select distinct
`g`.`name` AS `Region`,
`hi`.`site_city` AS `City`,
`hi`.`location` AS `Address`,
`hi`.`model` AS `Model`,
(select ifnull(`hu`.`value`, 'Counter unavailable') from `history_uint` `hu` where `hu`.`itemid` = `i`.`itemid` order by clock desc limit 1) AS `Counter`,
(select from_unixtime(`hu`.`clock`) from `history_uint` `hu` where `hu`.`itemid` = `i`.`itemid` order by clock desc limit 1) AS `Counter date`,
`hi`.`serialno_b` AS `Identification_number`,
`hi`.`notes` AS `Service rules`
from
((((`groups` `g`
join `items` `i`)
join `hosts_groups` `hg`)
join `interface` `ifa`)
join (`hosts` `h`
left join `host_inventory` `hi` ON ((`hi`.`hostid` = `h`.`hostid`))))
where
((`g`.`groupid` in (23, 27, 28, 29, 30, 31, 32))
and (`hg`.`hostid` = `h`.`hostid`)
and (`hg`.`groupid` = `g`.`groupid`)
and (`i`.`hostid` = `h`.`hostid`)
and (`i`.`name` = 'Counter32')
and `hi`.`serialno_b` <> '--'
and (`ifa`.`hostid` = `h`.`hostid`)
and (not ((`h`.`host` like '%Template%'))))
order by `g`.`name`, `hi`.`serialno_b`;

Bolded subqueries makes this query soooooo sloooow....
How to replace these subqueries with something faster? I'm on Zabbix 2.2.2 right now.

Edit:
Error Code: 2013. Lost connection to MySQL server during query 6000,000 sec
Grrrrr....!!!!!
(My mysql dbsize is 92GB at the moment and getting bigger...)

[steveboyson]

You are using two queries against the history_uint table. Try to use only one:

select value, from_unixtime(clock) from history_uint where ...

Maybe that speeds it up a little bit. But if you have a lot of records, this will not help very much.
We are using similar statements, by the way.

[skygge]

I did it two hours ago and sql returned rows in 4695 seconds.

I will have to make a separate table with latest values and update it with some kind of trigger. I hate the 2.2.x version, it consumes my nerves and increases blood pressure.

EDIT:

OK, I made this table and called it "latest_values". Columns: itemid(UQ,PK), history_uint, clock

Trigger on table "history_uint":

DELIMITER $$
CREATE TRIGGER latest_values_uint_bi
BEFORE INSERT ON `history_uint` FOR EACH ROW
begin
       DECLARE itemid_exists Boolean;
       -- check if itemid in latest_values table exists
       SELECT
              count(itemid)
       INTO
              @itemid_exists
       FROM
              latest_values
       WHERE
              latest_values.itemid = NEW.itemid;

       IF @itemid_exists = 0
       THEN
	INSERT INTO
	           latest_values (itemid, history_uint, clock)
	VALUES
		(NEW.itemid, NEW.value, NEW.clock);
	ELSE
           UPDATE
                  latest_values
           SET 
                  history_uint = NEW.value,
                  clock = NEW.clock
           WHERE
                  itemid = NEW.itemid;		
END;
$$
DELIMITER ;

My latest_values table is working, and my printers counter view works like a charm (0,022s)!