Installation guide for the Performance Monitor for MySQL

tags:

Table of Content

We kept the old installation guide.

Performance Monitor for MySQL (mpm)

Prerequisites
- Installation requirements
Overview
Modules
Installation of the Zabbix templates
Creating groups
Creating a host
Hook the Performance Monitor for MySQL into the Zabbix agent
Configuration of the Performance Monitor for MySQL Agent
- Database user other than root
Enable the host (database) for monitoring
Prerequisites for specific modules
- For the ndb module
- For the drbd module
Trouble shooting
- Troubleshooting the Zabbix server or the Zabbix Web Application
- Troubleshooting the Zabbix Agent
Start/stop scripts for Zabbix Agent and Server
MySQL Performance Monitor Upgrade
- General upgrade path
- Specific upgrade paths

For more information look also into our Forum or our Bug database.

Please let us know if there are some errors in this documentation or if something is not described clearly!

MySQL Performance Monitor as a Service

MySQL Performance Monitor MaaS installation Guide
Monitoring as a Service (MaaS)
Installation requirements
How to install MPM for MaaS
How to configure MPM for MaaS
- Database user other than root
- Queries sent against the database
Testing the MPM for Maas
Viewing Performance data
Continuously sending data
Upgrading the MaaS agent

Overview

The Performance Monitor for MySQL will be hooked into the Zabbix agent (zabbix_agentd). It consists of:

the Monitor script itself (FromDualMySQLagent.pl) which controls the other modules and its own module (FromDualMySQLagent.pm),
a template for the Performance Monitor configuration file (FromDualMySQLagent.conf.template) and
several different modules for monitoring MySQL (FromDualMySQL*.pm)

The architecture looks as follows:

Installation of the Zabbix templates

Step 1:

Choose the templates you need. The template mpm is mandatory. All other templates are optional but you have to load them if you want to monitor some specific storage engines or features.
If you do not want to think about install ALL templates. A typical good choice of templates for the server (host) is: mpm server and for the a normal MySQL database itself: process mysql myisam innodb. If you run MySQL Cluster you should choose the templatess: process ndb mysql.
If you have a master/slave set-up you should add in addition to the above recommendations the master and slave templates.

tar xf mysql_performance_monitor-latest.tar.gz
tar xf mysql_performance_monitor_templates-latest.tar.gz

Step 2:

Load the templates (*.xml) into the Zabbix monitor (leave all the checkboxes as is):

Go to:

Configuration → Templates → Import → Select the module → Import

There should not be any conflict with other rules because a different name space was chosen for the FromDual modules.

Creating groups

We found, that it is a good idea to create a host groups for your MySQL databases and one for your MySQL cluster to separate things. But you are free to have even a more granular separation or to have just one host-group.

We miss-use this host groups for separating our databases and our cluster. So the name could be a bit miss-leading how we used it for our purposes. We do not monitor hosts but databases/instances.

Go to:

Configuration → Host Groups → Create host group → then enter a group name for example as follows: Database Server and MySQL

Creating a host

For every database server (= host) and for every database instance ( = mysqld) we need a host (host is miss-leading here again because we need it for our database instances as well).

Go to:

Configuration → Hosts → Create host

Then enter a UNIQUE Name for this host (= server or database). Be very careful with choosing the Name (in other places also called Hostname). The Name is the identifier for authenticating the agent to the Zabbix server and is the marker where the data are stored. If the names do not match, the data cannot be delivered!

Add this host to the right group, add the IP address and Link at least the template Template_FromDual.MySQL.mpm from the Templates tab to it (and possibly some others you have chosen above). Then save the changes.

Example for the Server:

...

Example for the Database:

...

Hook the Performance Monitor for MySQL into the Zabbix agent

Untar the Performance Monitor for MySQL Agent to /opt.

Copy and rename the Performance Monitor for MySQL Agent configuration file template.

cd /opt
tar xf /download/mysql_performance_monitor_agent-latest.tar.gz 
ln -s mysql_performance_monitor_agent-latest mysql_performance_monitor_agent
cd mysql_performance_monitor_agent/etc
cp FromDualMySQLagent.conf.template /etc/zabbix/FromDualMySQLagent.conf
mkdir -p /var/log/zabbix /var/log/zabbix/cache
chown -R zabbix: /var/log/zabbix /var/log/zabbix/cache

Hook the Performance Monitor for MySQL Agent into the Zabbix Agent by adding the following line at the end of the Zabbix Agent configuration file (/etc/zabbix/zabbix_agentd.conf):
```
#
# /etc/zabbix/zabbix_agentd.conf
#

UserParameter=FromDual.MySQL.check,/opt/mysql_performance_monitor_agent/FromDualMySQLagent.pl /etc/zabbix/FromDualMySQLagent.conf
```
Then configure the Performance Monitor for MySQL Agent (/etc/zabbix/FromDualMySQLagent.conf).
Restart the Zabbix agent: service zabbix_agentd restart.

