pt-log-player sets autocommit=0 which changes the behaviour of the workload that is being replayed

[Ovais Tariq]

Daniel,

I am reporting it as a bug here based on the discussion that we had.

pt-log-player starts separate threads and sets autocommit=0 beginning implicit transaction. Then it just replays everything that is present in the session file that the thread has to replay and the thread issues a COMMIT statement after it has replayed its session file(s), furthermore if there are COMMIT statements already present in the session file(s) then they would also be executed. So either its one single transaction or many transactions if COMMIT statements are present in the session file its playing.

That is certainly not the behaviour that I understood it to be. I do not think that the tool should implicitly set autocommit=0. Because that is then going to mean that queries would have a different meaning. Let me show you what I mean. Suppose I would like to play a slow query log that contains selects queries as well as DML queries, then playing DML queries using a transactions is going to have a different affect as not playing them in a transaction. For example, suppose the session files created after splitting the query log contain the following queries:

-- session file 1:
update table_x set abc=1 where b=100;
update table_y set xyz=1 where a=100;

-- session file 2:
update table_y set xyz=1 where a=100;
update table_x set abc=1 where b=100;

And suppose the session files are played with two threads, then executing the above queries in an implicit transaction has high chances of causing deadlocks, while if there was no implicit transaction there would have been no deadlock, as each update query would have been a separate transaction that completes as soon as the query completes.

This problem of deadlocks is exactly what I faced when I was doing stress tests on a customer server using production query load. The stress tests caused deadlocks on simple update by PK queries that are not executed inside a transaction on the production server, and hence the stress test introduced its own problems that do not exist on the production server. This is why I enquired in the first place if pt-log-player groups statements while executing them.

I think its wrong to alter the behaviour of how a query stream is executed because then it defeats the purpose of replaying production load, as then the results that are obtained would give an entirely different picture.