In this lab, you will install MySQL Enterprise Monitor on MySQLnode2 (192.168.56.42) and learn how to monitor MySQL instances
- Install MySQL server, MySQL Router, MySQL Shell
cd /opt/download/lab/01-install
cd 01-mysql-server
sudo ./05-installTar.sh
cd ../02-router
sudo ./installTar.sh
cd ../03-shell
sudo ./installTar.sh
- Install MySQL Enterprise Monitor
cd /opt/download/mysql/mem8
sudo chmod +x ./*.bin
./mysqlmonitor-8.0.18-1217-linux-x86_64-installer.bin
Just hit "Enter"-key for all the question prompts except the password
password: mysql
-
Once installed, you can use your local browser to navigate to https://192.168.56.42:18443
-
Enter the following in the user fileds: Create user with 'manager' role
Username: admin
Password: admin
Create user with 'agent' role
Username: agent
Password: agent
- Install MySQL Enterprise Monitor Agent
cd /opt/download/mysql/mem8/
sudo chmod +x *.bin
./mysqlmonitoragent-8.0.18-linux-x86-64bit-installer-bin
Enter the following:
IP Address for the Monitor: 192.168.56.42
Port: <Enter>
Agent User: <Enter>
Password: agent
- Start the agent
/home/mysql/mysql/enterprise/agent/bin/agent.sh &
Or
/home/mysql/mysql/enterprise/agent/etc/init.d/mysql-monitor-agent start
There is a lot of information in MEM, it can be overwhelming if you don't know what you are looking for or looking at. Let's expore what is available in MEM and how you can use MEM to help you understand MySQL better
The main dashboard displays the following information
- A list of Timeseries graphs
- MySQL instances monitored, problematic instances, hosts, and database activities
- Critical events
You can check the Events tab for critical events for immediate remedy or actions
You will add new instances to MEM dashboard by specifying the hosts/ports of the MySQL instances to be added to MEM dashboard
Once the instance is added to MEM, you can then navigate the various configuration tabs to inspect the current configuration details of the monitored MySQL instance
You can find lots of useful metrics by selecting the various metrics on the left panel of the MEM dashboard
- Timeseries Graph There are a list of timeseries graph to let you peek into the health of MySQL instances you are monitoring:
- Connections
- Connections - Aborted
- Connections - Cache
- Connections - Maximum
- Database Activitiy
- Database Queries
- Agent operations on MySQL Instance
- Binary Log Cache Efficiency
- Binary Log Space Usage
- InnoDB Data Fille I/O - Bytes
- InnoDB Temporary File I/O - Bytes
- InnoDB Redo Log File I/O - Bytes
- InnoDB Temporary File I/O - Latency
- Relay Log File I/O - Bytes
- Binary Log File I/O - Latency
- Binary Log File I/O - Bytes
- InnoDB Data Fille I/O - Latency
- Relay Log File I/O - Latency
- InnoDB Relay Log File I/O - Latency
- Database Transactions
- Group Replication - Conflicts Detected
- Group Replication - Transactions In Queue
- Group Replication - Transactions Checked
- Group Replication - Transactions Rows Validating
- Hit Ratios
- InnoDB Adaptive Hash Index Memory
- InnoDB Adaptive Hash Index Searches
- InnoDB Buffer Pool
- InnoDB Buffer Pool Page Activity
- InnoDB Buffer Pool Pending Operations
- InnoDB Change Buffer Activity
- InnoDB Change Buffer Usage
- InnoDB Compression Time
- InnoDB Data Dictionary Memory
- InnoDB Doublewrite Buffer Activity
- InnoDB OS File Access
- InnoDB Open Files
- InnoDB Redo Log Checkpoint Lag
- InnoDB Row Details
- InnoDB Row Lock Time
- InnoDB Row Lock Time Statistics
- InnoDB Row Lock Waits
- InnoDB Row Lock Current
- InnoDB Transaction History
- InnoDB Transaction Lock Memory
- InnoDB Transactions
- MyISAM Key Buffer Activity
- MyISAM Key Buffer Usage
- Network - Database Throughput
- Opened Tables
- Prepared Statement API
- Prepared Statement API Invalidations
- Prepared Statement SQL
- Prepared Statement Utilization
- Query Response Time Index
- Replication Delay - Per Channel
- Row Access Statistics
- Row Accesses
- Row Accesses Index Usage Ratio
- Row Writes
- Row Returned
- Statement Statistics
- Slow Queries
- Sort Acitivity
- Statement Errors and Warnings
- variablelsReport
- Table Lock Wait Ratio
- Table Locks
- Temporary Tables
- Table Statistics You can find out which are the HOT tables accessed by users/applications
- User Staticstics You can find out avarage memory consumptions by users (useful to estimate average memory requirments for applications)
- Memory Usage
- Database File I/O
** By File Type
** By Wait Type
** By Thread Type
- InnoDB Buffer Pool
You need to click on the Generate Report to see the InnoDB Buffer utilization
- Processes
- Lock Waits
When users complain about not able to access data, it is likely that some tables/rows are locked
Row Lock
Table Lock
It is very common that users encountered slow response time when executing queries, you can turn to MEM to monitor slow running queries from time to time to fine tune the database to improve these slow running queries. MEM provides Query Analyzer to categorize slow running queries by color using Query Response Time Index (QRTi) in the following categories:
- Less than 100ms (Green)
- Between 100ms and 400ms (Yellow)
- More than 400ms (Red)
You can drill into one of these queries for more details
Besides standalone MySQL instances, you can also monitor MySQL replica and InnoDB Cluster
You can also monitor the status of database backup
MEM comes bundled with more than 200 Advisors to monitor critical database events using a set of threadholds, you can create event handlers to define actions whenever any of these threadhold is breached, for example, trigger an email, SMTP traps to alert the DBA
