MySQL5.5.49大内存优化配置文件优化详解_MySQL

my-small.cnf
my-medium.cnf
my-large.cnf
my-huge.cnf
my-innodb-heavy-4G.cnf

二、详解 my-innodb-heavy-4G.cnf
三、配置文件优化
注：环境说明，CentO5.5 x86_64+MySQL-5.5.32 相关软件下载：http://yunpan.cn/QtaCuLHLRKzRq

一、配置文件说明

Mysql-5.5.49是Mysql5.5系列中最后一个版本，也是最后一个有配置文件的版本，为什么这么说呢，用过5.6的博友都知道，在mysql5.6中已经不提供配置文件选择，只有一个默认的配置文件，好了，我们今天说的是5.5.49这个版，就不和大家说5.6了，下面我们来具体说一下，mysql5.5.49中，提供可选的几个配置文件，
my-small.cnf
my-medium.cnf
my-large.cnf
my-huge.cnf
my-innodb-heavy-4G.cnf

下面我们就来分别的看一下^_^……
1.my-small.cnf

[root@mysql support-files]# vim my-small.cnf
# Example MySQL config file for small systems.
# This is for a system with little memory (<= 64M) where MySQL is only used 
# from time to time and it's important that the mysqld daemon 
# doesn't use much resources.

这是my-small.cnf配置文件中开头的简介，它说明了，这个配置文件是在内存小于等于64M时使用的，小型数据库系统，目的是不占更多的系统资源！
2.my-medium.cnf

[root@mysql support-files]# vim my-medium.cnf
# Example MySQL config file for medium systems.
# This is for a system with little memory (32M - 64M) where MySQL plays 
# an important part, or systems up to 128M where MySQL is used together with 
# other programs (such as a web server)

这个配置文件是中型数据系统使用，内存在128M左右！
3.my-large.cnf

[root@mysql support-files]# vim my-large.cnf
# Example MySQL config file for large systems. 
# This is for a large system with memory = 512M where the system runs mainly 
# MySQL.

这个配置文件是大型数据库系统使用，内存在512M左右！
4.my-huge.cnf

[root@mysql support-files]# vim my-huge.cnf
# Example MySQL config file for very large systems. 
# This is for a large system with memory of 1G-2G where the system runs mainly 
# MySQL.

这个配置文件是巨型数据库系统使用，内存在1G-2G左右！
5.my-innodb-heavy-4G.cnf

[root@mysql support-files]# vim my-innodb-heavy-4G.cnf
#BEGIN CONFIG INFO 
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries 
#TYPE: SYSTEM 
#END CONFIG INFO
# This is a MySQL example config file for systems with 4GB of memory 
# running mostly MySQL using InnoDB only tables and performing complex 
# queries with few connections.

这个配置文件主要作用是,支持4G内存，支持InnoDB引擎，支持事务（ACID）等特性所使用！
说明：ACID，指数据库事务正确执行的四个基本要素的缩写。包含：原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）！
6.总结
从上面的说明我们可以出，基本是通过内存大小来选择mysql的配置文件的，那有博友会说了，现在的服务器动不动就是32G内存或者64G内存，甚至更大的内存，你那个配置文件最大只支持4G内存是不是有点小了，确认会有这样的问题，从mysql5.6以后，为了更大的发挥mysql的性能，已经去除了配置文件选择，只有一个默认的配置文件，里面只有一些基本配置，所有设置管理员都可以根据自己实际的需求进行自行设置，好了说了这么多，我们就来说一说，在企业的用的最多的my-innodb-heavy-4G.cnf配置文件！
二、详解 my-innodb-heavy-4G.cnf
1.详细说明
注：下面是my-innodb-heavy-4G.cnf默认配置我没有做任何修改，下面我们就来详细的说一说！

