Minimalistic MySQL collection scripts

README.md

These bash scripts do the smallest amount of work possible to collect simple MySQL metrics and running SQL queries of a running system for subsequent analysis. These scripts include:

• collect-mysql-status Snapshot of Status, ProcessList and supporting information for 60 seconds
• sample-mysql-queries SQL sampling (brute force approach) for approximately 20 seconds

These scripts are designed to be executed on a Linux host, and require the mysql client to be installed, and MySQL credentials to run SQL placed in the $HOME/.my.cnf file.

NOTE: The output of information of SQL statements may include PII information. It is important data is reviewed and masked.

sample-mysql-queries is not an ideal way to collect SQL statements, the Slow Query Log is the ideal mechanism, tuning the long_query_time down incremently for improved granularity. This SQL script runs without any configuration changes and offers example full SQL statements that can be used for subsequent testing.

Execution

time ./collect-mysql-status
time ./sample-mysql-queries

Customizations

The collection of SQL queries runs a quickly repeating processlist. You can tune the number of iterations and duration between if this default settings are seen to be impacting system usage.

COUNTER=100 SLEEP_DELAY=0.02 ./sample-mysql-querie

Example Output

$ ./collect-mysql-status
INFO:  Validating pre-requisites
INFO:  Configuring
INFO:  MySQL available
INFO:  Writing output for '20240923.150114'
INFO:  variables
version	8.0.36
version_comment	Source distribution
version_compile_machine	x86_64
version_compile_os	Linux
version_compile_zlib	1.2.13
INFO:  innodb status
INFO:  processlist
16
INFO:  00 status
INFO:  01 status
INFO:  04 status
INFO:  10 status
INFO:  45 status
20240923.150114/20240923.150114.status.00.txt:Uptime	3393
20240923.150114/20240923.150114.status.01.txt:Uptime	3394
20240923.150114/20240923.150114.status.04.txt:Uptime	3398
20240923.150114/20240923.150114.status.10.txt:Uptime	3408
20240923.150114/20240923.150114.status.45.txt:Uptime	3453
20240923.150114/20240923.150114.status.00.txt:Com_select	3787724
20240923.150114/20240923.150114.status.01.txt:Com_select	3794255
20240923.150114/20240923.150114.status.04.txt:Com_select	3821314
20240923.150114/20240923.150114.status.10.txt:Com_select	3846182
20240923.150114/20240923.150114.status.45.txt:Com_select	3846406
INFO:  innodb status
INFO:  processlist
16

$ time ./sample-mysql-queries
INFO:  Validating pre-requisites
INFO:  MySQL available
INFO:  Writing output for '20240923.153316'
INFO:  brute force capture running SQL
....................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................................
26939 20240923.153316/20240923.153316.unique-sql.txt
States
  25400 statistics
    441 Opening tables
    154 preparing
    146 executing
    131 freeing items
    130 closing tables
    125 optimizing
    110 starting
    107 waiting for handler commit
     72 System lock
     32 init
     25 checking permissions
     23 end
     17 query end
     10 cleaning up
      8 Sending to client
      4 NULL
      2 update
      1 Receiving from client
      1 Executing hook on transaction begin.
Execution times
  26896 0
     43 1
Commands
  26938 Query
      1 Connect

collect-mysql-status

#!/usr/bin/env bash


readonly DT_FORMAT="+%Y%m%d.%H%M%S"
readonly MYSQL_OPTIONS="-N"
error() {
  echo "ERROR: $*"
  exit 1
}

info() {
  echo "INFO:  $*"
  return 0
}


info "Validating pre-requisites"
command -v mysql >/dev/null || error "mysql not in \$PATH"
[[ -s "${HOME}/.my.cnf" ]] || error "Setup \$HOME/.my.cnf"
mysql -e "SELECT VERSION()" >/dev/null 2>&1 || error "Cannot connect to host, check \$HOME/.my.cnf"

info "Configuring"
info "MySQL available"

