©2020-2024 ioDraw All rights reserved,
Privacy Policy
MySQL非缓存参数变量介绍
#修改back_log参数值:由默认的50修改为500.（每个连接256kb,占用：125M）
back_log=500
解释：
如果MySql的连接数据达到max_connections时，新来的请求被退回、记录即back_log，此时tcp连接就会排队，所以mysql也记录了它，简单理解等待连接的请求个数。
连接的数量超过back_log，将不被授予连接资源。将会报：unauthenticated user | xxx.xxx.xxx.xxx | NULL |
Connect | NULL | login | NULL 的待连接进程时.
back_log值不能超过TCP/IP连接的侦听队列的大小，若超过则无效。可以把它理解为处在tcp协议层的一个连接统计功能函数。
查看当前系统的TCP/IP连接的侦听队列的大小命令：
cat /proc/sys/net/ipv4/tcp_max_syn_backlog目前系统为1024。
查看当前数量
show variables like 'back_log';
#修改wait_timeout参数值，由默认的8小时，修改为30分钟。
wait_timeout=1800
理解：
MySQL客户端的数据库连接闲置最大时间值，一般设置为多数用户使用系统从开始到结束所用的时间为合适。
说得比较通俗一点，就是当你的MySQL连接闲置超过一定时间后将会被强行关闭。MySQL默认的wait-timeout 值为8个小时。
show variables like 'wait_timeout'查看结果值;。
show processlist 如果经常发现MYSQL中有大量的进程长时间停留，则需要 修改wait-timeout值了。
interactive_timeout：服务器关闭交互式连接前等待活动的秒数。这两个参数必须配合使用，当连接时间同时达到这两个参数则被关闭，否则单独设置wait_timeout无效
#修改max_connections参数值，由默认的151。（它是四层比较靠下级的参数，重度影响性能）
max_connections=3000
解释：
max_connections是指MySql的最大连接数，如果服务器的并发连接请求量比较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，介于MySql会为每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。可以过'conn%'通配符查看当前状态的连接数量，以定夺该值的大小。
MySQL服务器允许的最大连接数16384；
查看系统当前最大连接数：
show variables like 'max_connections';
#修改max_user_connections值，由默认的0。（它是四层比较靠上级的参数，轻度影响性能）
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';
#修改thread_concurrency值，由目前默认的8，修改为64
thread_concurrency=64
解释：
thread_concurrency的值的正确与否, 对mysql的性能影响很大,
在多个cpu(或多核)的情况下，错误设置了thread_concurrency的值, 会导致mysql不能充分利用多cpu(或多核),
出现同一时刻只能一个cpu(或核)在工作的情况。
thread_concurrency应设为CPU核数的2倍.
查看命令：
show variables like 'thread_concurrency';
#添加skip-name-resolve，默认被注释掉，没有该参数，一般也不用，都是直接IP连接。
skip-name-resolve
skip-name-resolve：禁止MySQL对外部连接进行DNS解析，使用这一选项可以消除MySQL进行DNS解析的时间。但需要注意，如果开启该选项，则所有远程主机连接授权都要使用IP地址方式，否则MySQL将无法正常处理连接请求！
#skip-networking，默认被注释掉。没有该参数。
解释：
skip-networking建议被注释掉，不要开启
开启该选项可以彻底关闭MySQL的TCP/IP连接方式，无法正常连接！
MySQL缓存变量介绍
#key_buffer_size,本系统目前为384M,可修改为400M
key_buffer_size=400M
解释：
key_buffer_size是用于索引块的缓冲区大小，增加它可得到更好处理的索引(对所有读和多重写)，对MyISAM(MySQL表存储的一种类型，可以百度等查看详情)表性能影响最大的一个参数。如果你使它太大，系统将开始换页并且真的变慢了。严格说是它决定了数据库索引处理的速度，尤其是索引读的速度。对于内存在4GB左右的服务器该参数可设置为256M或384M.
命令查得：show status like 'key_read%';
+-------------------+-------+
| 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值。
#innodb_buffer_pool_size(默认128M)
innodb_buffer_pool_size=1024M(1G)
解释：
innodb_buffer_pool_size:主要针对InnoDB表性能影响最大的一个参数。功能与Key_buffer_size一样。
另外InnoDB和 MyISAM 存储引擎不同， MyISAM 的 key_buffer_size 只能缓存索引键，而
innodb_buffer_pool_size 却可以缓存数据块和索引键。适当的增加这个参数的大小，可以有效的减少 InnoDB 类型的表的磁盘 I/O 。
当我们操作一个 InnoDB 表的时候，返回的所有数据或者去数据过程中用到的任何一个索引块，都会在这个内存区域中走一遭。
命令查得：show status like 'Innodb_buffer_pool_read%';
比如查看当前系统中系统中
| Innodb_buffer_pool_read_requests | 1283826 |
| Innodb_buffer_pool_reads | 519 |
+---------------------------------------+---------+
其命中率99.959%=（1283826-519）/1283826*100% 命中率越高越好。
#innodb_additional_mem_pool_size(默认8M)
innodb_additional_mem_pool_size=20M
解释：
innodb_additional_mem_pool_size
设置了InnoDB存储引擎用来缓存数据结构，所以当我们一个MySQL数据类型非常多的时候，是需要适当调整该参数的大小以确保所有数据都能存放在内存中提高访问效率的。
这个参数大小是否足够还是比较容易知道的，因为当过小的时候，MySQL会记录Warning信息到数据库的error
log中，这时候你就知道该调整这个参数大小了。
查看当前系统mysql的error日志 cat /var/lib/mysql/机器名.error 发现有很多waring警告。所以要调大为20M.
根据MySQL手册，对于2G内存的机器，推荐值是20M。
32G内存的 100M
#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 参数设置其可以使用的最大内存空间。
InnoDB 将日志写入日志磁盘文件前的缓冲大小。理想值为 1M 至
8M。大的日志缓冲允许事务运行时不需要将日志保存入磁盘而只到事务被提交(commit)。 因此，如果有大的事务处理，设置大的日志缓冲可以减少磁盘I/O。 在
my.cnf中以数字格式设置。
默认是8MB，一般来说不建议超过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(默认32M)
query_cache_size=40M
解释：
query_cache_size:
主要用来缓存MySQL中的ResultSet，也就是一条SQL语句执行的结果集，所以仅仅只能针对select语句。根据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