MySQLPerformanceMonitoring withZabbix AnalternativetotheMySQLEnterprise Monitor?

byOliSennhauser
[email protected] http://www.fromdual.com
www.fromdual.com 1

Howmanyofyou...

monitortheirdatabaseservers? monitortheir(MySQL)databases? monitorperformancemetrics? alltheothers:Whynot?

www.fromdual.com

Linuxmonitoring

Question:WhatisyourfavouriteLinuxmonitoring application? 1187votes,April2010 Manipulated? Lit:LinuxJournal,[1]

www.fromdual.com 3

Whattomonitor?

Devices/Software

Server,Router,Switches,I/Osystemsetc. OperatingSystem,Networks,Applications,etc. DBdown,Replicationstopped,Servernotreachable,etc. Diskmorethann%fullorlessthanmGbytefree, Replicationmorethannsecondslagging,Datanodedown, 100%CPUutilization,etc.

www.fromdual.com 4

Incidents

CriticalEvents

Alert,immediateintervention,firefighting

Whattomonitor?

Trends(includestime!) Graphs Howlongdoesittakeuntil

mydiskisfull? myIndexMemoryisfilledup? Peak?Backup? Onceaday?AlwaysatSundaynight? I/Oproblemsduringourbackupwindow?

Whendoesithappen?

Howoftendoesithappen?Doesithappenperiodically?

Howdoesitcorrelatetootherinformations?

Readingthepatterns! thiscanhelpustofindtherootcauseofproblems
www.fromdual.com 5

Howtomonitor?

Basicsolutions:

top,vmstat,iostat,mytop,innotop,SHOW GLOBALSTATUS,SHOWINNODBSTATUS CLI!,nographs,nologterminformation,but goodforadhocanalysis! Nagios,Cactii,Zabbix, TypicallyNOTspecialisedinDBmonitoring...

www.fromdual.com 6

Graphicalsolutions

Focus

Database+Server Trends Nottheotherstuff... Problem:

Monitoringsolutionsaregeneric Adatabaseisaspecificapplication!
www.fromdual.com 7

MySQLmonitoring

Question:Whatmonitoringsoftwaredoyouuse? 907votes,January2005 PreMySQLEnterpriseMonitorera Lit:MySQL,[1]

www.fromdual.com 8

TheMySQLEnterpriseMonitor[4]

www.fromdual.com

TheMySQLEnterpriseMonitor

Pros

TailoredforMySQL Easyinstall HandyGUI QueryAnalyser(caution!) ReplicationMonitor Onlyforpayingcustomers Proprietarycode DoesNOTmonitorMySQLClusteryet[2],[3] Veryresourceintensive(JavaAppServer!) Future:IntegrationintoOracleEnterpriseManager??? MariaDB,Aria,XtraDB,PBXT,Drizzle,SphinxSE,?
www.fromdual.com 10

Cons

Kontrollbase[5]

www.fromdual.com

11

Kontrollbase

Pros

