User Performance Data on MySQL

When you need a fast snapshot of what a particular MySQL user is doing, running two ad-hoc queries by hand gets tedious. The command below grabs the current system user from your working directory, then pulls both historical statistics and live session data in a single call.

The Command

user=$(pwd | cut -d / -f3)
mysql INFORMATION_SCHEMA -e "
  SELECT USER, CPU_TIME, TOTAL_CONNECTIONS, BUSY_TIME,
         BYTES_RECEIVED, BYTES_SENT, SELECT_COMMANDS,
         DENIED_CONNECTIONS, LOST_CONNECTIONS
  FROM USER_STATISTICS
  WHERE USER LIKE '$user%'
  ORDER BY BUSY_TIME DESC, TOTAL_CONNECTIONS DESC
  LIMIT 20;

  SELECT DB, COMMAND, TIME, STATE, LEFT(INFO, 1440)
  FROM PROCESSLIST
  WHERE USER LIKE '$user%'
  ORDER BY TIME DESC
  LIMIT 30;
"

What It Does

• User detection. pwd | cut -d / -f3 extracts the third path segment (typically the username) so the query filters to the relevant MySQL account without you typing it.
• USER_STATISTICS query. Returns aggregate metrics per user: CPU time, total and denied connections, bytes in/out, and select command count. Results are sorted by busy time, then connection count, capped at 20 rows.
• PROCESSLIST query. Shows currently active sessions for that user, including the database, command, elapsed time, state, and the first 1,440 characters of the running statement. Sorted by elapsed time descending, capped at 30 rows.

Tips

• If your directory structure does not place the username in the third path segment, adjust the cut -f field number accordingly.
• The LIKE '$user%' pattern is a prefix match, so it will also catch accounts that share the same prefix. Swap it for = '$user' if you need an exact match.
• USER_STATISTICS is available in MySQL 5.6+. On MariaDB the equivalent table is INFORMATION_SCHEMA.USER_STATISTICS as well, but column names may differ slightly.

• Technology
• Bigdata

• Sql
• SysAdmin
• Bash