Command line QPS (Queries Per Second) Quick and Dirty

Ever wanted to just look at QPS in real time while logged into your server?

Well here’s a little command line hackery to do it quick and dirty.

[user@yourserver ~] $ LASTVAL=0; while true; do CURVAL=`mysql --batch -N -e "show status like 'Quer%';" | awk '{print $2}'`; QPS=`expr $CURVAL - $LASTVAL`; if [ $LASTVAL -ne 0 ]; then echo "$CURVAL $QPS"; fi; LASTVAL=$CURVAL; sleep 1; done

The output looks like this:

65549603430 2439
65549605421 1991
65549606912 1491
65549611219 4307
65549614186 2967
65549618048 3862
65549620853 2805

The first column is just the Query counter value. The second column is the QPS.

This script requires a .my.cnf to exist in your home directory (or that you do something nastily insecure and supply -u user -ppassword to the mysql command in the example above).

Fetch CSV of MySQL table size vs .ibd container size

This only works if you’re using innodb_file_per_table.

Purpose: import this csv quickly into google sheets (or other spreadsheet) and compare MySQL’s internal data size to the container size on disk to determine tables needing to be optimized (or “null altered”) to reclaim disk space and maybe increase performance due to defragmentation. Rule of thumb is probably something like >=10% difference may warrant action.

I wrote this loop as a one-liner dynamically / ad-hoc on the command line a couple weeks ago but made it into a configurable, yet quick-and-dirty shell script, below.

Add -u and -p arguments to MySQL CLI command if you need to, or just place a .my.cnf in your home directory and use the script as-is.


DATADIR="/path/to/datadir" # ex: /var/lib/mysql

for x in `mysql --batch -n -e "select concat(concat(table_name,'.ibd'),',',(data_length + index_length)) as total_length from information_schema.tables where table_schema = '${SCHEMANAME}';"`
	FILE=`echo $x | cut -d , -f1`;
	FSIZE=`ls -la ${DATADIR}/${SCHEMANAME}/${FILE} | awk '{print $5}'`; 
	echo ${x},${FSIZE};

New site is reborn as a technology blog!

  • MySQL tips and tricks
  • MySQL deep dives
  • Linux/Bash command line hackery
  • Other items that come to mind
  • Cookin’ (maybe with tech, maybe with food)

Please come back soon!