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)

2

Brian Cain

Working at Dropbox as a MySQL SRE

• Database Engineer
• Python and Bash Developer
• Based in Seattle

3

Agenda

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

4

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, ...

5

More than one way

sample.lst 1 2 3 4 5 1,2,3,4,5

• cat sample.lst | paste -s -d ',' -
• cat sample.lst | tr '\n' ',' | sed 's/,$/\n/'
• awk -v ORS=, '{print $1}' sample.lst | sed 's/,$/\n/'
• awk ' { if($1 != "") { if(NR==1) { printf $1 } else {

printf "," $1 } } }' sample.lst | paste -

6

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

7

How bad is it?

mysql -e "show full processlist" | tail -n +2 | awk '{print $5}' | sort | uniq -c

8

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

9

For the MySQL purist

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

10

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

11

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

12

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')

13

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/'

14

Hidden temp files taking up disk

sudo lsof | grep $(mysql -e "select @@tmpdir" -BN) | sort -n -k 8

15

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

16

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;

17

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

18

Parallel SSH (Dropbox gsh)

gsh

• F256
• c
• q'slave mysql-fake_shard_db metro=sjc'

'mysql -e "show slave status\G" | grep Sec'

19

Q&A

Any questions?

20

Rate My Session