Skip to content

Database Server Monitoring

Any application that uses a database directly depends on it. This means that if the database server is running slowly or not running at all, the same happens with the application.

Imagine an online store website. Product information is stored in a database. To return a page with products to the user, the web application must make a request to the database, receive a response, and process it. If the application takes 10 seconds to receive a response from the database server, it simply will not be able to return the product page to the user in time. With such a slow website, most users will simply go to another online store.

Database server monitoring helps prevent such situations. With metrics and indicators, you can see potential problems before users notice them. Monitoring also allows you to fix many problems, for example, by creating an additional index or increasing the resources for the database server.

In general, monitoring consists of collecting system metrics and indicators, analyzing them, and responding to certain values. Monitoring can be done manually or with the help of separate tools called monitoring systems. In the first case, you need to regularly review metrics and indicators yourself. Monitoring systems collect the necessary metrics on their own and send notifications in case of problems.

Regardless of the approach, you need to know which metrics to pay attention to when working with a database server. They can be divided into four main categories:

  • Service availability monitoring. This is the actual state of the database system service. You can read about this metric and its indicators in the Error Log section.
  • Server throughput monitoring. You can read about this set of metrics in the Maximum Number of Connections section.
  • Query performance monitoring. The Slow Query Log section is dedicated to this category of metrics.
  • Infrastructure monitoring. In our case, this is monitoring the server on which the database service is running, i.e., its CPU usage, memory, and disk resources. If analyzing all the previous metrics did not help solve the problem, then infrastructure monitoring most likely will. For example, if the database server is running slowly even though all known schema optimization techniques have already been applied, increasing the server's resources, such as an additional CPU core, will most likely solve the problem.

Analyzing these metrics and indicators in this order will help resolve or prevent most problems you may encounter when administering databases.

Error Log

You can check the status of the MySQL service using the following command:

systemctl status mysql

If the service does not start, the error log will help you find out the cause of the error. If you encounter incorrect or anomalous behavior of the database server, this is the first place to look for additional information.

The error log is a log file that records errors that occur during the operation of the MySQL server. This file often contains problems that may arise when starting the service. You can also find information about configuration issues, as well as warnings and other diagnostic messages. Regularly reviewing this log allows you to proactively respond to potential problems.

The error log in MySQL is active by default; it does not need to be enabled separately. MySQL automatically creates and maintains it, recording error messages, warnings, and other important events that occur during server operation. You can manage the location and other parameters of this log through the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

The path to the error log file is specified in the log_error parameter.

Maximum Number of Connections

When you establish a connection with a MySQL server, for example, from MySQL Workbench, the server reserves a certain amount of computing resources to service that connection. To avoid leaving the database management system components, such as storage engines, without computing resources, MySQL has a limit on the maximum number of connections. If the number of connections exceeds the maximum, the server stops accepting new connections. To avoid this, you need to know the maximum number of connections to the server and monitor the active number of connections.

The maximum number of client connections can be set in the MySQL configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

This number is stored in the max_connections parameter. By default, the server can accept up to 151 connections. This value can, although it is not recommended, be increased to 100000.

You can view the current number of active connections from the client by executing the following query:

sudo mysql -u root
show status like 'max_used_connections';

If the current number of connections is approaching the maximum, you should increase the maximum number of connections. However, it is very important to monitor how this affects query execution performance. If increasing the number of client connections affects how queries are executed, you should either allocate more computing resources to the database server or reduce the number of client connections. For example, move some databases to another, separate database server.

Slow Query Log

The slow query log is needed to identify inefficient or problematic queries that may cause database performance degradation. The fact that a query is slow may indicate that the database is using the database server's resources inefficiently. This, in turn, leads to performance degradation for all users. Identifying and fixing such queries helps avoid similar problems. The slow query log enables the discovery of slow queries for further database performance optimization (rewriting slow queries, creating indexes, or changing the database structure).

Let's see how this works. First of all, you need to enable slow query logging. To do this, open the database server configuration file:

sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf

To enable slow query logging, you need to make sure that the corresponding configuration parameters are not commented out. The slow_query_log parameter needs to be set to 1, and you also need to specify the path to the log file using the slow_query_log_file parameter:

slow_query_log = 1
slow_query_log_file = /var/log/mysql/mysql-slow.log

To more precisely configure slow query logging, you should consider a few more configuration parameters. First of all, we have the ability to define what exactly we consider a slow query. After all, for different databases, this can be a different time. You can set this parameter in long_query_time in seconds. If a query's execution time is greater than the specified value, it will be recorded in the slow query log. For example, if long_query_time is set to 2 seconds, any SQL query that takes longer than two seconds will be logged.

Let's look at an example and set long_query_time to 0. In real life, it makes sense to use a value of 2 or more, otherwise the log will be too large and it will be difficult to determine which queries are problematic and which are not:

long_query_time = 0

There is also another parameter - log_queries_not_using_indexes, which allows adding to the log only those queries that do not use indexes. This can help simplify the search, since often queries that do not use an index are the easiest to fix.

After we changed the database server configuration, we need to restart the service:

sudo systemctl restart mysql

Now let's connect to the database server using the MySQL client:

mysql -u mysqluser -pP@ssw0rd

Let's execute a few queries:

use company;
select * from Employees;
exit;

Since we set long_query_time to 0, the queries we just executed ended up in our slow query log, despite the fact that they executed quite quickly. Let's look at the file:

sudo tail /var/log/mysql/mysql-slow.log

# Time: 2024-02-02T17:33:25.391117Z
# User@Host: mysqluser[mysqluser] @  [93.175.200.108]  Id:   796
# Query_time: 0.000122  Lock_time: 0.000000 Rows_sent: 0  Rows_examined: 0
select * from Employees;
set autocommit=1;

Here we have a lot of useful information:

  1. The query that was executed.
  2. The query execution time and information about the user who executed the query.
  3. The total query execution time - query_time.

  4. The time the query spent waiting for a locked object - lock_time.

    Locking is a mechanism that checks whether data is being updated correctly. Locking is implemented differently in different database engines. MyISAM locks the entire table when updating a database row. Therefore, if even one client is updating data in a table, the rest of the queries will have a large lock_time different from zero, since they will have to wait for the data update to complete. If data in such a table is updated relatively frequently, it makes sense to switch the storage engine to InnoDB, which, when updating, only locks the row being updated and still allows it to be read.

    A large number of overly complex transactions that use more than one table also affects lock_time. If your application uses transactions and the average lock_time is quite large relative to query_time, it is worth reconsidering their necessity. 5. rows_sent and rows_examined - the number of rows sent and the number of rows on which the WHERE query condition was checked. If these numbers differ and the table is frequently searched by one of the columns, creating an index on that column should improve the performance of such queries.

As you can see, the slow query log provides many clues for improving database performance. However, when it is enabled, the error log constantly grows in size. Therefore, when using this logging, carefully monitor the disk space usage on your server and disable the slow query log when you do not need it. To disable slow query logging, simply comment out the configuration parameters we added at the beginning of this lesson and restart the MySQL service.