NOW="$(date ${DT_FORMAT})"
mkdir -p "${NOW}"
info "Writing output for '${NOW}'"
info "variables"
mysql "${MYSQL_OPTIONS}" -e "SHOW GLOBAL VARIABLES" > "${NOW}/${NOW}.variables.0.txt"
grep ^version "${NOW}/${NOW}.variables.0.txt"
info "innodb status"
mysql "${MYSQL_OPTIONS}" -e "SHOW ENGINE INNODB STATUS\G" > "${NOW}/${NOW}.innodb.0.txt"
info "processlist"
mysql "${MYSQL_OPTIONS}" -e "SHOW FULL PROCESSLIST" > "${NOW}/${NOW}.processlist.0.txt"
grep -v Sleep "${NOW}/${NOW}.processlist.0.txt" | wc -l
for SLEEP in 00 01 04 10 45; do
  info "${SLEEP} status"
  sleep "${SLEEP}"
  mysql "${MYSQL_OPTIONS}" -e "SHOW GLOBAL STATUS" > "${NOW}/${NOW}.status.${SLEEP}.txt"
done
grep "^Uptime[^_]" ${NOW}/${NOW}.status.*.txt
grep "^Com_select" ${NOW}/${NOW}.status.*.txt
info "innodb status"
mysql "${MYSQL_OPTIONS}" -e "SHOW ENGINE INNODB STATUS\G" > "${NOW}/${NOW}.innodb.1.txt"
info "processlist"
mysql "${MYSQL_OPTIONS}" -e "SHOW FULL PROCESSLIST" > "${NOW}/${NOW}.processlist.1.txt"
grep -v Sleep "${NOW}/${NOW}.processlist.0.txt" | wc -l


exit 0

sample-mysql-queries

#!/usr/bin/env bash
# This is a dumb brute force approach at capturing full SQL statements in flight
# when using the slow_query_log/long_query_time=0 is not possible.
# Capturing partial queries or digests do not enable analysis and replay

readonly DT_FORMAT="+%Y%m%d.%H%M%S"
readonly MYSQL_OPTIONS="-N"
COUNTER=${COUNTER:-500}
SLEEP_DELAY=${SLEEP_DELAY:-0.01}

error() {
  echo "ERROR: $*"
  exit 1
}

info() {
  echo "INFO:  $*"
  return 0
}


info "Validating pre-requisites"
command -v mysql >/dev/null || error "mysql not in \$PATH"
[[ -s "${HOME}/.my.cnf" ]] || error "Setup \$HOME/.my.cnf"
mysql -e "SELECT VERSION()" >/dev/null 2>&1 || error "Cannot connect to host, check \$HOME/.my.cnf"

info "MySQL available"

NOW="$(date ${DT_FORMAT})"
mkdir -p "${NOW}"
info "Writing output for '${NOW}'"
info "brute force capture running SQL"
while [[ ${COUNTER} -gt 0 ]]; do
  echo -n "."
mysql "${MYSQL_OPTIONS}" -e " \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	SELECT ID, USER, HOST, DB, COMMAND, TIME, STATE, INFO FROM information_schema.processlist WHERE command NOT IN ('Sleep', 'Daemon', 'Binlog') AND ID != CONNECTION_ID(); \
	" >> "${NOW}/${NOW}.sql.txt"
  sleep "${SLEEP_DELAY}"
  COUNTER=$(( COUNTER - 1 ))
done
echo ""
sort "${NOW}/${NOW}.sql.txt" | uniq -c | sort -nr > "${NOW}/${NOW}.unique-sql.txt"
wc -l "${NOW}/${NOW}.unique-sql.txt"
echo "States"
cut -d$'\t' -f7 "${NOW}/${NOW}.unique-sql.txt"  | sort | uniq -c | sort -nr
echo "Execution times"
cut -d$'\t' -f6 "${NOW}/${NOW}.unique-sql.txt"  | sort | uniq -c | sort -nr
echo "Commands"
cut -d$'\t' -f5 "${NOW}/${NOW}.unique-sql.txt"  | sort | uniq -c | sort -nr
exit 0