Transcription
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 http://www.zabbix.com/download.php 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 http://www.howtoforge.com/zabbix 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
Screenswww.fromdual.com23
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 mysql.ping,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 t.pl \/etc/zabbix/FromDualMySQLagent.conf Restart Zabbix agentwww.fromdual.com32
Templateswww.fromdual.com33
Host configurationwww.fromdual.com34
Overview / Dashboardwww.fromdual.com35
Triggerswww.fromdual.com36
Eventswww.fromdual.com37
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: http://www.fromdual.com/mysql monitoring solutions[2] Cluster Monitoring uster monitoring cmon.html[3] Configure MySQL Enterprise Monitor to monitor MySQL Cluster:http://www.clusterdb.com/mysql cluster/configure mysql enterprise monitor to monitor mysql cluster/[4] The MySQL Enterprise Monitor: tml[5] Kontrollbase: http://kontrollsoft.com/software / [6] Zabbix: http://www.zabbix.org/ [7] Zabbix features: http://www.zabbix.org/features.php [8] Zabbix documentation: http://www.zabbix.com/documentation.php [9] Download MySQL Performance Monitor: http://www.fromdual.com/download [10] Installation documentation: http://www.fromdual.com/mysql 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
