最佳 MySQL 配置 (my.cnf)
以下是我的默认生产 MySQL 配置文件 (my.cnf)，用于使用 InnoDB 作为默认存储引擎的纯 UTF-8 设置。
[server]
bind-address=127.0.0.1
innodb_file_per_table
default-character-set=utf8
default-storage-engine=innodb
该设置执行以下操作：
- 绑定到 localhost:3306（环回）而不是默认的 *:3306（所有接口）。 这样做是为了增加安全性。
- 为每个表设置一个表空间。 这样做是为了增加可维护性。
- 将默认字符集设置为 UTF-8。 默认情况下完成是为了轻松国际化。
- 将默认存储引擎设置为 InnoDB。 完成后默认允许行级锁定。
假设您可以通过添加最多三 (3) 个配置参数来进一步改进设置。 您会添加哪一项，为什么？
在这种情况下，改进意味着性能改进、可靠性改进或易于使用/易于维护性的提高。 您可以假设运行 MySQL 实例的计算机将具有 1000 MB RAM。
The following is my default production MySQL configuration file (my.cnf) for a pure UTF-8 setup with InnoDB as the default storage engine.
[server]
bind-address=127.0.0.1
innodb_file_per_table
default-character-set=utf8
default-storage-engine=innodb
The setup does the following:
- Binds to localhost:3306 (loopback) instead of the default *:3306 (all interfaces). Done to increase security.
- Sets up one table space per table. Done to increase maintainability.
- Sets the default character set to UTF-8. Done to allow for easy internationalization by default.
- Sets the default storage engine to InnoDB. Done to allow for row-level-locking by default.
Assume that you could further improve the setup by adding a maximum of three (3) configuration parameters. Which would you add and why?
An improvement would in this context mean either a performance improvement, a reliability improvement or ease-of-use/ease-of-maintainability increase. You can assume that the machine running the MySQL instance will have 1000 MB of RAM.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
缓存更多数据：
如果写入大量数据：
，避免过多的日志切换。
无论如何，我不会添加第三个，所有其他都取决于。
To cache more data:
If you write lots of data:
, to avoid too much log switching.
There is no third I'd add in any case, all other depend.
为 InnoDB 分配比默认 8M 更多的内存（使用 innodb_buffer_pool_size）无疑是一种增强。 关于该值，在您的专用数据库服务器上，您可以将其设置为 RAM 的 80%，并且该值设置得越高，与硬盘的交互就越少。 顺便说一句，我想提一下，您可以通过调整 innodb_flush_log_at_trx_commit 的值来提高性能，但是会牺牲 ACID 合规性...根据 MySQL手册：
因此，您可能会由于崩溃或任何故障而丢失一些未正确写入数据库的数据。 再次根据MySQL手册：
所以，我建议：
最后，如果你有很高的连接率（即，如果你需要配置 MySQL 以支持访问数据库的 Web 应用程序），那么你应该考虑将最大连接数增加到 500 之类的值。或多或少是一些琐碎且众所周知的事情，因此我想强调
back_log对于确保连接性的重要性。我希望这些信息能够帮助您优化数据库服务器。
Allocating more memory than the default of 8M to InnoDB (using innodb_buffer_pool_size) is surely an enhancement. Regarding the value, on a dedicated database server as yours you can set it up to the 80% of your RAM and the higher you set this value, the fewer the interactions with the hard disk will be. Just to give my two cents, I'd like to mention that you can have some performance boost tweaking the value of
innodb_flush_log_at_trx_commit, however sacrificing ACID compliance... According to the MySQL manual:So you might loose some data that were not written properly in the database due to a crash or any malfunction. Again according to the MySQL manual:
So, I would suggest:
Finally if you have a high connection rate (i.e. if you need to configure MySQL to support a web application that accesses the database) then you should consider increasing the maximum number of connections to something like 500. But since this is something more or less trivial and well known, so I'd like to emphasize on the importance of
back_logto ensure connectivity.I hope these information will help you optimize your database server.
增加 innodb 缓冲池大小，尽可能大：
您还需要一些用于临时表的关键缓冲区空间：
其他取决于您对数据库所做的操作，但 table_cache 或 query_cache_size 将是其他几个潜力。
Increase the innodb buffer pool size, as big as you can practically make it:
You'll also want some key buffer space for temp tables:
Others would depend on what you are doing with the database, but table_cache or query_cache_size would be a couple other potentials.