search

Percona-Lab / sysbench-tpcc

Sysbench scripts to generate a tpcc-like workload for MySQL and PostgreSQL
Apache License 2.0
292 stars 104 forks source link

`innodb_flush_log_at_trx_commit` is not reset correctly at the end of `prepare` #37

Closed sayap closed 3 years ago

sayap commented 3 years ago

Commit d3ebc95e tries to get the current value of innodb_flush_log_at_trx_commit, set it globally to 0 to make data loading faster, and then reset it back to the original value.

However, when there are multiple threads doing the same thing, some threads will end up getting SET @trx = 0, as the value was already modified globally by the earlier threads. Subsequently, once the prepare phase is done, the server will now be running with innodb_flush_log_at_trx_commit = 0. This then typically makes the benchmark numbers look much better than actual.

For more context, I discovered this flaw because I just couldn't get my numbers to match with Percona's or Dimitri's. In my test steps, I would usually do the prepare first, then stop the server to take a ZFS snapshot, start back the server, and then only execute the run. This means the numbers are not impacted by this issue. Then, yesterday, I decided to just do the prepare and run without restarting the server in between, and boom, the numbers are magically 8x better.

I suspect some of the posted benchmark numbers by Percona or Dimitri could be impacted by this (but can't tell based on the posting date / commit date).

vadimtk commented 3 years ago

This is valid point. I reverted changes for innodb_flush_log_at_trx_commit