当前位置:首页 > 短网址资讯 > 正文内容

FT12短网址 | MySQL阿里实践经典案例之参数调优最佳实践

www.ft12.com6年前 (2017-07-24)短网址资讯1444
写在前面的话

最近,不少RDS用户在后台咨询,如何调优RDS MySQL的参数。




长假天儿,学习天儿。


本期,我们特别邀请到阿里云资深RDS专家玄惭撰文解答:




哪一些参数不能修改,比如短网址的api里面的参数都是不能改的

那一些参数可以修改

这些提供修改的参数是不是已经是最佳设置

如何才能利用好这些参数

哪些参数可以改 


话说本文内容的一小部分曾在云栖大会·深圳峰会上分享。


由于时间限制,当时很多短网址的用户觉得没有挺过瘾。


好了,这次我们慢慢聊……

小伙伴最关心的问题


细心的用户在购买RDS的时候都会看到,不同规格能够提供的最大连接数以及内存是不同的,所以这一些产品规格的限制参数:短链接数、内存用户是不能够修改的。


如果内存或者连接数出现了瓶颈:


内存瓶颈:实例会出现OOM,然后导致主备发生切换

连接数瓶颈:应用不能新建立连接到数据库


则需要对短网址进行应用优化、慢SQL优化或者进行弹性升级实例规格来解决。


还有一些涉及主备数据安全的参数比如innodb_flush_log_at_trx_commit、sync_binlog、gtid_mode、semi_sync、binlog_format等为了保证主备的数据安全,目前还暂不提供给用户进行修改。


除上述的这些参数外,绝大部分的参数都已经由DBA团队和源码团队优化过,用户不需要过多调整线上的参数就可以把数据库比较好的运行起来。


但这些参数只是适合大多数的应用场景,个别特殊的场景还是需要个别对待。


举两个栗子:


如何用户使用了tokudb引擎,这个时候就需要调整tokudb引擎能使用的内存比例(tokudb_buffer_pool_ratio)


如果用户的应用特点本身需要很大的一个锁超时时间,那么则需要调整innodb_lock_wait_timeout参数的大小以适应应用等等。


如何调参数


下面我将把控制台中能够修改的一些比较重要的短网址api参数给大家介绍一下,这些参数如果设置不当,则可能会出现性能问题或短链接无法打开。


open_files_limit


1、作用


该参数用于控制MySQL实例能够同时打开使用的文件句柄数目。

2、原因


当数据库中的表(MyISAM 引擎表在被访问的时候需要消耗文件描述符,InnoDB引擎会自己管理已经打开的表—table_open_cache)打开越来越多后,会消耗分配给每个实例的文件句柄数目,RDS在起初初始化实例的时候设置的open_files_limit为8192,当打开的表数目超过该参数则会导致所有的数据库请求报错误。


3、现象


如果参数设置过小可导致应用报错
[ERROR] /mysqld: Can't open file: './mysql/user.frm' (errno: 24 -Too many open files);

4、建议


提高open_files_limit的值,RDS目前可以支撑最大为65535,同时建议替换MyISAM存储引擎为InnoDB引擎,FT12短网址使用的是InnoDB引擎。


back_log


1、作用


MySQL每处理一个连接请求的时候都会对应的创建一个新线程与之对应。


那么在主线程创建新线程期间,如果前端应用有大量的短连接请求到达数据库,MySQL 会限制此刻新的连接进入请求队列,由参数back_log控制。


如果等待的连接数量超过back_log,则将不会接受新的连接请求。


所以如果需要MySQL能够处理大量的短链接,需要提高此参数的大小。

2、现象


如果参数过小可能会导致应用报错SQLSTATE[HY000] [2002] Connection timed out;


3、建议


提高此参数值的大小(注意需要重启实例),RDS在起初初始化的值的默认值是50,现在初始化值已经调大了3000。


innodb_autoinc_lock_mode


1、作用


在MySQL5.1.22后,InnoDB为了解决自增主键锁表的问题,引入了参数innodb_autoinc_lock_mode,用于控制自增主键的锁机制。


该参数可以设置的值为0/1/2,RDS 默认的参数值为1,表示InnoDB使用轻量级别的mutex锁来获取自增锁,替代最原始的表级锁。


