MySQL参数配置优化

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL参数配置优化

余二五 2017-11-16 23:10:00 浏览566
展开阅读全文

                                            MySQL参数配置优化

max_connections
Variable Scope:      Global
Dynamic Variable:  Yes
Default:                   151 (mysql5.5+)
Meaning:                 允许客户端同时连接的最大数
默认值以前是100,MySQL5.5+后151,但是默认值对大部分应用来说这都不够。通过观察Max_used_connections
状态变量随着时间的变化。可以告诉你服务器连接是不是在某个时间点有个尖峰。如果这个值达到了max_connections,说明客户端至少被拒绝了一次。
建议值: 500+ (设置为你认为正常情况下有300或者更多连接,则可以设置为500或更多)

thread_cache_size
Variable Scope:       Global
Dynamic Variable:   Yes
Default:                    0(mysql5.6.7-)
Meaning:                 有多少线程应该缓存重用
其默认值在mysql5.6.8+(autosized),根据如下公式得到:8 + (max_connections / 100),其上限值为100.设置这个变量,可以通过观察服务器一段时间的活动,来计算一个有理有据的值。观察Threads_connected状态变量并且找到它在一般情况下的最大值和最小值。例如:若Threads_connected状态从150变化到175,可以设置线程缓存为75。但是不用设置的非常大,因为保持大量等待连接的空闲线程并没有什么真正的用处。也可以观察Threads_created状态随时间的变化。如果这个值很大或一直增长,这是另一个线索,告诉你可能需要调大thread_cache_size变量。Threads_cached来查看有多少线程已经在缓存中了。
建议值:50-100

table_open_cache
Variable Scope:         Global
Dynamic Variable:     Yes
Default:                      400( mysql5.6.7- )
Meaning:                    所有线程打开表的数量
从官方文档看出在MySQL5.6.8+开始默认值为2000,就能简单的判断出原来默认值是不够的。可以通过观察Opened_tables其值及其一段时间的变化来检查该变量。如果看到Opened_tables的值很大并且又不经常执行FLUSH TABLES(执行其命令强制所有的表重新关闭且打开),那么可能你应该增加该变量的值。
建议值: 4096(有另一种说法:这个值从max_connections的10倍开始设置)

open_files_limit
Variable Scope:       Global
Dynamic Variable:   No
Default:                    0 (mysql5.6.7-)
Meaning:                 操作系统允许mysqld服务打开的文件数。
其默认值在mysql5.6.8+(autosized),根据如下公式得到:
1) 10 + max_connections + (table_open_cache * 2)
2) max_connections * 5
3) open_files_limit value specified at startup, 5000 if none
要知道每个MyISAM表打开需要2个文件句柄;每个客户端的连接也是一个文件句柄。有效的open_files_limit的值是基于系统启动时所指定的值和max_connections,table_open_cache有关联。
建议值:65535 (其值在大多操作系统是最安全的)

table_definition_cache
Variable Scope :       Global
Dynamic Variable :   Yes
Default :                    400(mysql5.6.7-)
Meaning:                   缓存表定义的的数量(以.frm结尾的文件)
其默认值在mysql5.6.8+(autosized),根据如下公式得到:400 + (table_open_cache / 2)   其上限值为:2000。常可以把table_definition_cache 设置得足够高,以缓存所有的表定义。除非有上万张表,否则这可能是最简单的方法。
建议值:  根据真正的数据库中表的数量(例如:数据库实例有1000张表,可以将其设置为1000+)

back_log
Variable Scope:           Global
Dynamic Variable:       No
Default:                        50( mysql5.6.5- )
Meaning:                     在很短时间内,可以有多少个请求链接在堆栈中等待被处理。
其默认值在mysql5.6.6+(autosized),根据如下公式得到:  50 + (max_connections / 5) 其上限值为:900。
如果每秒的连接数很多,可以将其值调大。其值和OS的TCP/IP链接有关联,和内核参数net.ipv4.tcp_max_syn_backlog的值相关,back_log的值不能大于其值。
建议值:2048 

