Mysql配置参数说明摘要
mysql安装之后并不是默认参数就能满⾜⽣产系统的需要,需要根据业务调整很多的参数。参数说明
硬件 16 CPU 32G Memory
# For advice on how to change settings please see
# sql/doc/refman/5.7/en/server-configuration-defaults.html
[mysql]
default-character-set = utf8mb4
[mysqld]
character-set-client-handshake = FALSE
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'
skip_host_cache
skip-name-resolve=1
###NEW####
expire_logs_days=7
back_log=300
max_connections=8000
max_connect_errors=30
#table_cache=4096
open_files_limit=10240
#thread_concurrency=64
max_allowed_packet=100000000
net_buffer_length=100000
interactive_timeout=300
wait_timeout=300
thread_cache_size=100
slow_query_log=1
long_query_time=5
sort_buffer_size=16M
read_buffer_size=16M
read_rnd_buffer_size=32M
join_buffer_size=128M
tmp_table_size=64M
table_open_cache=5000
key_buffer_size=400M
query_cache_size=128M
thread_stack=512K
innodb_buffer_pool_size=24G
#innodb_additional_mem_pool_size=20M
mysql配置与安装过程#innodb_file_io_threads=4
innodb_thread_concurrency=16
innodb_log_buffer_size=16M
innodb_log_file_size=512M
innodb_log_files_in_group=3
innodb_lock_wait_timeout=120
###NEW####
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else10%.
# innodb_buffer_pool_size = 128M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
# log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
datadir=/opt/data/mysql
socket=/var/lib/mysql/mysql.sock
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
lower_case_table_names =1
# master
log-bin=mysql-bin
server-id=2
back_log
该值指出在MySQL暂时停⽌回答新请求之前的短时间内多少个请求可以被存在堆栈中。也就是说,如果MySql的连接数据达到max_connections时,新来的请求将会被存在堆栈中,以等待某⼀连接释放资源,该堆栈的数量即back_log,如果等待连接的数量超过back_log,将不被授予连接资源。将会报:unauthenticated user | x | NULL | Connect | NULL | login | NULL 的待连接进程时.
back_log值不能超过TCP/IP连接的侦听队列的⼤⼩。若超过则⽆效,查看当前系统的TCP/IP连接的侦听队列的⼤⼩命令:
cat /proc/sys/net/ipv4/tcp_max_syn_backlog⽬前系统为1024。对于Linux系统推荐设置为⼩于512的整数。
wait-timeout
MySQL默认的wait-timeout 值为8个⼩时,可以通过命令show variables like 'wait_timeout'查看结果值;。
设置这个值是⾮常有意义的,⽐如你的⽹站有⼤量的MySQL链接请求(每个MySQL连接都是要内存资源开销的),由于你的程序的原因有⼤量的连接请求空闲啥事也不⼲,⽩⽩占⽤内存资源,或者导致MySQL超过最⼤连接数从来⽆法新建连接导
致“Too many connections”的错误。在设置之前你可以查看⼀下你的MYSQL的状态(可⽤show processlist),如果经常发现MYSQL中有⼤量的Sleep进程,则需要修改wait-timeout值了。
interactive_timeout:服务器关闭交互式连接前等待活动的秒数。交互式客户端定义为在mysql_real_connect()中使⽤
CLIENT_INTERACTIVE选项的客户端。
wait_timeout:服务器关闭⾮交互连接之前等待活动的秒数。在线程启动时,根据全局wait_timeout值或全局 interactive_timeout值初始化会话wait_timeout值,取决于客户端类型(由mysql_real_connect()的连接选项CLIENT_INTERACTIVE定义).
这两个参数必须配合使⽤。否则单独设置wait_timeout⽆效。
max_connections
指MySql的最⼤连接数,如果服务器的并发连接请求量⽐较⼤,建议调⾼此值,以增加并⾏连接数量,当然这建⽴在机器能⽀撑的情况下,因为如果连接数越多,介于MySql会为每个连接提供连接缓冲区,就会开销越多的内存,所以要适当调整该值,不能盲⽬提⾼设值。可以过'conn%'通配符查看当前状态的连接数量,以定夺该值的⼤⼩。
MySQL服务器允许的最⼤连接数16384;
查看系统当前最⼤连接数:
show variables like 'max_connections';
max_user_connections
是指每个数据库⽤户的最⼤连接
针对某⼀个账号的所有客户端并⾏连接到MYSQL服务的最⼤并⾏连接数。简单说是指同⼀个账号能够同时连接到mysql服务的最⼤连接数。设置为0表⽰不限制。
⽬前默认值为:0不受限制。
这⼉顺便介绍下Max_used_connections:它是指从这次mysql服务启动到现在,同⼀时刻并⾏连接数的最⼤值。它不是指当前的连接情况,⽽是⼀个⽐较值。如果在过去某⼀个时刻,MYSQL服务同时有1000个请求连接过来,⽽之后再也没有出现这么⼤的并发请求时,则Max_used_connections=1000.请注意与show variables ⾥的max_user_connections的区别。默认为0表⽰⽆限⼤。
查看max_user_connections值
show variables like 'max_user_connections';
thread_concurrency
该值的正确与否, 对mysql的性能影响很⼤, 在多个cpu(或多核)的情况下,错误设置了thread_concurrency的值, 会导致mysql不能充分利⽤多cpu(或多核), 出现同⼀时刻只能⼀个cpu(或核)在⼯作的情况。
thread_concurrency应设为CPU核数的2倍. ⽐如有⼀个双核的CPU, 那thread_concurrency 的应该为4; 2个双核的
cpu, thread_concurrency的值应为8.
⽐如:根据上⾯介绍我们⽬前系统的配置,可知道为4个CPU,每个CPU为8核,按照上⾯的计算规则,这⼉应为:4*8*2=64
查看系统当前thread_concurrency默认配置命令:
show variables like 'thread_concurrency';
skip-name-resolve
禁⽌MySQL对外部连接进⾏DNS解析,使⽤这⼀选项可以消除MySQL进⾏DNS解析的时间。但需要注意,如果开启该选项,则所有远程主机连接授权都要使⽤IP地址⽅式,否则MySQL将⽆法正常处理连接请求!
innodb_buffer_pool_size
主要针对InnoDB表性能影响最⼤的⼀个参数。功能与Key_buffer_size⼀样。InnoDB占⽤的内存,除innodb_buffer_pool_size⽤于存储页⾯缓存数据外,另外正常情况下还有⼤约8%的开销,主要⽤在每个缓存页帧的描述、adaptive hash等数据结构,如果不是安全关闭,启动时还要恢复的话,还要另开⼤约12%的内存⽤于恢复,两者相加就有差不多21%的开销。假设:12G的
innodb_buffer_pool_size,最多的时候InnoDB就可能占⽤到14.5G的内存。若系统只有16G,⽽且只运⾏MySQL,且MySQL只⽤InnoDB,
那么为MySQL开12G,是最⼤限度地利⽤内存了。
另外InnoDB和 MyISAM 存储引擎不同, MyISAM 的 key_buffer_size 只能缓存索引键,⽽ innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的⼤⼩,可以有效的减少 InnoDB 类型的表的磁盘 I/O 。
当我们操作⼀个 InnoDB 表的时候,返回的所有数据或者去数据过程中⽤到的任何⼀个索引块,都会在这个内存区域中⾛⼀遭。
可以通过 (Innodb_buffer_pool_read_requests – Innodb_buffer_pool_reads) / Innodb_buffer_pool_read_requests * 100% 计算缓存命中率,并根据命中率来调整 innodb_buffer_pool_size 参数⼤⼩进⾏优化。值可以⽤以下命令查得:
show status like 'Innodb_buffer_pool_read%';
innodb_log_buffer_size
这是InnoDB存储引擎的事务⽇志所使⽤的缓冲区。类似于Binlog Buffer,InnoDB在写事务⽇志的时候,为了提⾼性能,也是先将信息写⼊Innofb Log Buffer中,当满⾜innodb_flush_log_trx_commit参数所设置的相应条件(或者⽇志缓冲区写满)之后,才会将⽇志写到⽂件 (或者同步到磁盘)中。可以通过innodb_log_buffer_size 参数设置其可以使⽤的最⼤内存空间。
InnoDB 将⽇志写⼊⽇志磁盘⽂件前的缓冲⼤⼩。理想值为 1M ⾄ 8M。⼤的⽇志缓冲允许事务运⾏时不需要将⽇志保存⼊磁盘⽽只到事务被提交(commit)。因此,如果有⼤的事务处理,设置⼤的⽇志缓冲可以减少磁盘I/O。在myf中以数字格式设置。
默认是8MB,系的如频繁的系统可适当增⼤⾄4MB~8MB。当然如上⾯介绍所说,这个参数实际上还和另外的flush参数相关。⼀般来说不建议超过32MB
注:innodb_flush_log_trx_commit参数对InnoDB Log的写⼊性能有⾮常关键的影响,默认值为1。该参数可以设置为0,1,2,解释如下:
0:log buffer中的数据将以每秒⼀次的频率写⼊到log file中,且同时会进⾏⽂件系统到磁盘的同步操作,
但是每个事务的commit并不会触发任何log buffer 到log file的刷新或者⽂件系统到磁盘的刷新操作;
1:在每次事务提交的时候将log buffer 中的数据都会写⼊到log file,同时也会触发⽂件系统到磁盘的同步;
2:事务提交会触发log buffer到log file的刷新,但并不会触发磁盘⽂件系统到磁盘的同步。此外,每秒会有⼀次⽂件系统到磁盘同步操作。
实际测试发现,该值对插⼊数据的速度影响⾮常⼤,设置为2时插⼊10000条记录只需要2秒,设置为0时只需要1秒,⽽设置为1时则需要229秒。因此,MySQL⼿册也建议尽量将插⼊操作合并成⼀个事务,这样可以⼤幅提⾼速度。根据MySQL⼿册,在存在丢失最近部分事务的危险的前提下,可以把该值设为0。
query_cache_size
query_cache_size: 主要⽤来缓存MySQL中的ResultSet,也就是⼀条SQL语句执⾏的结果集,所以仅仅只能针对select语句。当我们打开了 Query Cache功能,MySQL在接受到⼀条select语句的请求后,如果该语句满⾜Query Cache的要求(未显式说明不允许使⽤Query Cache,或者已经显式申明需要使⽤Query Cache),MySQL会直接根据预先设定好的HASH算法将接受到的select语句以字符串⽅
式进⾏hash,然后到Query Cache中直接查是否已经缓存。也就是说,如果已经在缓存中,该select请求就会直接将数据返回,从⽽省略了后⾯所有的步骤(如SQL语句的解析,优化器优化以及向存储引擎请求数据等),极⼤的提⾼性能。根据MySQL⽤户⼿册,使⽤查询缓冲最多可以达到238%的效率。
当然,Query Cache也有⼀个致命的缺陷,那就是当某个表的数据有任何任何变化,都会导致所有引⽤了该表的select语句在
Query Cache中的缓存数据失效。所以,当我们的数据变化⾮常频繁的情况下,使⽤Query Cache可能会得不偿失
Query Cache的使⽤需要多个参数配合,其中最为关键的是query_cache_size和query_cache_type,前者设置⽤于缓存 ResultSet的内存⼤⼩,后者设置在何场景下使⽤Query Cache。在以往的经验来看,如果不是⽤来缓存基本不变的数据的MySQL数据
库,query_cache_size⼀般256MB是⼀个⽐较合适的⼤⼩。当然,这可以通过计算Query Cache的命中率
(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))来进⾏调整。 query_cache_type可以设置为0(OFF),1(ON)或者2(DEMOND),分别表⽰完全不使⽤query cache,除显式要求不使⽤query cache
(使⽤sql_no_cache)之外的所有的select都使⽤query cache,只有显⽰要求才使⽤query cache(使⽤sql_cache)。如果Qcache_lowmem_prunes的值⾮常⼤,则表明经常出现缓冲. 如果Qcache_hits的值也⾮
常⼤,则表明查询缓冲使⽤⾮常频繁,此时需要增加缓冲⼤⼩;
根据命中率(Qcache_hits/(Qcache_hits+Qcache_inserts)*100))进⾏调整,⼀般不建议太⼤,256MB可能已经差不多了,⼤型的配置型静态数据可适当调⼤.
可以通过命令:show status like 'Qcache_%';查看⽬前系统Query catch使⽤⼤⼩
| Qcache_hits | 1892463 |
| Qcache_inserts | 35627
命中率98.17%=1892463/(1892463 +35627 )*100
局部缓存
除了全局缓冲,MySql还会为每个连接发放连接缓冲。个连接到MySQL服务器的线程都需要有⾃⼰的
缓冲。⼤概需要⽴刻分配256K,甚⾄在线程空闲时,它们使⽤默认的线程堆栈,⽹络缓存等。事务开始之后,则需要增加更多的空间。运⾏较⼩的查询可能仅给指定的线程增加少量的内存消耗,然⽽如果对数据表做复杂的操作例如扫描、排序或者需要临时表,则需分配⼤约read_buffer_size,
sort_buffer_size,read_rnd_buffer_size,tmp_table_size ⼤⼩的内存空间. 不过它们只是在需要的时候才分配,并且在那些操作做完之后就释放了。有的是⽴刻分配成单独的组块。tmp_table_size 可能⾼达MySQL所能分配给这个操作的最⼤内存空间了
。注意,这⾥需要考虑的不只有⼀点——可能会分配多个同⼀种类型的缓存,例如⽤来处理⼦查询。⼀些特殊的查询的内存使⽤量可能更⼤——如果在MyISAM表上做成批的插⼊
时需要分配 bulk_insert_buffer_size ⼤⼩的内存;执⾏ ALTER TABLE, OPTIMIZE TABLE, REPAIR TABLE 命令时需要分
配 myisam_sort_buffer_size ⼤⼩的内存。
read_buffer_size
read_buffer_size 是MySql读⼊缓冲区⼤⼩。对表进⾏顺序扫描的请求将分配⼀个读⼊缓冲区,MySql会为它分配⼀段内存缓冲区。read_buffer_size变量控制这⼀
缓冲区的⼤⼩。如果对表的顺序扫描请求⾮常频繁,并且你认为频繁扫描进⾏得太慢,可以通过增加该变量值以及内存缓冲区⼤⼩提⾼其性能.
read_rnd_buffer_size
read_rnd_buffer_size 是MySql的随机读缓冲区⼤⼩。当按任意顺序读取⾏时(例如,按照排序顺序),将分配⼀个随机读缓存区。进⾏排序查询时,MySql会⾸先扫描⼀遍该缓冲,以避免磁盘搜索,提⾼查询速度,如果需要排序⼤量数据,可适当调⾼该值。但MySql会为每个客户连接发放该缓冲空间,所以应尽量适当设置该值,以避免内存开销过⼤。
tmp_table_size
tmp_table_size是MySql的heap (堆积)表缓冲⼤⼩。所有联合在⼀个DML指令内完成,并且⼤多数联合甚⾄可以不⽤临时表即可以完成。⼤多数临时表是基于内
存的(HEAP)表。具有⼤的记录长度的临时表 (所有列的长度的和)或包含BLOB列的表存储在硬盘上。如果某个内部heap(堆积)表⼤⼩超过tmp_table_size,MySQL可以根据需要⾃动将内存中的heap表改为基于硬盘的MyISAM表。还可以通过设置tmp_table_size选项来增加临时表的⼤⼩。也就是说,如果调⾼该值,MySql同时将增加heap表的⼤⼩,可达到提⾼联接查询速度的效果。
thread_cache_size
默认的thread_cache_size=8,但是看到好多配置的样例⾥的值⼀般是32,64,甚⾄是128,感觉这个参数对优化应该有帮助,于是查了下:
根据调查发现以上服务器线程缓存thread_cache_size没有进⾏设置,或者设置过⼩,这个值表⽰可以重新利⽤保存在缓存中线程的数量,当断开连接时如果缓存中还有空间,那么客户端的线程将被放到缓存中,如果线程重新被请求,那么请求将从缓存中读取,如果缓存中是空的或者是新的请求,那么这个线程将被重新创建,如果有很多新的线程,增加这个值可以改善系统性能.通过⽐
较 Connections 和 Threads_created 状态的变量,可以看到这个变量的作⽤。(–>表⽰要调整的值) 根据物理内存设置规则如下:
1G —> 8
2G —> 16
3G —> 32 >3G —> 64
mysql> show status like 'thread%';
+——————-+——-+
| Variable_name | Value |
+——————-+——-+
| Threads_cached | 0 | <—当前被缓存的空闲线程的数量
| Threads_connected | 1 | <—正在使⽤(处于连接状态)的线程
| Threads_created | 1498 | <—服务启动以来,创建了多少个线程
| Threads_running | 1 | <—正在忙的线程(正在查询数据,传输数据等等操作)
+——————-+——-+
查看开机起来数据库被连接了多少次?
mysql> show status like '%connection%';
+———————-+——-+
| Variable_name | Value |
+———————-+——-+
| Connections | 1504 | –>服务启动以来,历史连接数
| Max_used_connections | 2 |
+———————-+——-+
通过连接线程池的命中率来判断设置值是否合适?命中率超过90%以上,设定合理。 (Connections - Threads_created) / Connections * 100 %
参考
版权声明:本站内容均来自互联网,仅供演示用,请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198,我们将在24小时内删除。
发表评论