You are here

Installation guide for the Performance Monitor for MySQL

Important: For installing the new PHP version of mpm called FromDual Performance Monitor for MySQL and Mariadb (fpmmm) please look at the fpmmm Installation Guide.

Table of Content

Performance Monitor for MySQL (mpm)

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

Prerequisites

Important: If you want to install the MySQL Performance Monitor as a Service (MaaS) this is the wrong part! Please go here.

For using the Performance Monitor for MySQL (mpm) you have to meet the following prerequisites:

  • Have a Zabbix server and the Zabbix web frontend (v2.0 or newer) installed and properly running. The Zabbix server is only available for Linux/Windows systems. How to install Zabbix server you can find here: xxx.
  • Have a Zabbix agent installed and running (v2.0 or newer).
    Important: The Performance Monitor for MySQL was only tested on Linux. It may work on other Unixoides but most probably will NOT work with Windows yet. Please let us know if you plan to run mpm or mpm MaaS on Windows...

Installation requirements

The MPM is based on Perl and needs some Perl modules:

  • libfile-which-perl
  • libwww-perl
  • perl-DBD-MySQL
  • perl-Digest-SHA
  • perl-Time-HiRes (Debian/Ubuntu: its in core included)
  • perl-Crypt-SSLeay (if you want to make use of https)

You can install these Perl modules as follows:

On Ubuntu/Debian:

apt-get install libfile-which-perl libwww-perl libdigest-sha-perl \
libdbd-mysql-perl libtime-hires-perl libcrypt-ssleay-perl

or on CentOS/RedHat:

# perl-File-Which is in the Dries repository
# perl-Crypt-SSLeay is only needed for https
yum install perl-File-Which perl-libwww-perl perl-Digest-SHA perl-DBD-MySQL perl-Time-HiRes \
perl-Crypt-SSLeay

or on SLES 11 SP3 (from Amazon):

zypper install perl-File-Which perl-DBD-mysql

And for the zabbix-agent (Install on openSUSE / SLES):

zypper addrepo http://download.opensuse.org/repositories/server:/monitoring/SLE_11_SP3/ server_monitoring
zypper update
zypper install zabbix-agent

or directly via CPAN:

# perl -MCPAN -e shell
cpan> install File::Which
cpan> install Bundle::LWP
cpan> install Digest::SHA
cpan> install DBD::mysql
cpan> install Time::HiRes
cpan> install Crypt::SSLeay

back

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:

mpm_inst_guide00.png

Modules

The MySQL Performance Monitor agent is split into several different modules. For every module there is a:

  • Zabbix template (Template_FromDual.MySQL.*.xml)
  • MySQL Performance Monitor agent Perl module (FromDualMySQL*.pm)

Each module is for its own specific purpose. Available modules are:

ariaModule to monitor the Aria Storage Engine status variables.
drbdModule to monitor DRBD device status information.
galeraModule to monitor Galera Cluster for MySQL.
innodbModule to monitor the InnoDB Storage Engine status variables.
masterModule 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.
myisamModule to monitor the MyISAM Storage Engine status variables.
mysqlModule to monitor general MySQL status variables.
ndbModule to monitor a MySQL Cluster set-up.
pbxtModule to monitor the PBXT Storage Engine status variables.
processModule to monitor all kinds of Linux processes (mysqld, ndbd, etc.)
serverModule to monitor a Linux server (additional items related to database use).
slaveModule to monitor a Replication Slave in a MySQL Master/Slave set-up.

* required modules

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

mpm_inst_guide01.png

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:

...

mpm_inst_guide02.png

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';
-- For the security module
GRANT SELECT ON mysql.user 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...

mpm_inst_guide03.png

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:

mpm_inst_guide04.png

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):

mpm_inst_guide05.png

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:

  1. Locally in your company on your companies servers.
  2. 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 2nd 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 cus­tomers.

mpm_inst_guide06.png

The data are uploaded via a http file upload request. So only one way communication is possible! We cannot ac­cess 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 data­ba­se 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-ambig­uous 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 re­sources 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 informa­tion 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/FromDualMySQL­agent.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 cre­dentials we provide you.

Then go to to Monitoring → Latest data and choose the right Group and Host (it may take some time until Zab­bix 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.