[root@mysql support-files]# vim my-innodb-heavy-4G.cnf
#BEGIN CONFIG INFO 
#DESCR: 4GB RAM, InnoDB only, ACID, few connections, heavy queries 
#TYPE: SYSTEM 
#END CONFIG INFO
# 
# This is a MySQL example config file for systems with 4GB of memory 
# running mostly MySQL using InnoDB only tables and performing complex 
# queries with few connections. 
# 
# MySQL programs look for option files in a set of 
# locations which depend on the deployment platform. 
# You can copy this option file to one of those 
# locations. For information about these locations, see: 
# http://dev.mysql.com/doc/mysql/en/option-files.html
# 
# In this file, you can use all long options that a program supports. 
# If you want to know which options a program supports, run the program 
# with the "--help" option. 
# 
# More detailed information about the individual options can also be 
# found in the manual. 
#
# 
# The following options will be read by MySQL client applications. 
# Note that only client applications shipped by MySQL are guaranteed 
# to read this section. If you want your own MySQL client program to 
# honor these values, you need to specify it as an option during the 
# MySQL client library initialization. 
#
# 以下选项会被MySQL客户端应用读取, 注意只有MySQL附带的客户端应用程序保证可以读取这段内容,如果你想你自己的MySQL应用程序获取这些值,需要在MySQL客户端库初始化的时候指定这些选项
[client] 
#password  = [your_password] #mysql客户端连接mysql时的密码 
port    = 3306 #mysql客户端连接时的默认端口 
socket    = /tmp/mysql.sock #与mysql服务器本地通信所使用的socket文件路径
# *** Application-specific options follow here ***
# 
# The MySQL server 
# 
[mysqld]
# generic configuration options #一般配置选项 
port    = 3306 #mysql服务器监听的默认端口 
socket    = /tmp/mysql.sock #socket本地通信文件路径
# back_log is the number of connections the operating system can keep in 
# the listen queue, before the MySQL connection manager thread has 
# processed them. If you have a very high connection rate and experience 
# "connection refused" errors, you might need to increase this value. 
# Check your OS documentation for the maximum value of this parameter. 
# Attempting to set back_log higher than your operating system limit 
# will have no effect.
# back_log 是操作系统在监听队列中所能保持的连接数, 
# 队列保存了在MySQL连接管理器线程处理之前的连接. 
# 如果你有非常高的连接率并且出现“connection refused”报错, 
# 你就应该增加此处的值. 
# 检查你的操作系统能打开文件数来获取这个变量的最大值. 
# 如果将back_log设定到比你操作系统限制更高的值,将会没有效果
back_log = 50
# Don't listen on a TCP/IP port at all. This can be a security 
# enhancement, if all processes that need to connect to mysqld run 
# on the same host. All interaction with mysqld must be made via Unix 
# sockets or named pipes. 
# Note that using this option without enabling named pipes on Windows 
# (via the "enable-named-pipe" option) will render mysqld useless!
# 不在TCP/IP端口上进行监听. 
# 如果所有的进程都是在同一台服务器连接到本地的mysqld, 
# 这样设置将是增强安全的方法 
# 所有mysqld的连接都是通过Unix sockets 或者命名管道进行的. 
# 注意在windows下如果没有打开命名管道选项而只是用此项 
# (通过 “enable-named-pipe” 选项) 将会导致mysql服务没有任何作用!
#skip-networking #默认是没有开启的
# The maximum amount of concurrent sessions the MySQL server will 
# allow. One of these connections will be reserved for a user with 
# SUPER privileges to allow the administrator to login even if the 
# connection limit has been reached.
# MySQL 服务器所允许的同时会话数的上限 
# 其中一个连接将被SUPER权限保留作为管理员登录. 
# 即便已经达到了连接数的上限. 
max_connections = 100
# Maximum amount of errors allowed per host. If this limit is reached, 
# the host will be blocked from connecting to the MySQL server until 
# "FLUSH HOSTS" has been run or the server was restarted. Invalid 
# passwords and other errors during the connect phase result in 
# increasing this value. See the "Aborted_connects" status variable for 
# global counter.
# 每个客户端连接最大的错误允许数量,如果达到了此限制. 
# 这个客户端将会被MySQL服务阻止直到执行了”FLUSH HOSTS” 或者服务重启 
# 非法的密码以及其他在链接时的错误会增加此值. 
# 查看 “Aborted_connects” 状态来获取全局计数器. 
max_connect_errors = 10
# The number of open tables for all threads. Increasing this value 
# increases the number of file descriptors that mysqld requires. 
# Therefore you have to make sure to set the amount of open files 
# allowed to at least 4096 in the variable "open-files-limit" in 
# section [mysqld_safe]
# 所有线程所打开表的数量. 
# 增加此值就增加了mysqld所需要的文件描述符的数量 
# 这样你需要确认在[mysqld_safe]中 “open-files-limit” 变量设置打开文件数量允许至少2048
table_open_cache = 2048
# Enable external file level locking. Enabled file locking will have a 
# negative impact on performance, so only use it in case you have 
# multiple database instances running on the same files (note some 
# restrictions still apply!) or if you use other software relying on 
# locking MyISAM tables on file level.
# 允许外部文件级别的锁. 打开文件锁会对性能造成负面影响 
# 所以只有在你在同样的文件上运行多个数据库实例时才使用此选项(注意仍会有其他约束!) 
# 或者你在文件层面上使用了其他一些软件依赖来锁定MyISAM表
#external-locking #默认是没有开启的
# The maximum size of a query packet the server can handle as well as 
# maximum query size server can process (Important when working with 
# large BLOBs). enlarged dynamically, for each connection.
# 服务所能处理的请求包的最大大小以及服务所能处理的最大的请求大小(当与大的BLOB字段一起工作时相当必要) 
# 每个连接独立的大小.大小动态增加 
max_allowed_packet = 16M
# The size of the cache to hold the SQL statements for the binary log 
# during a transaction. If you often use big, multi-statement 
# transactions you can increase this value to get more performance. All 
# statements from transactions are buffered in the binary log cache and 
# are being written to the binary log at once after the COMMIT. If the 
# transaction is larger than this value, temporary file on disk is used 
# instead. This buffer is allocated per connection on first update 
# statement in transaction
# 在一个事务中binlog为了记录SQL状态所持有的cache大小 
# 如果你经常使用大的,多声明的事务,你可以增加此值来获取更大的性能. 
# 所有从事务来的状态都将被缓冲在binlog缓冲中然后在提交后一次性写入到binlog中 
# 如果事务比此值大, 会使用磁盘上的临时文件来替代. 
# 此缓冲在每个连接的事务第一次更新状态时被创建 
binlog_cache_size = 1M
# Maximum allowed size for a single HEAP (in memory) table. This option 
# is a protection against the accidential creation of a very large HEAP 
# table which could otherwise use up all memory resources.
# 独立的内存表所允许的最大容量. 
# 此选项为了防止意外创建一个超大的内存表导致永尽所有的内存资源. 
max_heap_table_size = 64M
# Size of the buffer used for doing full table scans. 
# Allocated per thread, if a full scan is needed.
#MySql读入缓冲区大小。对表进行顺序扫描的请求将分配一个读入缓冲区，MySql会为它分#配一段内存缓冲区。read_buffer_size变量控制这一缓冲区的大小。如果对表的顺序扫描请求非常频繁，#并且你认为频繁扫描进行得太慢，可以通过增加该变量值以及内存缓冲区大小提高其性能。 
read_buffer_size = 2M
# When reading rows in sorted order after a sort, the rows are read 
# through this buffer to avoid disk seeks. You can improve ORDER BY 
# performance a lot, if set this to a high value. 
# Allocated per thread, when needed.
#是MySql的随机读缓冲区大小。当按任意顺序读取行时(例如，按照排序顺序)，将分配一个随机读缓存区。进行排序查询时，MySql会首先扫描一遍该缓冲，以避免磁盘搜索，提高查询速度，如果需#要排序大量数据，可适当调高该值。但MySql会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存开销过大。 
read_rnd_buffer_size = 16M
# Sort buffer is used to perform sorts for some ORDER BY and GROUP BY 
# queries. If sorted data does not fit into the sort buffer, a disk 
# based merge sort is used instead - See the "Sort_merge_passes" 
# status variable. Allocated per thread if sort is needed.
# 排序缓冲被用来处理类似ORDER BY以及GROUP BY队列所引起的排序 
# 如果排序后的数据无法放入排序缓冲, 
# 一个用来替代的基于磁盘的合并分类会被使用 
# 查看 “Sort_merge_passes” 状态变量. 
# 在排序发生时由每个线程分配 
sort_buffer_size = 8M
# This buffer is used for the optimization of full JOINs (JOINs without 
# indexes). Such JOINs are very bad for performance in most cases 
# anyway, but setting this variable to a large value reduces the 
# performance impact. See the "Select_full_join" status variable for a 
# count of full JOINs. Allocated per thread if full join is found
# 此缓冲被使用来优化全联合(full JOINs 不带索引的联合). 
# 类似的联合在极大多数情况下有非常糟糕的性能表现, 
# 但是将此值设大能够减轻性能影响. 
# 通过 “Select_full_join” 状态变量查看全联合的数量 
# 当全联合发生时,在每个线程中分配 
join_buffer_size = 8M
# How many threads we should keep in a cache for reuse. When a client 
# disconnects, the client's threads are put in the cache if there aren't 
# more than thread_cache_size threads from before. This greatly reduces 
# the amount of thread creations needed if you have a lot of new 
# connections. (Normally this doesn't give a notable performance 
# improvement if you have a good thread implementation.)
# 我们在cache中保留多少线程用于重用 
# 当一个客户端断开连接后,如果cache中的线程还少于thread_cache_size, 
# 则客户端线程被放入cache中. 
# 这可以在你需要大量新连接的时候极大的减少线程创建的开销 
# (一般来说如果你有好的线程模型的话,这不会有明显的性能提升.) 
thread_cache_size = 8
# This permits the application to give the threads system a hint for the 
# desired number of threads that should be run at the same time. This 
# value only makes sense on systems that support the thread_concurrency() 
# function call (Sun Solaris, for example). 
# You should try [number of CPUs]*(2..4) for thread_concurrency
# 此允许应用程序给予线程系统一个提示在同一时间给予渴望被运行的线程的数量. 
# 此值只对于支持 thread_concurrency() 函数的系统有意义( 例如Sun Solaris). 
# 你可可以尝试使用 [CPU数量]*(2..4) 来作为thread_concurrency的值 
thread_concurrency = 8
# Query cache is used to cache SELECT results and later return them 
# without actual executing the same query once again. Having the query 
# cache enabled may result in significant speed improvements, if your 
# have a lot of identical queries and rarely changing tables. See the 
# "Qcache_lowmem_prunes" status variable to check if the current value 
# is high enough for your load. 
# Note: In case your tables change very often or if your queries are 
# textually different every time, the query cache may result in a 
# slowdown instead of a performance improvement.
# 查询缓冲常被用来缓冲 SELECT 的结果并且在下一次同样查询的时候不再执行直接返回结果. 
# 打开查询缓冲可以极大的提高服务器速度, 如果你有大量的相同的查询并且很少修改表. 
# 查看 “Qcache_lowmem_prunes” 状态变量来检查是否当前值对于你的负载来说是否足够高. 
# 注意: 在你表经常变化的情况下或者如果你的查询原文每次都不同, 
# 查询缓冲也许引起性能下降而不是性能提升. 
query_cache_size = 64M
# Only cache result sets that are smaller than this limit. This is to 
# protect the query cache of a very large result set overwriting all 
# other query results.
# 只有小于此设定值的结果才会被缓冲 
# 此设置用来保护查询缓冲,防止一个极大的结果集将其他所有的查询结果都覆盖.
query_cache_limit = 2M
# Minimum word length to be indexed by the full text search index. 
# You might wish to decrease it if you need to search for shorter words. 
# Note that you need to rebuild your FULLTEXT index, after you have 
# modified this value.
# 被全文检索索引的最小的字长. 
# 你也许希望减少它,如果你需要搜索更短字的时候. 
# 注意在你修改此值之后, 
# 你需要重建你的 FULLTEXT 索引 
ft_min_word_len = 4
# If your system supports the memlock() function call, you might want to 
# enable this option while running MySQL to keep it locked in memory and 
# to avoid potential swapping out in case of high memory pressure. Good 
# for performance.
# 如果你的系统支持 memlock() 函数,你也许希望打开此选项用以让运行中的mysql在在内存高度紧张的时候,数据在内存中保持锁定并且防止可能被swapping out 
# 此选项对于性能有益 
#memlock
# Table type which is used by default when creating new tables, if not 
# specified differently during the CREATE TABLE statement.
# 当创建新表时作为默认使用的表类型, 
# 如果在创建表示没有特别执行表类型,将会使用此值 
default-storage-engine = MYISAM
# Thread stack size to use. This amount of memory is always reserved at 
# connection time. MySQL itself usually needs no more than 64K of 
# memory, while if you use your own stack hungry UDF functions or your 
# OS requires more stack for some operations, you might need to set this 
# to a higher value.
# 线程使用的堆大小. 此容量的内存在每次连接时被预留. 
# MySQL 本身常不会需要超过64K的内存 
# 如果你使用你自己的需要大量堆的UDF函数 
# 或者你的操作系统对于某些操作需要更多的堆, 
# 你也许需要将其设置的更高一点. 
thread_stack = 192K
# Set the default transaction isolation level. Levels available are:
# 设定默认的事务隔离级别.可用的级别如下: 
# READ-UNCOMMITTED, READ-COMMITTED, REPEATABLE-READ, SERIALIZABLE 
transaction_isolation = REPEATABLE-READ
# Maximum size for internal (in-memory) temporary tables. If a table 
# grows larger than this value, it is automatically converted to disk 
# based table This limitation is for a single table. There can be many 
# of them.
# 内部(内存中)临时表的最大大小 
# 如果一个表增长到比此值更大,将会自动转换为基于磁盘的表. 
# 此限制是针对单个表的,而不是总和. 
tmp_table_size = 64M
# Enable binary logging. This is required for acting as a MASTER in a 
# replication configuration. You also need the binary log if you need 
# the ability to do point in time recovery from your latest backup.
# 打开二进制日志功能. 
# 在复制(replication)配置中,作为MASTER主服务器必须打开此项 
# 如果你需要从你最后的备份中做基于时间点的恢复,你也同样需要二进制日志. 
log-bin=mysql-bin
# binary logging format - mixed recommended
#设定记录二进制日志的格式，有三种格式，基于语句 statement、 基于行 row、 混合方式 mixed 
binlog_format=mixed
# If you're using replication with chained slaves (A->B->C), you need to 
# enable this option on server B. It enables logging of updates done by 
# the slave thread into the slave's binary log.
# 如果你在使用链式从服务器结构的复制模式 (A->B->C), 
# 你需要在服务器B上打开此项. 
# 此选项打开在从线程上重做过的更新的日志, 
# 并将其写入从服务器的二进制日志. 
#log_slave_updates
# Enable the full query log. Every query (even ones with incorrect 
# syntax) that the server receives will be logged. This is useful for 
# debugging, it is usually disabled in production use.
# 打开查询日志. 所有的由服务器接收到的查询 (甚至对于一个错误语法的查询) 
# 都会被记录下来. 这对于调试非常有用, 在生产环境中常常关闭此项. 
#log #默认是没有开启的，会影响服务器性能
# Print warnings to the error log file. If you have any problem with 
# MySQL you should enable logging of warnings and examine the error log 
# for possible explanations.
# 将警告打印

