MySQL myisam_sort_buffer_size 与 sort_buffer_size
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/7871027/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
myisam_sort_buffer_size vs sort_buffer_size
提问by D3 K
I am MySQL on server with 6GB RAM. I need to know what is the difference between myisam_sort_buffer_size and sort_buffer_size?
我在具有 6GB RAM 的服务器上使用 MySQL。我需要知道 myisam_sort_buffer_size 和 sort_buffer_size 有什么区别？
I have following size set to them:
我为它们设置了以下尺寸：
myisam_sort_buffer_size = 8M
myisam_sort_buffer_size = 8M
sort_buffer_size = 256M
sort_buffer_size = 256M
Please also mention if these values are fine or need adjustments?
还请说明这些值是否合适或需要调整？
Thanks
谢谢
回答by thomasrutter
sort_buffer_size:
sort_buffer_size:
MySQL documentation:
MySQL 文档：
Each session that needs to do a sort allocates a buffer of this size. sort_buffer_size is not specific to any storage engine and applies in a general manner for optimization.
每个需要进行排序的会话分配一个这样大小的缓冲区。sort_buffer_size 并非特定于任何存储引擎，而是以通用方式应用于优化。
Your sort_buffer_size value seems extremely high. The default is 2M. I'd recommend going no larger than that since there is a performance penalty for going higher. Some people recommend smaller valuessuch as 256kB. One thing to remember is this is per-client-session, it's not a global value. Large values will add up fast.
您的 sort_buffer_size 值似乎非常高。默认值为 2M。我建议不要超过那个，因为更高会导致性能下降。 有些人建议使用较小的值，例如 256kB。要记住的一件事是这是每个客户端会话，它不是全局值。大值会快速累加。
myisam_sort_buffer_size:
myisam_sort_buffer_size:
MySQL documentation:
MySQL 文档：
The size of the buffer that is allocated when sorting MyISAM indexes during a REPAIR TABLE or when creating indexes with CREATE INDEX or ALTER TABLE.
在 REPAIR TABLE 期间对 MyISAM 索引进行排序或使用 CREATE INDEX 或 ALTER TABLE 创建索引时分配的缓冲区大小。
Your myisam_sort_buffer_size seems fine. This won't be relevant unless you are rebuilding indexes using ALTER TABLE or REPAIR TABLE etc.
您的 myisam_sort_buffer_size 看起来不错。除非您使用 ALTER TABLE 或 REPAIR TABLE 等重建索引，否则这无关紧要。
回答by Mladen Adamovic
These arguments is per thread, so check out the number of max_connections.
这些参数是针对每个线程的，因此请查看 max_connections 的数量。
I.e. with 15GB of RAM
即具有 15GB 的 RAM
max_connections = 1500
sort_buffer_size = 32M
I'm getting mysqltuner warning:
我收到 mysqltuner 警告：
[--] Total buffers: 928.0M global + 32.7M per thread (1500 max threads)
[!!] Maximum possible memory usage: 48.8G (312% of installed RAM)
So I did lower it to the default value.
所以我确实将其降低到默认值。
回答by sachin
sort_buffer_size =256K is best.you try this and restart the mysql server and monitor for few hours you can easily notice the benefit
sort_buffer_size = 256K 是最好的。你试试这个，重启 mysql 服务器并监控几个小时，你可以很容易地注意到好处
