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)
- 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
- Enable the host (database) for monitoring
- Prerequisites for specific modules
- Trouble shooting
- Start/stop scripts for Zabbix Agent and Server
- MySQL Performance Monitor Upgrade
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
- Testing the MPM for Maas
- Viewing Performance data
- Continuously sending data
- Upgrading the MaaS agent
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...
The MPM is based on Perl and needs some Perl modules:
- 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:
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
# 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
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 (
- a template for the Performance Monitor configuration file (
- several different modules for monitoring MySQL (
The architecture looks as follows:
The MySQL Performance Monitor agent is split into several different modules. For every module there is a:
- Zabbix template (
- MySQL Performance Monitor agent Perl module (
Each module is for its own specific purpose. Available modules are:
|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 (|
|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.|
* required modules
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
tar xf mysql_performance_monitor-latest.tar.gz tar xf mysql_performance_monitor_templates-latest.tar.gz
Load the templates (
*.xml) into the Zabbix monitor (leave all the checkboxes as is):
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.
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.
Configuration → Host Groups → Create host group → then enter a group name for example as follows: Database Server and MySQL
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).
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:...
- Untar the Performance Monitor for MySQL Agent to
- 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 # UserParameter=FromDual.MySQL.check,/opt/mysql_performance_monitor_agent/FromDualMySQLagent.pl /etc/zabbix/FromDualMySQLagent.conf
- Then configure the Performance Monitor for MySQL Agent (
- Restart the Zabbix agent:
service zabbix_agentd restart.
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!!!
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';
When you have adapted the configuration file to your needs restart the Zabbix agent as follows:
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:
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!
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.
- 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.logfor reasons, check if the database is up an running).
If everything is fine it should look as follows (Zabbix server is running):
- 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 [18.104.22.168]. 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!).
DebugLevel = 4in 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.checkis 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=8is 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).
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
To install the start/stop scripts copy the
bin/zabbix_agentd.init and the
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
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
- 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 (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.
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 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.
The MPM is based on Perl and needs some Perl modules. How you can install them, please see .
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
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).
If you plan to use a monitoring user other than root please look here.
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
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.
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.
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
The MaaS agent is upgraded in the same way as the normal MPM agent. For more details see here.