Configuration of the Performance Monitor for MySQL Agent

A typical configuration file for the Performance Monitor for MySQL Agent looks as follows:

[default]

Type         = mysqld
LogFile      = /var/log/zabbix/FromDualMySQLagent.log
Username     = root
Password     = <secret>
MysqlHost    = 127.0.0.1
MysqlPort    = 3306
ZabbixServer = localhost
Modules      = process mysql myisam innodb


[<db_server>]

Type         = mysqld
Modules      = mpm server

[<mysqld>]

Type         = mysqld
Modules      = process mysql myisam innodb

There is typically a default section where you can specify all the defaults which are valid for this server.

For every database (instance) you want to monitor you have to add an extra section. This allows you to monitor several MySQL databases running on the same server.

Important: The section name MUST match the Hostname which you have entered in the Zabbix server!!!

Database user other than root

If you plan to use a monitoring user other than root (which is not a bad idea) grant this user the PROCESS and the REPLICATION CLIENT privilege. Otherwise it cannot access the SHOW ENGINE INNODB STATUS and SHOW SLAVE STATUS command.

CREATE USER 'mpm'@'127.0.0.1' IDENTIFIED BY 'mpmsecret';
GRANT PROCESS ON *.* TO 'mpm'@'127.0.0.1';
GRANT REPLICATION CLIENT ON *.* TO 'mpm'@'127.0.0.1';

back

When you have adapted the configuration file to your needs restart the Zabbix agent as follows:

/etc/init.d/zabbix_agentd restart

Enable the host (database) for monitoring

When you have done all the steps above you can enable the monitored database server by clicking on its Not monitored status. In the availability you should see if a certain host is monitored and if not why...

The most common reasons why an agent is not monitored are:

Hostname on Zabbix Server and Section in Performance Monitor for MySQL Agent configuration file do NOT match.
Agent is down or not reachable (firewall, etc.).

To see if data is sent and what the actual values are you can go to:

Monitoring → Latest data → then filter by group and host:

Prerequisites for specific modules

For the ndb module

When you want to use the ndb module make sure that you have started your MySQL Cluster with the following parameters:

# config.ini

[NDBD DEFAULT]
MemReportFrequency            =  60
LogLevelStatistic             =   8

And run the following command in the management client:

ndb_mgm> ALL CLUSTERLOG STATISTICS=8;

The module needs the information written to the cluster log for sending data to the Zabbix server. With MySQL Cluster >= 7.1 this is not necessary any more!

For the drbd module

If the MySQL Performance Monitor agent is run under a different user than root not all information will be displayed correctly because of missing privileges.

You either have to run the FromDual MySQL Performance Monitor Agent under root (security!) or grant the user the agent is running under, the appropriate rights.

Trouble shooting

Troubleshooting the Zabbix server or the Zabbix Web Application

Make sure the database of the Zabbix server is running (otherwise the Zabbix web application will complain and the Zabbix server will not start).
Make sure the Zabbix server is running (check the zabbix_server.log for reasons, check if the database is up an running).

If everything is fine it should look as follows (Zabbix server is running):

Troubleshooting the Zabbix Agent

Make sure the Zabbix Agent is running (check the Zabbix agent logfile).
Check if the Status of the host is on Monitored and if Availability is on green.
If Availability is red and you see the following message:
```
Got empty string from [137.58.246.161]. Assuming that agent dropped connection because of access permissions
```
Check if the hostname of the Zabbix Agent matches with the name on the host in the Zabbix server (case sensitive, white space etc. matters!).
Set DebugLevel = 4 in the Zabbix Agent configuration file, restart the agent and see if you can see any reason in the agent log file (do not forget to set it back afterwards because it becomes very verbose).

Try to send a message manually from as the Zabbix Agent to the Zabbix Server:

sudo -u zabbix zabbix_sender --zabbix-server=192.168.0.1 \
--host=mysql_2_4_2 --key=FromDual.MySQLmysql.Questions \
--value="123456789" --verbose
Info from server: "Processed 1 Failed 0 Total 1 Seconds spent 0.000180"
sent: 1; skipped: 0; total: 1

