Mysql的并发参数调整详解

目录

• 查询缓存优化
• 概述
• 查询流程
• 查询缓存配置
• 查询缓存失效的情况
• 内存管理优化
• 内存优化原则
• MyISAM内存优化
• InnoDB内存优化
• 连接优化
• max_connection
• back_log
• table_open_cache
• thread_cache_size
• innodb_lock_wait_timeout
• 日志
• log_bin
• binlog_do_db binlog_ignore_db
• sync_binlog
• general_log=1
• general_log_filefile_name(默认host_name.log)
• slow_query_log=1
• long_query_time=3
• log_slow_admin_statements

查询缓存优化

不建议使用，一般是在应用服务做缓存层，毕竟数据并发能力是有限的

概述

当开始Mysql的查询缓存，执行相同的sql语句的时候，服务器就会直接从缓存中读取结果，当数据被修改，之前的缓存会失效，修改比较频繁的表不适合做查询缓存

查询流程

1、客户端发送一条查询给服务器
2、服务器先会检查查询缓存，如果命中了缓存，则立即返回存储在缓存中的结果，否则进入下一阶段
3、服务器端进行sql解析、预处理、再由优化器生成对应的执行计划
4、根据生成的执行计划，调用存储引擎的Api来执行查询
5、将结果返回给客户端--并将查询到的结果放到查询缓存中

查询缓存配置

查询数据库是否支持查询缓存(YES or ON)
	show variables like 'have_query_cache';
查看数据库是否开启了查询缓存
	show variables like 'query_cache_type';
	OFF 或 0 查询缓存功能关闭
	ON 或 1查询缓存功能打开，select的结果符合缓存条件即会缓存，否则不予缓存，显示指定SQL_NO_CACHE,不予缓存
	DEMAND 或 2 查询缓存功能按需进行，显示指定SQL_CACHE的SELECT语句才会缓存；其他不予缓存
查看查询缓存的大小
	show variables like 'query_cache_size';
查看缓存的状态
	show status like 'Qcache%';
		Qcache_free_blocks 缓存中可用的内存块数量
		Qcache_free_memory 缓存中可用的内存量
		Qcache_hits 查询缓存命中数
		Qcache_lowmen_prunes 由于内存不足而从查询缓存中删除的查询数
		Qcache_not_cached 非缓存查询的数量（query_cache_type设置而无法缓存）
		Qcache_queries_in_cache 查询缓存中注册的查询数
		Qcache_total_blocks 查询缓存中的块总数
开启查询缓存
	找到mysql的配置文件添加 my.cnf 添加query_cache_type=1
	开启查询缓存结果是
		query_cache_type=DEMAND
		select SQL_CACHE id from table
	开启查询缓存结果是
		query_cache_type=ON
		select SQL_NO_CACHE id from table

查询缓存失效的情况

sql语句不一致情况
查询sql中有不确定信息：
	如 now(),current_date(),curdate(),curtime(),rand(),uuid(),database()
不使用任何表查询语句
查询 mysql ，infomation_schema或 performance_schema数据库中的表时，不会走查询缓存
在存储函数，触发器或事件的主体内执行的查询
表修改会删除查询缓存，如 insert，update，delete，drop...

内存管理优化

内存优化原则

将尽量多的内存分配给Mysql做缓存，要给操作系统和其他程序预留足够内存
MyISAM存储引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存
排序区，连接区等缓存是分配给每个数据库会话（session）专用的，其默认的设置要根据最大的连接数合理分配，如果设置太大，不但浪费资源，而且在并发连接较高时会导致无力内存耗尽

MyISAM内存优化

myisam存储引擎使用key_buffer缓存索引块，加速myisam索引的读写速度。对于myisam表的数据块，mysql没有特别的缓存机制，完全依赖于操作系统的io缓存
key_buffer_size=512M
	key_buffer_size的值越大效率越高，建议至少将1/4可用内存分配给key_buffer_size
