New MySQL monitoring recipe provided

[MrKen]

Hi Mike,

When I set up the mysql monitoring, I get the following error in the Zabbix_Server log as well as on the Config of Items page:

6373:20090810:160251 Item [Server_ZABBIX_1.6:mysql.daily] error: Type of received value [Physical Memory: 2G, Swap: 2GSkipping daily gathering1] is not suitable for value type [Numeric (integer 64bit)]
6373:20090810:160251 Parameter [mysql.daily] is not supported by agent on host [Server_ZABBIX_1.6] Old status [0]

I'm sure this is wrong, but I changed the Type of information from Numeric to Character, and Live Controller is giving me values like this in Latest data:

Physical Memory: 2G, Swap: 2G
zabbix_sender -z 123.40.5.60,124.178.2.60 -p 10051 -s Server_ZABBIX_1.6 -k mysql-debug.Available -o 1
zabbix_sender -z 123.40.5.60,124.178.2.60 -p 10051 -s Server_ZABBIX_1.6 -k mysql-debug.Uptime -o "12d 5h 37m 9s"
But, there are no other values being collected.

I changed it back to Numeric (unsigned), restarted the Agent, but still just get the error above. On a positive note /tmp/zabbix_mysql.dat is full of interesting data.

Please tell me I'm stupid, but I'm trying to monitor mysql on the Zabbix_Server. Is your script designed for this purpose?

Zabbix_Server & Agent 1.6.5
Mysql 5.0.45

Any ideas?

MrKen

[mikecrowe]

Hi MrKen,

The way this works is as follows:

1. There are 2 "active" entries in zabbix, mysql.daily and mysql.live (does the 1/day and 1/2m calls to the target machine respectively).
2. The script uses zabbix_sender to send the 100+ entries to zabbix. Each of these entries are "trapper" entries, so they are passive in zabbix until it receives something from that host.

All mysql.daily and mysql.live return is a 1, because they just kick off the script to initiate the sending of all those entries (i.e. we don't need that 1, it's just there).

If you are receiving "Physical Memory: 2G, Swap: 2GSkipping daily gathering1", either DEBUG is still true, or there's an errant echo statement in the script.

LMK if this helps
Mike

[MrKen]

Got SIGPIPE. Where it came from???

Thanks for your reply Mike.

Yesterday, thinking that the mysql.php may have been corrupted, I replaced the script but forgot to set Debug to false. My mistake.

So, today I set Debug to false, stopped and started the agent numerous times, and finally there is no error on the Items config page. However, in Latest Data I have
MySQLaily Controller latest value = 1
MySQL:Live Controller latest value = 0

The agentd.log set to debug 4 has a whole bunch of these:

29802:20090811:134746 Before
29802:20090811:134746 Run remote command [php /etc/zabbix/mysql.php live admin admin] Result [1] [1]..
.
29802:20090811:134746 Sending back [1]
29802:20090811:134746 Got SIGPIPE. Where it came from???
29802:20090811:134746 Process listener error: ZBX_TCP_WRITE() failed [Broken pipe]

*You had this problem in Feb2008, but never got a reply to your post*

And in the zabbix_mysql.log there are lots of these, which I'm sure are a result of the broken SIGPIPE:

[root@zabbix zabbix]# cat /tmp/zabbix_mysql.log
Sending failed. Use option -vv for more detailed output.
Sending failed. Use option -vv for more detailed output.
Sending failed. Use option -vv for more detailed output.

My guess is that zabbix_sender simply timed out due to the Broken pipe, because yesterday zabbix_sender was actually sending all the data to zabbix_server.log

In the agent.conf I increased the number of StartAgents from 5 to 10. No change.
I DisabledActive. No change.

I'm stumped! Searching "Got SIGPIPE. Where it came from???" doesn't really come up with anything tangible.

Any ideas?

[mikecrowe]

Where did you get your zabbix sender? I found some distro's that have old versions that use different command line parameters. Here's what I see:

zabbix_sender -h
ZABBIX send v1.4.2 (20 August 2007)

usage: zabbix_sender [-Vhv] {[-zps] -ko | -i <file>} [-c <file>]

