这篇文章将为大家详细讲解有关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=512Mkey_buffer_size的值越大效率越高,建议至少将1/4可用内存分配给key_buffer_sizeshow 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=521Minnodb_log_buffer_size决定了innodb重做日志缓存的大小,对于可能产生大量更新记录的大事务,增加innodb_log_buffer_size大小,可以避免innodb在事务提交前执行不必要的日志写入磁盘ioinnodb_log_buffer_size=10Mshow variables like 'innodb_buffer_pool_size';innodb_flush_log_at_trx_commit控制innodb将log buffer中的数据写入日志文件并flush磁盘的时间点,值分别为,0,1,2innodb_thread_concurrency设置innodb线程的并发数,默认为0表示不受限制,如果要设置建议跟服务器的CPU核心数一致或者CPU核心数的两倍innodb_log_buffer_size执行日志文件所用的内存大小,以M为单位innodb_log_files_in_group以循环的方式将日志文件写到多个文件中read_buffer_sizemysql读入缓冲区大小,对表进行顺序扫描的请求将分配到一个读入缓冲区read_rnd_buffer_sizemysql随机读的缓冲区大小
连接优化
max_connection
最大连接数,默认是151,linux一般可以支持500--1000具体根据服务器性能评估show variables like 'max_connections';查看最大连接数
back_log
超过最大连接数后最大的等待数量,存放在堆栈中(一般是50+(max_connections/5)),最大不超过900show 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.logmysqldumpslow slow_query.log;查看慢查询日志文件
slow_query_log=1
是否开启慢查询日志,1代表开启,0代表关闭
long_query_time=3
设置慢查询的时间,超过这个时间的查询语句才会记录日志
log_slow_admin_statements
是否将管理语句写入慢查询日志
关于“Mysql中并发参数调整的示例分析”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,使各位可以学到更多知识,如果觉得文章不错,请把它分享出去让更多的人看到。