Bash One-Liners and Other Tools to Simplify MySQL Ops at Scale - PowerPoint PPT Presentation

Bash One-Liners and Other Tools to Simplify MySQL Ops at Scale Percona Live Santa Clara, California | April 25th, 2017 Brian Cain (Dropbox)

Brian Cain Working at Dropbox as a MySQL SRE ● Database Engineer ● Python and Bash Developer ● Based in Seattle 2

Agenda ● Overview of a some common Bash tools ● Too many running threads scenarios ● Random sometimes useful one-liners 3

Bash tools ● grep - Search input ● sort - Sort alphabetically or numerically ● uniq - Limit input to unique lines ● awk - Manipulate input a line at a time ● sed - Stream editor ● tr - Translate or delete characters ● cut - Break apart a line ● paste - Merge lines into one ● bc, seq, lsof, du, ss, printf, ... 4

More than one way sample.lst ● cat sample.lst | paste -s -d ',' - 1 2 ● cat sample.lst | tr '\n' ',' | sed 's/,$/\n/' 3 4 ● awk -v ORS=, '{print $1}' sample.lst | sed 's/,$/\n/' 5 ● awk ' { if($1 != "") { if(NR==1) { printf $1 } else { 1,2,3,4,5 printf "," $1 } } }' sample.lst | paste - 5

Scenario: Too many running threads ● Overwhelming number of fast queries ● Smaller number of long running queries ● Lock contention by DDL or update ● Semi-sync replication delay SHOW [FULL] PROCESSLIST 6

How bad is it? mysql -e "show full processlist" | tail -n +2 | awk '{print $5}' | sort | uniq -c 7

Top 5 longest running queries mysql -e "show full processlist" | tail -n +2 | egrep -i 'select |insert |update |delete ' | sort -k6,6 -n -r | head -5 8

For the MySQL purist mysql -e "SELECT * FROM INFORMATION_SCHEMA.PROCESSLIST WHERE COMMAND='Query' ORDER BY TIME DESC LIMIT 5;" 9

Kill all threads associated with a query while [ 1 ]; do sleep 1; mysql -e "show full processlist" | grep 'SELECT \* from very_large_table' | awk '{print "kill “$1”;"}' | mysql; done 10

What if it’s a sleeping open transaction mysql -e "show engine innodb status\G" | grep -A 3 ACTIVE mysql -e "show full processlist" | grep Sleep | grep write_user awk '{print "kill “$1”;"}' | mysql 11

Picking out a section print_it=1; while read line; do if [ "$print_it" -eq 0 ]; then echo "$line" | egrep -q '^TRANSACTIONS'; stop=$?; if [ "$stop" -eq 1 ]; then echo "$line"; else break; fi; else echo "$line" | egrep -q '^LATEST DETECTED DEADLOCK'; print_it=$?; if [ "$print_it" -eq 0 ]; then echo "$line"; fi; fi; done < <(mysql -e 'show engine innodb status\G') 12

Other random one-liners: Disk usage du -b /var/lib/mysql/mysql-bin.0* | awk '{print $1}' | paste -sd+ - | sed 's/.*/scale=3; \(\0\)\/1024\/1024\/1024/' | bc | sed 's/.*/\0 G/' 13

Hidden temp files taking up disk sudo lsof | grep $(mysql -e "select @@tmpdir" -BN) | sort -n -k 8 14

Network traffic (MySQL and proxy) mysql -e "show processlist" | tail -n +2 | awk '{print $3}' | cut -f 1 -d ':' | sort | uniq -c ss | grep 3001 | awk '{print $5}' | cut -f 1 -d ':' | sort | uniq -c | sort -n 15

Comparing tables across shards table_stmt=''; for shard in $(mysql -e "show databases like 'shard%'" -BN); do next_table_stmt= $(mysql -e "show create table $shard.changeset" -BN | sed 's/AUTO_INCREMENT=[0-9]\+//'); if [ "$table_stmt" == '' ]; then table_stmt="$next_table_stmt"; elif [ "$table_stmt" != "$next_table_stmt" ]; then echo "Mismatch found in $shard.changeset"; else echo "$shard.changeset matches"; fi; done; 16

Using sequences for index in $(seq 0 8 63); do shard=$(printf "shard%03d" $index); echo $shard; done for index in $(seq 0 8 63); do shard=$(printf "shard%06d" $(echo "obase=2;$index" | bc)); echo $shard; done 17

Parallel SSH (Dropbox gsh) gsh -F256 -c -q'slave mysql-fake_shard_db metro=sjc' 'mysql -e "show slave status\G" | grep Sec' 18

Q&A Any questions? 19

Rate My Session 20