Zabbix 1.5.3 beta MySQL usage

[skullone]

I just updated the 1.5.3 beta from the website
Everything is working, but MySQL load has quadrupled, and the number of slow queries has gone through the roof.

Before, MySQL would use about 20-25% CPU, now it's using 100% CPU, and there are now almost 40,000 slow queries (There were 0 slow queries before).



Connection id: 469616
Current database:
Current user: root@localhost
SSL: Not in use
Current pager: stdout
Using outfile: ''
Using delimiter: ;
Server version: 5.0.45 MySQL Community Server (GPL)
Protocol version: 10
Connection: Localhost via UNIX socket
Server characterset: latin1
Db characterset: latin1
Client characterset: latin1
Conn. characterset: latin1
UNIX socket: /tmp/mysql.sock
Uptime: 20 days 3 hours 7 min 30 sec

Threads: 29 Questions: 756973625 Slow queries: 39137 Opens: 10040 Flush tables: 1 Open tables: 256 Queries per second avg: 435.230


Has anything changed in 1.5.3 that I need to change with MySQL (besides updgrading the tables, which has been done)

Thanks!

[nelsonab]

Check the Zabbix processes too. If one Zabbix process is taking up a lot of CPU you may have a similar problem to what I had in this thread.

http://www.zabbix.com/forum/showthread.php?t=10096

The way I discovered this was by upping the debug level to 4 in the log files. I also had to increase the size of the log file before rotation as they were rotating at an extraordinary rate. If you see one query repeated over and over and it relates to the "escalations" table then you had the same problem and the above fix will work for you. If not please post a snippit of the log file.

[Alexei]

Thanks for the details. We are about to release 1.5.4, which seems does not contains a fix for this problem. This will be fixed asap.

On a related note, performance of 1.5.4 is expected to be 4 up-to 10x better than any previous release, both 1.4.x and 1.5.x. We did massive improvements!

[skullone]

I recompiled Zabbix with your patch, unfortunately it seems I have a different problem.

I am seeing some slow queries with it comes to sorting queries from history_log:
Heres a process from MySQL:

Query  	22  	Sorting result  	select value from history_log where itemid=26658 order by id desc limit 1

They typically run for 50 seconds, and several of them run at once sometimes using all the available resources on the server...
Seems the history_log table needs an index or something?

[skullone]

Well I found out why that table was so slow.. thats the Event Log table that my hosts write event log data to.

Set it to keep 60 day history, but maybe thats too much for Zabbix (about 70 hosts write event log to zabbix)

Im cleaning the table since I dont need history on it for long, and ill see if maybe housekeeper wasn't cleaning or what

[skullone]

Cleaned -all- my history and trends, Zabbix still makes MySQL consume all resources:

Threads: 17  Questions: 147299  Slow queries: 0  Opens: 73  Flush tables: 1  Open tables: 67  Queries per second avg: 1363.880

Very odd...
Not a single query takes longer than 1 second anymore though, but wonder why its querying so much, even with just 5 Zabbix servers started...

[skullone]

I found out what it was... a server went haywire, and put like 500,000 items+ in it's Event Log

Zabbix agent on the server then began feeding all those items into Zabbix server, causing all the resource usage.
Man... damn Windows
Was not a Zabbix issue at all, but merely Microsofts fault..

Sorry all

[Alexei]

It is ok. Just wondering if you still see the SQL statement in the log of slow queries?

[skullone]

I did not have slow query logging turned on...
But was querying and sorting the table that contained the ID for the Event Logs.
Which contained millions of rows...