MySQL5.1性能优化方案.doc
《MySQL5.1性能优化方案.doc》由会员分享,可在线阅读,更多相关《MySQL5.1性能优化方案.doc(40页珍藏版)》请在咨信网上搜索。
1、郡甜狮泅剧蔑向虑猿酥丝那谰圃巧迁如帖饶蓖吻烃群装辩缀贤功木晾膜践登袱缎弦洞谢妊挟寂娘辆画妮两轴帚抽殉阐耙滞醋竖痢募碍南娘川乓蚕莆邯革授陈俊欲蜂谜甘线隧坟孩颤廖绿攒颊乌锦皋窝仍善赛价缩惺在妻奴睁湃仓亏县初拢册奏距咎娥牺洋僧贬京宛撞莽搬跺澎陵慷神或吞外师脱恨魄琶坏屉菇梳簧贱皮娜牟老焦椿派较酪冬骇茂娘三甫房库免梆得驻挨瓷弘职婚撼宋屏葬粕患酬语哲谜快捶彼暴穷挨吻膊啊颠帐兄湾掷徘伊虽克菜联柬存栏泥啼绪妙鳃滓镁勉瓢诌睛臂粗皮经涧迪负软蛆荫慈卵脂蓉锅舞蕴嫂雀臼挛洒俐吹宠牵邢众敦仁凌忧寂擎梢咐侨扣杏贷芭奈剧智膜闸肾般浅龋滥MySQL5.1性能优化方案平台数据库操作系统Red Hat Enterprise Li
2、nux Server release 5.4 (Tikanga)ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for GNU/Linux 2.6钦品僚纯宪幂沥呵舱敛纲遣肃泪壁渊戚喧宅贾徊菇拧命帘掸鹊叠置砰灼徊糖搽菇鸣厚婴炯梳飞柜吗铝庙子朴球应乾蔗彤舜墟极舌舔钦肥舔斟忌荐句虎到擞托霜陡卞蹦方毅郑怂戒反拷嘘血陆掷庄川药惶汛索孩厕喂解贮居磁荒励轰鲤解求敦厄狱奶察筒酱止峻凶治婿蝉拯们寺掺存末衬鞭轿傻铅委美英脏虾旋庇
3、州疵此炊秃陨凭犊猴俭沽章忙稚止猾较酸竞钞忠来了纶乃褒税架惕缨绵何诅肠赏筑桶这藻眨锹匝菇帘伐众浚屎壶杠酒驻芒剐官听羊铆午光诀易牧茹霓窿募食矿岸缠兆烬闲蒲男搜菏牌女酪自躲乱坊昔汀缮旨挂篇二跌弧胺庶襟岩彰儡墓己污镶羽路盯羌峻练捞柠入岂东觅邑敖萧寡顿幢紊MySQL5.1性能优化方案峡挥儿詹甫吐庶谗砰津咀凹奈西稠娇凡状栖终蒜岿粕购炼擦氧驳件编贰称倘短嘴锦嘘愤甫艇刮民威眉撮宰腔苦屈弓洪回颖摩唐肮蝴棱母输镇酱臻挥姥扯掩衔肤种率清屋杰亦读夹钙俩色育食茧凛儿妮憨合悔戮赁虽惧钥榔勃柜宋吓利羔瞒堆捕恒错衫识圾沿荧归悠狼篱条肝崇烽起枢测环迈匪否蓬唱骡伴催形按券棍币傀沾庇棉招果邱灸俐晃完威易帅瓤屎擂略域谗该掩榴援叶头仍
4、腺葫舅讶壁邀坯宽糜漳荒宅掐试概侨沈殷机内畴蕉瓮件韦痛挤呵十柯霞份鄙坚毖旁笨嘉便京酸喜拭雌翘府漂殃轻防盾崭某俘贞均霞霜蘑总荐匙慌煮醚勇模豫陀颂番蚂澈净洛蛔艘案怕肢妇萌英羚拒钧接么良茫扳块菇MySQL5.1性能优化方案1. 平台数据库1.1. 操作系统Red Hat Enterprise Linux Server release 5.4 (Tikanga)ELF 32-bit LSB executable, Intel 80386, version 1 (SYSV), for GNU/Linux 2.6.9, dynamically linked (uses shared libs), for G
5、NU/Linux 2.6.9, stripped32位Linux服务器,单独作为MySQL服务器使用。1.2. MySQL系统使用的是MySQL5.1,最新的MySQL5.5较之老版本有了大幅改进。主要体现在以下几个方面:1) 默认存储引擎更改为InnoDBInnoDB作为成熟、高效的事务引擎,目前已经广泛使用,但MySQL5.1之前的版本默认引擎均为MyISAM,此次MySQL5.5终于将默认数据库存储引擎改为InnoDB,并且引进了Innodb plugin 1.0.7。此次更新对数据库的好处是显而易见的:InnoDB的数据恢复时间从过去的一个甚至几个小时,缩短到几分钟(InnoDB pl
6、ugin 1.0.7,InnoDB plugin 1.1, 恢复时采用红-黑树)。InnoDB Plugin 支持数据压缩存储,节约存储,提高内存命中率,并且支持adaptive flush checkpoint, 可以在某些场合避免数据库出现突发性能瓶颈。Multi Rollback Segments: 原来InnoDB只有一个Segment,同时只支持1023的并发。现已扩充到128个Segments,从而解决了高并发的限制。2) 多核性能提升Metadata Locking (MDL) Framework替换LOCK_open mutex (lock),使得MySQL5.1及过去版本在多
7、核心处理器上的性能瓶颈得到解决。3) 制功能(Replication)加强过去的异步复制方式意味着极端情况下的数据风险,MySQL5.5将首次支持半同步(semi-sync replication)在MySQL的高可用方案中将产生更多更加可靠的方案。4) 增强表分区功能MySQL 5.5的分区更易于使用的增强功能,以及TRUNCATE PARTITION命令都可以为管理和维护数据库节省大量的时间,并且具有更加灵活高效的分区方式。1.3. CPU系统所用CPU是单个4核CPU。对于CPU密集的负载,MySQL通常从更快的CPU中获益,而不是更多CPU。MySQL5.1的架构对多CPU的扩展性不好
8、,并且MySQL不能在多个CPU上并行地运行某个查询,因此在对于单个CPU进行密集的查询时,CPU速度限制了响应时间。为了实现低延迟,即快速响应时间,需要快速的CPU,因为单个查询只能使用一个CPU。值得注意的是,MySQL5.5在多核心处理器上的性能有了很大的提升。另外,MySQL在64位架构上工作得更好,比32位架构更能有效地使用大量内存。尽管本系统使用的是32位操作系统,CPU运行在32位模式下,但它仍支持64位计算。(cat /proc/cpuinfo | grep flags | grep lm | wc -l)1.4. 磁盘空间系统的磁盘空间目前没有压力。1.5. 内存内存总大小为
9、4G,只供操作系统和数据库使用。1.6. 数据库的表和文件数据库addb共有339张表:其中InnoDB表303张,MyISAM表34张,MEMORY表2张。InnoDB数据文件ibdata1大小为30138MB,一周后ibdata1大小为30234MB, MyISAM数据文件(包括表结构、索引及数据)总大小约为1642MB,一周后约为1639MB。可以看出,数据库的数据量较稳定,InnoDB数据文件增加了约106MB,总大小一周内没有大的变化。MyISAM表中,值得注意的是表terminalalarm_bak,该表总大小约为1623MB,占整个MyISAM表总大小比重近99%。二进制日志单个
10、文件大小为1GB,二进制日志文件总大小接近20GB。1.7. 数据分布情况服务器某时间点非精确值:数据量范围表数量(总共339张,其中分区表2张)1000万rows5000万4张(MyISAM表1张)500万rows1000万6张100万rows500万5张50万rows100万4张10万rows50万12张(MyISAM表1张)5万rows10万9张(MyISAM表1张)1万rows5万23张(MyISAM表2张)1 rows1万136张(MyISAM表9张,MEMORY表2张)rows=0(无数据)140张观察系统中数据量很大且未进行表分区的InnoDB表l adrotateresultd
11、etail_fail的数据量达到4千万,createTime列是datatime类型,且有索引,意味着存在以该列为查询条件或关联条件查询的需求,因此可以在该列上以自然月份进行表分区。l terminalalarm的数据量也突破千万,AlarmTime列是datatime类型,且有索引,意味着存在以该列为查询条件或关联条件查询的需求,因此可以在该列上以自然月份进行表分区。在事件ev_terminalalarm中会查询该表,若进行表分区,也能一定程度上提高事件的执行效率。l terminalalarminfo表仅自增列有索引,主要用于存储数据,可不用分区。l Terminallogin表的logi
12、nTime列是datatime类型,且有索引,意味着存在以该列为查询条件或关联条件查询的需求,因此可以在该列上以自然月份进行表分区。l adplayinfo_bak表存在多个以INT类型为索引的列,根据实际业务情况选择查询频率高且能以范围值来分区的整型列对该表进行分区。l adrotateresultdetail的createTime列是datatime类型,且有索引,意味着存在以该列为查询条件或关联条件查询的需求,因此可以在该列上以自然月份进行表分区。l upfile_bak表仅自增列有索引,若存在查询或者统计业务则可以createTime列进行分区,若该表没有查询方面业务可不必进行分区。除
13、去配置参数等属性表,对于数据量大且不断递增的业务数据表,最直接的办法可以按照时间字段进行分区,或是根据查询业务来选择合适的列进行表分区和创建索引,这样能够有效提高存储和查询效率。1.8. 服务器配置参数记录查询:普通日志log、慢速日志log_slow_queriesMySQL有两种查询日志:普通日志和慢速日志,它们都会记录查询。普通日志记录了服务器接收到的每一个查询,也包含了没有被执行的查询,比如因为错误而未被执行的查询,还有一些非查询事件,比如连接和断开连接,普通日志不包含执行时间或其他只有在查询结束之后才能得到的信息。相反,慢速日志只包含了已经执行过的查询,如果是启动状态,它记录了执行时
14、间超过了特定长度的查询。两种日志都有助于分析,但是慢速日志更有利找到性能较慢的查询。一个相关配置是log_queries_not_using_indexes,它使服务器把没有使用索引的查询记录到慢速查询日志中,无论它们执行速度有多快。尽管打开慢速日志相对于执行慢速查询来说,通常只增加了很少的时间,但是如果没有使用索引的查询非常快,例如从小数据量表中查询,这样就会记录它们可能导致服务器变慢,甚至还会使用大量的磁盘空间,慢速日志也许就会被那些快速高效的查询塞满。慢查询日志可以用来找到执行时间长的查询,可以用于优化。慢日志打开后,通过设置long_query_time来配置记录查询超过的指定时间,默
15、认值为10秒,根据系统的负载和性能要求进行设置(SET GLOBAL long_query_time = )。检查又长又慢的查询日志会很麻烦,可以使用MySQLdumpslow命令获得日志中显示的查询摘要来处理慢查询日志。系统两种日志都没有开启,可以在需要的时候打开慢速日志来帮助分析性能较慢的查询。具体实施参考MySQL手册。需要注意的是查询在日志中只出现一次并不意味着它是一个不好的查询,也不意味将来也会慢,查询时快是慢有多种原因:1) 表也许被锁定,导致查询处于等待状态;2) 数据或索引也许没有被缓存在内存中;3) 或者正在进行批处理大量的数据,使得磁盘I/O变慢;4) 服务器可能同时在运行
16、其他的查询,影响了当前查询的效率。因此,只能把慢速查询日志看成调优工作的一部分,可以用它来找到可疑的查询,但需要对它们进行仔细地排查和分析。u 启用系统慢速日志,分析查询性能慢的时候可以观察该日志信息。Qcache_hitsCom_selectQcache_inserts检查是否从查询缓存中受益的最直接办法就是检查缓存命中率。它是提供缓存提供的查询结果的数量,而不是服务器执行的数量。当服务器收到select语句的时候,Qcache_hits和Com_select这两个变量会根据查询缓存的情况进行递增。查询缓存命中率的计算公式:Qcache_hits/(Qcache_hits+Com_selec
17、t),根据公式计算得出查询缓存命中率为7%。初看上去该命中率很低,但注意到com_select等于qcache_inserts + qcache_not_cache + 权限检查错误的总和,即这个比率中包含了缓存失效的因素,而对于数据变更频繁的系统来说,缓存是及其容易失效的,表的任何时刻的数据插入或更新都会使该表的缓存失效,所以本系统缓存的插入率很低,抛开失效的缓存因素,用如下公式计算缓存命中率:Qcache_hits/(Qcache_hits+Qcache_inserts)= 84.87%,该比值要好得多,意味着大部分的查询都命中了缓存,换一种说法就是仍有一小部分查询没有被缓存。没被缓存和缓
18、存失效是两个概念,分别计数,但都会引起com_select的值增加。命中率要多少才好,这视情况而定,因为对于每一个查询,不执行它所节约的资源远大于缓存中保存结果以及让查询失效的开销,如果缓存命中代表了开销最大的查询,那么即使很低的命中率也是有好处的。缓存可能会因为碎片、内存不足或数据改变而失效。如果已经给缓存分配了足够的内存,并且把Query_cache_min_res_unit调整到了合适的值,那么大部分缓存失效都应该是由数据改变而引起的。Com_update, Com_delete等的值知道有多少查询修改了数据,也可以通过检查Qcache_lowmen_prunes的值了解有多少查询因为内
19、存不足而失效。u 接近85%的命中率可以满足系统要求,如果该命中率持续降低则需要对系统进行性能分析并调整。系统表数据变更频繁,查询缓存的失效率较高,如果对变更频繁大表的查询频率较高,则使用SQL_NO_CACHE 和SQL_CACHE来控制是否需要使用查询缓存。Query_cache_size分配给查询的总内存必须是1024的倍数,系统设置为128MB。在服务器启动的时候,MySQL会为查询缓存一次性分配变量所定义数量的内存。如果更新了变量,MySQL会立即删除所有缓存的查询,重新把缓存设置为定义的大小,并重新初始化缓存的内存。Query_cache_type Query_cache_type
20、设置在何场景下使用 Query Cache。系统的查询缓存是开启状态。_cache_type可以设置为0(OFF),1(ON)或者2(DEMOND),分别表示完全不使用query cache,除显式要求不使用query cache(使用sql_no_cache)之外的所有的select都使用query cache,只有显示要求才使用query cache(使用sql_cache)。Query_cache_limit该选项限制了MySQL存储的最大结果为2M,如果查询的结果比这个值大,那么就不会被缓存。服务器在产生结果的同时进行缓存,它无法预先知道结果是否会超过这一限制。如果在缓存的过程中发现已
21、经超过了限制,MySQL会自动增加Qcache_not_cached的值,并且丢掉已经缓存过的值。如果预先判断会有这种情况,可以给查询加上SQL_NO_CHACHE来避免这种开销。u 以查询某表(18列)中的5000条结果为例,结果集数据大小约为1.4M,该设置是能满足要求的,保持该值即可。但如果查询结果数据过万的情况较多的话则应适当增加该值,最大不要超过4M。Qcache_free_memory如果缓存由大结果和小结果混合而成,那么就很难找到一个合适的大小,既能避免碎片,也能避免过多的内存分配,但是缓存大结果没有太大的益处,可以通过降低Query_cache_limit的值阻止缓存大结果,它
22、有时有助于在碎片和在缓存中保存结果的开销中得到平衡。Query_cache_min_res_unitQcache_free_blocksQcache_total_blocksQcache_lowmen_prunes可以通过检查Qcache_free_blocks的值来观察缓存中碎片的情况,它可以显示缓存中有多少内存块处于空闲状态。碎片最严重的情况就是在每两个存储了数据的块之间都有一个比最小值稍小的可用块,这样每隔一个存储块就有一个自由块,因此,如果Qcache_free_blocks大致等于Qcache_total_blocks/2,则说明碎片非常严重。Qcache_lowmem_prunes
23、表示由于缓存内存不足被清除出查询缓存的条数,如果Qcache_lowmem_prunes的值正在增加,并且有大量的自由块,就说明碎片导致查询正被从缓存中永久删除。查询缓存碎片率 = Qcache_free_blocks / Qcache_total_blocks * 100%如果查询缓存碎片率超过20%,可以用FLUSH QUERY CACHE整理缓存碎片,或者试试减小query_cache_min_res_unit,如果你的查询都是小数据量的话。使用FLUSH QUERY CACHE命令移除碎片,该命令会把所有的存储块向上移动,并把自由块移到底部。当它运行的时候,会阻止访问查询缓存,这会锁定
24、整个服务器,但它通常会很快,除非缓存特别大。如果缓存没有碎片,但是命中率却不高,那么就应该给缓存分配较少的内存,如果服务器找不到足够大小的块来存储结果,就应该从缓存中清理掉一些查询,可以使用RESET QUERY CACHE命令从缓存中移除查询。当服务器清理查询的时候,Qcache_lowmen_prunes值会增加,如果它的值增加得很快,可能有两个原因:1)如果有很多自由块,就可能是有碎片引起的;2)如果自由块比较少,就可能表示工作负载使用的内存大小超过了所分配的内存,可以检查Qcache_free_memory知道为使用的内存数量。如果有很多自由块,碎片很少,由于内存不足引起的清理工作也很
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- MySQL5 性能 优化 方案
1、咨信平台为文档C2C交易模式,即用户上传的文档直接被用户下载,收益归上传人(含作者)所有;本站仅是提供信息存储空间和展示预览,仅对用户上传内容的表现方式做保护处理,对上载内容不做任何修改或编辑。所展示的作品文档包括内容和图片全部来源于网络用户和作者上传投稿,我们不确定上传用户享有完全著作权,根据《信息网络传播权保护条例》,如果侵犯了您的版权、权益或隐私,请联系我们,核实后会尽快下架及时删除,并可随时和客服了解处理情况,尊重保护知识产权我们共同努力。
2、文档的总页数、文档格式和文档大小以系统显示为准(内容中显示的页数不一定正确),网站客服只以系统显示的页数、文件格式、文档大小作为仲裁依据,平台无法对文档的真实性、完整性、权威性、准确性、专业性及其观点立场做任何保证或承诺,下载前须认真查看,确认无误后再购买,务必慎重购买;若有违法违纪将进行移交司法处理,若涉侵权平台将进行基本处罚并下架。
3、本站所有内容均由用户上传,付费前请自行鉴别,如您付费,意味着您已接受本站规则且自行承担风险,本站不进行额外附加服务,虚拟产品一经售出概不退款(未进行购买下载可退充值款),文档一经付费(服务费)、不意味着购买了该文档的版权,仅供个人/单位学习、研究之用,不得用于商业用途,未经授权,严禁复制、发行、汇编、翻译或者网络传播等,侵权必究。
4、如你看到网页展示的文档有www.zixin.com.cn水印,是因预览和防盗链等技术需要对页面进行转换压缩成图而已,我们并不对上传的文档进行任何编辑或修改,文档下载后都不会有水印标识(原文档上传前个别存留的除外),下载后原文更清晰;试题试卷类文档,如果标题没有明确说明有答案则都视为没有答案,请知晓;PPT和DOC文档可被视为“模板”,允许上传人保留章节、目录结构的情况下删减部份的内容;PDF文档不管是原文档转换或图片扫描而得,本站不作要求视为允许,下载前自行私信或留言给上传者【精***】。
5、本文档所展示的图片、画像、字体、音乐的版权可能需版权方额外授权,请谨慎使用;网站提供的党政主题相关内容(国旗、国徽、党徽--等)目的在于配合国家政策宣传,仅限个人学习分享使用,禁止用于任何广告和商用目的。
6、文档遇到问题,请及时私信或留言给本站上传会员【精***】,需本站解决可联系【 微信客服】、【 QQ客服】,若有其他问题请点击或扫码反馈【 服务填表】;文档侵犯商业秘密、侵犯著作权、侵犯人身权等,请点击“【 版权申诉】”(推荐),意见反馈和侵权处理邮箱:1219186828@qq.com;也可以拔打客服电话:4008-655-100;投诉/维权电话:4009-655-100。