TailoredforMySQL Lightweightapplication(PHP) OpenSource(NewBSD) Easyinstall??? ReplicationMonitor Didnotworkforme(nondefaultinstallations:()! Answerfromdeveloper:itworksformeandmycustomers... Foundafewbugs. DoesNOTmonitorMySQLCluster? QueryAnalyser MariaDB,Aria,XtraDB,PBXT,Drizzle,SphinxSE,?
www.fromdual.com 12

Cons

Andnow?

MEMnogo Kontrollbasenoworknogo Selfwritten?notimenogo Nagios?complicated?Shudder...nogo Othersolutions? Afriendtoldmethathewillevaluate ZabbixformonitoringMySQL...

www.fromdual.com 13

Zabbix[6]
Itclaims:[7]

Commercialsupport

Annualsupportagreements Troublefreedeployment ProfessionalServices TechnicalAccountManager ZabbixTraining UpgradeServices

Allinonesolution

OpenSource Performancemonitoring Agentsforallplatforms AvailabilityandSLAreporting Collectionofanydata Greatgraphsandnetworkmaps

etc,etc...Wow!

Scalability

Upto100kmonitoreddevices Upto1Mofmetrics Thousandsofcheckspersecond Smalltolargedistributedsetups Easymaintenance

www.fromdual.com

14

Zabbixarchitecture

ZabbixisatypicalAgentServersetup:
zabbix_ agentd.conf zabbix_ proxy.conf zabbix_ proxy.log zabbix_ server.conf zabbix_ server.log

zabbix_ agentd.conf

Webbrowser ServerA zabbix_ server

zabbix_get

zabbix_ zabbix_ zabbix_ agentd agentd agentd ServerZ zabbix_ agentd

Device Device Device

zabbix_ proxy

ServerC Apache PHP

ServerB
zabbix_ sender

MySQL Database

Repository Database
(MySQL, PostgreSQL, Oracle, SQLlite)

Application Application Application

www.fromdual.com

15

InstallationofZabbix

Download

http://www.zabbix.com/download.php Server(Sourceonly,forLinuxandWindowsonly) Agent(Binariesfor:AIX,FreeBSD,HPUX,Linux,OpenBSD,Solaris, Windows) PackagesfromyourfavouriteLinuxDistro

http://www.zabbix.com/documentation/1.8/manual/installation http://www.howtoforge.com/zabbix_network_monitoring

Documentation

Recommendation:

Onethingaftertheother Serverfirst,Webinterfacenext,thenagents
www.fromdual.com 16

InstallZabbixserver

Createzabbixuser Untarsourcetarball Createzabbixdatabaseandpopulateit

AMySQL(PostgreSQL,)installationisneeded... Somepackagesmaybemissing... Doesnottaketoolong(<10min) (misc/conf/zabbix_server.conf)

./configure ; make ; make install

Createconfigurationfileforzabbixserver

Startthezabbixserver
www.fromdual.com 17

InstalltheZabbixwebinterface

Apache/PHPisrequired CopyPHPfilesto$DocumentRoot/zabbix http://localhost/zabbix Changephp.ini

Defaultsettingsareby farnotenough! date.timezone= Europe/Zurich

Restartwebserver Finishconfiguration Loginwithadmin/zabbix

www.fromdual.com 18

TheZabbixWebInterface

Itworks!

www.fromdual.com

19

TheZabbixagent

Eitherfromcompilingorusetheprovided binary Createzabbixuser Createconfigurationfileforzabbixagent

(misc/conf/zabbix_agentd.conf) Usezabbix_agentdNOTzabbix_agent Alsoinstallanagentontheservermachine!

www.fromdual.com 20

Startthezabbixagent

TheZabbixAgent

AndnowHurray! Ups,itdoesNOTwork:( Configuration HostsStatus ClickonNotMoni toredtoenable... Happenedsometimes tome Patiencehelps...

www.fromdual.com 21

WewantGraphs!

www.fromdual.com

22

Screens

www.fromdual.com

23

Latestdata

www.fromdual.com

24

Thedatabase

Nice,butwhataboutmyDatabase?
#/etc/zabbix/zabbix_agentd.conf ###SetofparametersformonitoringMySQLserver(v3.23.42andlater) ###Changeu<username>andaddp<password>ifrequired #UserParameter=mysql.ping,mysqladminurootping|grepalive|wcl #UserParameter=mysql.uptime,mysqladminurootstatus|cutf2d":"|cutf1d"T" #UserParameter=mysql.threads,mysqladminurootstatus|cutf3d":"|cutf1d"Q" #UserParameter=mysql.questions,mysqladminurootstatus|cutf4d":"|cutf1d"S" #UserParameter=mysql.slowqueries,mysqladminurootstatus|cutf5d":"|cutf1d"O" #UserParameter=mysql.qps,mysqladminurootstatus|cutf9d":" #UserParameter=mysql.version,mysqlV

Enablethoseandrestarttheagent... Nothingwillhappen! Wehavetoaddatemplatetotheserverfirst

Template_App_MySQL
www.fromdual.com 25

MySQLLatestData

www.fromdual.com

26

MySQLQueriespersecond

www.fromdual.com

27

1 impression

s t

NotreallymuchdataforMySQL:( Noefficientgatheringmethod Nonicegraphs Isthatall? Zabbixseemstobeveryflexible,solet's doitourself!

www.fromdual.com 28

Requirements

FitintoZabbixarchitecture Mustsupportseveralmysqldperserver Shouldprovidemuchmoreinformation MustsupportotherStorageEnginesaswell

MySQLCluster,XtraDB,Aria,PBXT,SphinxSE

Simple Flexible Modular WorkonUnixoides(andWindows?)

www.fromdual.com 29

FromDualPerformanceMonitor forMySQL/ZabbixArchitecture

www.fromdual.com

30

Availablemodules[10]

Aria(forAriaSE,ex.MariaSE(crashsafeMyISAM)) InnoDB(commercial) Master(forMasterSlavereplication) MyISAM(forMyISAMSE) MySQL(nonSErelatedstuffforMariaDBandMySQL) NDB(commercial,forMySQLClusterSE,pre7.1and7.1) PBXT(forPBXTSE) Process(forUNIXprocesses) Slave(forMasterSlavereplication) XtraDB(forXtraDBSE(derivatofInnoDB)) Sphinx(fortheSphinxSEplugin(inplanning) Othermodulesonrequest...
www.fromdual.com 31

FromDualMonitorforZabbix

InstallZabbixasdescribedbefore. DownloadFromDualPerformanceMonitorforMySQL[9] Readinstallationdocumentation[10] LoadtheFromDualtemplates Createahostgroup(optional) Createahost(=database!)

Careful!HostnameMUSTmatchthenameintheFromDualagentconfigurationfile!!!

Linkthechosentemplatestothehost ConfigureFromDualagent HookFromDualagentintoZabbixagent:

#/etc/zabbix/zabbix_agentd.conf UserParameter=FromDual.MySQL.check,/etc/zabbix/FromDualMySQLagent.pl\ /etc/zabbix/FromDualMySQLagent.conf

RestartZabbixagent
www.fromdual.com 32

Templates

www.fromdual.com

33

Hostconfiguration

www.fromdual.com

34

Overview/Dashboard

www.fromdual.com

35

Triggers

www.fromdual.com

36

Events

www.fromdual.com

37

Latestdata

www.fromdual.com

38

Rawgraphfromlatestdata

www.fromdual.com

39

Mapsforscaleoutsetups

www.fromdual.com

40

ScreenforMySQLCluster

www.fromdual.com

41

Graph(MySQLNetworktraffic)

www.fromdual.com

42

Graph(MySQLstatements)

www.fromdual.com

43

Screen(InnoDBbufferpool)

www.fromdual.com

44

Graph(PBXTfileread/write)

www.fromdual.com

45

Problems/Discussion

Onecandonearlyeverything(!) Itisalotofworkuntileverythingisinplace. Thewholesetupistrickyandneedssometimeuntilunderstood. ItisnotsosmoothlikeMEM.Installationismorelabourish. ThereisnoQueryAnalyser(QUAN)yet.Zabbixdevelopersayitcouldbe possiblewithv2.0! Usabilityisnotperfectyet(linksarenotendingupwhereIexpectit). Nobinariesavailableforserver,onlycommercial! Installationdocuisnotfoolprove? Needssomeexperiencetofindproblems but,itscool,giveitatryifyouhavenosolutioninplaceyet!
www.fromdual.com 46

Literature

[1]MySQLMonitoringsolutions:http://www.fromdual.com/mysqlmonitoringsolutions [2]ClusterMonitoringCMON: http://johanandersson.blogspot.com/2008/08/clustermonitoringcmon.html [3]ConfigureMySQLEnterpriseMonitortomonitorMySQLCluster: http://www.clusterdb.com/mysqlcluster/configuremysqlenterprisemonitortomonitormysqlcluster/ [4]TheMySQLEnterpriseMonitor:http://www.mysql.com/products/enterprise/monitor.html [5]Kontrollbase:http://kontrollsoft.com/softwarekontrollbase, http://code.google.com/p/kontrollbase/ [6]Zabbix:http://www.zabbix.org/ [7]Zabbixfeatures:http://www.zabbix.org/features.php [8]Zabbixdocumentation:http://www.zabbix.com/documentation.php [9]DownloadMySQLPerformanceMonitor:http://www.fromdual.com/download [10]Installationdocumentation:http://www.fromdual.com/mysqlperformancemonitor

www.fromdual.com

47