Using resource monitoring to avoid user service overload

Wed, 2014-08-20 11:47anatoliydimitrov

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 FLUSH USER_STATISTICS;.

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).

Taking action

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.

Tags: DBAHowto About the Author Anatoliy Dimitrov

Anatoliy Dimitrov is an open source enthusiast with substantial professional experience in databases and web/middleware technologies. He is as interested in technical writing and documentation as in practical work on complex IT projects. His favourite databases are MariaDB (sometimes MySQL) and PostgreSQL. He is currently graduating his master's degree in IT and aims to a PhD in Bionformatics in his home town University of Sofia.