Zabbix / Mysql network usage / db writes seem very high

[stever]

Hi -

We are running the latest zabbix (1.6.1), with a mysql back end, running Oracle enterprise linux 5 (basically RHEL5). We are seeing a LOT of network traffic being generated, most likely writes to the DB - I say writes because we see consistent, high outgoing traffic on the zabbix server, and corresponding high incoming traffic on our NFS file server (Netapp).

We see a constant 7Mb/s, but with the low number of hosts and items, this seems out of whack... zabbix is saying there are ~10 new values per second, this doesn't seem to justify 7Mb/s of data written to the network filesystem. Any ideas? Zabbix is the only thing running on this machine...

ZABBIX server is running Yes -
Number of hosts (monitored/not monitored/templates) 40 13 / 0 / 27
Number of items (monitored/disabled/not supported) 961 868 / 0 / 93
Number of triggers (enabled/disabled)[true/unknown/false] 188 188 / 0 [1 / 2 / 185]
Number of users (online) 4 2
Required server performance, new values per second 9.8967 -

[richlv]

you could try logging sql queries and then checking them to see whether it explains the traffic

[nelsonab]

That does seem to be pretty out of whack with what you would expect. Logging the queries may help but my gut tells me it won't give you the full picture.

See if you can run Wireshark on the link and have a look at the traffic and see if anything sticks out. My hunch is that the oracle client and server are very chatty, perhaps with many very small packets.

[bbrendon]

Maybe the switch can't find a home for a MAC address and is broadcasting to multiple ports?

[stever]

Hmm... I turned on query logging in mysql, and ran for about a minute. I unfortunately don't have a baseline to compare to, but I see these sort of queries, repeated for every item i have:

                     13 Query       select min(clock) from history where itemid=10005
                     13 Query       select min(clock) from history_uint where itemid=10005
                     13 Query       select min(clock) from history_str where itemid=10005
                     13 Query       select min(clock) from history_text where itemid=10005
                     13 Query       select min(clock) from history_log where itemid=10005
                     13 Query       select min(clock) from trends where itemid=10005

In my logs for 1 or 2 minutes of running, I see ~19,000 of these queries...

[nelsonab]

Hmmm... ya... that's not right.

Try and increase the log level on your server. You might also want to increase the log size as it will generate A LOT of logging very quickly. Let's see if that helps point to a bug or a misconfiguration.

[stever]

I got wireshark/ethereal running on the box, and did some more investigation. The thing that stands out is a constant mysql tmp file creation, write, access, and delete cycle.

I see it creating a file named #sql_174_0.MYI, writing to it, reading from it, and deleting it, constantly. This happens over and over again. Any ideas? I can't tell the exact directory from the wireshark logs, but from investigating elsewhere it seems to follow my mysql directory around, so I'm 99% sure the directory is somewhere under my mysql data dir (which is currently on NFS).

Ever seen anything like this?

Of note, I'm running RHEL5 (Oracle Enterprise linux 5), x86_64, zabbix 1.6.1, and mysql 5.0.45-7.el5.

[richlv]

it seems that you have been hit by http://www.mysqlperformanceblog.com/...porary-tables/

[Alexei]

For large (hundreds and thousands of devices) ZABBIX installations I would recommend keeping MySQL temporary directory (tmpdir) in memory filesystem like ramfs or tmpfs. Not 100% sure, but storing tmdir and especially MySQL itself on NFS is a very bad idea.

[stever]

it seems that you have been hit by http://www.mysqlperformanceblog.com/...porary-tables/

That definitely looked like what I was seeing, thanks for that. Today I upgraded to the latest 5.1 - 5.1.30 i think, and had high hopes. Unfortunately, I am still seeing the same network load after the upgrade. I even ran across the hidden StartDBSyncers parameter, but still no luck. I tried to throw some hardware at the problem as well, I put in place a tweaked my-large.cnf file for mysql, and made sure tmpdir was pointing to /dev/shm, again, no improvement.

I started thinking back through things, I first started off with postgresql, and found that performance was horrible - when zabbix was running a several hosts were being monitored, postgres was consuming all my cpu all the time. I have an identically configured postgres server elsewhere, running zabbix 1.4.6, and it works great.

So I started to suspect zabbix 1.6.1 is at fault here - maybe too chatty / too many sql queries? I ran a quick/crude test, as follows:

[root@corezabbix etc]# /etc/init.d/mysql stop; date; /etc/init.d/mysql start; /etc/init.d/zabbix_server_ctl start
Shutting down MySQL.... SUCCESS! 
Fri Jan  9 16:35:23 PST 2009
Starting MySQL. SUCCESS! 
/etc/init.d/zabbix_server_ctl start: zabbix_server started
......
....
..
[root@corezabbix etc]# /etc/init.d/zabbix_server_ctl stop; /etc/init.d/mysql stop; date
/etc/init.d/zabbix_server_ctl stop: zabbix_server process(es) stopped
Shutting down MySQL... SUCCESS! 
Fri Jan  9 16:37:17 PST 2009
[root@corezabbix etc]# wc -l /tmp/mysql.query.log 
23677 /tmp/mysql.query.log

So that works out to around 208 queries per second? Does that sound right, with around 1000 items, and zabbix reported 10 new values per second? Anybody have any similar metrics?

I tried on my "properly" working postgresql / zabbix 1.4.6 setup, and got the following:

[root@noc data]# /etc/init.d/postgresql reload; date
Fri Jan  9 16:49:18 PST 2009
.....
...
..
[root@noc data]# /etc/init.d/postgresql reload; date
Fri Jan  9 16:50:18 PST 2009
[root@noc data]# wc -l pg_log/postgresql-Fri.log 
7483 pg_log/postgresql-Fri.log

That got me ~124 queries per second, with a server with ~400 items monitored, can't tell what the new values per second value is with zabbix 1.4.5.... That doesn't sound very conclusive to me, but thought I'd put it out there...

[stever]

For large (hundreds and thousands of devices) ZABBIX installations I would recommend keeping MySQL temporary directory (tmpdir) in memory filesystem like ramfs or tmpfs. Not 100% sure, but storing tmdir and especially MySQL itself on NFS is a very bad idea.

NFS always seems to get a bad rap. When configured properly for production environments (using tcp, and proper mount options), it is a highly reliable, scalable filesystem - especially when backed by Netapp and their ONTAP operating system.

[nelsonab]

NFS always seems to get a bad rap. When configured properly for production environments (using tcp, and proper mount options), it is a highly reliable, scalable filesystem - especially when backed by Netapp and their ONTAP operating system.

I'll second that. I used to have the same opinion, but as I learned more and became exposed to even larger environments it's not that uncommon and at times considered to be a best practice. Netaps, are definitely fast, and expensive. ;-)

[richlv]

just some data : 7100 monitored items, 1.4.6, mysql.
qps graph shows me averages of 500-600, topping out at 4.57k qps during last week.