mysql8配置参数优化_MySQL性能优化之参数配置
1、⽬的：
通过根据服务器⽬前状况，修改Mysql的系统参数，达到合理利⽤服务器现有资源，最⼤合理的提⾼MySQL性能。
2、服务器参数：
32G内存、4个CPU，每个CPU 8核。
3、MySQL⽬前安装状况。
MySQL⽬前安装，⽤的是MySQL默认的最⼤⽀持配置。拷贝的是my-hugef.编码已修改为UTF-8.具体修改及安装MySQL，可以参考<>帮助⽂档。
4、修改MySQL配置
打开MySQL配置⽂件myf
vi  /etc/myf
4.1 MySQL⾮缓存参数变量介绍及修改
4.1.1修改back_log参数值：由默认的50修改为500.(每个连接256kb，占⽤：125M)
back_log=500
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的整数。
查看mysql 当前系统默认back_log值，命令：
show variables like 'back_log'; 查看当前数量
4.1.2修改wait_timeout参数值，由默认的8⼩时，修改为30分钟。(本次不⽤)
wait_timeout=1800(单位为妙)
我对wait-timeout这个参数的理解：MySQL客户端的数据库连接闲置最⼤时间值。
说得⽐较通俗⼀点，就是当你的MySQL连接闲置超过⼀定时间后将会被强⾏关闭。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⽆效
4.1.3修改max_connections参数值，由默认的151，修改为3000(750M)。
max_connections=3000
max_connections是指MySql的最⼤连接数，如果服务器的并发连接请求量⽐较⼤，建议调⾼此值，以增加并⾏连接数量，当然这建⽴在机器能⽀撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲⽬提⾼设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的⼤⼩。
MySQL服务器允许的最⼤连接数16384；
查看系统当前最⼤连接数：
show variables like 'max_connections';
4.1..4修改max_user_connections值，由默认的0，修改为800
max_user_connections=800
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';
4.1.5修改thread_concurrency值，由⽬前默认的8，修改为64
thread_concurrency=64
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';
4.1.6添加skip-name-resolve，默认被注释掉，没有该参数。
skip-name-resolve
skip-name-resolve：禁⽌MySQL对外部连接进⾏DNS解析，使⽤这⼀选项可以消除MySQL进⾏DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使⽤IP地址⽅式，否则MySQL将⽆法正常处理连接请求！
4.1.7 skip-networking，默认被注释掉。没有该参数。(本次⽆⽤)
java中nextint
skip-networking建议被注释掉，不要开启
开启该选项可以彻底关闭MySQL的TCP/IP连接⽅式，如果WEB服务器是以远程连接的⽅式访问MySQL数据库服务器则不要开启该选项！否则将⽆法正常连接！
4.1.8  default-storage-engine(设置MySQL的默认存储引擎)
default-storage-engine= InnoDB(设置InnoDB类型，另外还可以设置MyISAM类型)
设置创建数据库及表默认存储类型
show table status like ‘tablename’显⽰表的当前存储状态值
查看MySQL有哪些存储状态及默认存储状态
show engines;
创建表并指定存储类型
CREATE TABLE mytable (id int, title char(20)) ENGINE = INNODB;
修改表存储类型：
Alter table tableName engine =engineName
备注：设置完后把以下⼏个开启：
# Uncomment the following if you are using InnoDB tables
innodb_data_home_dir = /var/lib/mysql
#innodb_data_file_path = ibdata1:1024M;ibdata2:10M:autoextend(要注释掉，否则会创建⼀个新的把原来的替换的。)
innodb_log_group_home_dir = /var/lib/mysql
# You can set .._buffer_pool_size up to 50 - 80 %
# of RAM but beware of setting memory usage too high
innodb_buffer_pool_size = 1000M
innodb_additional_mem_pool_size = 20M
# Set .._log_file_size to 25 % of buffer pool size
innodb_log_file_size = 500M
innodb_log_buffer_size = 20M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50
设置完后⼀定记得把MySQL安装⽬录地址(我们⽬前是默认安装所以地址/var/lib/mysql/)下的ib_logfile0和ib_logfile1删除掉。否则重启MySQL起动失败。
4.2 MySQL缓存变量介绍及修改
数据库属于IO密集型的应⽤程序，其主职责就是数据的管理及存储⼯作。⽽我们知道，从内存中读取⼀个数据库的时间是微秒级别，⽽从⼀块普通硬盘上读取⼀个 IO是在毫秒级别，⼆者相差3个数量级。所以，要优化数据库，⾸先第⼀步需要优化的就是IO，尽可能将磁盘IO转化为内存IO。本⽂先从MySQL数据库 IO相关参数(缓存参数)的⾓度来看看可以通过哪些参数进⾏IO优化
4.2.1全局缓存
启动MySQL时就要分配并且总是存在的全局缓存。⽬前有：key_buffer_size(默认值：402653184，即384M)、
innodb_buffer_pool_size(默认值：134217728即：128M)、innodb_additional_mem_pool_size(默认值：8388608即：8M)、innodb_log_buffer_size(默认值：8388608即：8M)、query_cache_size(默认值：33554432即：32M)等五个。总共：560M.
这些变量值都可以通过命令如：show variables like '变量名'；查看到。
4.2.1.1：key_buffer_size，本系统⽬前为384M，可修改为400M
key_buffer_size=400M
key_buffer_size是⽤于索引块的缓冲区⼤⼩，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM(MySQL表存储的⼀种类型，可以百度等查看详情)表性能影响最⼤的⼀个参数。如果你使它太⼤，系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M.
怎么才能知道key_buffer_size的设置是否合理呢，⼀般可以检查状态值Key_read_requests和Key_reads  ，⽐例
key_reads / key_read_requests应该尽可能的低，⽐如1:100，1:1000 ，1:10000。其值可以⽤以下命令查得：mysql无法连接到服务器
show status like 'key_read%';
⽐如查看系统当前key_read和key_read_request值为：
+-------------------+-------+
| Variable_name    | Value |
+-------------------+-------+
| Key_read_requests | 28535 |
| Key_reads        | 269  |
+-------------------+-------+
可知道有28535个请求，有269个请求在内存中没有到直接从硬盘读取索引.
未命中缓存的概率为：0.94%=269/28535*100%.  ⼀般未命中概率在0.1之下⽐较好。⽬前已远远⼤于0.1，证明效果不好。若命中率在0.01以下，则建议适当的修改key_buffer_size值。
excel教程视频怎样求和MyISAM、InnoDB、MyISAM Merge引擎、InnoDB、memory(heap)、archive
4.2.1.2：innodb_buffer_pool_size(默认128M)
innodb_buffer_pool_size=1024M(1G)
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，是最⼤限度地利⽤内存了。
gets怎么读音发音英语怎么说
另外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_buffer_pool_read_requests      | 1283826 |
| Innodb_buffer_pool_reads              | 519    |
+---------------------------------------+---------+
其命中率99.959%=(1283826-519)/1283826*100%  命中率越⾼越好。
4.2.1.3：innodb_additional_mem_pool_size(默认8M)
innodb_additional_mem_pool_size=20M
innodb_additional_mem_pool_size 设置了InnoDB存储引擎⽤来存放数据字典信息以及⼀些内部数据结构的内存空间⼤⼩，所以当我们⼀个MySQL Instance中的数据库对象⾮常多的时候，是需要适当调整该参数的⼤⼩以确保所有数据都能存放在内存中提⾼访问效率的。
这个参数⼤⼩是否⾜够还是⽐较容易知道的，因为当过⼩的时候，MySQL会记录Warning信息到数据库的error log中，这时候你就知道该调整这个参数⼤⼩了。
查看当前系统mysql的error⽇志  cat  /var/lib/mysql/机器名.error 发现有很多waring警告。所以要调⼤为20M.
根据MySQL⼿册，对于2G内存的机器，推荐值是20M。
32G内存的 100M
4.2.1.4：innodb_log_buffer_size(默认8M)
innodb_log_buffer_size=20M
innodb_log_buffer_size  这是InnoDB存储引擎的事务⽇志所使⽤的缓冲区。类似于Binlog Buffer，InnoDB在写事务⽇志的时候，为了提⾼性能，也是先将信息写⼊Innofb Log Buffer中，当满⾜innodb_flush_log_trx_commit参数所设置的相应条件(或者⽇志缓冲区写满)之后，才会将⽇志写到⽂件 (或者同步到磁盘)中。可以通过innodb_log_buffer_size 参数设置其可以使⽤的最⼤内存空间。osi参考模型是一种国际标准对不对
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。
4.5.1.5：query_cache_size(默认32M)
网站源码测试工具query_cache_size=40M
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使⽤⼤⼩

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。