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.