Using resource monitoring to avoid user service overload
With MariaDB, as with any service, you must monitor user resource usage to ensure optimal performance. MariaDB provides detailed statistics for resource usage on per-user basis that you can use for database service monitoring and optimization. User statistics are especially useful in shared environments to prevent a single gluttonous user from causing server-wide performance deterioration. If you detect abnormal use, you can apply fine-grained limits, as we'll see.
To enable user statistics in MariaDB, edit the server configuration file /etc/my.cnf.d/server.cnf. In the
[mysqld] section, add
userstat = 1, then restart the service.
Now MariaDB will gather and store usage statistics in the table USER_STATISTICS in the database information_schema. USER_STATISTICS uses the Memory engine and does not preserve information upon service restarts, so statistics are reset when you restart the MariaDB service. You can also reset statistics manually with the command
Retrieving user statistics
To see all the user statistics, use the command
SHOW USER_STATISTICS. It returns all the information about all the users, and gives you an overall look at resource usage. The output can help you spot inappropriately high usage by one user compared to others.
You can get more summarized information by filtering your query and retrieving information directly from the database with a command such as
select CPU_TIME from information_schema.USER_STATISTICS where USER='test1';. This command shows the cumulative CPU time in seconds spent on serving user test1's connections.
Understanding user statistics
An example output of all the user statistics for user test1 might look like this:
MariaDB [(none)]> select * from information_schema.USER_STATISTICS where USER='test1' \G *************************** 1. row *************************** USER: test1 TOTAL_CONNECTIONS: 105 CONCURRENT_CONNECTIONS: 0 CONNECTED_TIME: 0 BUSY_TIME: 0.10427200000000013 CPU_TIME: 0.028732600000000018 BYTES_RECEIVED: 8190 BYTES_SENT: 86520 BINLOG_BYTES_WRITTEN: 0 ROWS_READ: 630 ROWS_SENT: 735 ROWS_DELETED: 0 ROWS_INSERTED: 0 ROWS_UPDATED: 0 SELECT_COMMANDS: 210 UPDATE_COMMANDS: 0 OTHER_COMMANDS: 0 COMMIT_TRANSACTIONS: 105 ROLLBACK_TRANSACTIONS: 0 DENIED_CONNECTIONS: 15 LOST_CONNECTIONS: 0 ACCESS_DENIED: 15 EMPTY_QUERIES: 0 1 row in set (0.00 sec)
The names of the fields explain what information they hold. The most important ones are:
- TOTAL_CONNECTIONS, CONCURRENT_CONNECTIONS, and CONNECTED_TIME – If any or all of these are high, you may see errors such as 'Too many connections.' By default, in MariaDB the maximum number of connections to the servers is just 151, which aggressive users can easily exhaust.
- BUSY_TIME and CPU_TIME – BUSY_TIME indicates for how long there was activity on the user connections, while CPU_TIME indicates the CPU time spent on servicing the user connections. The latter is more important, as it shows the direct user impact on CPU utilization.
- BYTES_RECEIVED and BYTES_SENT – These two indicators are useful for monitoring network traffic that originates with MariaDB users. Usually high traffic is not a problem with databases, but in times of service overload the traffic statistics could help spot the base problem faster.
- BINLOG_BYTES_WRITTEN – This indicator may help spot abnormal activity in the binary logs, which are used for replication or backup purposes. If your binary log starts growing unexpectedly, check this indicator first.
- ROWS_READ, ROWS_SENT, ROWS_DELETED, ROWS_INSERTED, SELECT_COMMANDS, UPDATE_COMMANDS, OTHER_COMMANDS, COMMIT_TRANSACTIONS – These indicators give detailed information about a user's SQL work. Along with BUSY_TIME and CPU_TIME, they can give a full picture of the user's impact on the system's load.
- ROLLBACK_TRANSACTIONS – An unusually high number or peaks in this indicator may show problems in the front-end application. A high number of rollback transactions may cause overload, because the front-end application is usually supposed to try recreating the information or query, thus causing additional load for every rolled-back transaction.
- DENIED_CONNECTIONS and ACCESS_DENIED – These two indicators are useful mostly for security purposes and for troubleshooting application problems with incorrect logins. When a user is denied a connection, the attempt goes to DENIED_CONNECTIONS. A denied connection usually indicates incorrect privileges to establish the connection in the first place. ACCESS_DENIED, on the other hand, usually appears when a user has already established a successful connection but has been denied access to certain resource (database or table).
Once you detect abnormally high activity from a user, you can take action to limit the resources allocated to the user by using the account resource limits feature. For instance, for the test1 user, you could run the query
update mysql.user set max_connections=10,max_updates=100,max_questions=1000 where user='test1';. To make this change take effect, also run the command
flush privileges;. The specified user's resources will be limited, and your server's performance should return to normal.
As you can see, MariaDB resource statistics and limits are useful for maintaining optimal service performance.