New MySQL monitoring recipe provided

[lostmarbles]

Hi Mike!

Thanks for validating! I completed the install.

Now, the very **dumb** question.

Where do I monitor your excellent parameter?

I looked everywhere in the "Monitor" part of the menu but only found something under Reports->MOST BUSY TRIGGERS TOP 100

Where do I actual monitor these metrics?

[mikecrowe]

Latest Data, then select the mysql machine in the Host drop-down.

Now, here's a subtle point: As you get this up and going, there 2 classes of communications going on here:

• Zabbix Server -> MySQL machine
  • mysql.live
  • mysql.daily
• MySQL machine -> Zabbix Server
  • Whole bunch of items that are trappers (i.e. passive, sent by mysql.php)

As you are getting this up and going, the mysql.live and mysql.daily must be active. If you activate them and they fail, you need to go back here and re-activate them (just click on "Not supported" link). After this, wait a few seconds and refresh. You may have to do this several times, until the whole process is fleshed out. Here's what I typically do:

1. Load the templates and get the scripts ready on the client machine
2. Enable the live and daily items in Configuration -> Items for the selected machine
3. Wait a bit for Zabbix to contact the client machine
4. Click on the Items breadcrumb at the top of the web page (you can't click on refresh, as it will try and post the command to activate again)
5. If it is back to Not Supported, check out the error message in the right column to see what went wrong.
6. Lather, rinse, repeat until all issues have been solved

Once you have reviewed and understand what you are looking for, you can create graphs/screens of performance that you like.

[lostmarbles]

Hi Mike,

Yes, thanks. We did all that and everything is green and active everywhere, but in the Monitoring->Latest Data menu, nothing for your MySQL data, as you can see from this cut and paste:

image Description	Last check	Last value	Change	History
image Availability (51 Items)
image CPU (7 Items)
      CPU idle time (avg1)	02 Mar 15:17:14	73.35 	+0.37	Graph
      CPU nice time (avg1)	02 Mar 15:17:14	0.02 	+0	Graph
      CPU system time (avg1)	02 Mar 15:17:15	6.63 	-0.16	Graph
      CPU user time (avg1)	02 Mar 15:17:06	21.03 	-0.10	Graph
      Processor load	02 Mar 15:17:11	1.27 	-0.11	Graph
      Processor load15	02 Mar 15:17:01	1.45 	+0.02	Graph
      Processor load5	02 Mar 15:17:12	1.41 	-0.01	Graph
image Filesystem (44 Items)
image General (7 Items)
image Integrity (6 Items)
image Log files (1 Items)
image Memory (5 Items)
image Network (6 Items)
image OS (8 Items)
image Performance (13 Items)
image Processes (10 Items)
image Services (7 Items)
image Website Check (5 Items)
image - other - (8 Items)

[mikecrowe]

I don't think you have the template activated for that host. It should look like:

Memory (5 Items)
MySQL (185 Items)
MySQL: Accounts or Users excessive revokes 02 Mar 01:07:03 0 - Graph
MySQL: Accounts with Alter privileges 02 Mar 01:06:52 8 - Graph
MySQL: Accounts with blank host or wildcard specifiers 02 Mar 01:06:51 1

[lostmarbles]

Sorry Mike, I don't know what I am missing, LOL. I followed the instructions :-) and there was no instruction to do anything except what we said were the necessary steps

Are their other steps not covered in what we discussed?

BTW, here is the configuration of items, where I assume we check to see if they are active.

CONFIGURATION OF ITEMS 	

ITEMS 	
[Hide disabled items] Group  Host  
Description	Key	Update interval	History	Trends	Type	Status	Applications	Error
MySQL: Accounts or Users excessive revokes	mysql.Excessive_revokes	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Alter privileges	mysql.Alter_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with blank host or wildcard specifiers	mysql.Accounts_with_broad_host_specifier	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with blank password	mysql.Accounts_without_password	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with blank usernames	mysql.Anonymous_accounts_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Delete privileges	mysql.Delete_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Drop privileges	mysql.Drop_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with File privileges	mysql.File_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Grant privileges	mysql.Grant_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Insert privileges	mysql.Insert_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Lock privileges	mysql.Lock_tables_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Process privileges	mysql.Process_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Shut Down privileges	mysql.Shut_down_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Super privileges	mysql.Super_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Accounts with Update privileges	mysql.Update_priv_count	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Architecture: Hardware supports total memory	mysql.Architecture_handles_all_memory	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Architecture: Max mem MySQL could use	mysql.Maximum_memory_total	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Architecture: MySQL max mem over 32-bit capabilities	mysql.Maximum_memory_exceeds_32bit_capabilities	300	7	365	ZABBIX trapper	Active 	MySQL	-
MySQL: Architecture: MySQL may use more memory than available	mysql.Maximum_memory_over_physical	300	7	365	ZABBIX trapper	Active 	MySQL	-


--- and so forth --- all active items ----

[mikecrowe]

What do mysql.daily and mysql.live look like in your configuration?

[lostmarbles]

What configuration? Agent side or server side?

[mikecrowe]

On the Zabbix server, in Configuration -> Items for the host where MySQL is being monitored, the mysql.live and mysql.daily items are the two key items which gather the info from the remote server.

What do those say?

[lostmarbles]

Active.

I have it working now, thanks. I had to delete the templates and import again. Seem so be OK, now, thanks.

I had to link the template to the host, so the steps are:

• Upload the mysql.php file to the directory of choice
• Modify the zabbix_agentd.conf file accordingly
• Import the provided template in the server control panel
• Link the imported template to the host in the control panel
• Restart the zabbix agent

[mikecrowe]

Excellent. Enjoy!

[lostmarbles]

Excellent. Enjoy!

Thank YOU Mike!

Very cool.....

OBTW, there is an error in the "Change" part of this trigger:

MySQL: Query cache fragmented > 20% on www.server.com
{www.server.com:mysql.Percent_query_cache_fragment ation.last(0)}>20

Incorrectly suggesting change action:

Binlog provides point-in-time recovery. You probably want this enabled.

[lostmarbles]

Hi Mike!

My marbles are lost again

I made a change to my.cnf and restarted mysql. All is fine and the database is working correctly.

However, after restarting mysql, zabbix has stopped updating your template.

I have tried restarting the agent on the remote mysql host, but that was no joy.

Any clues no how to get zabbix collecting the mysql info again?

[lostmarbles]

Nevermind....

It seems to have "come back from the dead" after a few hours of sleeping

[lostmarbles]

Hi Mike and All!

I have been using this excellent MySQL monitoring package for around a week and have decided to use the code to rewrite it to be more robust, as follows:

First, I will break the large application into 4 or 5 smaller ones, for example MySQL Configuration, MySQL Security, MySQL Queries and Connections, MySQL MyISAM, MySQL Innodb ... something like this.

Then, I will also change the agent script to run multiple scripts from cron and write the data to a tmp file instead of sending it directly to the server using trapper and zabbix_sender.

Then, I will build and reorganize the new application templates to use Zabbix Agent instead of trapper, which I have found to be a bit unreliable.

In my rewrite of this package, I will also make the update frequencies more flexible.

The basic code is solid and it a great base; however, I need to rewrite it too be more robust and bullet proof. Will post when I have time to rewrite and test.

Cheers.

[lostmarbles]

Kindly note in the current PERL script, I think the code below:

$engines = array('have_myisam'=>'YES','have_memory'=>'YES');

should actually be:

$engines = array('have_myisam'=>'YES','have_innodb'=>'YES');