centos7阿里云服务器8核16G配置mysql服务设置编辑设置my.cnf文件常规项详解

管理员 发布于 3年前   529

centos7阿里云服务器8核16G配置mysql服务编辑设置my.cnf文件常规项详解，

目前该服务器做数据库服务器，redis服务器，后面根据项目模块的增加会增加到12核24G，最后就根据瓶颈做增加服务器操作，做负载均衡，主从复制，读写分离这些

该文章就对下面配置常规参数做介绍，更加详细，更多参数自行搜索了解

[client]
port        = 3306
socket      = /tmp/mysql.sock

[mysqld]
port        = 3306
socket      = /tmp/mysql.sock
datadir = /usr/local/mysql/var
skip-external-locking
key_buffer_size = 128M
max_allowed_packet = 512M
table_open_cache = 512
sort_buffer_size = 2M
net_buffer_length = 8K
read_buffer_size = 2M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 32M
thread_cache_size = 64
query_cache_size = 256M
query_cache_type=1
query_cache_limit=50M
tmp_table_size = 128M
performance_schema_max_table_instances = 4000

explicit_defaults_for_timestamp = true
#skip-networking
max_connections = 1000
max_connect_errors = 100
open_files_limit = 65535
#skip-grant-tables
log-bin=mysql-bin
binlog_format=mixed
server-id   = 1
expire_logs_days = 10
early-plugin-load = ""

default_storage_engine = InnoDB
innodb_file_per_table = 1
innodb_data_home_dir = /usr/local/mysql/var
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /usr/local/mysql/var
innodb_buffer_pool_size = 10240M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 0
innodb_lock_wait_timeout = 50

[mysqldump]
quick
max_allowed_packet = 16M
[mysql]
no-auto-rehash
[myisamchk]
key_buffer_size = 128M
sort_buffer_size = 2M
read_buffer = 2M
write_buffer = 2M
[mysqlhotcopy]
interactive-timeout

1.连接请求的变量 max_connections

MySQL的最大连接数，如果服务器的并发连接请求量较大，建议调高此值，以增加并行连接数量，当然这建立在机器能支撑的情况下，因为如果连接数越多，MySQL回味每个连接提供连接缓冲区，就会开销越多的内存，所以要适当调整该值，不能盲目提高设值。
数值过小经常会出现ERROR 1040：Too mant connetcions错误，可以通过mysql>show status like ‘connections'；通配符来查看当前状态的连接数量（试图连接到MySQL（不管是否连接成功）的连接数），以定夺该值的大小。
show variadles like ‘max_connections'最大连接数
show variables like ‘max_used_connection'相应连接数
max_used_connection/max_connections*100%（理想值约等于85%）
如果max_used_connections和max_connections相同，那么就是max_connections值设置过低或者超过服务器的负载上限了，低于10%则设置过大了。

2.查询缓存简称QC query_cache_size

使用查询缓存，MySQL将查询结果存放在缓冲区中，今后对同样的select语句（区分大小写），将直接从缓冲区中读取结果。
一个SQL查询如果以select开头，那么MySQL服务器将尝试对其使用查询缓存。
注：两个SQL语句，只要相差哪怕是一个字符（例如 大小写不一样：多一个空格等），那么两个SQL将使用不同的cache
通过 show ststus like ‘Qcache%' 可以知道query_cache_size的设置是否合理
Qcache_free_blocks：缓存中相邻内存块的个数。如果该值显示过大，则说明Query Cache中的内存碎片较多了。
注：当一个表被更新后，和他相关的cache block将被free。但是这个block依然可能存在队列中，除非是在队列的尾部。可以用 flush query cache语句来清空free blocks。
Qcache_free_memory:Query Cache 中目前剩余的内存大小。通过这个参数我们可以较为准确的观察当前系统中的Query Cache内存大小是否足够，是需要增多还是过多了。
Qcache_hits：表示有多少次命中缓存。我们主要可以通过该值来验证我们的查询能缓存的效果。数字越大缓存效果越理想。
Qcache_inserts：表示多少次未命中而插入，意思是新来的SQL请求在缓存中未找到，不得不执行查询处理，执行查询处理后把结果insert带查询缓存中。这样的情况次数越多，表示查询缓存 应用到的比较少，效果也就不理想。
Qcache_lowmen_prunes：多少条Query因为内存不足而被清除出Query Cache，通过Qcache_lowmem_prunes和Qcache_free_memory 相互结合，能够更清楚的了解到我们系统中Query Cache的内存大小是否真的足够，是否非常频繁的出现因为内存不足而有Query被换出。这个数字最好是长时间来看，如果这个数字在不断增长，就表示可能碎片化非常严重，或者内存很少。
Qcache_queries_in_cache：当前Query Cache 中cache的Query数量
Qcache_total_blocks：当前Query Cache中block的数量

