Help in finding large transaction

MySQL & MariaDB Percona XtraDB Cluster 5.x
1

Hi,

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?

2

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):
[url]http://www.percona.com/doc/percona-toolkit/2.2/pt-stalk.html[/url]

PTDEST=/tmp/pt/collected/hostname/
mkdir -p $PTDEST;
cd /tmp/pt;
wget percona.com/get/pt-stalk;
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.