You are here: Home > Products > Network Monitor (Pro) > User Manual > Network Monitoring Checks > Database Server (MySQL, MS SQL, ODBC) Monitoring

Database server (MySQL, MS SQL, ODBC) Monitoring

The program allows you to monitor network MS SQL, MySQL, and ODBC databases.

The “MS SQL Server”, “MySQL”, and “ODBC” checks allow monitoring the state of the respective DBMS and alert when malfunctions in their operation are detected. The check algorithm consists in attempting to connect to the databases with the specified authentication parameters periodically. If the DBMS returns the successful connection result, the check is considered passed.

Moreover, the program can run arbitrary SQL queries, get the results, and analyze the returned values.

The client DBMS library libmySQL.dll is necessary for the successful operation of the “MySQL” check. One of its versions is distributed along with the application. It can be located either in the Windows folder or in the application's folder.

To enable the “MS SQL Server” check, the computer must have the client part of that DBMS. The application supports both Windows and custom authentication by specified login and password.

 

How to Configure Database Monitoring (MySQL Example)

The database health monitoring ensures that the database performance and availability are maintained. The database monitoring includes the control of many parameters and metrics provided by the DBMS, as well as the detection of errors in its operation.

We will describe several basic metrics that can be used to judge the state of the database using the 10-Strike Network Monitor program and MySQL as the example.

1. Availability and Uptime

The availability of a database can be checked by connecting to it from remote hosts. A connection error may indicate the database service's stop or a blocking of the database port by third-party programs.

The uptime parameter helps you learn about fast database restarts. Such restarts may not be covered by the availability check, so it makes sense to check how long the database has been running without failures and shutdowns. For example, you can set the alert threshold when

uptime < 1800

where 1800 is the database uptime in seconds since the last run.

database uptime monitoring

2. Error Connections

One of possible problems in the database operation is the connection errors of new clients. The Number of Connection Errors metric lets you identify times when the database generates these errors too frequently. It includes the following MySQL server state variables (counters):

  • Connection_errors_accept
    The number of errors that occurred during the accept() calls on the listening port.
  • Connection_errors_internal
    The number of connections rejected due to internal server errors, such as failure to start a new thread or running out of memory.
  • Connection_errors_max_connections
    The number of connections rejected because the server's max_connections limit was reached.
  • Connection_errors_peer_address
    The number of errors encountered while looking up the connecting client IP addresses.
  • Connection_errors_select
    The number of errors that occurred during the select() or poll() calls on the listening port (failure of this operation does not necessarily mean that the client connection was rejected).
  • Connection_errors_tcpwrap
    The number of connections rejected by libwrap.

If the max_connections parameter is incorrectly set in the MySQL server settings, then the database may fail with a large number of simultaneous connections, increasing the Connection_errors_max_connections counter. You can prevent this situation by monitoring the ratio of the number of current connections to the maximum possible. For this purpose, you can use the following query:

SELECT ROUND(100 - (100 * Variable_value / [max_connections]), 0) as tc FROM sys.metrics where Variable_name = 'threads_connected'

where instead of [max_connections] you need to substitute the actual value of the max_connections parameter (by default it is set to 100). You can get it with a query in the MySQL console:

show variables like "max_connections";

This command will return something like this:

show variables like max_connections

The tc query variable will show the percentage of available connections, and the threshold can be set to 30%.

Monitoring Database Number of Connection Errors

 

3. Slow Queries

MySQL keeps a log of slow queries. The number of entries in this log can be found using the slow_queries metric. To find its value, you can use a simple SQL query:

SELECT Variable_value FROM sys.metrics where Variable_name = 'slow_queries'

The program may respond to changes to this setting by reporting new entries in the slow query log, which may indicate performance issues.

 

4. Join Queries

Using the join request execution counters, you can be notified about operations that consume too many resources.

  • Select_full_join
    The number of joins that perform table scans because they do not use indexes. If this value is not 0, you should carefully check your table indexes.
  • Select_full_range_join
    The number of joins that used range lookups in the lookup table.
  • Select_range_check
    The number of joins without keys that check for key usage after each row. If they are not 0, then you should carefully check your table indexes.
  • Select_scan
    The number of joins that performed a full scan in the first table.
  • Select_range
    The number of joins that used the ranges in the first table. This is usually not a critical issue, even if the value is quite large.

 

