First the table is not that large (1.1GB) and this number of rows:
The table is 1GB and the server has 192 GB of RAM.
This table exists on two different machines (lets call them machine1 and machine2). These machines are on two different replication streams and this causes that table contents on these machines may differ. Periodically, customer executes Percona tool pt-sync-table (http://www.percona.com/doc/percona-toolkit/2.2/pt-table-sync.html). On master this produces following kind of transactions (single transaction containing lot of statements):
On slave this same transaction is ~10x slower:
So runtime: 246994 = ~ 2 days and 21 hours. Again vs the original ~ 7 hours (on the master) this behaviour is really not good. So the question is: why it takes so much longer and what can be done about this to prevent it?
Server was not I/O bound. It was 100% cpu bound with a single thread pegged at 100%
Number of different statements on that one big transaction: