MySQL Performance Monitoringwith ZabbixAn alternative to the MySQL EnterpriseMonitor?by Oli omdual.comwww.fromdual.com1

How many of you . monitor their database servers? monitor their (MySQL) databases? monitor performance metrics? all the others: Why not?www.fromdual.com2

Linux monitoring Question: What is your favourite Linux monitoringapplication? 1187 votes, April 2010 Manipulated? Lit: Linux Journal, [1]www.fromdual.com3

What to monitor? Devices / Software Server, Router, Switches, I/O systems etc. Operating System, Networks, Applications, etc.Incidents DB down, Replication stopped, Server not reachable, etc.Critical Events Disk more than n% full or less than m Gbyte free,Replication more than n seconds lagging, Data node down,100% CPU utilization, etc. Alert, immediate intervention, fire fightingwww.fromdual.com4

What to monitor? Trends (includes time!) Graphs How long does it take until my disk is full? my Index Memory is filled up?When does it happen? How often does it happen? Does it happen periodically? Once a day? Always at Sunday night?How does it correlate to other informations? Peak? Backup?I/O problems during our backup window?Reading the patterns! this can help us to find the root cause of problems www.fromdual.com5

How to monitor? Basic solutions: top, vmstat, iostat, mytop, innotop, SHOWGLOBAL STATUS, SHOW INNODB STATUSCLI!, no graphs, no log term information, butgood for adhoc analysis!Graphical solutions Nagios, Cactii, Zabbix, Typically NOT specialised in DB monitoring.www.fromdual.com6

Focus Database Server TrendsNot the other stuff. Problem: Monitoring solutions are generic A database is a specific application!www.fromdual.com7

MySQL monitoring Question: What monitoring software do you use? 907 votes, January 2005 Pre MySQL Enterprise Monitor era Lit: MySQL, [1]www.fromdual.com8

The MySQL Enterprise Monitor [4]www.fromdual.com9

The MySQL Enterprise Monitor Pros Tailored for MySQL Easy install Handy GUI Query Analyser (caution!) Replication MonitorCons Only for paying customers Proprietary code Does NOT monitor MySQL Cluster yet [2], [3] Very resource intensive (Java App Server!) Future: Integration into Oracle Enterprise Manager? MariaDB, Aria, XtraDB, PBXT, Drizzle, Sphinx SE, ?www.fromdual.com10

Kontrollbase [5]www.fromdual.com11

