4 ms·

I think it would be interesting if respective technology vendors could be motivated to tune the configuration, and be bound by some rules. eWeek did a DB shoot

by morgo 13y ago

I think it would be interesting if respective technology vendors could be motivated to tune the configuration, and be bound by some rules. eWeek did a DB shootout like this 10 years ago, and (as the rumor goes) led to the creation of the MySQL query cache to specifically beat the benchmark. Getting the various vendors involved would require a lot of clout though :( Here is some commentary on the MySQL configuration: https://github.com/TechEmpower/FrameworkBenchmarks/blob/master/config/my.cnf https://github.com/TechEmpower/FrameworkBenchmarks/blob/mast... * innodb_log_flush_at_trx_commit is not specified, so it's going to default to 1 (durable). This is a little unfair, since other databases won't do this. * innodb_buffer_pool_size isn't specified, so it's going to default to 128M. A database that uses mmap files will have an atvantage here - since it can automatically use free memory and won't require this level of tuning. * I thought it was fair to disable query cache though :)

bhauer 13y ago
Actually, mostly in an informal matter, we have in fact received many test implementations from maintainers of frameworks directly. If you go through the pull requests, you may recognize some folks. I don't want to name drop though. I had in passing asked a few if they would be willing to apply an "official" indicator next to their rows. But the response had been uniformly "not interested." Maybe later I'll ask again. :) Thanks for the notes on the MySQL configuration. There are a lot of gray areas in setting up the configuration, but we do seek recommendations such as these. Aside from concessions we've made to the matter of running a benchmark, the overriding goal for configuration is that "production-class" configuration is preferred. I'm not an expert at InnoDB tuning, so you might tell me that "innodb_log_flush_at_trx_commit = 1" is not typical for production environments, in which case I'd want to make a change. But if it is typical for production, I'd like to keep it as-is. If the point is that other databases do not have something comparable, that speaks to the point of the linked blog entry. These things are not directly comparable, feature-by-feature. We would like Postgres and MongoDB to be deployed in a typical "production-class" configuration, but that may or may not be feature-identical with MySQL.
- rsynnott 13y ago
  In many production environments where a few lost writes in the event of OS crash aren't entirely the end of the world, innodb_log_flush_at_trx_commit is set to 2. In some it will even be set to 0 (in that instance, data is lost in the event of MySQL crash). If you care deeply about every write definitely hitting disk in a recoverable manner, you set it to 1, but at that point you may have to do other stuff (is the OS lying to you? Is your RAID controller lying to you? Is your disk lying to you?) > If the point is that other databases do not have something comparable, that speaks to the point of the linked blog entry. Other databases do have something comparable. It looks like MongoDB actually doesn't, but that's highly unusual. Postgres does (I believe it's on by default), Riak does, Voldemort does (one could make an argument that it's safer to leave it off in the latter two cases than elsewhere).
  - bhauer 13y ago
    If I am parsing you right, it sounds like "innodb_log_flush_at_trx_commit = 2" is what you would recommend for a majority of production environments.
    - rsynnott 13y ago
      Really depends on the scenario, but for most web stuff, I'd tend to recommend a setting of 2, if the data durability caveats were acceptable.
    - morgo 13y ago
      Durability will typically be very expensive to implement unless you have a fast SSD or RAID controller with NVRAM, so it will skew the test considerably if you make MySQL offer it, but say MongoDB does not have to. But I do recommend it for the majority of people. Shameless plug: http://www.tocker.ca/2013/06/19/deciding-whether-or-not-to-make-mysql-durable.html http://www.tocker.ca/2013/06/19/deciding-whether-or-not-to-m... Also: http://dom.as/2013/08/09/on-durability/ http://dom.as/2013/08/09/on-durability/ (Domas works on MySQL@Facebook).
      - 13y ago
venomsnake 13y ago
It worked wonders with specially "optimized" drivers for 3d mark back in the day. I think it is the exact opposite - a benchmark is wonderful in showing a product weakness but you should be very careful of http://en.wikipedia.org/wiki/Goodharts_law http://en.wikipedia.org/wiki/Goodharts_law this. And old joke back in the day with 3d mark enthusisasts was "I tried this 3d mark thing but after 8 hours still could not find the second level"

by morgo 13y ago

bhauer 13y ago

Actually, mostly in an informal matter, we have in fact received many test implementations from maintainers of frameworks directly. If you go through the pull requests, you may recognize some folks. I don't want to name drop though. I had in passing asked a few if they would be willing to apply an "official" indicator next to their rows. But the response had been uniformly "not interested." Maybe later I'll ask again. :) Thanks for the notes on the MySQL configuration. There are a lot of gray areas in setting up the configuration, but we do seek recommendations such as these. Aside from concessions we've made to the matter of running a benchmark, the overriding goal for configuration is that "production-class" configuration is preferred. I'm not an expert at InnoDB tuning, so you might tell me that "innodb_log_flush_at_trx_commit = 1" is not typical for production environments, in which case I'd want to make a change. But if it is typical for production, I'd like to keep it as-is. If the point is that other databases do not have something comparable, that speaks to the point of the linked blog entry. These things are not directly comparable, feature-by-feature. We would like Postgres and MongoDB to be deployed in a typical "production-class" configuration, but that may or may not be feature-identical with MySQL.

rsynnott 13y ago
In many production environments where a few lost writes in the event of OS crash aren't entirely the end of the world, innodb_log_flush_at_trx_commit is set to 2. In some it will even be set to 0 (in that instance, data is lost in the event of MySQL crash). If you care deeply about every write definitely hitting disk in a recoverable manner, you set it to 1, but at that point you may have to do other stuff (is the OS lying to you? Is your RAID controller lying to you? Is your disk lying to you?) > If the point is that other databases do not have something comparable, that speaks to the point of the linked blog entry. Other databases do have something comparable. It looks like MongoDB actually doesn't, but that's highly unusual. Postgres does (I believe it's on by default), Riak does, Voldemort does (one could make an argument that it's safer to leave it off in the latter two cases than elsewhere).
- bhauer 13y ago
  If I am parsing you right, it sounds like "innodb_log_flush_at_trx_commit = 2" is what you would recommend for a majority of production environments.
  - rsynnott 13y ago
    Really depends on the scenario, but for most web stuff, I'd tend to recommend a setting of 2, if the data durability caveats were acceptable.
  - morgo 13y ago
    Durability will typically be very expensive to implement unless you have a fast SSD or RAID controller with NVRAM, so it will skew the test considerably if you make MySQL offer it, but say MongoDB does not have to. But I do recommend it for the majority of people. Shameless plug: http://www.tocker.ca/2013/06/19/deciding-whether-or-not-to-make-mysql-durable.html http://www.tocker.ca/2013/06/19/deciding-whether-or-not-to-m... Also: http://dom.as/2013/08/09/on-durability/ http://dom.as/2013/08/09/on-durability/ (Domas works on MySQL@Facebook).
    - 13y ago

venomsnake 13y ago

It worked wonders with specially "optimized" drivers for 3d mark back in the day. I think it is the exact opposite - a benchmark is wonderful in showing a product weakness but you should be very careful of http://en.wikipedia.org/wiki/Goodharts_law http://en.wikipedia.org/wiki/Goodharts_law this. And old joke back in the day with 3d mark enthusisasts was "I tried this 3d mark thing but after 8 hours still could not find the second level"