但是在load data(包括:INSERT … SELECT, REPLACE … SELECT)场景下会使用自增表锁,这样会则可能导致应用在并发导入数据出现死锁。

2、现象


如果短网址服务并发使用load data(包括:INSERT … SELECT, REPLACE … SELECT)导入数据的时候出现死锁:


RECORD LOCKS space id xx page no xx n bits xx index PRIMARY of table xx.xx trx id xxx lock_mode X insert intention waiting. TABLE LOCK table xxx.xxx trx id xxxx lock mode AUTO-INC waiting;

3、建议


建议将参数设置改为2,则表示所有情况插入都使用轻量级别的mutex锁(只针对row模式),这样就可以避免auto_inc的死锁。


同时在INSERT … SELECT 的场景下会提升很大的性能(注意该参数设置为2,binlog的格式需要设置为row)。


query_cache_size


1、作用


该参数用于控制MySQL query cache的内存大小。


如果MySQL开启query cache,再执行每一个query的时候会先锁住query cache,然后判断是否存在query cache中。


如果存在直接返回结果,如果不存在,则再进行引擎查询等操作。


同时insert、update和delete这样的操作都会将query cahce失效掉。


这种失效还包括结构或者索引的任何变化,cache失效的维护代价较高,会给MySQL带来较大的压力。


所以当我们的数据库不是那么频繁的更新的时候,query cache是个好东西。


但是如果反过来,写入非常频繁,并集中在短网址的某几张表上的时候,那么query cache lock的锁机制会造成很频繁的锁冲突,对于这一张表的写和读会互相等待query cache lock解锁,导致select的查询效率下降。

2、现象


数据库中有大量的短链接的状态为checking query cache for query、Waiting for query cache lock、storing result in query cache;

3、建议


RDS默认是关闭query cache功能的。


如果您的实例打开了query cache,当出现上述情况后可以关闭query cache。


