Implementing MySQL performance monitoring via the MySQL v2 sensor.
- Create the SQL file to pull the statistics you would like.
C:\Program Files (x86)\PRTG Network Monitor\Custom Sensors\sql\mysql\Queries_Threads_SlowQueries.sql - Insert the following query:
SELECT * from information_schema.GLOBAL_STATUS where VARIABLE_NAME='QUERIES' OR VARIABLE_NAME='THREADS_CONNECTED' OR VARIABLE_NAME='THREADS_RUNNING' OR VARIABLE_NAME='SLOW_QUERIES' ; - Add MySQL v2 sensor and configure it like this:
| Option | Setting Or Value |
|---|---|
| Sensor Name | QUERIES THREADS_CONNECTED SLOW_QUERIES |
| Database | <name of your mysql database> |
| Select Channel Value by | Key Value Pair |
| Sensor Channel #1 Name | Queries |
| Select Channel Value by | Key value pair |
| Sensor Channel #1 Name | QUERIES |
| Sensor Channel #1 Key | QUERIES |
| Sensor Channel #1 Mode | Difference |
| Sensor Channel #1 Unit | Count |
| Sensor Channel #2 Name | THREADS_CONNECTED |
| Sensor Channel #2 Key | THREADS_CONNECTED |
| Sensor Channel #2 Mode | Absolute |
| Sensor Channel #2 Unit | Count |
| Sensor Channel #3 Name | THREADS_RUNNING |
| Sensor Channel #3 Key | THREADS_RUNNING |
| Sensor Channel #3 Mode | Absolute |
| Sensor Channel #3 Unit | Count |
| Sensor Channel #4 Name | SLOW_QUERIES |
| Sensor Channel #4 Key | SLOW_QUERIES |
| Sensor Channel #4 Mode | Difference |
| Sensor Channel #4 Unit | Count |
Article Comments
Thanks for the formatting. I did not realize you supported wiki markup.
Can we please change the title to:
"How can I monitor MySQL performance via MySQLv2 sensor"
Oct, 2016 - Permalink
Hello,
Your .sql file doesn't work
i got this "Can not log in using the specified credentials!"
The linux account is informed well about the equipment.
Any idea ?
Jun, 2018 - Permalink
Did you try this in the SQLv2.exe (in the sensor system directory)? Are Windows credentials configured for the device? If so, please remove them (or break inheritance). Otherwise, kerberos might be used in combination with the Linux credentials.
Kind regards,
Stephan Linke, Tech Support Team
Jun, 2018 - Permalink
Instead of using 'INFORMATION_SCHEMA.GLOBAL_STATUS' you should use 'performance_schema.global_status' when using MySQL 5.7.6 or newer. A deprecation warning is raised when selecting from the INFORMATION_SCHEMA tables using newer MySQL versions and in MySQL 8.0 it wont even work with compatibility options enabled.
SELECT * from performance_schema.global_status where VARIABLE_NAME='QUERIES' OR VARIABLE_NAME='THREADS_CONNECTED' OR VARIABLE_NAME='THREADS_RUNNING' OR VARIABLE_NAME='SLOW_QUERIES';
Jul, 2018 - Permalink
Sharing is caring, thanks! :)
Oct, 2016 - Permalink