Using pt-query-digest to analyze workloads for PXC

gistfile1.txt

#!/bin/sh


PATH=$PATH:~


echo "Creating digests for $1"


# find busy writes
pt-query-digest --filter '$event->{fingerprint} !~ m/^select|^set|^commit|^show|^admin|^rollback|^begin/i' --order-by Query_time:cnt --limit 20 $1 > $1-busy-writes.digest


# find lock waits
pt-query-digest --order-by InnoDB_rec_lock_wait:sum --limit 20 $1 > $1-rec-lock-wait.digest


# find transactions with most rows affected
pt-query-digest --group-by InnoDB_trx_id --order-by Rows_affected:sum  --limit 20 $1 > $1-trx-rows-affected.digest


# find transaction by hightest statement count
pt-query-digest --group-by InnoDB_trx_id --order-by Query_time:cnt  --limit 20 $1 > $1-trx-most-statements.digest


# find largest statement by rows affected
pt-query-digest --order-by Rows_affected:max  --limit 20 $1 > $1-stmt-most-affected.digest




# Find busiest tables updated
pt-query-digest --group-by tables --order-by Query_time:cnt  --limit 20 $1 > $1-busiest-tables.digest