1

I want to benchmark the performance of MySQL, I want to know if logging affect performance.

MySQL has logs: general-log, binlog, errlog, redo log. Do these logging behaviors affect performance? For performance, I mean throughput, TPS obtained from benchmarks like TPC-C TPC-H YCSB sysbench oltp.

I know that the redo log (WAL) does affect performance because the queries should wait until the log to be flushed to disk. But do queries wait for other logs?

Thanks!

2

Log files

  • Error log -- very little activity; don't worry about it.
  • redo/undo -- Mandatory; you cannot avoid their use (for InnoDB).
  • binlog -- for replication and/or recovery. Clearly state in your benchmark results the setting of log_bin.
  • general log -- This should normally be turned off. It is a disk hog. Leave it off for benchmarking.
  • slowlog -- It is reasonable to leave it off for benchmarking. (And so-state.) For production systems, I recommend leaving it on. It does not hurt much, even with long_query_time = 1. (That is "seconds".)
  • Double-buffer -- Keep on unless the disk subsystem handles it. (Cf "torn page")
  • innodb_flush_log_at_commit -- =1 for banking-like security; =2 for more speed, at the expense of very rare loss of data.

The disk subsystem makes a big difference in certain write-intensive apps:

  • Plain HDD -- slow
  • SSD -- noticeably faster, but non-zero write time.
  • RAID with Battery Backed Write Cache -- virtually eliminates I/O time for writes. The BBWC avoids data loss even during power failure.

Rant

Benchmarks are a popular way to lie about how great your thing is. What is your goal for the benchmark?

It is somewhat reasonable to compare two hardware setups or versions via comparing benchmarks. However, TPC (etc) benchmarks only test a few focused things. Keep in mind that your app may not be really like any of the TPCs.

Also, any 'real' system has multiple things going on. Benchmarks (like drug trials) mostly punt when it comes to combinations.

Some benchmarks focus on "how many connections can be run before the system keels over?" If you are anywhere near that limit, you are already in trouble, and latency may make the system a failure.

4
  • Thank you very much for the help! I want to find some generic performance drawbacks of popular DBMS towards the newest storage devices (NVMe SSD, etc.). Some examples are MDEV-17380 and MDEV-9916. So I want to use benchmarks (general enough) to expose such drawbacks. The reason why I open this issue is I want to figure out some logging behavior that have heavy performance impact since may trigger such drawbacks. – Tim He Nov 18 '20 at 11:46
  • So I do not target on 'lying about how great a DBMS is', I want to show how bad it may be under a specific device. While I am wondering if the workload is not generic enough, it can not be convincing enough. In other words, if I say: "DBMS-A is very slow when doing a specific query upon SSD", it may make no sense I think. But, I am also not sure about it. Looking forward to your comments @RickJames, thanks! – Tim He Nov 18 '20 at 11:54
  • 1
    @haochenhe - OK. Having the general log on may be best for your test. This might be stressful on writes, especially on SSDs: UPDATE t SET n=n+1 WHERE ... Using UUIDs for the PRIMARY KEY jumps around in the table a lot; have a table bigger than the buffer_pool. Etc. – Rick James Nov 18 '20 at 16:05
  • Thank you for the comment! I am running some experiments, I will put the result in this community later. Thanks! – Tim He Nov 23 '20 at 14:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.