pt-log-player starts with auto_commit=0

[Daniel Nichter]

Imported from http://code.google.com/p/maatkit/issues/detail?id=1267

Quoting the original:

mk-log-player set autocommit mode to 0 by default which means if no transaction related statements are in the log file it will replay all queries in the same transaction

[Daniel Nichter (daniel-nichter)]

Since Percona Playback is working, we may WontFix all pt-log-player bugs.

[Brian Fraser (fraserbn)]

I just marked bug 1080060 as a duplicate of this, but I'm copypasting Ovais' description here:

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.

[Ovais Tariq (ovais-tariq)]

Thanks Brain, I failed to see that a similar bug was already reported :)

[Daniel Nichter (daniel-nichter)]

Your argument is clear and persuasive. I agree: the tool should AutoCommit => 1 and let the user turn that off if they have a reason for doing so.

For the record, the reason the tool does not do this already is because it was originally designed to simply blast a server with queries in order to load CPU cores and MySQL--that's it, nothing else, and no other considerations. So the current code is rather simplistic and does not include a lot of thought about general use-cases.

[Ovais Tariq (ovais-tariq)]

Daniel,

Thanks for agreeing, and you see now pt-log-player is used for a lot more sophisticated reasons then it was originally designed to work with :)

[Brian Fraser (fraserbn)]

So, do we actually want this on as a default, or do we leave it off for backcompat? Also, what should we call the new option? The linked branch went for the least imaginative solution: --[no]autocommit, but I from the user-side, I dunno if that tells much of anything. --[no]implicit-transaction?

[Ovais Tariq (ovais-tariq)]

Brian,

I think that the value of autocommit should not be changed by the tool by default, rather there should be an option such as "implicit-transaction". And I agree that the option "implicit-transaction" sounds much better.

[Daniel Nichter (daniel-nichter)]

Ovais, do you want to be able to specify N statements per-transaction? Or how do you envision --implicit-transaction working?

[Daniel Nichter (daniel-nichter)]

[2:26pm] Daniel: brian: actually i think your original suggestion is good: --autocommit. but we need to just do the right thing and since 2.1 is coming to an end...
[2:26pm] Daniel: I say we target this to 2.2, make the tool start with autocommit=1, i.e. the MySQL default, and rather than add Yet Another Option, document that you can do --set-vars autocommit=<whatever-you-want>

Is this ok for you Ovais since you know how to hack to tool to do what you need in the interim?

[Ovais Tariq (ovais-tariq)]

Daniel,

That looks fine to me, however, having N statements per-implicit-transaction is also a very good idea. If the fix for this bug is not too much work then it would be really nice to have the fixed pt-log-player available in next 2.1 release, but of course you know better how that will fit into your timeline :)

[Daniel Nichter (daniel-nichter)]

Ovais,

There's a workaround for 2.1: --set-vars "wait_timeout=10000,autocommit=0". This tool will be removed in2.2, so I'd rather not do any more work on it. We need to get away from this tool and focus more on Percona Playback.

[Ovais Tariq (ovais-tariq)]

Daniel,

That sounds good to me.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PT-868