show variables like 'key_buffer_size'; 查询key_buffer_size的大小
red_buffer_size
	如果经常顺序扫描myisam表，可以通过增大read_buffer_size的大小值来改善性能，但不能调的过大，因为key_buffer_size是每个session独占的，如果设置多个会造成资源浪费
red_rnd_buffer_size
	经常需要排序的myisam表的查询，如果带有order by 子句的sql 适当增加red_rnd_buffer_size的值可以改善此类sql性能---也是session独享的，也不能设置过大

InnoDB内存优化

InnoDB用一块内存区做io缓存池，该缓存池不仅用来缓存innoDB的索引块，而且也用来缓存innoDB的数据块
inodb_buffer_pool_size
	该变量决定了innoDB存储引擎和索引数据的最大缓存区大小，在保证操作系统及其他程序有足够内存可用的情况下，值越大缓存命中率越高，访问innoDb表需要的磁盘io就越少，性能也就越高
	innodb_buffer_pool_size=521M
innodb_log_buffer_size
	决定了innodb重做日志缓存的大小，对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size大小，可以避免innodb在事务提交前执行不必要的日志写入磁盘io
	innodb_log_buffer_size=10M
show variables like 'innodb_buffer_pool_size';
innodb_flush_log_at_trx_commit
	控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点，值分别为，0，1，2
innodb_thread_concurrency
	设置innodb线程的并发数，默认为0表示不受限制，如果要设置建议跟服务器的CPU核心数一致或者CPU核心数的两倍
innodb_log_buffer_size
	执行日志文件所用的内存大小，以M为单位
innodb_log_files_in_group
	以循环的方式将日志文件写到多个文件中
read_buffer_size
	mysql读入缓冲区大小，对表进行顺序扫描的请求将分配到一个读入缓冲区
read_rnd_buffer_size
	mysql随机读的缓冲区大小

连接优化

max_connection

最大连接数，默认是151，linux一般可以支持500--1000具体根据服务器性能评估
show variables like 'max_connections';
	查看最大连接数

back_log

超过最大连接数后最大的等待数量，存放在堆栈中（一般是50+（max_connections/5）），最大不超过900
show variables like 'back_log'

table_open_cache

控制所有sql语句执行线程可打开表缓存数量，而在执行sql语句时，每一个sql执行线程至少要打开1个表缓存，该参数的值应该根据设置的最大连接数max_connectons以及每个连接执行关联查询中涉及的表的最大数量来设定

thread_cache_size

为了加快连接数据库的速度，mysql会缓存一定数量的客户服务线程以备重用，通过参数thread_cache_size可控制Mysql缓存客户服务线程的数量

innodb_lock_wait_timeout

设置innoDB事务等待行锁的时间，默认是50ms，可以根据需要动态设置，对于需要快速反馈的业务系统，可以将行锁的等待时间调小，避免事务长时间挂起，对于后台运行的批量处理程序，可以将行锁的等待时间调大，以避免发生大的回滚操作
show variables like 'innodb_lock_wait_timeout';

日志

错误日志文件名称，用户记录当mysql启动和停止时，以及服务器在运行中发生发生任何验证错误时相关信息

log_bin

指定二进制日志文件名称，用于记录对数据库造成更改的所有语句

binlog_do_db binlog_ignore_db

指定将更新记录到二进制日志的数据库，其他所有没有显示指定的数据库更新将忽略，不记录在日志中

sync_binlog

指定多少次写日志后同步磁盘

general_log=1

是否开启查询日志记录

general_log_filefile_name(默认host_name.log)

指定查询日志文件名，用户记录所有的查询语句
查询日志文件名：slow_query_log_file=slow_query.log
mysqldumpslow slow_query.log;查看慢查询日志文件

slow_query_log=1

是否开启慢查询日志，1代表开启，0代表关闭

long_query_time=3

设置慢查询的时间，超过这个时间的查询语句才会记录日志

log_slow_admin_statements

是否将管理语句写入慢查询日志

到此这篇关于Mysql的并发参数调整详解的文章就介绍到这了,更多相关Mysql并发参数调整内容请搜索我们以前的文章或继续浏览下面的相关文章希望大家以后多多支持我们！