安装完 MySQL 后必须调整的 10 项配置 已翻译 100%

oschina 投递于 2014/01/29 08:26 (共 10 段, 翻译完成于 02-01)
阅读 17975
收藏 901
30
加载中

When we are hired for a MySQL performance audit, we are expected to review the MySQL configuration and to suggest improvements. Many people are surprised because in most cases, we only suggest to change a few settings even though hundreds of options are available. The goal of this post is to give you a list of some of the most critical settings.

We already made such suggestions in the past here on this blog a few years ago, but things have changed a lot in the MySQL world since then!

已有 1 人翻译此段
我来翻译

Before we start…

Even experienced people can make mistakes that can cause a lot of trouble. So before blindly applying the recommendations of this post, please keep in mind the following items:

  • Change one setting at a time! This is the only way to estimate if a change is beneficial.

  • Most settings can be changed at runtime with SET GLOBAL. It is very handy and it allows you to quickly revert the change if it creates any problem. But in the end, you want the setting to be adjusted permanently in the configuration file.

  • A change in the configuration is not visible even after a MySQL restart? Did you use the correct configuration file? Did you put the setting in the right section? (all settings in this post belong to the [mysqld] section)

  • The server refuses to start after a change: did you use the correct unit? For instance, innodb_buffer_pool_size should be set in MB while max_connection is dimensionless.

  • Do not allow duplicate settings in the configuration file. If you want to keep track of the changes, use version control.

  • Don’t do naive math, like “my new server has 2x RAM, I’ll just make all the values 2x the previous ones”.

已有 1 人翻译此段
我来翻译

Basic settings

Here are 3 settings that you should always look at. If you do not, you are very likely to run into problems very quickly.

innodb_buffer_pool_size: this is the #1 setting to look at for any installation using InnoDB. The buffer pool is where data and indexes are cached: having it as large as possible will ensure you use memory and not disks for most read operations. Typical values are 5-6GB (8GB RAM), 20-25GB (32GB RAM), 100-120GB (128GB RAM).

innodb_log_file_size: this is the size of the redo logs. The redo logs are used to make sure writes are fast and durable and also during crash recovery. Up to MySQL 5.1, it was hard to adjust, as you wanted both large redo logs for good performance and small redo logs for fast crash recovery. Fortunately crash recovery performance has improved a lot since MySQL 5.5 so you can now have good write performance and fast crash recovery. Until MySQL 5.5 the total redo log size was limited to 4GB (the default is to have 2 log files). This has been lifted in MySQL 5.6.

已有 1 人翻译此段
我来翻译

Starting with innodb_log_file_size = 512M (giving 1GB of redo logs) should give you plenty of room for writes. If you know your application is write-intensive and you are using MySQL 5.6, you can start with innodb_log_file_size = 4G.

max_connections: if you are often facing the ‘Too many connections’ error, max_connections is too low. It is very frequent that because the application does not close connections to the database correctly, you need much more than the default 151 connections. The main drawback of high values for max_connections (like 1000 or more) is that the server will become unresponsive if for any reason it has to run 1000 or more active transactions. Using a connection pool at the application level or a thread pool at the MySQL level can help here.

已有 1 人翻译此段
我来翻译

InnoDB settings

InnoDB has been the default storage engine since MySQL 5.5 and it is much more frequently used than any other storage engine. That’s why it should be configured carefully.

innodb_file_per_table: this setting will tell InnoDB if it should store data and indexes in the shared tablespace (innodb_file_per_table = OFF) or in a separate .ibd file for each table (innodb_file_per_table= ON). Having a file per table allows you to reclaim space when dropping, truncating or rebuilding a table. It is also needed for some advanced features such as compression. However it does not provide any performance benefit. The main scenario when you do NOT want file per table is when you have a very high number of tables (say 10k+).

With MySQL 5.6, the default value is ON so you have nothing to do in most cases. For previous versions, you should set it to ON prior to loading data as it has an effect on newly created tables only.

已有 1 人翻译此段
我来翻译

innodb_flush_log_at_trx_commit: the default setting of 1 means that InnoDB is fully ACID compliant. It is the best value when your primary concern is data safety, for instance on a master. However it can have a significant overhead on systems with slow disks because of the extra fsyncs that are needed to flush each change to the redo logs. Setting it to 2 is a bit less reliable because committed transactions will be flushed to the redo logs only once a second, but that can be acceptable on some situations for a master and that is definitely a good value for a replica. 0 is even faster but you are more likely to lose some data in case of a crash: it is only a good value for a replica.

已有 1 人翻译此段
我来翻译

