Mysql starts to eat up CPU time

[alj]

Hi, I have about 15 servers in zabbix and my mysql is already eatign 30% of cpu almost all the time with some fluctuation.
(debian-testing, standard mysql setup with innodb)
What parameters I can play with to speed that up?
Is there any innecessary keys in the database i can kill to speed up data writing?

[nelsonab]

Something similar noticed here too

I've been wandering through the forums and on Google for a while trying to find an answer to this, but so far nothing. Perhaps if it's phrased differently someone might have a clue as to what is happening and maybe help you too.

What I noticed system was that when we ran Zabbix (server and frontend), MySql and Apache all on one system the system load would climb to 1 and stay there for days at a time. This behavior was noted on a dual core P4 and a P3. However when the frontend was run from a seperate computer the system load barely touches .2 on the Zabbix server. Now I realize system loads are an aggregate function of disk load, process que length and whatnot, but is that expected behavior? Normally I wouldn't care much but this was experienced with only 3 computers being monitired with Zabbix. It seems Apache and Mysql conflict with each other when they are on the same computer. Any ideas? Stopping housekeeping and reducing the time between checks for items seems to have little if any effect.

Zabbix is being run on SuSE 10 (frontend) and OpenSuSE10.1 (server+db).

server details:
Mysql 5.0.18
Apache-Prefork 2.2.0
Zabbix 1.1.1

[edeus]

I posted various mysql issues and results on the boards.

Basically this issue of 30% usage (even on high end machines) always seemed to go back to zabbix running a "select count(*) from history".

I thought this was due to internal zabbix checking functions but then discovered I had an internal zabbix item for history count. Turned this off and mysql hovers around 0.3% now.

25 hosts, 30-60 items per host.

Apart from this, check normal things like mysql RAM usage, optimising innodb/mysql settings, checking "show processlist" for any slow queries, etc.

[nelsonab]

Basically this issue of 30% usage (even on high end machines) always seemed to go back to zabbix running a "select count(*) from history".

I thought this was due to internal zabbix checking functions but then discovered I had an internal zabbix item for history count. Turned this off and mysql hovers around 0.3% now.

Edus thanks for the info, the usual no replies to other posts of a similar nature were getting annoying. :-) Could you provide some more information about what you are referring to with the history count? I'm not quite following where you are referring to for the use of the history function and turning it off.

Also any idea why when Apache (Prefork) and MySQL are run on the same computer MySQL cpu time and process load increase. Whereas when they are on seperate machines the system is near idle, with the sum of their seperate loads not coming close to what it is when on one computer. Could perhaps there be a very mild "race" condition of sorts where Apache and MySQL are competing for IO time, even though it would seem they use/burden different IO resources (net/disk), or does PHP factor into this equation to be the bottleneck which causes MySQL's load to jump. I don't know if I will ever fully understand this phenomenon but it would be nice to have a better understanding of what's going on under the hood in case I (or others who read this) come accross it or something similar later.