Help in finding large transaction


We have somewhat of a stall in our PXC setup, once or twice a day there is a query creating a huge transaction which stalls the cluster until the transaction hits the transaction limit, i.e, this is logged:

140504 12:34:37 [Warning] WSREP: transaction size limit (1073741824) exceeded: 1073774592

I’ve tried tracking down the query causing this on my own but I can’t seem to find it. Are there any way i can log querys causing large transactions?

Hi blodan,

In case if query/transaction is cslow - you can check the following things:

  • entire error log
  • show engine innodb status\G
  • show global status like ‘ws%’
  • you can enable slow query log and check for slow queries

The best is to run pt-stalk (our tool):

mkdir -p $PTDEST;
cd /tmp/pt;
chmod +x pt*;
sudo ./pt-stalk --no-stalk --iterations=2 --sleep=30 --dest=$PTDEST – --user=root --pass=;

Then it will collect a lot of info in $PTDEST folder and you’ll be able to check info there.