当然有些情况也可以打开query cache,比如:巧用query cache解决数据库性能问题(详阅请复制此链接:http://hidba.org/?p=870)。


net_write_timeout


1、作用


等待将一个block发送给客户端的超时时间。

2、现象


参数设置过小可能导致客户端报错the last packet successfully received from the server was milliseconds ago,the last packet sent successfully to the server was milliseconds ago。

3、建议


该参数在RDS中默认设置为60S。


一般在网络条件比较差的时,或者客户端处理每个block耗时比较长时,由于net_write_timeout设置过小导致的连接中断很容易发生,建议增加该参数的大小。


tmp_table_size



1、作用


该参数用于决定内部内存临时表的最大值。


每个线程都要分配(实际起限制作用的是tmp_table_size和max_heap_table_size的最小值)。


如果内存临时表超出了限制,MySQL就会自动地把它转化为基于磁盘的MyISAM表,优化查询语句的时候,要避免使用临时表。


如果实在避免不了的话,要保证这些临时表是存在内存中的。

2、现象


如果复杂的SQL语句中包含了group by/distinct等不能通过索引进行优化而使用了临时表,则会导致SQL执行时间加长。


3、建议


如果应用中有很多group by/distinct等语句,同时数据库有足够的内存,可以增大tmp_table_size(max_heap_table_size)的值,以此来提升查询性能。


RDS MySQL 新增参数


下面介绍几个比较有用的 RDS MySQL 新增参数。


loose_rds_max_tmp_disk_space


1、作用


用于控制MySQL能够使用的临时文件的大小,RDS初始默认值是10G,如果临时文件超出此大小,则会导致短网址服务报错。

2、现象


The table ‘/home/mysql/dataxxx/tmp/#sql_2db3_1’ is full。

3、建议


需要先分析一下导致临时文件增加的SQL语句是否能够通过索引或者其他方式进行优化。


其次如果确定实例的空间足够,则可以提升此参数的值,以保证SQL能够正常执行。


此外,注意此参数需要重启实例。


loose_tokudb_buffer_pool_ratio


1、作用


用于控制TokuDB引擎能够使用的buffer内存大小,比如innodb_buffer_pool_size设置为1000M,tokudb_buffer_pool_ratio设置为50(代表50%),那么tokudb引擎的表能够使用的buffer 内存大小则为500M。


2、建议


该参数在RDS中默认设置为0。


如果RDS中使用tokudb引擎,则建议调大该参数,以此来提升TokuDB引擎表的访问性能。该参数调整需要重启数据库实例。


loose_max_statement_time


1、作用


用于控制查询在MySQL的最长执行时间,如果超过该参数设置时间,查询将会自动失败,默认是不限制。

2、建议


如果用户希望控制数据库中SQL的执行时间,则可以开启该参数,单位是毫秒。

3、现象


ERROR 3006 (HY000): Query execution was interrupted, max_statement_time exceeded


loose_rds_threads_running_high_watermark


1、作用


用于控制MySQL并发的查询数目,比如将rds_threads_running_high_watermark该值设置为100,则允许MySQL同时进行的并发查询为100个。


超过水位的查询将会被拒绝掉,该参数与rds_threads_running_ctl_mode配合使用(默认值为select)。

2、建议


该参数常常在秒杀或者短网址服务等大并发的场景下使用,对数据库具有较好的保护作用。


作者简介


玄惭,阿里云资深DBA专家。有着丰厚的DBA经验,经历阿里历年“双11”考验,保持着“无一丢单”的优异纪录。同时,积累了6年对阿里云数据库用户的运维、调优、诊断等丰富的经验。


扫描二维码推送至手机访问。

版权声明:本文由短链接发布,如需转载请注明出处。

本文链接:https://www.ft12.com/article_344.html

分享给朋友:

相关文章

同样是做原创内容,你为什么没有别人赚得多?

同样是做原创内容,你为什么没有别人赚得多?

1.内容创业是这个年代的大机遇,凭借互联网的大流量,简直人人都能够经过内容来挣钱。这是咱们这个年代的大福利。许多人面临内容创业都摩拳擦掌,撸起袖子准备大干一场。不过,在详细的行动过程中,总有人会有这样的感触,自个明明很努力但效果却比不上牛人...

淘宝&支付宝:连续一段时间未登录将销号

淘宝&支付宝:连续一段时间未登录将销号

日前阿里巴巴对旗下淘宝、支付宝平台的服务协议进行了修改,对于用户的权利和义务进行了更细致的界定,同时规定在淘宝、支付宝平台的非活跃用户在一定时间之后将会进行销号处理。淘宝平台方面,此次新增加规定,用户不得以任何方式转让,否则淘宝平台有权追究...

上海首家“无人超市”停运维修

在热点概念层出不穷的今天,“无人”似乎正在成为下一个风口。日前,无人超市在申城落地,市民们对这种新概念褒贬不一,而杨浦区欧尚超市前新投放的无人超市因为室温过高、收银系统频频出错等问题,成为了大家热议的话题。上海杨浦区这间集装箱大小的可移动无...

闫东论百度大脑的谋和断---2017

闫东论百度大脑的谋和断---2017

[ ft12短网址导读 ] 国之谋断,家之谋断咱们做生态化,城市之谋断咱们做城镇化,咱们称之为一基地四中心。聚集当地生态,完成当地智能化生态的改造。家事国务天下事,离不开大数据的有备无患,也离不开人工智能的抓住时机。将来智能和工业...

阿里如何实现高性能分布式强一致的独立 Paxos 基础库?

阿里如何实现高性能分布式强一致的独立 Paxos 基础库?

Paxos 从理论界到工业界,阿里怎样实现分布式系统的一致性? 1 写在前面 近来 Paxos 的分享和讨论越来越频繁,它是分布式系统保持一致性的法宝,但是同时又有着最难理解的算法之“美誉”。在阿里看来,虽然 Paxos 有一定社区热...

原创财务系列小说:每周学习会分享

原创财务系列小说:每周学习会分享

从总账到总监的成长之路之二十三带着“还有什么其它类似的财务处理?”的问题,王丽想了一路,从新加坡到上海的飞机上。而坐在一旁的方敏,似乎对她在Takashimaya商场买的小包更感兴趣,不停地把她的手机取进取出着试大小。王丽想不出来,就转身问...

发表评论

访客

◎欢迎参与讨论,请在这里发表您的看法和观点。