Download as ODP, PPTX
Uploaded byMark Leith
Getting to Know MySQL Enterprise Monitor
The document outlines the features and architecture of the MySQL Enterprise Monitor, focusing on its ability to monitor and streamline database management at scale. It discusses the installation process, key components like the mem server and agent, and the user interface's monitoring capabilities. Additionally, various metrics, configuration settings, and tools for troubleshooting and performance analysis are covered.
Getting to Know MySQL Enterprise Monitor
- 1.
- 2. Getting To KnowMySQL Enterprise Monitor Mark Leith (@MarkLeith)
- 3. Agenda The MySQLEnterprise Monitor Vision
- 4. Architecture A TourAround The UI Key Configuration
- 5.
- 6. The MySQL EnterpriseMonitor Team Vision Continually monitor all aspects of MySQL environments Analyze for problems, alerting when necessary Streamline common “database problem” resolution Simplify database management at scale
- 7. The MySQL EnterpriseMonitor Vision “ MySQL is simple to use ” ... but … there are many complex interactions happening within the database Much of what is happening within MySQL has historically been termed as “ a black-box ” … but … that is changing rapidly No one person can continually monitor what a single database is doing, let alone 10's, or 100's, of instances
- 8. The Obligatory BlackBoxes
- 9. The MySQL EnterpriseMonitor Way
- 10. Agenda Architecture ATour Around The UI Key Configuration
- 11.
- 12. MEM Installation Aminimal installation consists of: MEM Server (Tomcat app server, MySQL DB) Ideally on dedicated resources
- 13. App server andDB can have separate hardware MEM Agent (C based, multiple plugins) Ideally installed on each database server
- 14. Can also monitorremotely (less OS stats)
- 15. MEM Architecture TomcatApp Server MySQL Repository Admins MEM Server MEM Agent Monitored DB Instance
- 16. MEM Installation –Statement Collection MySQL Proxy integrated in to the Agent process Funnel “legacy” app traffic for analysis
- 17. Data sent viathe agent plugin Connector Plugins for app side statement monitoring Allows client side monitoring of statement latency
- 18. Allow tracing statementorigination
- 19. Also spreads theload of statement analysis
- 20. Data sent directlyto the MEM server via REST
- 21. MEM Architecture JavaWeb App MySQL Repository Admins Java App Server (or .Net) MEM Traffic App Traffic “ Legacy” App Users MEM Server Connector Plugins MEM Agent/Proxy
- 22. NEW – PHPand the Aggregator Admins MEM Server MEM Agent Monitored Instance PHP App MEM Traffic App Traffic MEM Aggregator
- 23. Agenda A TourAround The UI Key Configuration
- 24.
- 25. The Monitor Tab– Server Overview Key Graphs Current Critical Events Selected Server Overview Key Indicators
- 26. The Monitor Tab– Support Issues / Heat Chart Waiting support issues MOS account in settings Status of agent and MySQL Open event counts & drill down Visual status of key statistics See Heatchart Advisor to tune
- 27. MEM Advisors Acollection (>160) of simple expression based “ Rules ” 100-((%Threads_created% / %Connections%)*100) < THRESHOLD All check standard best practices for MySQL
- 28. Scheduled to runon a set frequency basis
- 29. “ Counters” evaluatedeltas, otherwise, evaluate totals
- 30. Rules trigger “Events”on three levels of “ Threshold ” Info -> Warning -> Critical
- 31. The Advisors Tab– Current Schedule Edit current settings
- 32. Per Group /Server
- 33.
- 34. Advisors Tab -Manage Rules Overview >1000 MySQL metrics SHOW STATUS
- 35.
- 36.
- 37. SHOW INNODB STATUS
- 38.
- 39. PERFORMANCE_SCHEMA ~100 OSmetrics
- 40. Custom SQL possibleCreate Custom Edit defaults Even app specific
- 41. Advisors Tab -Edit Rule The Rule is the Expression
- 42. Certified rules allow threshold and frequency changes
- 43. Default values aregenerally conservative
- 44. Gather some graphdata before refining
- 45. Events Tab Current/ Worst Event states Where What When Drill In
- 46. Event Results Adviceon the problem, and how to go about drilling in to it Recommended action to resolve the problem after investigation
- 47. Event Results Theactual evaluation Advisor Config
- 48. Closing Events Whetherto Continue Monitoring this rule Track a history of changes made for events Choose Auto-Close wisely: Flapping events notify, a lot
- 49. The Graphs TabStacked for easy correlation
- 50.
- 51. InnoDB, Cluster, SQLlayer activity, Query Cache, Thread Pool, Connection usage, Statement, Transaction and Row throughput, OS stats etc. ~75 Graphs ~182 series ~220 data points
- 52. Graphs and FilteringWhen groups are selected graphs summarize (avg/min/max)
- 53. Drill in toservers to see raw stats
- 54. Filter by namewith “Contains” matching or regex – i.e “^row”
- 55. ..or just selectspecific graphs
- 56. Time range filteringfor history Keep defaults minimal
- 57. Data points everyminute
- 58. Graphs – QueryCorrelation Select any portion of any graph to take you to a filtered Query Analyzer view for that time period
- 59. The Query AnalyzerTab Correlated Graph Advanced Filtering Normalized Statements (drill down) Key Aggregated Statistics Table Scan
- 60. Statement Normalization Normalizationdoes the following: Strip all literals, replacing each with “?”
- 61. Strip pure comments,leading/trailing spaces
- 62.
- 63. Roll multi-row INSERTup to “(?,?) /*, … */”
- 64. Roll IN() liststo “IN (? /*, … */)”
- 65. Query Analysis –Example Queries Non-normalized Example statement Who it was executed by Where in the application it originated from (with connector plugins) When, and how long it took
- 66. Query Analysis –Explain / History How the query executed over time (rows, bytes, time, execs) Execution plan if captured
- 67.
- 68. “ Your replicationpage shows our replication topology incorrectly, that server isn't a slave any more... ” Actually... Your topology probably is configured like that : CHANGE MASTER TO MASTER _HOST = '' RESET SLAVE ALL (now in 5.5.16)
- 69. The Replication SlaveDetails
- 70. Agenda Key ConfigurationQuestions
- 71. Key Configuration Ofcourse, the first thing to get right is Advisors.. Let the monitor run for a while with no rules enabled
- 72. Be selective onwhich rules you enable
- 73. When enabling rules,use graph info for thresholds
- 74. Set notifications wisely(do you need an email alert at 5 am that a user exists without a password?)
- 75. General Settings Notificationmechanisms Data retention MOS credentials
- 76. Notification Groups ShortMessages MEM Server Events Custom Event subjects
- 77. Query Analysis ConfigurationCollect Normalized Data Also get non-normalized example Threshold for how Long a statement Should run, before Generating an EXPLAIN
- 78. Key Configuration Files$mem_server_dir/apache-tomcat/bin/setenv.sh Configure Java options (heap space) $mem_server_dir/mysql/my.cnf Database options – For larger installs, tune InnoDB $mem_agent_dir/share/items/items-mysql-monitor.xml Tweak SQL based data collection items
- 79. Tweaking / AddingSQL Collections <class> <namespace>mysql</namespace> <classname>foo_bar</classname> <query><![CDATA[SELECT foo, bar, FROM … ]]></query> <attributes> <attribute name="foo" counter="false" type="STRING" /> <attribute name="bar" counter="true" type="INTEGER" /> </attributes> </class>
- 80. Some resources.. Download- https://edelivery.oracle.com/ http://dev.mysql.com/doc/mysql-monitor/2.3/en/index.html http://www.slideshare.net/Leithal/extending-mysql-enterprise-monitor?type=powerpoint
- 81.
- 82.