mysql - 启用 innodb_dedicated_server 对性能有好处吗？

Question

来自 MySQL8 文档:

When innodb_dedicated_server is enabled, InnoDB automatically configures the following variables:

innodb_buffer_pool_size

innodb_log_file_size

innodb_log_files_in_group (as of MySQL 8.0.14)

innodb_flush_method

Only consider enabling innodb_dedicated_server if the MySQL instance resides on a dedicated server
where it can use all available system resources. Enabling innodb_dedicated_server is not recommended
if the MySQL instance shares system resources with other applications.

假设服务器专用于 MySQL，启用 innodb_dedicated_server 实际上是否比我自己调整这些参数提供更好的性能？

Answer 1

简答:不，它不会比自己设置这些调整选项更能提高性能。
变量 innodb_dedicated_server 在该功能发布时(2017-08-24)有详细说明:
https://mysqlserverteam.com/plan-to-improve-the-out-of-the-box-experience-in-mysql-8-0/
它只是许多调整选项的简写。新变量不会以任何特殊方式提高性能，它与您自己设置其他调整选项完全相同。
当他们宣布该功能时，我在博客上写了以下评论:

I’m sorry, but I don’t like this feature at all. I understand the goalof improving the out-of-the-box experience for naive users, but Idon’t think this solution will be successful at this goal.

Trying to pre-tune a MySQL installation with some formula is aone-size-fits-all solution, and these kinds of solutions areunreliable. We can recall examples of other products that have triedto do this, but eventually removed their auto-tuning features.

It’s not a good assumption that the buffer pool needs as much physicalRAM as you can afford. You already know this, because you need theinnodb_dedicated_server option. Rick mentioned the possibility thatthe dataset is already smaller than RAM. In this case, adding more RAMhas little or no benefit.

Many naive users mistakenly believe (after reading some blog) thatincreasing RAM allocation always increases performance. It’s difficultto explain to them why this is not true.

Likewise innodb log file. We assume that bigger is better, because ofbenchmarks showing that heavy write traffic benefits from bigger logfiles, because of delaying checkpoints. But what if you don’t haveheavy write traffic? What if you use MySQL for a blog or a CMS that is99% reads? The large log file is unnecessary. Sizing it for an assumedworkload or dataset size has a high chance of being the wrong choicefor tuning.

I understand the difficulty of asking users questions duringinstallation. I recently did a project automating MySQL provisioningwith apt. It was annoying having to figure out debconf to work aroundthe installation prompts that do exist (btw, please document MySQL’sdebconf variables!).

There’s also the problem that even if you do prompt the user forinformation, they don’t know the answers to the questions. This isespecially true of the naive users that you’re targeting with thisfeature.

If the installer asks “Do you use MySQL on a dedicated server?” dothey even know what this means? They might think “dedicated” is simplythe opposite of shared hosting.

If the installer asks “Do you want to use all available memory on thissystem?” you will be surprised at how many users think “memory” refersto disk space, not RAM.

In short: (1) Using formulas to tune MySQL is error-prone. (2) Askingusers to make choices without information is error-prone.

I have an alternative suggestion: Make it easier for users to becomeless naive about their choices.

I think users need a kind of friendly cheat-sheet or infographic ofhow to make tuning decisions. This could include a list of questionsabout their data size and workload, and then a list of performanceindicators to monitor and measure, like buffer pool page create rate,and log file write rate. Give tips on how to measure these things,what config options to change, and then how to measure again to verifythat the change had the desired effect.

A simple monitoring tool would also be useful. Nothing sosophisticated as PMM or VividCortex for long-term trending, butsomething more like pt-mext for quick, ephemeral measurements.

The only thing the installation process needs to do is tell the userthat tuning is a thing they need to do (many users don’t realizethis), and refer them to the cheat-sheet documentation.