[client] 
port    = 3306 
socket    = /tmp/mysql.sock
default-character-set = utf8 #设置客户端的字符编码
[mysqld]
# generic configuration options 
port    = 3306 
socket    = /tmp/mysql.sock
#*** char set ***
character-set-server = utf8 #设置服务器端的字符编码
            
#*** network ***
back_log = 512
#skip-networking #默认没有开启
max_connections = 3000
max_connect_errors = 30
table_open_cache = 4096
#external-locking #默认没有开启
max_allowed_packet = 32M
max_heap_table_size = 128M
            
# *** global cache ***
read_buffer_size = 8M
read_rnd_buffer_size = 64M
sort_buffer_size = 16M
join_buffer_size = 16M
            
# *** thread ***
thread_cache_size = 16
thread_concurrency = 8
thread_stack = 512K
            
# *** query cache ***
query_cache_size = 128M
query_cache_limit = 4M
            
# *** index ***
ft_min_word_len = 8
            
#memlock #默认没有开启
default-storage-engine = INNODB
transaction_isolation = REPEATABLE-READ
            
# *** tmp table ***
tmp_table_size = 64M
            
# *** bin log ***
log-bin=mysql-bin
binlog_cache_size = 4M
binlog_format=mixed
#log_slave_updates #默认没有开启
#log #默认没有开启，此处是查询日志，开启会影响服务器性能
log_warnings #开启警告日志
            