5. Cache Hit Rate

MySQL uses an in-memory cache to optimize disk reads and writes. A low Cache Hit Rate affects database performance. This query helps to calculate the cache hit rate value of open tables:

SELECT ROUND((open_cache_hits / (open_cache_hits + open_cache_misses)), 2) * 100 OpenTableFactor FROM (SELECT variable_value open_cache_misses FROM sys.metrics WHERE variable_name = 'table_open_cache_misses') miss, (SELECT variable_value open_cache_hits FROM sys.metrics WHERE variable_name = 'table_open_cache_hits') hits;

Set the threshold OpenTableFactor to 90%, and if the value is less, the program will notify you.

 

6. Query Execution Time

Another indicator of the database performance degradation is the query execution time. You can write a test SQL query and measure its execution time under normal conditions. The resulting value can be used as a threshold for this test. A significant query timeout for a long period of time may indicate that the database is heavily loaded, and users have to wait a long time for a response from it.

 

MySQL has a large number of metrics that allow you to evaluate the health of the database. Most of them can be found in the INFORMATION_SCHEMA (sys) system tables. The tables and views in this schema are available for data retrieval. The program supports the execution of arbitrary queries, allowing you to create a wide range of database performance checks.

 

Monitoring Other Databases

Nobody knows what a failure in the operation of a DBMS can turn out to be for a company. But, unfortunately, this happens. And if the database has become unavailable, then the faster it is restored, the less losses can be dispensed with. That is why it is very important to learn about database failures and their nature in a timely manner.

You can set up the SQL database monitoring using 10-Strike Network Monitor. Add the "MySQL", "MSSQL", or "ODBC" check and configure the connection settings by specifying the database SQL server address, port, login and password. Select a metric to monitor or specify an SQL query that retrieves values from database tables.

The 10-Strike Network Monitor program connects to the database at a specified frequency and executes a test SQL query, thereby making sure that the DBMS is working properly.

The following DBMS are supported:
• Microsoft SQL
• MySQL
• any other DBMS that supports connection via the ODBC interface (MSAccess®, Oracle®, Sybase, FireBird, and many others).

Database SQL Query Monitoring

Using SQL queries to the database server, you can also monitor third-party services, that store and update their parameters in it. If the parameter value is out of range, the program will notify the administrator about it.

SQL Database Monitoring - Response Time Chart

 

Business process monitoring

You can also set up monitoring of the company business processes by processing the values and metrics stored in the database using SQL queries: the number of sales, revenue, costs , etc. Checks with SQL queries can be used either one at a time or combined into a formula, to calculate a more complex value. For these purposes, the program implements the Check Counted by Formula. For example, we can create two checks for different databases and use the resulting parameters to calculate some other value using the given formula.

Formula Claculated Check

 

See also: HTTP(S) Web Server Monitoring, Web Page Context Monitoring, FTP Server Monitoring

 

Requirements: Windows XP/Vista/7/8.1/10/11, Server 2003/2008/2012/2016/2019/2022 supported.

 

All products
network inventory program
Network Inventory Explorer
Inventory Hardware and Software on Network Computers
network monitoring program
Network Monitor
Monitor Network Servers, Services, Managed Switches, etc.
visual network mapper and monitor
LANState
Monitor and Manage Network Hosts on a Visual Map, Create Network Maps
bandwidth monitoring program
Bandwidth Monitor
Monitor Network Bandwidth and the Internet Traffic Usage
network diagramming program
Network Diagram
Create Network Diagrams, Discover Network Topology
file searching program for network shares and FTP
Network File Search
Search Files on Network Shares and FTP Servers
share access monitoring program
Connection Monitor
Audit Remote User Access to Shares (Folders and Files)
free port scanner
Network Scanner (NEW!)
Free Program for Scanning Networks, Hosts, and TCP Ports