Options:
-c --config <File> Specify configuration file
-z --zabbix-server <Server> Hostname or IP address of ZABBIX Server
-p --port <Server port> Specify port number of server trapper running on the server. Default is 10051
-s --host <Hostname> Specify host name. Host IP address and DNS name will not work.

-k --key <Key> Specify metric name (key) we want to send
-o --value <Key value> Specify value of the key

-i --input-file <input_file> Load values from input file
Each line of file contains: <zabbix_server> <hostname> <port> <key> <value>

-v --verbose Verbose mode, -vv for more details

Other options:
-h --help Give this help
-V --version Display version number

You may need to recompile the agent/zabbix sender to get a more up-to-date version.

[MrKen]

Hi Mike,

I'm using: zabbix_sender -h
ZABBIX send v1.6.5 (revision 7442) (3 April 2009)

usage: zabbix_sender [-Vhv] {[-zpsI] -ko | [-zpI] -i <file>} [-c <file>]

But I don't think that's the problem.

I'm not sure what to try next, but I will try something. I will also try installing on other hosts and see how it goes, but it was the zabbix db that I was most concerned about.

But what I really want to know is "Got SIGPIPE. Where it came from???"

MrKen

[mikecrowe]

Yeah, that was almost too long ago to remember how I solved.

The command I'm using from within the script is:

zabbix_sender -z $server -p 10051 -s $host -k mysql.$var -o $val

What happens when you do this from the command line?

[MrKen]

Hi Mike,

I chose a random Trapper item, ran zabbix_sender, and . . .

zabbix_sender -z <IPofZabbix> -p 10051 -s Server_ZABBIX_1.6 -k mysql.Version -o 7
Info from server: "Processed 1 Failed 0 Total 1 Seconds spent 0.002670"
sent: 1; skipped: 0; total: 1

In Latest data:
MySQL: Current version 12 Aug 09:09:38 7

I shall keep digging.

MrKen

[MrKen]

Hi Mike,

Good news and bad news.

This morning while attempting, yet again, to get the SQL monitoring running, I noticed in a 'ps -ef | grep zabbix' that zabbix_sender was trying to send to two IP addresses at the same time. Let me explain that this Server is my main ZabbixServer and the other IP is for the backup ZabbixServer. In the agentd.conf at the top where it says "List of comma delimited IP addresses,..etc"
Server=123.40.5.60,124.178.2.60 {Not real IP's}

After I removed the second IP address, the SQL monitoring started.

That's the good news.

Now the bad news. I really need to be monitoring the backup server. Is there anything that I can do in the script so that it only uses the first IP and not the second?

You can see in my post above (10Aug) that zabbix_sender is trying to send to both IP addresses. I wonder if zabbix_sender can handle that, I guess not.

Thanks
MrKen

[MrKen]

Well, now that I have some data, i have some questions.

The graphs for 'MySQL:Network bytes received' and 'MySQL:Network bytes sent' just keep going up, up, up....

Looking at the Item configuration, shouldn't they have a Custom Multiplier of 8, and Store Value = Delta (speed per second) ?

There are also many other items which are returning data measured in b, Kb, Mb and Gb, but I'm not sure why you have the item set to measure Units as b or B. Some examples:

MySQL:Connections:Refused
MySQL:Connections:Successful
MySQL:Connections:Total
MySQL:Queries:Selects w/full table scans
MySQL:Query cache hits

and many more (according to latest data)

Is there some reason for setting the Item Unit to b or B?

Thanks
MrKen

[antani]

Links are broken

Links don't work anymore. Do you know where I can download the files? Thanks

[MrKen]

I believe the wiki is undergoing some renovation at the moment.

Maybe, try again in a couple of days.

[MrKen]

Links don't work anymore. Do you know where I can download the files? Thanks

Looks like the path has changed. It's here now
http://www.zabbix.com/wiki/howto/monitor/db/mysql/extensive_mysql_monitoring_including_replication

[antani]

great. thanks

[matata]

Hi all
unfortunately all the files (xml and php script) are not found when downloading from the link above

Thanks,

[richlv]

links should be fixed now.
note that you can also leave comments on the wiki when logged in - that's the preferred way to point out problems there