innodb_flush_method: this setting controls how data and logs are flushed to disk. Popular values are O_DIRECT when you have a hardware RAID controller with a battery-protected write-back cache and fdatasync (default value) for most other scenarios. sysbench is a good tool to help you choose between the 2 values.

innodb_log_buffer_size: this is the size of the buffer for transactions that have not been committed yet. The default value (1MB) is usually fine but as soon as you have transactions with large blob/text fields, the buffer can fill up very quickly and trigger extra I/O load. Look at the Innodb_log_waits status variable and if it is not 0, increase innodb_log_buffer_size.

已有 1 人翻译此段
我来翻译

Other settings

query_cache_size: the query cache is a well known bottleneck that can be seen even when concurrency is moderate. The best option is to disable it from day 1 by setting query_cache_size = 0 (now the default on MySQL 5.6) and to use other ways to speed up read queries: good indexing, adding replicas to spread the read load or using an external cache (memcache or redis for instance). If you have already built your application with the query cache enabled and if you have never noticed any problem, the query cache may be beneficial for you. So you should be cautious if you decide to disable it.

已有 1 人翻译此段
我来翻译

log_bin: enabling binary logging is mandatory if you want the server to act as a replication master. If so, don’t forget to also set server_id to a unique value. It is also useful for a single server when you want to be able to do point-in-time recovery: restore your latest backup and apply the binary logs. Once created, binary log files are kept forever. So if you do not want to run out of disk space, you should either purge old files with PURGE BINARY LOGS or set expire_logs_days to specify after how many days the logs will be automatically purged.

Binary logging however is not free, so if you do not need for instance on a replica that is not a master, it is recommended to keep it disabled.

已有 1 人翻译此段
我来翻译

skip_name_resolve: when a client connects, the server will perform hostname resolution, and when DNS is slow, establishing the connection will become slow as well. It is therefore recommended to start the server with skip-name-resolve to disable all DNS lookups. The only limitation is that the GRANT statements must then use IP addresses only, so be careful when adding this setting to an existing system.

Conclusion

There are of course other settings that can make a difference depending on your workload or your hardware: low memory and fast disks, high concurrency, write-intensive workloads for instance are cases when you will need specific tuning. However the goal here is to allow you to quickly get a sane MySQL configuration without spending too much time on changing non-essential MySQL settings or on reading documentation to understand which settings do matter to you.

已有 1 人翻译此段
我来翻译
本文中的所有译文仅用于学习和交流目的,转载请务必注明文章译者、出处、和本文链接。
我们的翻译工作遵照 CC 协议,如果我们的工作有侵犯到您的权益,请及时联系我们。
加载中

评论(42)

赵云30
赵云30
"典型的值是5-6GB(8GB内存),20-25GB(32GB内存),100-120GB(128GB内存)。"

天,没用过这么多内存。
panzhc
panzhc
mark
xi4oh4o
xi4oh4o
赞一个
f
freetan
mark
doowan
doowan
mark
xueinsz
xueinsz
mark
理工小强
理工小强

引用来自“eechen”的评论

引用来自“理工小强”的评论

官方应该默认设置下

MySQL5.5系列的MySQL提供有下面一些配置文件样本(配置项都带有注释):
可以到这里Percona下载(20多M那个包):
http://www.percona.com/downloads/Percona-Server-5.5/LATEST/deb/precise/i686/
/usr/share/mysql/my-innodb-heavy-4G.cnf 适用于4G内存的系统,系统主要运行MySQL服务,使用InnoDB引擎(支持ACID).
/usr/share/mysql/my-huge.cnf 适用于1G-2G内存的系统,系统主要运行MySQL服务
/usr/share/mysql/my-large.cnf 适用于512M内存的系统,系统主要运行MySQL服务
/usr/share/mysql/my-medium.cnf 适用于128M内存的系统
/usr/share/mysql/my-small.cnf 适用于64M内存的系统
对于大内存服务器,4G的示例配置显然不能充分发挥MySQL的性能,所以从5.6开始,已经去除了配置文件样本.MySQL只提供了一个默认的配置文件,里面只有一些基本配置,管理员可以根据自己实际的需求自行设置.

my-innodb-heavy-4G.cnf中英对照注释以及MySQL优化可以看看这篇文章:
http://freeloda.blog.51cto.com/2033581/1253709

恩 也是 重口难调嘛
少荃
少荃
mark
老胡来了
老胡来了
mark
shuaia
shuaia

引用来自“秋天的菠菜”的评论

mark

mark,哈哈
返回顶部
顶部