Check under: Monitoring → Latest data if the data have been arrived (timestamp).
Try to reach the Zabbix Agent from as the Zabbix Server:
```
sudo -u zabbix zabbix_get --host=192.168.0.33 --source-address=192.168.0.1 --key="FromDual.MySQL.check"
echo $?
141
```
This typically means that host does not match.
```
sudo -u zabbix zabbix_get --host=192.168.0.33 --source-address=192.168.0.1 --key="FromDual.MySQL.check"
ZBX_NOTSUPPORTED
```
This means that the item FromDual.MySQL.check is not known to the Zabbix Server. Then you have possibly forgotten to ad the mpm module template to the host you want to monitor. If you are using an old mpm version you have to use the mysql module instead.
If there are no cluster information reported, make sure ALL CLUSTERLOG STATISTICS=8 is set.
If you run the process module you should make sure, that the user running the Zabbix agent has access to the pid file. Ideally you run the Zabbix agent with the same user as the database or MySQL Cluster process runs or you configure MySQL in the way that the PID file located on a location where also other processes can access to.

If all those hints do not help or if you have some comments or feedback please let us know at our MySQL Performance Monitor Forum (we do NOT cover Zabbix problems).

Start/stop scripts for Zabbix Agent and Server

Our philosophy is, that the Zabbix Agent and the Zabbix Server is running under the user mysql. For the Zabbix Agent, this is necessary because otherwise we do not have access to the PID file of the mysqld.

To install the start/stop scripts copy the bin/zabbix_agentd.init and the bin/zabbix_server.init to /etc/init.d.

Under Ubuntu you have to enable them with the following command:

# sudo cp bin/zabbix_agentd.init /etc/init.d/zabbix_agentd
# sudo cp bin/zabbix_server.init /etc/init.d/zabbix_server

# sudo update-rc.d zabbix_server defaults
# sudo update-rc.d zabbix_agentd defaults

MySQL Performance Monitor Upgrade

General upgrade path

The Performance Monitor for MySQL can be upgraded on the fly without any changes in configuration or any downtime. We typically do it like this:

cd /opt
tar xf /download/mysql_performance_monitor_agent-m.n.tar.gz
rm -f mpm ; ln -s mysql_performance_monitor_agent-m.n mpm

Specific upgrade paths

to v0.9
For the MPM agent v0.9 we use a Perl module which might not be available on your system. This module has to be installed beforehand. How to install these modules see here.

MySQL Performance Monitor MaaS installation Guide

MySQL Performance Monitor (MPM) you can run in 2 different modes:

Locally in your company on your companies servers.
As Monitoring as a Service (MaaS). This is what we will cover in this installation guide.

back

Monitoring as a Service (MaaS)

In this installation guide we only cover the 2^nd mode: Installing the MPM as a monitoring service agent. For this you have to install the MPM agent on your MySQL database server. He will collect statistical data about your server and your database and sends them to the remote service which is provided by FromDual for its customers.

The data are uploaded via a http file upload request. So only one way communication is possible! We cannot access back to your system!

On the Monitoring Server only you (and our engineers) can see your performance data.

Installation requirements

The MPM is based on Perl and needs some Perl modules. How you can install them, please see .

How to install MPM for MaaS

You can download the MPM Agent from here: www.fromdual.com/download.

Unpack it to a location on your database server, for example to /opt ideally as the user your MySQL database is running (typically mysql). Any other user is possible as well but you will not have all features available. You can also choose root but for security reasons, this is not recommended.

Further we recommend to create a link to the newly created directory for a more easy upgrade in the future:

su - mysql
cd /tmp

wget http://www.shinguz.ch/download/mysql_performance_monitor-latest.tar.gz
tar xf mysql_performance_monitor-latest.tar.gz
cd /opt/
tar xf /tmp/mysql_performance_monitor_agent-0.9.tar.gz
ln -s  mysql_performance_monitor_agent-0.9 mysql_performance_monitor

How to configure MPM for MaaS

Copy the configuration template outside of the mysql_performance_monitor directory structure (to not loose or forget it during a future upgrade). For example like this:

cp mysql_performance_monitor/etc/FromDualMySQLagent.conf.template \
/etc/mpm.conf

To make the MPM Agent work in a MaaS set-up you need a hash key from us so we can identify you non-ambiguous and we can make sure nobody is doing nasty things with your service. You can request such a hash key, if you have not received one yet from here. To allocate you the correct resources we further need to know from you:

The company name you want to run under (for example FromDual)
A/the server name the agent is running on (for example master)
Names which identifies your database (for example db1)

All those 3 identifiers must uniquely identify you database you want to monitor. When we have these information we can create the service for you and provide you a configuration file. (Possibly we will automatize these information in the future...)

The configuration file will look as follows:

[default]

Debug         = 2
LogFile       = /tmp/FromDualMySQLagent.log
CacheFileBase = /tmp/FromDualAgentCache

Modules       = process mysql myisam innodb