max_allowed_packet
Variable Scope:         Global
Dynamic Variable:     Yes
Default:                         1MB(mysql5.6.5-)
Meaning:                       这个设置防止服务器发送太大的包,也会控制多大的包可以被接受。
其默认值在mysql5.6.6+为4MB,其默认值可能太小了,但设置太大也可能有危险。如果设置太小,有时复制上会出现问题,通常表现为备库不能接收主库发过来的复制数据。使用mysql和mysqldump客户端程序都可以指定其值的大小。
建议值: 16MB

max_connect_errors
Variable Scope:          Global
Dynamic Variable:      Yes
Default:                        100(mysql5.6.6+)
Meaning:                     最大的连接错误数
如果有时网络短暂抽风了,或者应用配置出现错误,或另有问题,如权限,在短暂的时间内不断地尝试链接,客户端可能被列入黑名单,然后将无法连接,知道再次刷新主机缓存(FLUSH HOSTS)。这个选项的默认设置太小了,很容易导致问题。你也许希望增加这个值,实际上,如果知道服务器可以充分抵御蛮力攻击,可以把这个值设置的非常大,以有效地禁用主机黑名单。这个选项也就是所谓的可以防止暴力破解。
建议值: 1000000 (其值为Percona 给出的建议值,但是应该确定其主机的已有抵御蛮力攻击的能力)

skip_name_resolve
Variable Scope:          Global
Dynamic Variable:      No
Default:                       OFF
Meaning:                     DNS查找
这个选项禁用了另一个网络相关和鉴权认证的陷进:DNS查找。DNS是MySQL连接过程中的一个薄弱环节。当连接服务器时,它试图确定连接和使用的主机的主机名,作为身份验证凭据的一部分。(就是说,你的凭据是用户名,主机名,以及密码,并不只是用户名和密码)但是验证主机来源,服务器需要执行DNS的正向和反向查找。要是DNS有问题就悲剧了,在某些时间点这是必然的事。为了避免这种情况,我们强烈建议设置这个选项,在验证时关闭DNS查找,这样即快又安全。
建议值: ON

log_bin
Variable Scope:           Global
Dynamic Variable:       No
Meaning:                     是否开启binlog
开启此选项用来支持复制和时间点恢复。
建议值: 设置其值为mysql-bin来避免其默认生成的文件名(也就是与主机名无关)

sync_binlog
Variable Scope:           Global
Dynamic Variable:       Yes
Default:                         0
Meaning:                      控制MySQL怎么刷新二进制日志到磁盘
默认值为0,意味着MySQL并不刷新,有操作系统自己决定什么时候刷新缓存到持久化设备。如果这个值比0大,它指定了两次刷新到磁盘的动作之间间隔多少次二进制日志写操作(如果autocommit被设置了,每个独立的语句都是一次写,否则就是一个事务一次写)。如果没有设置sync_binlog为1,那么崩溃以后可能导致二进制日志没有同步事务数据。这可以轻易地导致复制中断,并且使得及时恢复变得不可能。无论如何,可以把这个值设置为1来获得安全的保障。这样就会要求MySQL把二进制日志和事务日志两个文件刷新到不同的位置。这可能需要磁盘寻道,相对来说是个很慢的操作。
建议值:   1

expire_log_days
Variable Scope :           Global
Dynamic Variable:        Yes
Default:                         0
Meaning:                      服务器在指定的天数之后清理旧的二进制日志
如果启用了二进制日志,应该打开这个选项,可以让服务器在指定的天数之后清理旧的二进制日志。如果不启用,最终服务器的空间会被耗尽,导致服务器卡住或崩溃。
建议值:  7~14