# *** slow query log ***
slow_query_log
long_query_time = 10
# *** Replication related settings
server-id = 1
#server-id = 2 
#master-host =  
#master-user =  
#master-password =  
#master-port = 
#read_only
#*** MyISAM Specific options
key_buffer_size = 128M
bulk_insert_buffer_size = 256M
myisam_sort_buffer_size = 256M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover
            
# *** INNODB Specific options ***
#skip-innodb #默认没有开启
innodb_additional_mem_pool_size = 64M
innodb_buffer_pool_size = 6G #注意在32位系统上你每个进程可能被限制在 2-3.5G 用户层面内存限制, 所以不要设置的太高.
innodb_data_file_path = ibdata1:10M:autoextend
#innodb_data_home_dir = 
innodb_write_io_threads = 8 
innodb_read_io_threads = 8
#innodb_force_recovery=1
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 2
#说明：innodb_flush_log_at_trx_commit = 2 如果是游戏服务器，建议此值设置为2；如果是对数据安全要求极高的应用，建议设置为1；设置为0性能最高，但如果发生故障，数据可能会有丢失的危险！默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统挂了时才可能丢数据。
#innodb_fast_shutdown
innodb_log_buffer_size = 16M
innodb_log_file_size = 512M
innodb_log_files_in_group = 3
#innodb_log_group_home_dir
innodb_max_dirty_pages_pct = 90
#innodb_flush_method=O_DSYNC
innodb_lock_wait_timeout = 120
[mysqldump] 
quick
max_allowed_packet = 32M
[mysql] 
no-auto-rehash
[myisamchk] 
key_buffer_size = 2048M 
sort_buffer_size = 2048M 
read_buffer = 32M 
write_buffer = 32M
[mysqlhotcopy] 
interactive-timeout
[mysqld_safe] 
open-files-limit = 10240

mysql> show status; #显示状态信息 
mysql> show variables; #显示系统变量 
mysql> show engines; #查看所有引擎
mysql> show engine innodb status; #显示InnoDB存储引擎的状态