MaaS          = on
Hash          = <hash>
Methode       = http
Url           = http://support.fromdual.com/maas/receiver.php


[<company>.<server_name>]

Modules       = mpm server


[<company>.<server_name>.<db_name>]

Username      = root
Password      = secret
MysqlHost     = 127.0.0.1
MysqlPort     = 3306
PidFile       = /var/lib/mysql/<server_name>.pid

We need one section for the server (host) and one section per database (typically only one per server).

Database user other than root

If you plan to use a monitoring user other than root please look here.

Queries sent against the database

The following queries are sent against the database:

SHOW BINARY LOGS
SHOW /*!50000 ENGINE */ INNODB STATUS
SHOW ENGINE NDB STATUS
SHOW /*!50000 GLOBAL */ STATUS
SHOW GLOBAL VARIABLES
SHOW MASTER STATUS
SHOW SLAVE HOSTS
SHOW SLAVE STATUS
SELECT counter_name, SUM(val) AS val FROM ndbinfo.counters GROUP BY counter_name
SELECT MAX(total) AS total, MAX(used) AS used FROM ndbinfo.logbuffers
SELECT memory_type, SUM(used) AS used, SUM(total) AS total FROM ndbinfo.memoryusage GROUP BY memory_type
SELECT ROUND(AVG(uptime), 0) AS uptime, SUM(start_phase) AS start_phase FROM ndbinfo.nodes
SELECT resource_name, SUM(reserved) AS reserved, SUM(used) AS used, SUM(max) AS max FROM ndbinfo.resources GROUP BY resource_name
SELECT status, COUNT(status) AS cnt FROM ndbinfo.transporters GROUP BY status
SELECT REPLACE(Name, ' ', '_') AS Name, Value FROM INFORMATION_SCHEMA.PBXT_STATISTICS
SELECT SUM(compress_time) AS compress_time, SUM(uncompress_time) AS uncompress_time FROM information_schema.INNODB_CMP
SELECT SUM(total) AS total, SUM(used) AS used FROM ndbinfo.logspaces
SELECT SUM(trx_rows_locked) AS rows_locked, SUM(trx_rows_modified) AS rows_modified, SUM(trx_lock_memory_bytes) AS lock_memory FROM information_schema.INNODB_TRX
SELECT LOWER(REPLACE(trx_state, " ", "_")) AS state, count(*) AS cnt from information_schema.INNODB_TRX GROUP BY state

Testing the MPM for Maas

If you have put this in place we can run the MPM agent for a first test:

/opt/mysql_performance_monitor/FromDualMySQLagent.pl /etc/mpm.conf
1

If the agent returns "1" then it seems to work correctly... Have a look at the agent log (/tmp/FromDualMySQLagent.log) for hidden problems. If you do not see there any message it should have worked correctly.

Then you should see the first data arriving in the Zabbix Monitor.

Viewing Performance data

To look at your performance data go to the following web-site: support.fromdual.com/mpm and log in with the credentials we provide you.

Then go to to Monitoring → Latest data and choose the right Group and Host (it may take some time until Zabbix recognizes a new agent (up to a minute) and displays its data correctly).

If they have arrived you can continue sending your data to the MPM Service. If not please get in contact with us for finding the problem.

Continuously sending data

If everything works correctly you can include the agent into the crontab (of the mysql user):

* * * * *  /opt/mysql_performance_monitor/FromDualMySQLagent.pl \
/etc/mpm.conf 1>/dev/null 2>&1

Upgrading the MaaS agent

The MaaS agent is upgraded in the same way as the normal MPM agent. For more details see here.

aria	Module to monitor the Aria Storage Engine status variables.
drbd	Module to monitor DRBD device status information.
galera	Module to monitor Galera Cluster for MySQL.
innodb	Module to monitor the InnoDB Storage Engine status variables.
master	Module to monitor a Replication Master in a MySQL Master/Slave set-up.
mpm *	Module to monitor the mpm agent itself. This module is mandatory because it triggers the send process.
myisam	Module to monitor the MyISAM Storage Engine status variables.
mysql	Module to monitor general MySQL status variables.
ndb	Module to monitor a MySQL Cluster set-up.
pbxt	Module to monitor the PBXT Storage Engine status variables.
process	Module to monitor all kinds of Linux processes (`mysqld`, `ndbd`, etc.)
server	Module to monitor a Linux server (additional items related to database use).
slave	Module to monitor a Replication Slave in a MySQL Master/Slave set-up.

Languages

Login

Search form

You are here

Installation guide for the Performance Monitor for MySQL

Table of Content

Performance Monitor for MySQL (mpm)

MySQL Performance Monitor as a Service

Navigation

Trademarks