tmp_table_size和max_heap_table_size
这两个设置控制使用Memory引擎的内存临时表能使用多大的内存。如果隐式内存临时表的大小超过这两个设置的值,将会被转换为MyISAM表,所以它的大小可以继续增长。(隐式临时表是一种并非由自己创建,而是服务器创建,用于保存执行行中的查询的中间结果的表)应该简单地把这两个变量设为同样的值。但是要谨防这个变量太大了,临时表最好呆在内存里,但是如果它们被撑得很大,实际上还是让它们使用磁盘比较好,否则可能会让服务器内存溢出。假设查询语句没有创建庞大的临时表(通常可以通过合理的索引和查询设计来避免),那把这些变量设大一点,免得把内存临时表转换为磁盘临时表。这个过程可以在SHOW PROCESSLIST中看到。使用临时表的情况可以通过状态变量Created_tmp_tables 和 Created_tmp_disk_tables 来监控。
建议值: 设置两个变量为同样的值(这个大小要根据自己的SQL查询级别及SQL语句的优化情况
query_cache_size
Variable Scope:          Global
Dynamic Variable :     Yes
Default:                       0(mysql5.6.7-)
Meaning:                       缓存查询结果的内存大小
查询缓存使用的总内存空间,单位是字节。这个值必须是1024的整数倍,否则MySQL实际分配的数据会和你指定的略有不同。
建议值:<512MB
sort_buffer_size
Variable Scope:              Global, Session
Dynamic Variable:          Yes
Meaning:                        查询需要做排序操作时为该缓存分配内存大小
MySQL只会在有查询需要做排序操作时才会为该缓存分配内存,然后,一旦需要排序,MySQL就会立刻分配该参数指定大小的全部内存,而不管该排序是否需要这么大的内存。 通过SHOW GLOBAL STATUS查看如Sort_merge_passes/s 有很多,可以考虑增加sort_buffer_size的值,以来加快order by 或 group by语句的查询速度(其排序或分组操作已不能查询优化或索引优化)。如果查询必须使用一个更大的排序缓存才能更好的执行,可以考虑session级别的设置其值。
建议值:1MB

join_buffer_size
Variable Scope:            Global, Session
Dynamic Variable :       Yes
Meaning:                       设置使用连接查询缓存的大小
此选项可以提高没有使用索引的连接查询的性能。全局的建议不要设置太大,可以动态设置session级别的值。
建议值:  8MB

read_rnd_buffer_size
Variable Scope :              Global, Session
Dynamic Variable :          Yes
Default:                            256kb
Meaning:                          读取排序行的缓存的大小
MySQL只会在有查询需要时才会为该缓存分配内存,并且只会分配需要的内存大小而不是全部指定的大小。如果增加其值,可以提高order by 的操作性能。
建议值: 16MB

key_buffer_size
Variable Scope:                    Global
Dynamic Variable :               Yes
Default:                                 8MB
Meaning:                               MyISAM 表索引缓存的大小
如果MySQL server的表全部是或者说大多为MyISAM存储引擎的,可以考虑将其设置为OS内存的30%。其缓存仅仅缓存的是索引块,而不缓存数据。
建议值:  根据其数据库中表的存储引擎的类型来作为参考

myisam_sort_buffer_size
Variable Scope:                   Global, Session
Dynamic Variable :              Yes
Default:                                8MB
Meaning:                             排序MyISAM的索引缓存的大小
当REPAIR TABLE或者创建索引,修改索引时操作(CREATE INDEX,ALTER TABLE)时,分配给用于其MyISAM索引排序的大小。
建议值:  8MB~256MB

innodb_buffer_pool_size
Variable Scope :                    Global
Dynamic Variable:                 No
Default:                               128MB
Meaning:                             InnoDB缓冲池的大小
如果大部分都是InnoDB表,InnoDB缓冲池或许比其他任何东西更需要内存。InnoDB缓冲池并不仅仅缓存索引:它还会缓存行的数据,自适应哈希索引,插入缓冲,锁,以及其他内部数据结构。
建议值:80%+ 物理内存

innodb_buffer_pool_instances
Variable Scope :                  Global
Dynamic Variable :              No
Default:                                1(mysql5.6.5-)
Meaning:                              InnoDB缓存池的实例个数
从InnoDB1.0.x版本开始,允许有多个缓冲池实例。每个页根据哈希值平均分配到不同的缓冲池实例中。这样做的好处是减少数据库内部资源竞争,增加数据库的并发处理能力。可以通过参数innodb_buffer_pool_instances来配置。在MySQL5.6.6+其默认值为:(autosized),除了Window 32bit其值是根据innodb_buffer_pool_size的大小动态得到,其它默认值为8.
建议值:4+ (mysql5.5+)

innodb_log_buffer_size
Variable Scope:                   Global
Dynamic Variable :              No
Default:                                8MB
Meaning:                              InnoDB写日志文件到磁盘上的缓冲大小
一个较大的日志缓冲,可以使一个大的事务在commit之前不用将log写到磁盘上。同样的在update,delete,Insert
很多行时,也可以减少磁盘I/O的调用。
建议值:8~128MB

innodb_flush_log_at_trx_commit
Variable Scope:                   Global
Dynamic Variable:               Yes
Default:                                1
Meaning:                              控制日志缓冲刷新的频繁程度
日志缓冲必须刷新到持久化存储,以确保提交的事务完全被持久化了。如果和持久相比更在乎性能,可以修改
innodb_flush_log_at_trx_commit变量来控制日志缓冲刷新的频繁程度。可能的设置如下:
0
把日志缓冲写到日志文件,并且每秒刷新一次,但是事务提交时不做任何事情。
1
将日志缓冲写到日志文件,并且每次事务提交都刷新到持久化存储。这是默认的(并且是最安全的)设置,该设置
能保证不会丢失任何已经提交的事务,除非磁盘或者OS是’伪‘刷新。
2
每次提交时把日志缓冲写到日志文件,但是并不刷新。Innodb每秒做一次刷新。0与2最重要的不同是,如果MySQL
进程“挂了”,2不会丢失事务。
建议值: 1
innodb_log_file_size
Variable Scope:                   Global
Dynamic Variable :              No
Meaning:                              指定每个重做日志文件的大小
重做日志文件的大小设置对于InnoDB存储引擎的性能有着非常大的影响。一方面重做日志文件不能设置的太大,如果设置得很大,在恢复时可能需要很长的时间;另一方面又不能设置太小了,否则可能导致一个事务的日志需要多次切换重做日志文件。
建议值: 根据自己能接受的方面(更好的性能or更短的恢复时间)决定

innodb_io_capacity
Variable Scope:                    Global
Dynamic Variable:                Yes
Default:                                  200
InnoDB曾经在代码里写死了假设服务器运行在每秒100个I/O操作的单硬盘上。默认值很糟糕。现在可以告诉InnoDB服务器有多大的I/O能力。有时需要把这个值设置得相当高(像SSD这样极快的存储设备上需要设置为上万)才能稳定地刷新脏页。
建议值: 根据server的I/O能力有关系

innodb_read_io_threads和innodb_write_io_threads
这些选项控制有多少后台线程可以被I/O操作使用。最近版本的MySQL里,默认值4个读线程和4个写线程,对大部分
服务器这都足够了,尤其是MySQL5.5里面可以用操作系统原生的异步I/O以后。
建议值:各为4(即默认值)

innodb_log_files_in_group
Variable Scope:                    Global
Dynamic Variable:                No
Default :                                2
Meaning:                               每组InnoDB重做日志文件的个数
建议值:                                    2

innodb_file_per_table
Variable Scope :                   Global
Dynamic Variable :               Yes
Default:                                 OFF(mysql5.6.5-)
Meaning:                              控制InnoDB表空间存储形式
其默认值在mysql5.6.6+后为ON。开启此选项后,关于InnoDB表的数据和索引单独存储在自己的表空间中(.ibd结尾的文件)。否则,存储在系统的表空间中(ibdata)。
建议值: ON
特别说明:
1):此文档并非涵盖所有的参数。
2):此总结不是乱写的,参考书籍:《High Performance MySQL》;《MySQL官方文档5.5,5.6》;《MySQL技术内幕InnoDB存储引擎》;《MySQL Troubleshooting》。
友情提示:
关于mysql参数配置的优化可以参考一下工具
1):mysqltuner
2):Percona 的在线工具https://tools.percona.com/
关于查看mysql的global  status状态值辅助工具
1):Percona的Percona_Toolkit工具包中的pt-mext
别人的不一定符合自己的环境。理解才是最重要。









本文转自 kuchuli 51CTO博客,原文链接:http://blog.51cto.com/lgdvsehome/1250793,如需转载请自行联系原作者

网友评论

登录后评论
0/500
评论
余二五
+ 关注