MySQL my.cnf 性能调优建议
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/10905226/
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
MySQL my.cnf performance tuning recommendations
提问by Skittles
I am kind of hoping that someone might be able to offer some assistance with optimizing a my.cnf file for an extremely high volume mysql database server.
我有点希望有人能够提供一些帮助来优化 my.cnf 文件以用于极高容量的 mysql 数据库服务器。
Our architecture is as follows:
Memory : 96GB
CPUs : 12
OS & Mysql : 64-bit
Disk Space : 1.2 TB
DB Engine : MyISAM
Our web application is used by roughly 300 client simultaneously. We need our my.cnf tuned to give the best possible performance for this infrastructure.
我们的 Web 应用程序同时被大约 300 个客户端使用。我们需要对 my.cnf 进行调整，以便为该基础架构提供最佳性能。
I am fully aware that indexes and optimized queries are a major factor in this, but we would like to start with a system that is configured properly and then follow that up with systematically re-engineering our queries accordingly.
我完全知道索引和优化查询是其中的一个主要因素，但我们希望从一个配置正确的系统开始，然后相应地系统地重新设计我们的查询。
Here is our current my.cnf file content:
[mysqld]
datadir=/home/mysql
socket=/home/mysql/mysql.sock
user=mysql
log-bin=mysql-bin
server-id=1
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=1
log-slow-queries = /var/log/mysqld_slow_queries.log
long_query_time = 10
max_connections = 500
key_buffer_size = 32768M
#max_allowed_packet = 2M
#table_open_cache = 128
#sort_buffer_size = 1024K
#net_buffer_length = 64K
#read_buffer_size = 1024K
#read_rnd_buffer_size = 1024K
#myisam_sort_buffer_size = 8M
query_cache_size = 128M
query_cache_limit = 128M
interactive_timeout = 300
wait_timeout = 300
# Added values after load testing
thread_cache_size = 8
#tmp_table_size = 256M
#max_heap_table_size = 256M
#table_cache = 512
#join_buffer_size = 512
log-error=/var/log/mysqld.log
innodb_buffer_pool_size=128M
#innodb_file_per_table
#innodb_log_file_size=250M
##innodb_buffer_pool_size=64M
#innodb_buffer_pool_size=1024M
#innodb_log_buffer_size=4M
##log-bin=mysql-bin
[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#[myisamchk]
#key_buffer = 64M
#sort_buffer = 64M
#read_buffer = 16M
#write_buffer = 16M
Any suggestions? Thanks folks.
有什么建议？谢谢各位。
Edit by RolandoMySQLDBA
由 RolandoMySQLDBA 编辑
Since all you data is MyISAM, please run this query and show the output
由于您的所有数据都是 MyISAM，请运行此查询并显示输出
SELECT CONCAT(ROUND(KBS/POWER(1024,
IF(PowerOf1024<0,0,IF(PowerOf1024>3,0,PowerOf1024)))+0.4999),
SUBSTR(' KMG',IF(PowerOf1024<0,0,
IF(PowerOf1024>3,0,PowerOf1024))+1,1))
recommended_key_buffer_size FROM
(SELECT LEAST(POWER(2,32),KBS1) KBS
FROM (SELECT SUM(index_length) KBS1
FROM information_schema.tables
WHERE engine='MyISAM' AND
table_schema NOT IN ('information_schema','mysql')) AA ) A,
(SELECT 3 PowerOf1024) B;
@ Rolando - Thanks...the results of that query was 4G.
@ Rolando - 谢谢...该查询的结果是 4G。
回答by KCD
Try starting with the Percona wizard and comparing their recommendations against your current settings one by one. Don't worry there aren't as many applicable settings as you might think.
尝试从 Percona 向导开始，将他们的建议与您当前的设置一一比较。别担心，适用的设置并不像您想象的那么多。
https://tools.percona.com/wizard
https://tools.percona.com/wizard
Update circa 2020: Sorry, this tool reached it's end of life: https://www.percona.com/blog/2019/04/22/end-of-life-query-analyzer-and-mysql-configuration-generator/
大约 2020 年更新：抱歉，此工具已寿终正寝：https: //www.percona.com/blog/2019/04/22/end-of-life-query-analyzer-and-mysql-configuration-generator/
Everyone points to key_buffer_sizefirst which you have addressed. With 96GB memory I'd be wary of any tiny default value (likely to be only 96M!).
每个人都指向key_buffer_size第一个你已经解决的问题。使用 96GB 内存时，我会警惕任何微小的默认值（可能只有 96M！）。
回答by Sooraj
I tried this tool and it gave me good results.
我试过这个工具，它给了我很好的结果。
