About MySQL Monitoring for Linux
MySQL monitoring allows you to test the availability of MySQL Server at specified domain/IP and designated port on your Linux machine, as well as to monitor health status of your MySQL server. You can also set metrics thresholds, so that you get alerted if any of the MySQL monitor metrics (see Viewing MySQL Monitor Data further in this document) reaches some critical level preset by you.
Once you add a MySQL monitor, Monitis will start logging in to your server at regular intervals of time to check if your MySQL server is accessible and fetching monitoring data onto MySQL monitor screen in your Monitis dashboard.
Adding MySQL Monitor for Linux
Go to the Monitors menu and select Application Monitors->MySQL.
The Add Application Monitor – Step 1 window will open.
If you have already downloaded and installed Smart Agent for Linux, select it from the Agent list.
Make sure that your Smart Agent for Linux is running, as shown under Agent Status. If the Agent is stopped, run it on your Linux machine (see Downloading, Installing and Running Smart Agent for Linux).
If you haven’t downloaded Smart Agent for Linux yet, click on the Download Agent button to download it. Follow the instructions under Downloading, Installing and Running Smart Agent for Linux.
Note that it may take up to 5 min for newly installed Agent to appear in the list (you can click Refresh to refresh the agent list).
Select the Linux Agent from the list, and click Next.
The Add Application Monitor – Step 2 window will open.
Select from the Monitor Group combo box the monitor group for this monitor to be assigned to.
You can also create a new monitor group by clicking on the “+” icon next to Add a new Group.
Under Server Host and Port enter the IP and port number for your MySQL server. These fields are mandatory to enable Monitis access to your MySQL server.
You can authenticate to MySQL server using one of two methods.
1. Standard authentication
Provide your login credentials under User and Password if required to access your MySQL server.
Under Database provide the name of the database if your provided login credentials are valid for a specific database only.
2. Encrypted login path file (.mylogin.cnf)
The mysql_config_editor utility (available as of MySQL 5.6.6) enables you to store authentication credentials in an encrypted login path file named .mylogin.cnf. The file can be read later by MySQL client programs to obtain authentication credentials for connecting to MySQL Server.
See http://dev.mysql.com/doc/refman/5.6/en/mysql-config-editor.html for detailed instructions.
Click Test to test establishing connection to your MySQL server. Wait a little, as the test takes about 1 min to complete.
If successfully connected, you will see a success message on the screen.
If there was a failure establishing connection, you will see an error message on the screen like the one below:
To add more Application monitors of different types to the same Smart Agent, click the green “+” button under Add More Monitors (see Adding Multiple Server-Device and Application Monitors for the Same Agent).
Click Add when finished.
The Alert Configuration window will appear.
- Click Send to All Contacts if you want to send alerts for this monitor to all contacts.
- Click Custom Configuration (Advanced) to configure sending alerts for this monitor only to selected contacts.
The Alert Rules window will open. Click on the Add Alert Rule button to configure alert rule(s) for your monitor.
Set up the alert rule and click Add Rule. See Alert Rules in Alerts 2.0 for more info about alert rules.
Note: To be able to add alert rules, you need to set up thresholds (Critical or Warning) in your monitor first.
A threshold includes two conditions connected by a logical OR. If any of the conditions matches the monitor will enter your specified state.
- Check fails (response timed out, connection failure, etc)
- Any or all of your selected metrics meet the failure conditions you specified for them
See Thresholds for more info.
- Click Don’t Send Alerts if you don’t want any alerts for this monitor to be sent.
Your newly created application monitor will appear in your Monitis dashboard. Wait until it loads the first results.
Viewing MySQL Monitor Data
By default, there are four MySQL metrics preselected in the chart view: Connections Usage, Threads Connected, Slow Queries and Queries/sec. You can toggle off or on any of these and other shown metrics.
If you switch to the table view, you can see a number of metrics, including the state of your MySQL application.
When you switch to the table view for the first time, you will see a pop-up window, where you can select the metrics to display.
Select the metrics and click OK. The table view will open, showing your selected metrics.
The following metrics are available in MySQL monitor.
Note that all the metrics are calculated for MySQL server, not for a specific DB running on MySQL server even if you have configured MySQL monitor using DB credentials.
- Connections Usage (%) – The percentage of used connections with respect to the maximum allowed connections count
- Threads Connected – The number of threads that are currently opened for connections which represent the number of connection attempts to the MySQL server
- Slow_queries – The number of queries that have taken more than long_query_time seconds in the monitoring period
- Queries/sec – The number of statements executed by the server per second
- Received (Kbps) – The average number of Kbytes received from all clients per second
- Sent (Kbps) – The average number of Kbytes sent to all clients per second
- Inserts/sec – Average number of executed INSERT commands per second
- Selects/sec – Average number of executed SELECT statements per second
- Updates/sec – Average number of executed UPDATE commands per second
- Deletes/sec – Average number of executed DELETE commands per second
- Threads Running – The number of threads that are not sleeping – just represents the amount of queries which are being currently in processing
- Uptime – The amount of time MySQL server has been up and running from the last server restart
- State – The evaluation of MySQL server health state at monitoring check time
Depending on the results of the last monitoring check, you will see one of the below described statuses under State in the table view:
This status means that there are queries running on your MySQL Server as per the last monitoring check. If the number shown under Slow Queries column in the table view is greater than 0, double-clicking the row or, alternatively, clicking on a value under Slow Queries will get one of the below four results:
1. If Slow Query logging (see below Enabling Slow Query Logging) is enabled in your MySQL server, you will see the list of top 10 slow queries, sorted in descending order by the number of times the query was run from the time it has been first logged into the Slow Query log file.
The following metrics are available:
- Execution Time (sec) – how long it took to execute the query
- Lock Time (sec) – how long was a lock required
- Rows – how many rows were investigated by the query
- Count – how many times was the query executed since it has been first logged in the log file
2. If Slow Query is not enabled in your MySQL, you will see the following error message:
To enable Slow Query logging in your MySQL server, please refer to Enabling Slow Query Logging below.
3. In order for the Slow Query log file to be accessible to Monitis to read data from, Smart Agent and MySQl server must be running on the same machine. If you have Slow Query logging enabled on your machine but Smart Agent is running on different machine, you will get the following error message.
4.If SlowQuery log file size is too big to be processed by MySQL monitor, the check will time out and MySQL monitor will return the following error message.
Delete your MySQL Slow Query log file, and set the path to the slow query log, e.g. set global slow_query_log_file =’/var/log/mysql/slow-query.log’, and new Slow Query log file will be generated.
- Unable to connect:
If Monitis monitoring check was unable to connect to your MySQL server, you will see the Unable to Connect status under State in the table view. Double-clicking the row will show the error returned by MySQL server.
Enabling Slow Query Logging
To see Slow Query data in your MySQL monitor, you need to enable slow query logging on your MySQL server.
For MySQL version 5.1.6 and above you can enable the slow query logging in MySQL by entering the following commands:
- Switch on the MySQL slow queries tracker: set global slow_query_log = ‘ON’
- Set the path to the slow query log (optional), e.g. set global slow_query_log_file =’/var/log/mysql/slow-query.log’
- Set the amount of time a query needs to run before it is considered slow (optional, default is set to 10 seconds): e.g. set global long_query_time = 20
For MySQL versions below 5.1.6:
- Open the /etc/my.cnf file with text editor
- Add the following line under the “[mysqld]” section:
- The path to the log file: can be whatever you want, e.g. log-slow-queries=/var/log/mysql/slow-query.log
- Set the amount of time a query needs to run before it is considered slow (optional, default is set to 10 seconds): e.g. long_query_time=20
- Restart the MySQL server, e.g. using the service mysqld restart command
In addition, please take into account that MySQL monitor should be run under a user that has credentials which allow (read/write) access to MySQL slow query log file. Alternatively, you can change the MySQL slow query log file permissions so that they allow the user running the MySQL monitor to have a (read/write) access to MySQL slow query log file.
For additional information, you can refer to MySQL documentation under: