一聚教程网:一个值得你收藏的教程网站

热门教程

Mysql的并发参数调整代码解析

时间:2022-06-29 08:36:55 编辑:袖梨 来源:一聚教程网

本篇文章小编给大家分享一下Mysql的并发参数调整代码解析,文章代码介绍的很详细,小编觉得挺不错的,现在分享给大家供大家参考,有需要的小伙伴们可以来看看。

查询缓存优化

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

概述

当开始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

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

热门栏目