查询服务器关于query_cache的配置,各字段的解释：
query_cache_limit：超出此大小的查询将不被缓存
query_cache_min_res_unit：缓存块的最小大小，query_cache_min_res_unit的配置是一柄双刃剑，默认是 4KB ，设置值大对大数据查询有好处，但是如果你查询的都是小数据查询，就容易造成内存碎片和浪费。
query_cache_size：查询缓存大小（注：QC存储的单位最小是1024byte，所以如果你设定的一个不是1024的倍数的值。这个值会被四舍五入到最接近当前值的等于1024的倍数的值。）
query_cache_type：缓存类型，决定缓存什么样子的查询，注意这个值不能随便设置必须设置为数字，可选值以及说明如下：
0：OFF 相当于禁用了
1：ON 将缓存所有结果，除非你的select语句使用了SQL_NO_CACHE禁用了查询缓存
2：DENAND  则只缓存select语句中通过SQL_CACHE指定需要缓存的查询。
query_cache_wlock_invalidate：当有其他客户端正在对MyISAM表进行写操作时，如果查询在query cache中，是否返回cache结果还是等写操作完成在读表获取结果。
查询缓存碎片率：Qcache_free_block/Qcache_total_block*100%
如果查询缓存碎片率超过20%，可以用flush query cache整理缓存碎片，或者试试减小query_cache_min_res_unit，如果你的查询都是小数据量的话。
查询缓存利用率：（query_cache_size-Qcache_free_memory)/query_cache_size*100%
查询缓存利用率在25%以下的话说明query_cache_size设置过大，可以适当减小：查询缓存利用率在80%以上而且Qcache_lowmem_prunes>50的话说明query_cache_size可能有点小，要不就是碎片太多
查询缓存命中率：Qcache_hits/(Qcache_hits+Qcache_inserts)*100%
Query Cache的限制
a）所有子查询中的外部查询SQL 不能被Cache：
b）在p'rocedure，function以及trigger中的Query不能被Cache
c）包含其他很多每次执行可能得到不一样的结果的函数的Query不能被Cache

3.Innodb的几个变量

innodb_buffer_pool_size
对于innodb表来说，innodb_buffer_pool_size的作用相当于key_buffer_size对于MyISAM表的作用一样。Innodb使用该参数指定大小的内存来缓冲数据和索引。最大可以把该值设置成物理内存的80%。
innodb_flush_log_at_trx_commit
主要控制了innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，取值分别为0，1，2.
实际测试发现，该值对插入数据的速度影响非常大，设置为2时插入10000条记录只需要两秒，设置为0时只需要一秒，设置为1时，则需要229秒。因此，MySQL手册也建议尽量将插入操作合并成一个事务，这样可以大幅度提高速度。
innodb_thread_concurrency=0
此参数用来设置innodb线程的并发数，默认值为0表示不被限制，若要设置则与服务器的CPU核心数相同或是CPU的核心数的2倍。
innodb_log_buffer_size
此参数确定日志文件所用的内存大小，以M为单位。缓冲区更大能提高性能，对于较大的事务，可以增大缓存大小。
innodb_log_file_size=50M
此参数确定数据日志文件的大小，以M为单位，更大的设置可以提高性能。
innodb_log_files_in_group=3
为提高性能，MySQL可以以循环方式将日志文件写到多个文件。推荐设置为3
read_buffer_size=1M
MySQL 读入缓冲区大小。对表进行顺序扫描的请求将分配到一个读入缓冲区MySQL会为他分配一段内存缓冲区
read_rnd_buffer_size=16M
MySQL 的随机读（查询操作）缓冲区大小。当按任意顺序读取行时（例如，按照排序顺序），将分配到一个随机都缓冲区。进行排序查询时，MySQL会首先扫描一遍该缓冲区，以避免磁盘搜索，提高查询速度，如果需要排序大量数据，可适当调高该值。但是MySQL会为每个客户连接发放该缓冲空间，所以应尽量适当设置该值，以避免内存消耗过大。
注：顺序读是根据索引的叶节点数据就能顺序的读取所需要的行数据。随机读是指一般需要根据辅助索引叶节点中的主键寻找侍其巷进行数据，而辅助索引和主键所在的数据端不同，因此访问方式是随机的。
bulk_insert_buffer_size=64M
批量插入数据缓存大小，可以有效的提高插入效率，默认为8M
expire_logs_days=7  
定义了mysql清除过期日志的时间


请勿发布不友善或者负能量的内容。与人为善，比聪明更重要！

该博客于2020-12-7日，后端基于go语言的beego框架开发
前端页面使用Bootstrap可视化布局系统自动生成
是我仿的原来我的TP5框架写的博客，比较粗糙，底下是入口
侯体宗的博客

      订阅博客周刊

文章标签

友情链接

HouTiZong
侯体宗的博客
© 2020 zongscan.com
版权所有ICP证 : 粤ICP备20027696号
PHP交流群
侯体宗的博客