Worse quering performance after upgrade from 1.1.1 to 1.1.2

[drathi81]

Hi,

after having trouble with quering the data in 1.1.1 (itmes with >5 minutes) I could work around this by increasing the number of running servers to a total of 40. So monitoring of 14444 itmes went fine - except of some MySQL performance problems I run into.
But after uprading to 1.1.2 it is even more worse. Again I use 40 server processes but I get every 6-7 minutes the data for the items - this results in uselss graphs (which are empty).
It seems like ony a few of the running processes are used to collect data and I can see clearly that the CPU-Usage of the MySQL-daemon is around 30% - so it gets not more data deliverd by zabbix.
Any suggestions how to increase the performance?


drathi81

[Alexei]

There were no changes in 1.1.2 which might affect overall performance apart of fixed housekeeper and better distribution of items next checks.

The housekeeper will periodically (once per hour by default) removes unnecessary data from history and trends. While it was broken in 1.1.1 you obviously have lots of data to be removed, so you may see periodic slowdown which will hopefully disappears when database size stabilises.

You may grep for "Housekeeper" your server's log file to see how many records the housekeeper deletes after each run.

[drathi81]

Hi Alexei,

thanks for you fast reply.
I'll have a look at this and post my results then.

[edeus]

Alexei,

I was hoping my history and trends databases would decrease dramatically in size as I believe there is at least 50% of dead data in there.

From 1.1.1 to 1.1.2 there has been no change (disearnable) in table size/rows.

Do I need to export, import (or manually recreate from start) to get zabbix tracking the data properly again?

P.S. I reset the data back a few months ago at 1.1.

[pdwalker]

What is the file format of your database? ISAM tables or INNODB tables?

[drathi81]

There were no changes in 1.1.2 which might affect overall performance apart of fixed housekeeper and better distribution of items next checks.

The housekeeper will periodically (once per hour by default) removes unnecessary data from history and trends. While it was broken in 1.1.1 you obviously have lots of data to be removed, so you may see periodic slowdown which will hopefully disappears when database size stabilises.

Hi Alexei,

I'm afraid but I do not see much improvment after the first runs of the housekeeper.
I've two grapsh for you, the first one is taken from data collected before the update to 1.1.1.

The second image is taken a few minutes ago from the now working 1.1.2 installation. I have done no changes to the number of monitored hosts and items nor the number of running server processes.


Any hints how to resolve this issues?

[Alexei]

The second image is taken a few minutes ago from the now working 1.1.2 installation.

On restart ZABBIX server immediately starts housekeeper which may slow your system very much especially when using MyISAM (table locking!). Please can you wait and post a graph for 4-6 hours period if possible?

[pdwalker]

And if you are using MYISAM tables, immediately make plans to convert them to INNODB tables as soon as possible.

- Paul

[drathi81]

Hi,

as requested I've taken a some screens again, the first one is the period 8h and the second one is 16h after.
Furthermore I've checked MySQL and can say that all tables are InnoDB.


Stefan

[pdwalker]

For those graphs, how often are you querying the stats?

[drathi81]

Quite a lot
About half of them every 90 sec and the other half (In- & OutOctest and IfOperStatus) every 60 sec.
Yeah, I know I could increase the timeslot between them but I still wonder why I had no problems using 1.1.1


Stefan

[Alexei]

Interesting, may I have an extract from you server's log file? You may send it privately to a l e x @ z a b b i x . c o m

[pdwalker]

Your intervals are long enough. Not sure what the problem is exactly. Sorry, I cannot suggest anything useful at this point.

- Paul

[drathi81]

Hi,

for the last 7 hours it looks like the system has returned to old power.
All graphs look well at the moment, except for the one or other gap but this was before already.
So it took nearly 6(!) days after the upgrade, till everything looks well. But from the logs I could see that the Housekeeper did not run the whole time. Maybe it's related to the 48GB of the MySQL-Database?


Stefan

[Alexei]

The housekeeper runs periodically once per hour. I bilieve it took 6 days (it is normal) to remove outdated information from history and trends, so now performance should be ok.