Kontrollbase Pros Tailored for MySQL Lightweight application (PHP) Open Source (New BSD) Easy install ? Replication MonitorCons Did not work for me (non default installations : ( )! Answer from developer: “it works for me and my customers.” Found a few “bugs”. Does NOT monitor MySQL Cluster? Query Analyser MariaDB, Aria, XtraDB, PBXT, Drizzle, Sphinx SE, ?www.fromdual.com12

And now? MEM nogo Kontrollbase no work nogo Self written? no time nogo Nagios? complicated? Shudder. nogo Other solutions? A friend told me that he will evaluateZabbix for monitoring MySQL.www.fromdual.com13

Zabbix [6]It claims: [7] All in one solution Open Source Performance monitoring Agents for all platforms Availability and SLA reporting Collection of any data Great graphs and network maps Commercial support Annual support agreements Trouble free deployment Professional Services Technical Account Manager Zabbix Training Upgrade Servicesetc, etc. Wow!Scalability Up to 100k monitored devices Up to 1M of metrics Thousands of checks per second Small to large distributed setups Easy maintenancewww.fromdual.com14

Zabbix architecture Zabbix is a typical Agent – Server set .logzabbixserver.logzabbixproxyServer ZWeb browserServer AzabbixserverServer CApachezabbix eDeviceDevicePHPServer .fromdual.com15

Installation of Zabbix Download Server (Source only, for Linux and Windows only) Agent (Binaries for: AIX, FreeBSD, HP UX, Linux, OpenBSD, Solaris,Windows)Packages from your favourite Linux DistroDocumentation tallation network monitoringRecommendation: One thing after the other Server first, Web interface next, then agentswww.fromdual.com16

Install Zabbix server Create zabbix user Untar source tarball Create zabbix database and populate it ./configure ; make ; make install Some packages may be missing. Does not take too long ( 10 min)Create configuration file for zabbix server A MySQL (PostgreSQL, ) installation is needed.(misc/conf/zabbix server.conf)Start the zabbix serverwww.fromdual.com17

Install the Zabbix web interface Apache/PHP is required Copy PHP files to DocumentRoot/zabbix http://localhost/zabbix Change php.ini Default settings are byfar not enough!date.timezone Europe/Zurich Restart webserver Finish configuration Login with admin/zabbixwww.fromdual.com18

The Zabbix Web Interface It works!www.fromdual.com19

The Zabbix agent Either from compiling or use the providedbinary Create zabbix user Create configuration file for zabbix agent (misc/conf/zabbix agentd.conf)Start the zabbix agent Use zabbix agentd NOT zabbix agent Also install an agent on the server machine!www.fromdual.com20

The Zabbix Agent And now Hurray! Ups, it does NOT work : ( Configuration Hosts Status Click on “Not Moni tored” to enable.Happened sometimesto mePatience helps.www.fromdual.com21

We want Graphs!www.fromdual.com22


Latest datawww.fromdual.com24

The database Nice, but what about my Database?# /etc/zabbix/zabbix agentd.conf### Set of parameters for monitoring MySQL server (v3.23.42 and later)### Change u username and add p password if required#UserParameter,mysqladmin uroot ping grep alive wc l#UserParameter mysql.uptime,mysqladmin uroot status cut f2 d":" cut f1 d"T"#UserParameter mysql.threads,mysqladmin uroot status cut f3 d":" cut f1 d"Q"#UserParameter mysql.questions,mysqladmin uroot status cut f4 d":" cut f1 d"S"#UserParameter mysql.slowqueries,mysqladmin uroot status cut f5 d":" cut f1 d"O"#UserParameter mysql.qps,mysqladmin uroot status cut f9 d":"#UserParameter mysql.version,mysql V Enable those and restart the agent. Nothing will happen! We have to add a template to the server first Template App MySQLwww.fromdual.com25

MySQL Latest Datawww.fromdual.com26

MySQL Queries per secondwww.fromdual.com27

st1 impression Not really much data for MySQL : ( No efficient gathering method No nice graphs Is that all? Zabbix seems to be very flexible, so let'sdo it ourself!www.fromdual.com28

Requirements Fit into Zabbix architecture Must support several mysqld per server Should provide much more information Must support other Storage Engines as well MySQL Cluster, XtraDB, Aria, PBXT, Sphinx SE Simple Flexible Modular Work on Unixoides (and Windows?)www.fromdual.com29

FromDual Performance Monitorfor MySQL / Zabbix Architecturewww.fromdual.com30

Available modules [10] Aria (for Aria SE, ex. Maria SE (crash safe MyISAM)) InnoDB (commercial) Master (for Master – Slave replication) MyISAM (for MyISAM SE) MySQL (non SE related stuff for MariaDB and MySQL) NDB (commercial, for MySQL Cluster SE, pre 7.1 and 7.1) PBXT (for PBXT SE) Process (for UNIX processes) Slave (for Master – Slave replication) XtraDB (for XtraDB SE (derivat of InnoDB)) Sphinx (for the Sphinx SE plug in (in planning) Other modules on request.www.fromdual.com31

FromDual Monitor for Zabbix Install Zabbix as described before. Download FromDual Performance Monitor for MySQL [9] Read installation documentation [10] Load the FromDual templates Create a host group (optional) Create a host ( database!) Careful! Hostname MUST match the name in the FromDual agent configuration file!!! Link the chosen templates to the host Configure FromDual agent Hook FromDual agent into Zabbix agent:# /etc/zabbix/zabbix agentd.confUserParameter \/etc/zabbix/FromDualMySQLagent.conf Restart Zabbix agentwww.fromdual.com32


Host configurationwww.fromdual.com34

Overview / Dashboardwww.fromdual.com35



Latest datawww.fromdual.com38

Raw graph from latest datawww.fromdual.com39

Maps for scale out set upswww.fromdual.com40

Screen for MySQL Clusterwww.fromdual.com41

Graph (MySQL Network traffic)www.fromdual.com42

Graph (MySQL statements)www.fromdual.com43

Screen (InnoDB buffer pool)www.fromdual.com44

Graph (PBXT file read/write)www.fromdual.com45

Problems / Discussion One can do nearly everything (!) It is a lot of work until everything is in place. The whole set up is tricky and needs some time until understood. It is not so smooth like MEM. Installation is more labourish. There is no Query Analyser (QUAN) yet. Zabbix developer say it could bepossible with v2.0! Usability is not perfect yet (links are not ending up where I expect it). No binaries available for server, only commercial! Installation docu is not fool prove? Needs some experience to find problems but, its cool, give it a try if you have no solution in place yet!www.fromdual.com46

Literature [1] MySQL Monitoring solutions: monitoring solutions[2] Cluster Monitoring uster monitoring cmon.html[3] Configure MySQL Enterprise Monitor to monitor MySQL Cluster: cluster/configure mysql enterprise monitor to monitor mysql cluster/[4] The MySQL Enterprise Monitor: tml[5] Kontrollbase: / [6] Zabbix: [7] Zabbix features: [8] Zabbix documentation: [9] Download MySQL Performance Monitor: [10] Installation documentation: performance monitorwww.fromdual.com47

zabbix_ server zabbix_ server.conf zabbix_ server.log Server B Repository Database (MySQL, PostgreSQL, Oracle, SQLlite) zabbix_ proxy zabbix_ agentd zabbix_ proxy.conf zabbix_ proxy.log zabbix_ agentd.conf zabbix_ agentd.conf zabbix_ agentd zabbix_ agentd zabbix_ sender zabbix