MySQL is one of the most popular open-source relational database management systems (RDBMS). Configure this plugin and ensure an efficient database monitoring experience through vital actionable information.
This document details the following:
Receive failover alerts each time there is a failover between master and slave. This will help you ensure that your MySQL environment is always monitored and you receive timely alerts.
You can monitor various metrics to stay on top of performance, including those related to MySQL connections, queries, aborted clients and connections, query cache items, handler, read-writes, MyISAM key cache, sort, data transferred, tables, replication, and InnoDB.
Connections usage metrics
Max connectionsshows the maximum number of connection attempts to the MySQL server.
Maximum used connections
Max Used Connectionsdisplays the maximum number of connections that have been in use simultaneously, since the server started.
Connection Usagedenotes the total number of connections with respect to the percentage of maximum connections in the database. This information can be used to tune the database connections for better performance.
Queries and questions metrics
Application Queriesprovides the number of statements executed by the server. This variable includes the statements executed within stored programs.
Client Queriesdisplays the number of statements executed by the server. This includes only the statements sent to the server by clients and not statements executed within stored programs.
Slow Queriesprovides the number of queries that have taken more time in seconds than the
Aborted clients and connections metrics
Aborted Clientsfetches the number of connections that were aborted because the client died without closing the connection properly.
Aborted Connectsdenotes the number of failed attempts to connect to the MySQL server.
Table locks waited metrics
Table locks waited
Table Locks Waitedis the number of times the requests for table locks had to wait.
Query cache items metrics
Hitsdenotes the number of query cache hits.
Free Memoryfetches the amount of free memory in bytes for the query cache.
Not Cacheddisplays the number of non-cached queries.
In Cachedenotes the number of queries registered in the query cache.
Free Blocksdisplays the number of free memory blocks in the query cache.
Insertsfetches the number of queries added to the query cache.
Low memory prunes
Low MemoryPrunes denotes the number of queries that were deleted from the query cache because of low memory.
Total Blocksis the total number of blocks in the query cache.
Handler Rollbackdenotes the rate of requests to perform an internal rollback operation.
Handler Deletefetches the number of times the rows in a table have been deleted.
Handler read first
Handler Read Firstdisplays the number of times the first entry in an index was read.
Handler read key
Handler Read Keyprovides the number of requests to read a row based on a key.
Handler random next
Handler Random Nextfetches the number of requests to read the next row in the data file.
Handler read random
Handler Read Randomdenotes the number of requests to read a row based on a fixed position.
Handler Updatefetches the number of requests to update a row in a table.
Handler Writedisplays the number of requests to insert a row in a table.
Read write metrics
Writesprovides the total number of writes done in a MySQL server. It is the sum of inserted queries, replaced queries, updated queries, and deleted queries.
Readsfetches the total number of reads done in a MySQL server. Technically, it is the number of selected queries and number of query cache hits.
Transactionsdenotes the number of transactions.
Read queries metrics
Full Joindenotes the number of joins that perform table scans because they do not use indexes.
Full range join
Full Range Joindisplays the number of joins that used a range search on a reference table.
Select Rangeshows the number of joins that used ranges on the first table.
Range Checkfetches the number of joins without keys that check for key usage after each row.
Select Scanprovides the number of joins that did a full scan of the first table.
Maximum execution time exceeded
Maximum Execution Time Exceededdenotes the number of select statements for which the execution timeout exceeded.
Write queries metrics
Commitprovides the number of commit statements executed.
Commit Selectfetches the number of select statements executed.
Commit Deletedisplays the number of delete statements executed.
Commit delete multi
Commit Delete Multidenotes the number of delete statements that use the multiple-table syntax.
Commit Insertfetches the number of insert statements executed.
Commit insert select
Commit Insert Selectdisplays the number of insert select statements executed.
Commit replace select
Commit Replace Selectdenotes the number of replace select statements executed.
Commit Rollbackprovides the number of rollback statements executed.
Commit Updateprovides the number of update statements executed.
Commit update multi
Commit Update Multishows the number of update statements that use the multiple-table syntax.
MyISAM key cache metrics
Blocks not flushed
Blocks Not Flushedshows the number of key blocks in the MyISAM key cache that have changed but have not yet been flushed to disk.
Read Requestsdenotes the number of requests to read a key block from the MyISAM key cache.
Key Readsdisplays the number of physical reads of a key block from the disk into the MyISAM key cache.
Write Requestsfetches the number of requests to write a key block to the MyISAM key cache.
Key Writesshows the number of physical writes of a key block from the MyISAM key cache to disk.
Merge Passesdenote the number of merge passes that the sort algorithm has had to execute.
Rangedisplays the number of sorts that were done using ranges.
Rowsfetch the number of sorted rows.
Scandisplays the number of sorts that were done by scanning the table.
Connectedshows the number of currently open connections.
Runningdisplays the number of threads that are not sleeping.
Cachedfetches the number of threads in the thread cache.
Createddenotes the number of threads created to handle connections.
Bytes received and sent metrics
Receivedprovides the number of bytes received from all clients.
Sentdisplays the number of bytes sent to all clients.
Table cache metrics
Open cache hits
Open Cache Hitsdisplays the number of hits for open tables cache lookups.
Open cache misses
Open Cache Missesfetches the number of misses for open tables cache lookups.
Open cache overflows
Open Cache overflowsprovides the number of overflows for the open tables cache..
Created temporary tables (TAB2) metrics
Temporary Tablesdisplays the number of internal temporary tables created by the server while executing statements.
Disk Tablesdenotes the number of internal on-disk temporary tables created by the server while executing statements.
Temporary Filesshows the number of internal temporary tables created by the server while executing statements.
Buffer pool pages data
Buffer Pool Pages Datadisplays the number of pages in the InnoDB buffer pool containing data.
Buffer pool pages dirty
Buffer Pool Pages Dirtydenotes the current number of dirty pages in the InnoDB buffer pool.
Buffer pool pages free
Buffer Pool Pages Freeprovides the number of free pages in the InnoDB buffer pool.
Buffer pool pages total
Buffer Pool Pages Totalfetches the total number of pages in the InnoDB buffer pool.
Buffer pool wait free
Buffer Pool Wait Freeshows the number of times a read or write to InnoDB had to wait as clean pages were not available in the buffer pool.
Log Waitsdisplays the number of times the log buffer was too small and a wait was required for it to be flushed before continuing.
Row lock time average
Row Lock Time Avgdenotes time to acquire a row lock for InnoDB tables, in milliseconds.
Row lock waits
Row Lock Waitsshows the number of times operations on InnoDB tables had to wait for a row lock.
Buffer pool pages flushed
Buffer Pool Pages Flushedfetches the number of requests to flush pages from the InnoDB buffer pool.
Buffer pool read ahead evicted
Buffer Pool Read Ahead Evicteddenotes the number of pages that read into the InnoDB buffer pool by the read-ahead background thread that were subsequently evicted without having been accessed by queries.
Buffer pool wait free
Buffer Pool Wait Freeshows the number of times a read or write to InnoDB had to wait as clean pages were not available in the buffer pool.
Buffer pool read ahead
Buffer Pool Read Aheaddisplays the number of pages that read into the InnoDB buffer pool by the read-ahead background thread.
Buffer pool read ahead random
Buffer Pool Read Ahead Randomdenotes the number of random read-aheads that were initiated by InnoDB.
Buffer pool read requests
Buffer Pool Read Requestsfetches the number of logical read requests.
Buffer pool reads
Buffer Pool Readsshows the number of logical reads that InnoDB could not satisfy from the buffer pool, and had to read directly from disk.
Buffer pool write requests
Buffer Pool Write Requestsfetches the number of writes in the InnoDB buffer pool.
Data Fsyncdisplays the number of fsync() operations per second.
Data pending fsync
Data Pending Fsyncshows the current number of pending fsync() operations.
Data pending reads
Data Pending Readsprovides the current number of pending reads.
Data pending writes
Data Pending Writesdenotes the current number of pending writes.
Data Readsshows the number of data reads.
Data Writesdisplays the number of data writes.
Data write requests
Data Write Requestssdenotes the number of write requests for the InnoDB redo log file.
Log Writesdepicts the number of physical writes to the InnoDB redo log file.
OS log fsyncs
OS Log Fsyncsprovides the number of fsync() writes done to the InnoDB redo log files.
OS log pending fsyncs
OS Log Pending Fsyncsfetches the number of pending fsync() operations for the InnoDB redo log files.
OS log pending writes
Os Log Pending Writesshows the number of pending writes to the InnoDB redo log files.
OS log written
Os Log Writtendisplays the number of bytes written to the InnoDB redo log files.
Pages Createddenotes the number of pages created by operations on InnoDB tables.
Pages Readdepicts the number pages read from the InnoDB buffer pool by operations on InnoDB tables.
Pages Writtenfetches the number of pages written by operations on InnoDB tables.
Rows Deletedprovides the number of rows deleted from InnoDB tables.
Rows Insertedshows the number of rows inserted into InnoDB tables.
Rows Readdenotes the number of rows read from InnoDB tables.
Rows Updateddepicts the number of rows updated in InnoDB tables.
Slave IO state
Slave IO Stateshows the state of what a thread is doing, such as trying to connect to the source, waiting for events from the source, reconnecting to the source, and so on.
Slave IO running
Slave IO Runningdisplays if the I/O thread has started and connected successfully to the source.
Slave SQL running
Slave Sql Runningshows if the SQL thread has started..
Slave Runningdenotes if a slave is running or not.
Connect Retryprovides the time between connect retires, in seconds.
Last IO error number
Last IO Errnothe error number of the most recent error that caused the I/O thread to stop.
Last SQL error number
Last Sql Errnoprovides the error number of the most recent error that caused the SQL thread to stop.
Master Hostis the source host that the replica is connected to.
Master retry count
Master Retry Countprovides the number of times the replica can attempt to reconnect to the source in the event of a lost connection.
Master server ID
Master Server IDis the server ID value from the source.
Master Useris the user name of the account used to connect to the source.
Relay log space
Relay Log Spaceis the total combined size of all existing relay log files in bytes.
Seconds behind master
Seconds Behind Masteris the difference in seconds between the slave’s clock time and the timestamp of the query, when it was recorded in the master’s binary log.
Skip Counterdenotes the number of events from the source that a replica server should skip.
- Our Linux/Windows server monitoring agent should be installed in the network or on the specific host where the MySQL instance is running.
- While adding a plugin, the plugin name and its folder name should be identical.
- The MySQL plugin will automatically verify, download, and install the 'pymysql' module required for monitoring your MySQL servers. This can be viewed in the mysql_monitoring.py file. In case the 'pymysql' module is not installed, follow the instructions given below to manually install it.
- Execute the following command in your server to install pymysql
pip install pymysql
Use "pip" to install pymysql module
Note: pip is a package management system that is used to install and manage software packages written in Python.
For CentOS, Fedora, RHEL:
yum install python-develyum install python-pip (or)easy_install pip
For Debian, Ubuntu:
apt-get -y install python-pip
- Open cmd as administrator.
- Go to the Python path.
cd [python path]
- Execute the following command:
python -m pip install PyMySQL
If pymysql is already installed in your server, follow the steps given in this article to install the MySQL plugin in Windows servers.
Roles and Permissions
SHOW GLOBAL STATUS, and
SHOW VARIABLES are the queries used in the MySQL plugin.
To create a MySQL user:
CREATE USER username@hostname IDENTIFIED BY 'password';
Select on queries permission is required to execute the queries mentioned above.
GRANT SELECT ON mysql.* TO username@hostname IDENTIFIED BY password;
CREATE USER site24x7@localhost IDENTIFIED BY 'site24x7';GRANT SELECT ON mysql.* TO site24x7@localhost IDENTIFIED BY 'site24x7';FLUSH PRIVILEGES;
- Download and install the latest version of the Site24x7 Linux agent in the server where you plan to run the plugin. If it is installed successfully, you will see a Linux server monitor in the Site24x7 Control Panel. This confirms that the agent is able to communicate with our data center.
Download the mysql_monitoring.py and mysql_monitoring.cfg file from our GitHub repository.
wget https://raw.githubusercontent.com/site24x7/plugins/master/mysql_monitoring/mysql_monitoring.pywget https://raw.githubusercontent.com/site24x7/plugins/master/mysql_monitoring/mysql_monitoring.cfg
Add the configuration in "mysql_monitoring.cfg" as below
[MySQL]host ="hostname"port="port"username="username"password ="password"logs_enabled ="logenabled"log_type_name ="logtypename"log_file_path ="logfilepath"
- Create a folder with the name 'mysql_monitoring', under the Site24x7 Linux agent plugin directory '/opt/site24x7/monagent/plugins/' and place the 'mysql_monitoring.py' and 'mysql_monitoring.cfg' under '/opt/site24x7/monagent/plugins/mysql_monitoring/'
- Download and install the latest version of the Site24x7 Windows agent in the network where you plan to run the plugin. If it is installed successfully, you will see a Windows server monitor in the Site24x7 Control Panel. This confirms that the agent is able to communicate with our data center.
- Download the mysql_monitoring.py and mysql_monitoring.cfg file from our GitHub repository.
- Follow the steps given in this article to know how to run the python script in Windows server.
- Create a folder with the name 'mysql_monitoring', under the Site24x7 Windows agent plugin directory - C:\Program Files (x86)\Site24x7\WinAgent\monitoring\Plugins\ and place the 'mysql_monitoring.py' file under 'C:\Program Files (x86)\Site24x7\WinAgent\monitoring\Plugins\mysql_monitoring\'
Automatic AppLogs integration
To analyze the metrics with MySQL logs and find the exact root cause of issues, you can perform configuration changes in the
mysql_configuration.cfgfile.Example:logs_enabled ="true"log_type_name="MySQL Error"log_file_path="/var/mysql/log/error.txt"
Manually execute the plugin script using the following command and verify its output:
python mysql_monitoring.py --host="host_name" --port="port_number" --username="username" --password="password"
View Data in the Site24x7 Web Client
- Log in to Site24x7 and go to Server > Plugins > click on the plugin monitor.
- You will be able to view the performance charts on the various metrics for your MySQL server.
Feel free to contribute to our existing plugin and come up with suggestions or feedback on our Community.