☰

如何优化 MySQL 服务器以获得更好的性能？

tag-icon • tag-icon performance mysql sql-server optimize
如何优化 MySQL 服务器以获得更好的性能？

最近我不得不使用一个遗留系统。处理它花费了我很多时间，因为许多表没有正确规范化。由于维护问题，无法更改。我的高级工程师告诉我优化 MySQL 服务器以获得更好的性能和速度。

我将我的C:\Program Files\MySQL\MySQL Server 5.6\my.ini文件更改为以下内容：

key_buffer_size = 128M 30% of your memory (Max 4GB)
max_allowed_packet = 5M
table_open_cache = 256
sort_buffer_size = 10M
read_buffer_size = 20M
read_rnd_buffer_size = 10M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size= 30M
thread_concurrency = 8
bulk_insert_buffer_size = 256
innodb_buffer_pool_size = 5G  to 50% - 70% of your ram.
innodb_additional_mem_pool_size = 1G to to 10% of ram
innodb_log_buffer_size = 1G
innodb_log_file_size = 1G (Max 4GB) larger the file recovery slow.
innodb_file_per_table = 1

一开始运行良好。但是几个小时后，我无法启动 MySQL，因此我不得不重新安装 MySQL 服务器。

我做错了什么？我该如何改正？

答案1

为了更好地了解你的配置文件的摘录，我将其与默认值进行了比较（1，2）。该符号将包含参数，后跟您的值，括号中为默认值。

key_buffer_size = 128M (8M)
max_allowed_packet = 5M (4M)
table_open_cache = 256 (2000)
sort_buffer_size = 10M (2M)
read_buffer_size = 20M (1M)
read_rnd_buffer_size = 10M (2M)
myisam_sort_buffer_size = 64M (8M)
thread_cache_size = 8 (-1)
query_cache_size= 30M (0/1M)
thread_concurrency = 8 (10)
bulk_insert_buffer_size = 256 (8M)
innodb_buffer_pool_size = 5G (128M)
innodb_additional_mem_pool_size = 1G (8M)
innodb_log_buffer_size = 1G (8M)
innodb_log_file_size = 1G (5M)
innodb_file_per_table = 1 (Off/On)

我们可以看到，您显著增加了大多数缓存。但这有意义吗？您说您的数据库使用的是 InnoDB 引擎。根据上述文档key_buffer_size，仅适用于 MyISAM 数据库。我不确定临时空间何时会在该数据库上运行。read_buffer_sizebulk_insert_buffer_size

使用myisam_sort_buffer_size似乎是倒退的，特别是因为文档指出：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.因此在大多数正常用例中它很少被使用。

此外，您使用的以下设置似乎严重依赖于您的数据库使用模式，，，max_allowed_packet但您没有提到任何有关使用模式的信息。因此，请考虑查看上述文档，看看它们实际上做了什么，并将它们与数据库的使用模式进行比较。table_open_cachethread_cache_sizeinnodb_log_buffer_size

请注意，sort_buffer_size和read_rnd_buffer_size是每个会话保留的。一些 MyISAM 参数可能也是如此，因此您确实会为每个会话产生相当多的开销。同样，您需要评估实际的使用模式。

现在，对于您指定的 InnoDB 设置。innodb_additional_mem_pool_size已弃用。不清楚您为什么要修改innodb_log_file_size，innodb_file_per_table不确定您进行这种更改的原因是什么。后者可能只是的默认值On？通过将的剩余设置设置innodb_buffer_pool_size为如此高的值，您可能还想考虑使用innodb_buffer_pool_instances。

总的来说，你似乎只是尝试了一些东西，但没有奏效。根据你的评论，你甚至没有查看 MySQL 日志文件来找出导致服务不接受连接或根本不出现的原因。如果你真的想优化数据库性能，你就必须这样做。考虑在合理的表上使用额外的索引来提高查询性能，并查看数据库上实际执行的查询。此外，尝试了解数据库在连接数和查询大小方面的使用模式。

相关内容